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.
It works with different operating systems. It was tested on Linux, FreeBSD, and Windows.
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
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.
$ tar -zxf pgquarrel-0.3.0.tgz
$ cd pgquarrel-0.3.0
$ cmake .
$ make
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'.
pgquarrel does not support all of the PostgreSQL objects. Also, pgquarrel does not manipulate data (i.e. DML).
Object | Support | Comments |
---|---|---|
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 | not implemented | |
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 | not implemented | |
SUBSCRIPTION | not implemented | |
ALTER DEFAULT PRIVILEGES | uncertain | |
ALTER LARGE OBJECT | uncertain | |
STATISTICS | uncertain |
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.
$ # adjust test/run-test.sh
$ cd pgquarrel-0.3.0/test
$ # test using 10 on both clusters
$ ./run-test.sh 10 10 init
Copyright © 2015-2017 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.