PostgreSQL DDL比对工具 pgquarrel

开源软件联盟PostgreSQL分会 2021-08-06

324

作者：阎书利

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 postgrespsql (10.14)Type ‘help’ for help.postgres=# \dtList of relationsSchema | Name | Type | Owner————+—————-+———-+—————public | passwd | table | postgrespublic | test_user | table | postgres(2 rows)postgres=# \dnList of schemasName | Owner————+—————public | pg10repmgr | postgres(2 rows)postgres=# \diList of relationsSchema | Name | Type | Owner | Table————+———————————+———-+—————+————public | passwd_pkey | index | postgres | passwdpublic | passwd_user_name_key | index | postgres | passwd(2 rows)[postgres@t1ysl pgquarrel]$ psql -d postgrespsql (11.5)Type ‘help’ for help.postgres=# \dtList of relationsSchema | Name | Type | Owner————+———+———-+—————public | qq | table | postgres(1 row)postgres=# \dnList of schemasName | 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.查看生成的对比文件