使用pglogical插件搭建PostgreSQL逻辑复制

前言

之前我们使用的逻辑复制，是系统自带的复制功能，它从PostgreSQL 10的版本开始。因此，当遇到PostgreSQL 9.x数据库版本时，就只能使用第二象限
公司开发的pglogical
插件。

通过pglogical
官网简介，我发现 pglogical有几个很好的特性：

• 提供程序上的行过滤：打个比方，我只想过滤price>30的数据进行复制。
• 提供列过滤，我只想复制一张表上的3-4个列的数据。
• 自动化冲突检测和解决。比如你遇到主键冲突后，可以选择是使用源端行进行更新还是保留本地行而应该放弃源端数据，它通过将insert转换成update解决主键冲突。
• 跨低版本支持，可以支持PostgreSQL 9.x版本。
• 提供replicate_ddl_command可以实现ddl复制。

安装pglogical

首先介绍一下我的环境。

我需要在逻辑主库和从库上分别下载pglogical软件包编译安装。

wget -c https://github.com/2ndQuadrant/pglogical/archive/refs/tags/REL2_3_3.zipexport PGHOME=/data/postgresql/pgsqlexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:$PGHOME/lib:$LD_LIBRARY_PATHexport PATH=$ORACLE_HOME/bin:$PGHOME/bin:$PATHunzip pglogical-REL2_3_3.zipcd pglogical-REL2_3_3make && make install

安装完成后，要要在主库和从库上配置参数。其实pglogical的设置和传统的逻辑复制参数差不多，唯一区别是需要设置shared_preload_libraries
为pglogical。

wal_level = 'logical'max_worker_processes = 10                            max_replication_slots = 10  max_wal_senders = 10        shared_preload_libraries = 'pglogical'

设置完参数后重启数据库实例。

创建hr数据库和hr用户，记住这里hr用户需要赋予superuser
或者是replication
权限（最好是superuser，实际测试replication权限不成功)。同时还需要将pglogical的使用权限赋予给hr用户。

CREATE USER hr WITH PASSWORD 'hr!123' Superuser;     CREATE DATABASE hr WITH OWNER hr;postgres=# \c hr postgresYou are now connected to database "hr" as user "postgres".hr=# create extension pglogical;CREATE EXTENSIONhr=# grant usage on schema pglogical to hr;

然后在hr中创建一张带主键的表，等会我们来同步这张表。

hr=# \c hr hr You are now connected to database "hr" as user "hr".hr=> create table t1(id int primary key);CREATE TABLEhr=> insert into t1 values(1);

Publication侧配置

创建提供者节点。

postgres=# \c hr You are now connected to database "hr" as user "postgres".SELECT pglogical.create_node(    node_name := 'provider1',    dsn := 'host=providerhost port=5432 dbname=hr user=hr password=hr!123'); create_node -------------  2976894835hr=# \dt          List of relations Schema |    Name     | Type  | Owner --------+-------------+-------+------- public | departments | table | hr public | jobs        | table | hr public | t1          | table | hr(3 rows)SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);ERROR:  table departments cannot be added to replication set defaultDETAIL:  table does not have PRIMARY KEY and given replication set is configured to replicate UPDATEs and/or DELETEsHINT:  Add a PRIMARY KEY to the tablehr=# drop table departments ;DROP TABLEhr=# drop table jobs;DROP TABLEhr=# SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']); replication_set_add_all_tables -------------------------------- t(1 row)

在配置过程中，它会提示表没有主键，不能被添加到复制集中。所以必须是有主键的表才能配置到复制集中。

subscription配置

设置订阅侧之前，需要确保从库有和主库相同的表结构。

[postgres@localhost ~]$ psqlpsql (13.2)Type "help" for help.postgres=# \c hr You are now connected to database "hr" as user "postgres".SELECT pglogical.create_node(     node_name := 'subscriber1',     dsn := 'host=192.168.56.170 port=5432 dbname=hr user=hr password=hr!123'); create_node -------------   330520249SELECT pglogical.create_subscription(     subscription_name := 'subscription1',     provider_dsn := 'host=192.168.56.119 port=5432 dbname=hr user=hr password=hr!123');

配置成功后日志会显示。

2021-03-30 15:12:28.813 CST [3169] LOG:  starting pglogical database manager for database hr2021-03-30 15:12:33.744 CST [3173] LOG:  starting apply for subscription subscription12021-03-30 15:12:33.746 CST [3172] LOG:  manager worker [3172] at slot 2 generation 7 detaching cleanly2021-03-30 15:12:33.748 CST [3174] LOG:  manager worker [3174] at slot 2 generation 8 detaching cleanly

此时查看节点2的日志的t1表，数据已经复制过来了。

hr=# select * from t1; id ----  1

检查从库复制状态

查看同步表的状态。

hr=# select * from pglogical.local_sync_status;                             sync_kind | sync_subid | sync_nspname | sync_relname | sync_status | sync_statuslsn -----------+------------+--------------+--------------+-------------+---------------- f         | 1763399739 | public       | t1           | r           | 0/4AAFCD10

通过show_subscription_status可以查看从库复制的状态。当前是replicating
的。

hr=# select subscription_name, status, replication_sets FROM pglogical.show_subscription_status(); subscription_name |   status    |           replication_sets            -------------------+-------------+--------------------------------------- subscription1     | replicating | {default,default_insert_only,ddl_sql}(1 row)

后记

pglogical
简单搭建已经完成，如果你想从PostgreSQL9.x升级到PostgreSQL 13.x，建议考虑它，可以减少停机时间。

励志成为PostgreSQL大神

长按关注吧