Introduction

pgquarrel is a program that compares PostgreSQL database schemas (DDL).

Given two database connections, it output a file that represent the difference between schemas. It means that if you run the output file into the target database, it'll have the same schema as the source database. The main use case is to deploy database changes into testing, staging or production environment.

pgquarrel does not rely on another tool (such as pg_dump) instead it connects directly to PostgreSQL server, obtain meta data from catalog, compare objects and output the commands necessary to turn target database into source database. It also has filter options; that means, you can compare part of your objects.

It could work with different PostgreSQL versions. The generated file could not work as expected if the source PostgreSQL version is greater than target PostgreSQL version. That's because the tool could generate commands that does not exist in a prior PostgreSQL version.

pgquarrel does not manipulate data (i.e. INSERT, UPDATE, DELETE, COPY, etc).

It works with different operating systems. It was tested on Linux, FreeBSD, and Windows.

Installation

pgquarrel is distributed as a source package and can be downloaded at GitHub. The installation steps depend on your operating system.

You can also keep up with the latest fixes and features cloning the Git repository.

$ git clone https://github.com/eulerto/pgquarrel.git

UNIX based Operating Systems

Before installing pgquarrel, you should have PostgreSQL 9.0+ installed (including the header files). If PostgreSQL is not in your search path add -DCMAKE_PREFIX_PATH=/path/to/pgsql to cmake command. If you are using PostgreSQL yum repository, install postgresql13-devel and add -DCMAKE_PREFIX_PATH=/usr/pgsql-13 to cmake command. If you are using PostgreSQL apt repository, install postgresql-server-dev-13 and add -DCMAKE_PREFIX_PATH=/usr/lib/postgresql/13 to cmake command.

If you compile PostgreSQL by yourself and install it in /home/euler/pg13:

$ tar -zxf pgquarrel-0.7.0.tgz
$ cd pgquarrel-0.7.0
$ cmake -DCMAKE_INSTALL_PREFIX=$HOME/pgquarrel -DCMAKE_PREFIX_PATH=/home/euler/pg13 .
$ make
$ make install

If you are using PostgreSQL yum repository:

$ sudo yum install postgresql13-devel
$ tar -zxf pgquarrel-0.7.0.tgz
$ cd pgquarrel-0.7.0
$ cmake -DCMAKE_INSTALL_PREFIX=$HOME/pgquarrel -DCMAKE_PREFIX_PATH=/usr/pgsql-13 .
$ make
$ make install

If you are using PostgreSQL apt repository:

$ sudo apt-get install postgresql-server-dev-13
$ tar -zxf pgquarrel-0.7.0.tgz
$ cd pgquarrel-0.7.0
$ cmake -DCMAKE_INSTALL_PREFIX=$HOME/pgquarrel -DCMAKE_PREFIX_PATH=/usr/lib/postgresql/13 .
$ make
$ make install

Windows

You should have CMake 2.8.11+ installed and MS Visual Studio (tested with 2017). Open CMake Gui. If PostgreSQL is not in your path add an entry CMAKE_PREFIX_PATH (e.g. C:/Program Files/PostgreSQL/10). Change CMAKE_INSTALL_PREFIX if you want to install in another directory. Click on 'Configure' and then 'Generate'. Open MS Visual Studio project (path is specified in CMake Gui), right-click on ALL_BUILD and 'Compile'. After that right-click on INSTALL and 'Deploy'.

Features

pgquarrel does not support all of the PostgreSQL objects.

