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进程:
六、总结
逻辑流复制比起物理流复制,可以针对指定的对象建立复制,具有更好的灵活性。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库