PG 数据库结构比对工具 pgquarrel
👉 本文共约2084个字，系统预计阅读时间或需8分钟。
简介
官网：
https://github.com/eulerto/pgquarrel
http://eulerto.github.io/pgquarrel/
pgquarrel是一个PostgreSQL数据库的数据库结构(DDL)比对工具。它会对比两个数据库源，并输出一个表示DDL差异的文件。 如果将输出文件运行到目标数据库中，它将具有与源数据库相同的结构。 主要使用场景是将数据库更改部署到测试或生产环境。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
pgquarrel不依赖于另一个工具（如pg_dump），而是直接连接到 PostgreSQL 服务器，从目录中获取元数据，比较对象并输出将目标数据库转换为源数据库所需的命令。它拥有过滤器选项：所以，可以比较部分对象。
它可以适用于不同的PostgreSQL版本。如果源PostgreSQL 版本大于目标PostgreSQL版本，生成的文件无法按预期工作。这是因为该工具会生成以前 PostgreSQL 版本中不存在的命令，在低版本中却不能应用。 适用于不同的操作系统，例如Linux, FreeBSD, 和 Windows。
pgquarrel源码包可以在GitHub下载：https://github.com/eulerto/pgquarrel.git
缺点
缺点：pgquarrel不支持所有 Postgresql 对象。
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:
- inheritance;
- roles.
安装
1 2 3 4 5 6 7 8 9 | git clone https://github.com/eulerto/pgquarrel.git wget https://codeload.github.com/eulerto/pgquarrel/legacy.tar.gz/master -O pgquarrel-0.7.0.tgz tar -zxf pgquarrel-0.7.0.tgz cd pgquarrel-0.7.0 cmake -DCMAKE_INSTALL_PREFIX=/pg13/pgquarrel -DCMAKE_PREFIX_PATH=/pg13/pg13/lib/ . make make install |
安装成功后查看工具可添加的选项
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 | [pg13@lhrpgall bin]$ ./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 Report bugs to <euler@eulerto.com>. |
示例
使用工具生成对比文件[pg10@sandata02 pgquarrel]$
1 2 3 4 5 | ./pgquarrel -s --file=DDL_diff.txt --ignore-version \ --source-host=172.72.6.30 --source-port=5432 --source-dbname=lhrdb --source-username=postgres \ --target-host=172.72.6.31 --target-port=5432 --target-dbname=lhrdb --target-username=postgres |
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | [pg13@lhrpgall bin]$ ./pgquarrel -s --file=DDL_diff.txt --ignore-version \ > --source-host=172.72.6.30 --source-port=5432 --source-dbname=lhrdb --source-username=postgres \ > --target-host=172.72.6.31 --target-port=5432 --target-dbname=lhrdb --target-username=postgres Target password: Source password: 1 table(s) added, 0 table(s) removed 0 sequence(s) added, 0 sequence(s) removed 0 index(es) added, 0 index(es) removed [pg13@lhrpgall bin]$ more DDL_diff.txt -- -- pgquarrel 0.7.0 -- quarrel between 13.4 (Debian 13.4-4.pgdg110+1) and 13.4 (Debian 13.4-4.pgdg110+1) -- CREATE TABLE public.t1 ( id integer, name text, reg_time timestamp without time zone ); |
可见，源端多了一张表，其它没有差别。
