PostgreSQL 配置逻辑复制
逻辑复制的体系结构：
逻辑复制体系结构如下图，来自于Noriyoshi Shinoda的Logical Replication Internals：
wal sender进程
逻辑解码插件pgoutput对WAL文件进行逻辑解码，然后wal sender进程将解码后的消息发送到订阅端。在发布实例进程如下所示：
logical replication launcher进程
logical replication launcher进程，在发布实例和订阅实例都存在。主要的作用就是启动逻辑复制工作进程。
logical replication worker for subscription进程
只在订阅实例上存在，每一个订阅都会启动一个对应的进程，它和wal sender进程进行连接，接收wal sender传输过来的信息并更新对应的表。
集群架构：
如下所示，发布者数据库系统使用pg V14版本，订阅者使用数据库系统pg V15版本：
一、查看数据库系统配置
发布者（pub）：
# 数据库版本
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
# 数据库配置
postgres=# show wal_level;
wal_level
-----------
logical
(1 row)
postgres=# show max_wal_senders;
max_wal_senders
-----------------
10
(1 row)
postgres=# show max_replication_slots;
max_replication_slots
-----------------------
10
(1 row)
订阅者（sub）：
# 数据库版本
postgres=# select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
(1 row)
# 数据库配置
[postgres@node202 bin]$ ./psql
psql (15.4)
Type "help" for help.
postgres=# show wal_level;
wal_level
-----------
logical
(1 row)
postgres=# show max_wal_senders;
max_wal_senders
-----------------
10
(1 row)
postgres=# show max_replication_slots;
max_replication_slots
-----------------------
10
(1 row)
postgres=# show max_logical_replication_workers ;
max_logical_replication_workers
---------------------------------
8
(1 row)
二、编辑pg_hba.conf（all nodes）
# 编辑pg_hba.conf：
host replication all 0.0.0.0/0 trust
host all all 0.0.0.0/0 trust
三、创建发布（publisher）
1、创建用户
postgres=# create user repl replication login password 'beijing';
CREATE ROLE
postgres=# \c prod
You are now connected to database "prod" as user "postgres".
prod=# grant all on schema public to repl;
GRANT
2、创建测试表
如下所示，需要发布的表，默认必须包含主键：
prod=# create table c1 (id int4 primary key,name text);
CREATE TABLE
prod=# insert into c1 values (1,'a');
INSERT 0 1
prod=# \d+ c1
Table "public.c1"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | not null | | plain | | |
name | text | | | | extended | | |
Indexes:
"c1_pkey" PRIMARY KEY, btree (id)
Access method: heap
3、创建发布
prod=# create publication pub1 for table c1;
CREATE PUBLICATION
prod=# select * from pg_publication;
oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
16393 | pub1 | 10 | f | t | t | t | t | f
(1 row)
# 授权用户
prod=# grant connect on database prod to repl;
GRANT
prod=# grant usage on schema public to repl;
GRANT
prod=# grant select on c1 to repl;
GRANT
四、创建订阅
1、创建用户
postgres=# \c prod
You are now connected to database "prod" as user "postgres".
prod=# create user repl replication login password 'beijing';
CREATE ROLE
prod=# grant all on schema public to repl;
GRANT
2、订阅创建测试表
如下所示，测试表需要创建主键：
prod=# create table c1 (id int4 primary key,name text);
CREATE TABLE
prod=# \d+ c1
Table "public.c1"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | not null | | plain | | |
name | text | | | | extended | | |
Indexes:
"c1_pkey" PRIMARY KEY, btree (id)
Access method: heap
3、创建订阅
prod=# create subscription sub1
prod-# connection 'host=192.168.1.201 port=5432 dbname=prod user=repl password=beijing' publication pub1;
NOTICE: created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
# 订阅端授权
prod=# grant connect on database prod to repl;
GRANT
prod=# grant usage on schema public to repl;
GRANT
prod=# grant select on c1 to repl;
GRANT
4、发布端日志信息
如下所示，订阅者创建订阅后，发布端自动创建逻辑复制槽，并建立流复制：
2024-06-28 19:03:22.445 CST [31198] LOG: logical decoding found consistent point at 0/1712400
2024-06-28 19:03:22.445 CST [31198] DETAIL: There are no running transactions.
2024-06-28 19:03:22.445 CST [31198] STATEMENT: CREATE_REPLICATION_SLOT "sub1" LOGICAL pgoutput NOEXPORT_SNAPSHOT
2024-06-28 19:03:22.468 CST [31199] LOG: starting logical decoding for slot "sub1"
2024-06-28 19:03:22.468 CST [31199] DETAIL: Streaming transactions committing after 0/1712438, reading WAL from 0/1712400.
2024-06-28 19:03:22.468 CST [31199] STATEMENT: START_REPLICATION SLOT "sub1" LOGICAL 0/0 (proto_version '2', publication_names '"pub1"')
2024-06-28 19:03:22.468 CST [31199] LOG: logical decoding found consistent point at 0/1712400
2024-06-28 19:03:22.468 CST [31199] DETAIL: There are no running transactions.
2024-06-28 19:03:22.468 CST [31199] STATEMENT: START_REPLICATION SLOT "sub1" LOGICAL 0/0 (proto_version '2', publication_names '"pub1"')
2024-06-28 19:03:22.481 CST [31200] LOG: logical decoding found consistent point at 0/1712438
2024-06-28 19:03:22.481 CST [31200] DETAIL: There are no running transactions.
2024-06-28 19:03:22.481 CST [31200] STATEMENT: CREATE_REPLICATION_SLOT "pg_24619_sync_24608_7273015491967192171" LOGICAL pgoutput USE_SNAPSHOT
2024-06-28 19:03:22.487 CST [31200] LOG: starting logical decoding for slot "pg_24619_sync_24608_7273015491967192171"
2024-06-28 19:03:22.487 CST [31200] DETAIL: Streaming transactions committing after 0/1712470, reading WAL from 0/1712438.
2024-06-28 19:03:22.487 CST [31200] STATEMENT: START_REPLICATION SLOT "pg_24619_sync_24608_7273015491967192171" LOGICAL 0/1712470 (proto_version '2', publication_names '"pub1"')
2024-06-28 19:03:22.487 CST [31200] LOG: logical decoding found consistent point at 0/1712438
2024-06-28 19:03:22.487 CST [31200] DETAIL: There are no running transactions.
2024-06-28 19:03:22.487 CST [31200] STATEMENT: START_REPLICATION SLOT "pg_24619_sync_24608_7273015491967192171" LOGICAL 0/1712470 (proto_version '2', publication_names '"pub1"')
五、查看流复制信息
1、发布端复制槽信息
如下所示，创建订阅后，自动创建复制槽：
postgres=# \c prod
You are now connected to database "prod" as user "postgres".
prod=# select slot_name,plugin,slot_type,database,active,restart_lsn from pg_replication_slots;
slot_name | plugin | slot_type | database | active | restart_lsn
-----------+----------+-----------+----------+--------+-------------
sub1 | pgoutput | logical | prod | t | 0/1712438
(1 row)
2、流复制信息
如下所示，发布端和订阅端主机之间创建流复制：
prod=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | b
ackend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_l
ag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-------+----------+---------+------------------+---------------+-----------------+-------------+----------
---------------------+--------------+-----------+-----------+-----------+-----------+------------+--------
---+-----------+------------+---------------+------------+-------------------------------
31199 | 16385 | repl | sub1 | 192.168.1.202 | | 57343 | 2024-06-2
8 19:03:22.451262+08 | | streaming | 0/1712470 | 0/1712470 | 0/1712470 | 0/1712470 |
| | | 0 | async | 2024-06-28 19:06:52.803854+08
(1 row)
3、测试复制
1）建立发布
prod=# select * from c1;
id | name
----+------
1 | a
(1 row)
prod=# insert into c1 values (2,'2a');
INSERT 0 1
prod=# select * from c1;
id | name
----+------
1 | a
2 | 2a
(2 rows)
2）查看订阅
如下所示，订阅端已经和发布端数据同步：
prod=# select * from c1;
-[ RECORD 1 ]
id | 1
name | a
-[ RECORD 2 ]
id | 2
name | 2a
4、查看数据库进程
1）发布端进程
[postgres@node201 bin]$ ps -ef |grep postgres
postgres 31188 1 0 19:02 ? 00:00:00 /usr/local/pg15/bin/postgres -D /home/postgres/db/pg14/data
postgres 31190 31188 0 19:02 ? 00:00:00 postgres: checkpointer
postgres 31191 31188 0 19:02 ? 00:00:00 postgres: background writer
postgres 31192 31188 0 19:02 ? 00:00:00 postgres: walwriter
postgres 31193 31188 0 19:02 ? 00:00:00 postgres: autovacuum launcher
postgres 31194 31188 0 19:02 ? 00:00:00 postgres: stats collector
postgres 31195 31188 0 19:02 ? 00:00:00 postgres: logical replication launcher
postgres 31199 31188 0 19:03 ? 00:00:00 postgres: walsender repl 192.168.1.202(57343) START_REPLICATION
发布端walsender进程：
2）订阅端进程
[postgres@node202 data]$ ps -ef |grep postgres
postgres 23517 1 0 18:45 ? 00:00:00 /usr/local/pg15/bin/postgres -D /home/postgres/db/pg15/data
postgres 23518 23517 0 18:45 ? 00:00:00 postgres: logger
postgres 23519 23517 0 18:45 ? 00:00:00 postgres: checkpointer
postgres 23520 23517 0 18:45 ? 00:00:00 postgres: background writer
postgres 23522 23517 0 18:45 ? 00:00:00 postgres: walwriter
postgres 23523 23517 0 18:45 ? 00:00:00 postgres: autovacuum launcher
postgres 23524 23517 0 18:45 ? 00:00:00 postgres: logical replication launcher
postgres 24722 22765 0 19:03 pts/1 00:00:00 ./psql
postgres 24729 23517 0 19:03 ? 00:00:00 postgres: postgres prod [local] idle
postgres 24738 23517 0 19:03 ? 00:00:00 postgres: logical replication worker for subscription 24619
订阅端logical replication worker进程：
六、总结
逻辑流复制比起物理流复制，可以针对指定的对象建立复制，具有更好的灵活性。
