PostgreSQL 資料庫結構(DDL)比對工具 pgquarrel

pgquarrel是一個PostgreSQL資料庫的資料庫結構(DDL)比對工具。它會對比兩個資料庫源，並輸出一個表示DDL差異的檔案。 如果將輸出檔案執行到目標資料庫中，它將具有與源資料庫相同的結構。 主要使用場景是將資料庫更改部署到測試或生產環境。

pgquarrel不依賴於另一個工具（如pg_dump），而是直接連線到 PostgreSQL 伺服器，從目錄中獲取後設資料，比較物件並輸出將目標資料庫轉換為源資料庫所需的命令。它擁有過濾器選項：所以，可以比較部分物件。

它可以適用於不同的PostgreSQL版本。如果源PostgreSQL 版本大於目標PostgreSQL版本，生成的檔案無法按預期工作。這是因為該工具會生成以前 PostgreSQL 版本中不存在的命令，在低版本中卻不能應用。 適用於不同的作業系統。

pgquarrel原始碼包可以在GitHub下載：

下邊是我在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 

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 物件
具體可去 檢視