pgquarrel是一个PostgreSQL数据库的数据库结构(DDL)比对工具。它会对比两个数据库源，并输出一个表示DDL差异的文件。 如果将输出文件运行到目标数据库中，它将具有与源数据库相同的结构。 主要使用场景是将数据库更改部署到测试或生产环境。
pgquarrel不依赖于另一个工具（如pg_dump），而是直接连接到 PostgreSQL 服务器，从目录中获取元数据，比较对象并输出将目标数据库转换为源数据库所需的命令。它拥有过滤器选项：所以，可以比较部分对象。
它可以适用于不同的PostgreSQL版本。如果源PostgreSQL 版本大于目标PostgreSQL版本，生成的文件无法按预期工作。这是因为该工具会生成以前 PostgreSQL 版本中不存在的命令，在低版本中却不能应用。 适用于不同的操作系统。
pgquarrel源码包可以在GitHub下载：https://github.com/eulerto/pgquarrel.git
下边是我在Linux环境进行的测试，数据库版本为源端172.20.10.7(主机名sandata02)：PostgreSQL 10.14，目标端172.20.10.8（主机名t1ysl）PostgreSQL 11.5。 1.源库下载git和编译所需的cmake
[root@sandata02 tmp]# yum install -y git cmake
2.源库下载源码包
[pg10@sandata02 ~]$ cd /tmp/ [pg10@sandata02 tmp]$ git clone https://github.com/eulerto/pgquarrel.git
3.编译安装
[pg10@sandata02 ~]$ cd /tmp/pgquarrel/ [pg10@sandata02 pgquarrel]$ cmake -DCMAKE_INSTALL_PREFIX=/tmp/pgquarrel -DCMAKE_PREFIX_PATH=/home/pgquarrel -- The C compiler identification is GNU 4.8.5 -- Check for working C compiler: /bin/cc -- Check for working C compiler: /bin/cc -- works -- Detecting C compiler ABI info -- Detecting C compiler ABI info - done COVERAGE: no pg_config: /home/pg10/soft/bin/pg_config PostgreSQL FOUND: LIBS: /home/pg10/soft/lib/libpgport.a;/home/pg10/soft/lib/libpgcommon.a PostgreSQL LIBRARIES: /home/pg10/soft/lib/libpq.so PostgreSQL LIBRARY DIRS: /home/pg10/soft/lib PostgreSQL INCLUDE DIRS: /home/pg10/soft/include/postgresql/server;/home/pg10/soft/include -- Configuring done -- Generating done -- Build files have been written to: /tmp/pgquarrel [pg10@sandata02 pgquarrel]$ make Scanning dependencies of target mini [ 2%] Building C object mini/CMakeFiles/mini.dir/mini-file.c.o [ 5%] Building C object mini/CMakeFiles/mini.dir/mini-parser.c.o [ 8%] Building C object mini/CMakeFiles/mini.dir/mini-readline.c.o [ 11%] Building C object mini/CMakeFiles/mini.dir/mini-strip.c.o Linking C shared library libmini.so [ 11%] Built target mini Scanning dependencies of target pgquarrel [ 13%] Building C object CMakeFiles/pgquarrel.dir/src/am.c.o [ 16%] Building C object CMakeFiles/pgquarrel.dir/src/aggregate.c.o [ 19%] Building C object CMakeFiles/pgquarrel.dir/src/cast.c.o [ 22%] Building C object CMakeFiles/pgquarrel.dir/src/collation.c.o [ 25%] Building C object CMakeFiles/pgquarrel.dir/src/common.c.o [ 27%] Building C object CMakeFiles/pgquarrel.dir/src/conversion.c.o [ 30%] Building C object CMakeFiles/pgquarrel.dir/src/domain.c.o [ 33%] Building C object CMakeFiles/pgquarrel.dir/src/eventtrigger.c.o [ 36%] Building C object CMakeFiles/pgquarrel.dir/src/extension.c.o [ 38%] Building C object CMakeFiles/pgquarrel.dir/src/fdw.c.o [ 41%] Building C object CMakeFiles/pgquarrel.dir/src/function.c.o [ 44%] Building C object CMakeFiles/pgquarrel.dir/src/index.c.o [ 47%] Building C object CMakeFiles/pgquarrel.dir/src/language.c.o [ 50%] Building C object CMakeFiles/pgquarrel.dir/src/matview.c.o [ 52%] Building C object CMakeFiles/pgquarrel.dir/src/operator.c.o [ 55%] Building C object CMakeFiles/pgquarrel.dir/src/policy.c.o [ 58%] Building C object CMakeFiles/pgquarrel.dir/src/publication.c.o [ 61%] Building C object CMakeFiles/pgquarrel.dir/src/privileges.c.o [ 63%] Building C object CMakeFiles/pgquarrel.dir/src/quarrel.c.o [ 66%] Building C object CMakeFiles/pgquarrel.dir/src/rule.c.o [ 69%] Building C object CMakeFiles/pgquarrel.dir/src/schema.c.o [ 72%] Building C object CMakeFiles/pgquarrel.dir/src/sequence.c.o [ 75%] Building C object CMakeFiles/pgquarrel.dir/src/server.c.o [ 77%] Building C object CMakeFiles/pgquarrel.dir/src/statistics.c.o [ 80%] Building C object CMakeFiles/pgquarrel.dir/src/subscription.c.o [ 83%] Building C object CMakeFiles/pgquarrel.dir/src/table.c.o [ 86%] Building C object CMakeFiles/pgquarrel.dir/src/textsearch.c.o [ 88%] Building C object CMakeFiles/pgquarrel.dir/src/transform.c.o [ 91%] Building C object CMakeFiles/pgquarrel.dir/src/trigger.c.o [ 94%] Building C object CMakeFiles/pgquarrel.dir/src/type.c.o [ 97%] Building C object CMakeFiles/pgquarrel.dir/src/usermapping.c.o [100%] Building C object CMakeFiles/pgquarrel.dir/src/view.c.o Linking C executable pgquarrel [100%] Built target pgquarrel [pg10@sandata02 pgquarrel]$ make install [ 11%] Built target mini [100%] Built target pgquarrel Install the project... -- Install configuration: "" -- Installing: /tmp/pgquarrel/bin/pgquarrel -- Set runtime path of "/tmp/pgquarrel/bin/pgquarrel" to "/tmp/pgquarrel/lib" -- Installing: /tmp/pgquarrel/lib/libmini.so
安装成功后查看工具可添加的选项
[pg10@sandata02 pgquarrel]$ ./pgquarrel --help pgquarrel shows changes between database schemas. Usage: pgquarrel [OPTION]... Options: -c, --config=FILENAME configuration file -f, --file=FILENAME receive changes into this file, - for stdout (default: stdout) --ignore-version ignore version check -s, --summary print a summary of changes -t, --single-transaction execute as a single transaction --temp-directory=DIR use as temporary file area (default: "/tmp") -v, --verbose verbose mode Object options: --access-method=BOOL access method (default: false) --aggregate=BOOL aggregate (default: false) --cast=BOOL cast (default: false) --collation=BOOL collation (default: false) --comment=BOOL comment (default: false) --conversion=BOOL conversion (default: false) --domain=BOOL domain (default: true) --event-trigger=BOOL event trigger (default: false) --extension=BOOL extension (default: false) --fdw=BOOL foreign data wrapper (default: false) --foreign-table=BOOL foreign table (default: false) --function=BOOL function (default: true) --index=BOOL index (default: true) --language=BOOL language (default: false) --materialized-view=BOOL materialized view (default: true) --operator=BOOL operator (default: false) --owner=BOOL owner (default: false) --policy=BOOL policy (default: false) --publication=BOOL publication (default: false) --privileges=BOOL privileges (default: false) --procedure=BOOL procedure (default: true) --rule=BOOL rule (default: false) --schema=BOOL schema (default: true) --security-labels=BOOL security labels (default: false) --sequence=BOOL sequence (default: true) --statistics=BOOL statistics (default: false) --subscription=BOOL subscription (default: false) --table=BOOL table (default: true) --text-search=BOOL text search (default: false) --transform=BOOL transform (default: false) --trigger=BOOL trigger (default: true) --type=BOOL type (default: true) --view=BOOL view (default: true) Filter options: --include-schema=PATTERN include schemas that match PATTERN (default: all schemas) --exclude-schema=PATTERN exclude schemas that match PATTERN (default: none) Source options: --source-dbname=DBNAME database name or connection string --source-host=HOSTNAME server host or socket directory --source-port=PORT server port --source-username=NAME user name --source-no-password never prompt for password Target options: --target-dbname=DBNAME database name or connection string --target-host=HOSTNAME server host or socket directory --target-port=PORT server port --target-username=NAME user name --target-no-password never prompt for password --help show this help, then exit --version output version information, then exit
4.查看数据库原本具有的一些对象
[pg10@sandata02 ~]$ psql -d postgres psql (10.14) Type "help" for help. postgres=# \dt List of relations Schema | Name | Type | Owner --------+-----------+-------+---------- public | passwd | table | postgres public | test_user | table | postgres (2 rows) postgres=# \dn List of schemas Name | Owner --------+---------- public | pg10 repmgr | postgres (2 rows) postgres=# \di List of relations Schema | Name | Type | Owner | Table --------+----------------------+-------+----------+-------- public | passwd_pkey | index | postgres | passwd public | passwd_user_name_key | index | postgres | passwd (2 rows)
[postgres@t1ysl pgquarrel]$ psql -d postgres psql (11.5) Type "help" for help. postgres=# \dt List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | qq | table | postgres (1 row) postgres=# \dn List of schemas Name | Owner --------+---------- public | postgres (1 row) postgres=# \di Did not find any relations.
5.使用工具生成对比文件
[pg10@sandata02 pgquarrel]$ ./pgquarrel --file=DDL_diff.txt --ignore-version --source-dbname=postgres --source-host=172.20.10.7 --source-port=5432 --source-username=postgres --target-dbname=postgres --target-host=172.20.10.8 --target-port=5666 --target-username=postgres
6.查看生成的对比文件
[pg10@sandata02 pgquarrel]$ cat DDL_diff.txt -- -- pgquarrel 0.7.0 -- quarrel between 10.14 and 11.5 -- CREATE TABLE public.qq ( age integer, name character(10) ); DROP SCHEMA repmgr; DROP SEQUENCE public.test_user_id_seq; DROP TABLE public.passwd; DROP TABLE public.test_user; DROP TABLE repmgr.events; DROP TABLE repmgr.monitoring_history; DROP TABLE repmgr.nodes; DROP TABLE repmgr.voting_term; DROP INDEX public.passwd_user_name_key; DROP INDEX repmgr.idx_monitoring_history_time; DROP INDEX repmgr.voting_term_restrict;
> 缺点：pgquarrel不支持所有 Postgresql 对象 具体可去 https://github.com/eulerto/pgquarrel查看