<table> <tr> <th>Object</th> <th>Support</th> <th>Comments</th> </tr> <tr> <td>ACCESS METHOD</td> <td>complete</td> <td></td> </tr> <tr> <td>AGGREGATE</td> <td>partial</td> <td></td> </tr> <tr> <td>CAST</td> <td>complete</td> <td></td> </tr> <tr> <td>COLLATION</td> <td>partial</td> <td></td> </tr> <tr> <td>COMMENT</td> <td>partial</td> <td></td> </tr> <tr> <td>CONVERSION</td> <td>partial</td> <td></td> </tr> <tr> <td>DOMAIN</td> <td>partial</td> <td></td> </tr> <tr> <td>EVENT TRIGGER</td> <td>complete</td> <td></td> </tr> <tr> <td>EXTENSION</td> <td>partial</td> <td></td> </tr> <tr> <td>FUNCTION</td> <td>partial</td> <td></td> </tr> <tr> <td>INDEX</td> <td>partial</td> <td></td> </tr> <tr> <td>LANGUAGE</td> <td>partial</td> <td></td> </tr> <tr> <td>MATERIALIZED VIEW</td> <td>partial</td> <td></td> </tr> <tr> <td>RULE</td> <td>complete</td> <td></td> </tr> <tr> <td>SCHEMA</td> <td>partial</td> <td></td> </tr> <tr> <td>SEQUENCE</td> <td>partial</td> <td></td> </tr> <tr> <td>TABLE</td> <td>partial</td> <td></td> </tr> <tr> <td>TRIGGER</td> <td>complete</td> <td></td> </tr> <tr> <td>TYPE</td> <td>partial</td> <td></td> </tr> <tr> <td>VIEW</td> <td>partial</td> <td></td> </tr> <tr> <td>GRANT</td> <td>complete</td> <td></td> </tr> <tr> <td>REVOKE</td> <td>complete</td> <td></td> </tr> <tr> <td>SECURITY LABEL</td> <td>partial</td> <td></td> </tr> <tr> <td>FOREIGN DATA WRAPPER</td> <td>complete</td> <td></td> </tr> <tr> <td>FOREIGN TABLE</td> <td>partial</td> <td></td> </tr> <tr> <td>SERVER</td> <td>complete</td> <td></td> </tr> <tr> <td>USER MAPPING</td> <td>complete</td> <td></td> </tr> <tr> <td>TEXT SEARCH CONFIGURATION</td> <td>partial</td> <td></td> </tr> <tr> <td>TEXT SEARCH DICTIONARY</td> <td>partial</td> <td></td> </tr> <tr> <td>TEXT SEARCH PARSER</td> <td>partial</td> <td></td> </tr> <tr> <td>TEXT SEARCH TEMPLATE</td> <td>partial</td> <td></td> </tr> <tr> <td>OPERATOR</td> <td>partial</td> <td></td> </tr> <tr> <td>OPERATOR CLASS</td> <td>partial</td> <td></td> </tr> <tr> <td>OPERATOR FAMILY</td> <td>partial</td> <td></td> </tr> <tr> <td>PUBLICATION</td> <td>partial</td> <td></td> </tr> <tr> <td>SUBSCRIPTION</td> <td>partial</td> <td></td> </tr> <tr> <td>POLICY</td> <td>partial</td> <td></td> </tr> <tr> <td>TRANSFORM</td> <td>complete</td> <td></td> </tr> <tr> <td>PROCEDURE</td> <td>partial</td> <td></td> </tr> <tr> <td>ALTER DEFAULT PRIVILEGES</td> <td>uncertain</td> <td></td> </tr> <tr> <td>ALTER LARGE OBJECT</td> <td>uncertain</td> <td></td> </tr> <tr> <td>STATISTICS</td> <td>complete</td> <td></td> </tr> </table>

Although pgquarrel does not support all PostgreSQL objects, it covers many of the use cases. In future releases, we expect to implement the TODO items to cover more cases. The main absences are:

inheritance;
roles.

Usage

$ pgquarrel [OPTION]...

By default, the changes will be output to stdout and only some kind of objects will be compared (those whose default is true).

The following command-line options are provided (all are optional):

config (-c): configuration file that contains source and target connection information and kind of objects that will be compared.
file (-f): send output to file, - for stdout (default: stdout).
ignore-version: ignore version check. pgquarrel uses the reserved keywords provided by the postgres version that it was compiled in. Server version greater than the compiled one could not properly quote some keywords used as identifiers.
summary (-s): print a summary of changes.
single-transaction (-t): output changes as a single transaction.
temp-directory: use this directory as a temporary area ( default: /tmp).
verbose (-v): verbose mode.
source-dbname: source database name or connection string ( keyword = value strings or URIs).
source-host: source host name.
source-port: source port.
source-username: source user name.
source-no-password: never prompt for password.
target-dbname: target database name or connection string ( keyword = value strings or URIs).
target-host: target host name.
target-port: target port.
target-username: target user name.
target-no-password: never prompt for password.
help: print help.
version: print version.
access-method: access method comparison (default: false).
aggregate: aggregate comparison (default: false).
cast: cast comparison (default: false).
collation: collation comparison (default: false).
comment: comment comparison (default: false).
conversion: conversion comparison (default: false).
domain: domain comparison (default: true).
event-trigger: event trigger comparison (default: false).
extension: extension comparison (default: false).
fdw: foreign data wrapper comparison (default: false).
foreign-table: foreign table comparison (default: false).
function: function comparison (default: true).
index: index comparison (default: true).
language: language comparison (default: false).
materialized-view: materialized view comparison (default: true).
operator: operator comparison (default: false).
policy: policy comparison (default: false).
procedure: procedure comparison (default: true).
publication: publication comparison (default: false).
owner: owner comparison (default: false).
privileges: privileges comparison (default: false).
rule: rule comparison (default: false).
schema: schema comparison (default: true).
security-labels: security labels comparison (default: false).
sequence: sequence comparison (default: true).
statistics: statistics comparison (default: false).
subscription: subscription comparison (default: false).
table: table comparison (default: true).
text-search: text search comparison (default: false).

Pgquarrel

Install / Use

README