View on GitHub

Pgquarrel

pgquarrel compares PostgreSQL database schemas (DDL)

Download this project as a .zip file Download this project as a tar.gz file

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 from database, it'll have the same schema as the to 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 from database into to database.

It could work with different PostgreSQL versions. The generated file could not work as expected if the to PostgreSQL version is greater than from 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 the cmake command.

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 postgresql12-devel and add -DCMAKE_PREFIX_PATH=/usr/pgsql-12 to cmake command. If you are using PostgreSQL apt repository, install postgresql-server-dev-12 and add -DCMAKE_PREFIX_PATH=/usr/lib/postgresql/12 to cmake command. If you compile PostgreSQL by yourself and install it in /home/euler/pg12: 

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

If you are using PostgreSQL yum repository: 


$ sudo yum install postgresql12-devel
$ tar -zxf pgquarrel-0.6.0.tgz
$ cd pgquarrel-0.6.0
$ cmake -DCMAKE_INSTALL_PREFIX=$HOME/pgquarrel -DCMAKE_PREFIX_PATH=/usr/pgsql-12 .
$ make
$ make install
If you are using PostgreSQL apt repository: 

$ sudo apt-get install postgresql-server-dev-12
$ tar -zxf pgquarrel-0.6.0.tgz
$ cd pgquarrel-0.6.0
$ cmake -DCMAKE_INSTALL_PREFIX=$HOME/pgquarrel -DCMAKE_PREFIX_PATH=/usr/lib/postgresql/12 .
$ 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.

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

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:

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):

You can use a configuration file to store the desired options. The general section specifies which kind of objects will be output. The target and source section options specifies connection options to both servers. Have in mind that any command-line option can override the configuration file option. The configuration file contains the following structure:


[general]
output = /tmp/diff.sql
temp-directory = /tmp
verbose = false
summary = false
comment = false
security-labels = false
owner = false
privileges = false
ignore-version = false
single-transaction = false

access-method = false
aggregate = false
cast = false
collation = false
conversion = false
domain = true
event-trigger = false
extension = false
fdw = false
foreign-table = false
function = true
index = true
language = false
materialized-view = true
operator = false
policy = false
procedure = true
publication = false
rule = false
schema = true
sequence = true
statistics = false
subscription = false
table = true
text-search = false
transform = false
trigger = true
type = true
view = true

[target]
host = 10.27.0.8
port = 5432
dbname = quarrel1
user = bob
no-password = false

[source]
host = 10.8.0.10
port = 5432
dbname = quarrel2
user = alice
no-password = false

Regression Tests

$ # adjust test/run-test.sh
$ cd pgquarrel-0.5.0/test
$ # test using 11 on both clusters
$ ./run-test.sh 11 11 init

License

Copyright © 2015-2019 Euler Taveira de Oliveira All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer; Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution; Neither the name of the Euler Taveira de Oliveira nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.