wal_level = logical #这个只能为logical，因为后续要配置逻辑复制
max_replication_slots = 10 #默认为10，根据具体的情况适当增大
max_wal_senders = 10 #默认为10，根据具体的情况适当增大
synchronous_standby_names = '*' #同步从库的名字，这里设置为*，代表所有备库都是同步模式，当然这里只有一个备库
synchronized_standby_slots = 'pgstandby_slot01' #这个名字是流复制配置中，流复制的复制槽名称备节点192.168.152.137参数
hot_standby_feedback = on; #默认关闭的，建议打开
sync_replication_slots = on; #默认关闭的，必须打开
#synchronous_standby_names = '*' #因为备库是pgbasebackup备份过来的，跟主库参数一样，所以这里备库的synchronous_standby_names要注释掉
#synchronized_standby_slots = 'pgstandby_slot01' #因为备库是pgbasebackup备份过来的，跟主库参数一样，所以这里备库的synchronized_standby_slots要注释掉对于备节点上的postgresql.auto.conf，也即流复制连接信息。因为 PG17开始支持的逻辑复制槽同步功能， slot sync worker 做的事情不是 WAL 级别协议，而是同时需要 replication protocol + 普通 SQL 连接能力，所以需要手动修改primary_conninfo，在连接串中指定一个dbname，默认的连接串为
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=repl_user password=repl_user_pwd channel_binding=prefer host=192.168.152.136 port=9700 sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
primary_slot_name = 'pgstandby_slot01'需要再primary_conninfo中指定一个dbname=postgres参数，如下
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=repl_user password=repl_user_pwd channel_binding=prefer host=192.168.152.136 port=9700 dbname=postgres sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
primary_slot_name = 'pgstandby_slot01'否则备库启动的时候会类似如下的错误： replication slot synchronization requires "dbname" to be specified in "primary_conninfo"
2026-06-29 08:37:37.106 UTC [2241336] LOG: starting PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
2026-06-29 08:37:37.106 UTC [2241336] LOG: listening on IPv4 address "0.0.0.0", port 9700
2026-06-29 08:37:37.106 UTC [2241336] LOG: listening on IPv6 address "::", port 9700
2026-06-29 08:37:37.108 UTC [2241336] LOG: listening on Unix socket "/tmp/.s.PGSQL.9700"
2026-06-29 08:37:37.110 UTC [2241340] LOG: database system was interrupted; last known up at 2026-06-29 08:36:52 UTC
2026-06-29 08:37:37.117 UTC [2241340] LOG: starting backup recovery with redo LSN 0/A000028, checkpoint LSN 0/A000080, on timeline ID 1
2026-06-29 08:37:37.117 UTC [2241340] LOG: entering standby mode
2026-06-29 08:37:37.122 UTC [2241340] LOG: redo starts at 0/A000028
2026-06-29 08:37:37.123 UTC [2241340] LOG: completed backup recovery with redo LSN 0/A000028 and end LSN 0/A000120
2026-06-29 08:37:37.123 UTC [2241340] LOG: consistent recovery state reached at 0/A000120
2026-06-29 08:37:37.123 UTC [2241336] LOG: database system is ready to accept read-only connections
2026-06-29 08:37:37.125 UTC [2241342] LOG: slot sync worker started
2026-06-29 08:37:37.127 UTC [2241342] ERROR: replication slot synchronization requires "dbname" to be specified in "primary_conninfo"
2026-06-29 08:37:37.299 UTC [2241341] LOG: started streaming WAL from primary at 0/B000000 on timeline 1
2026-06-29 08:38:37.148 UTC [2241734] LOG: slot sync worker started
2026-06-29 08:38:37.149 UTC [2241734] ERROR: replication slot synchronization requires "dbname" to be specified in "primary_conninfo"
2026-06-29 08:39:37.205 UTC [2242122] LOG: slot sync worker started
2026-06-29 08:39:37.206 UTC [2242122] ERROR: replication slot synchronization requires "dbname" to be specified in "primary_conninfo"
2026-06-29 08:40:37.270 UTC [2242510] LOG: slot sync worker started
2026-06-29 08:40:37.271 UTC [2242510] ERROR: replication slot synchronization requires "dbname" to be specified in "primary_conninfo"
2026-06-29 08:41:37.333 UTC [2242905] LOG: slot sync worker started
2026-06-29 08:41:37.333 UTC [2242905] ERROR: replication slot synchronization requires "dbname" to be specified in "primary_conninfo"启动从库，流复制自动开启，可以看到walreceiver进程启动，说明目前节点是一个备用节点
root@ubuntu27:/usr/local/pg_install# bash pg_streaming_replication_setup.sh
=== PostgreSQL Standby Initialization Start ===
Mon Jun 29 05:30:37 PM CST 2026
############ Data directory /usr/local/pgsql17/pg9700/data does not exist. Creating it...
############ Checking replication user repl_user on primary...
############ Replication user repl_user already exists.
############ Checking replication slot pgstandby_slot01 on primary...
############ Replication slot pgstandby_slot01 already exists.
############ Performing pg_basebackup from primary node...
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/E000060 on timeline 1
pg_basebackup: starting background WAL receiver
30961/30961 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/E000158
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
############ Starting PostgreSQL standby...
=== Standby Initialization Complete ===
Mon Jun 29 05:30:40 PM CST 2026
root@ubuntu27:/usr/local/pg_install#
root@ubuntu27:/usr/local/pg_install# systemctl restart postgresql9700
root@ubuntu27:/usr/local/pg_install#
root@ubuntu27:/usr/local/pg_install# systemctl status postgresql9700
● postgresql9700.service - PostgreSQL database server
Loaded: loaded (/etc/systemd/system/postgresql9700.service; enabled; preset: enabled)
Active: active (running) since Mon 2026-06-29 17:33:34 CST; 8s ago
Process: 2263119 ExecStart=/usr/local/pgsql17/server/bin/pg_ctl start -D ${PGDATA} -s -w -t ${PGSTARTTIMEOUT} (code=exited, status=0/SUCCESS)
Main PID: 2263123 (postgres)
Tasks: 6 (limit: 4543)
Memory: 63.8M (peak: 64.0M)
CPU: 124ms
CGroup: /system.slice/postgresql9700.service
├─2263123 /usr/local/pgsql17/server/bin/postgres -D /usr/local/pgsql17/pg9700/data
├─2263130 "postgres: logger "
├─2263131 "postgres: checkpointer "
├─2263132 "postgres: background writer "
├─2263133 "postgres: startup recovering 00000001000000000000000F"
└─2263139 "postgres: walreceiver streaming 0/F000060"
Jun 29 17:33:34 ubuntu27 systemd[1]: Starting postgresql9700.service - PostgreSQL database server...
Jun 29 17:33:34 ubuntu27 pg_ctl[2263123]: 2026-06-29 09:33:34.306 UTC [2263123] LOG: redirecting log output to logging collector process
Jun 29 17:33:34 ubuntu27 pg_ctl[2263123]: 2026-06-29 09:33:34.306 UTC [2263123] HINT: Future log output will appear in directory "/usr/local/pgsql17/pg9700/log".
Jun 29 17:33:34 ubuntu27 systemd[1]: Started postgresql9700.service - PostgreSQL database server.
root@ubuntu27:/usr/local/pg_install#订阅节点192.168.152.138参数
参数无特殊要求
192.168.152.136和192.168.152.137之间的流复制已完成同步，检查流复制的状态
主节点192.168.152.136上的状态
root@ubuntu26:/usr/local/pg_install# psql -h 127.0.0.1 -p 9700 postgres postgres
Password for user postgres:
psql (17.6)
Type "help" for help.
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 3019576
usesysid | 16388
usename | repl_user
application_name | walreceiver
client_addr | 192.168.152.137
client_hostname |
client_port | 45264
backend_start | 2026-06-29 09:03:23.143699+00
backend_xmin |
state | streaming
sent_lsn | 0/D000060
write_lsn | 0/D000060
flush_lsn | 0/D000060
replay_lsn | 0/D000060
write_lag | 00:00:00.022827
flush_lag | 00:00:00.023348
replay_lag | 00:00:00.154594
sync_priority | 1
sync_state | sync
reply_time | 2026-06-29 09:03:23.182874+00
postgres=#从节点192.168.152.137上的状态
root@ubuntu27:/usr/local/pg_install# psql -h 127.0.0.1 -p 9700 postgres postgres
Password for user postgres:
psql (17.6)
Type "help" for help.
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 2251271
status | streaming
receive_start_lsn | 0/D000000
receive_start_tli | 1
written_lsn | 0/D000060
flushed_lsn | 0/D000060
received_tli | 1
last_msg_send_time | 2026-06-29 09:04:53.210286+00
last_msg_receipt_time | 2026-06-29 09:04:53.21439+00
latest_end_lsn | 0/D000060
latest_end_time | 2026-06-29 09:03:23.156472+00
slot_name | pgstandby_slot01
sender_host | 192.168.152.136
sender_port | 9700
conninfo | user=repl_user password=******** channel_binding=prefer dbname=replication host=192.168.152.136 port=9700 fallback_application_name=walreceiver sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable
postgres=#创建逻辑复制用户
create database db01;
###创建复制用户
--drop user app_replication;
create user logic_repl_user with password 'logic_repl_user';
alter role logic_repl_user with replication;
###给复制用户授权，切换到目标数据库db01下执行
GRANT CONNECT ON DATABASE db01 TO logic_repl_user;
GRANT USAGE ON SCHEMA public TO logic_repl_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO logic_repl_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO logic_repl_user;--切换到db01下面
--创建测试表
create table test_slot_failover
(
c1 int generated always as identity primary key,
c2 timestamptz
);
--创建逻辑复制槽，注意最后一个参数是是否failover，默认是false，要明确指出failover为true
select * from pg_create_logical_replication_slot('logic_repl_slot01','pgoutput', false,false, true);
slot_name |lsn |
-----------------+---------+
logic_repl_slot01|0/31A9BB0|
--创建发布
create publication mysqlpg_db01_pulication for table test_slot_failover with (publish = 'insert,update');
Updated Rows 0
Execute time 0.008s
Start time Mon Jun 29 14:12:15 CST 2026
Finish time Mon Jun 29 14:12:15 CST 2026
Query create publication mysqlpg_db01_pulication for table test_slot_failover with (publish = 'insert,update')查看逻辑复制槽的状态
主节点上
postgres=# SELECT slot_name, database, failover, synced,active FROM pg_replication_slots;
slot_name | database | failover | synced | active
-------------------+----------+----------+--------+--------
pgstandby_slot01 | | f | f | t #注意这是物理复制槽，只有主节点上有
logic_repl_slot01 | db01 | t | f | f
(2 rows)备节点上
postgres=#
postgres=# SELECT slot_name, database, failover, synced,active FROM pg_replication_slots;
slot_name | database | failover | synced | active
-------------------+----------+----------+--------+--------
logic_repl_slot01 | db01 | t | t | f #可以发现逻辑复制槽已经被复制到备节点
(1 row)订阅服务器上创建测试库db01创建测试表
--切换到db01下创建订阅表
create table test_slot_failover
(
c1 int generated always as identity primary key,
c2 timestamptz
);创建订阅
--创建订阅
create subscription mypg_db01_subscription
connection 'host=192.168.152.136 port=9700 dbname=db01 user=logic_repl_user password=logic_repl_user'
publication mypg_db01_pulication
with
(
create_slot = false, #这里不创建复制槽，因为前面已经提前创建好了
slot_name = logic_repl_slot01,
streaming = parallel,
copy_data = true,
failover = true #注意如果没有创建复制槽，上面的create_slot为true的话，这里也一定要为failover = true，
);
Updated Rows 0
Execute time 0.051s
Start time Mon Jun 29 14:29:59 CST 2026
Finish time Mon Jun 29 14:29:59 CST 2026
create subscription mypg_db01_subscription
connection 'host=192.168.152.136 port=9700 dbname=db01 user=logic_repl_user password=logic_repl_user'
publication mypg_db01_pulication
with
(
create_slot = false,
slot_name = logic_repl_slot01,
streaming = parallel,
copy_data = true,
failover = true
)
--查看订阅状态
select * from pg_subscription;
oid |subdbid|subskiplsn|subname |subowner|subenabled|subbinary|substream|subtwophasestate|subdisableonerr|subpasswordrequired|subrunasowner|subfailover|subconninfo |subslotname |subsynccommit|subpublications |suborigin|
-----+-------+----------+----------------------+--------+----------+---------+---------+----------------+---------------+-------------------+-------------+-----------+-----------------------------------------------------------------------------------------+-----------------+-------------+-----------------------+---------+
16444| 16388|0/0 |mypg_db01_subscription| 10|true |false |p |d |false |true |false |false |host=192.168.152.136 port=9700 dbname=db01 user=logic_repl_user password=logic_repl_user|logic_repl_slot01|off |{mypg_db01_publication}|any |模拟主节点故障，备用节点提升为主节点
1，关闭主节点 systemctl stop postgresql9700
root@ubuntu26:/usr/local/pg_install# systemctl stop postgresql9700
root@ubuntu26:/usr/local/pg_install#2，从节点直接pg_promote select pg_promote()
postgres=# select pg_promote();
pg_promote
------------
t
(1 row)此时逻辑复制的订阅端开始报错，提示找不到发布端主机： could not connect to the publisher: connection to server at "192.168.152.136", port 9700 failed: Connection refused Is the server running on that host and accepting TCP/IP connections?
2026-06-29 09:14:43.546 UTC [2247682] LOG: background worker "logical replication apply worker" (PID 2265592) exited with exit code 1
2026-06-29 09:14:48.543 UTC [2265603] LOG: logical replication apply worker for subscription "mypg_db01_subscription" has started
2026-06-29 09:14:48.544 UTC [2265603] ERROR: could not connect to the publisher: connection to server at "192.168.152.136", port 9700 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
2026-06-29 09:14:48.545 UTC [2247682] LOG: background worker "logical replication apply worker" (PID 2265603) exited with exit code 1
2026-06-29 09:14:53.544 UTC [2265611] LOG: logical replication apply worker for subscription "mypg_db01_subscription" has started
2026-06-29 09:14:53.545 UTC [2265611] ERROR: could not connect to the publisher: connection to server at "192.168.152.136", port 9700 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
2026-06-29 09:14:53.546 UTC [2247682] LOG: background worker "logical replication apply worker" (PID 2265611) exited with exit code 1
2026-06-29 09:14:58.556 UTC [2265623] LOG: logical replication apply worker for subscription "mypg_db01_subscription" has started
2026-06-29 09:14:58.557 UTC [2265623] ERROR: could not connect to the publisher: connection to server at "192.168.152.136", port 9700 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
2026-06-29 09:14:58.559 UTC [2247682] LOG: background worker "logical replication apply worker" (PID 2265623) exited with exit code 1
2026-06-29 09:15:03.568 UTC [2265636] LOG: logical replication apply worker for subscription "mypg_db01_subscription" has started
2026-06-29 09:15:03.569 UTC [2265636] ERROR: could not connect to the publisher: connection to server at "192.168.152.136", port 9700 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
2026-06-29 09:15:03.570 UTC [2247682] LOG: background worker "logical replication apply worker" (PID 2265636) exited with exit code 1此时需要对订阅端执行故障转移，也即先停止订阅，然后修改订阅的连接串中的主机信息，指向新的主节点，最后启动订阅
--停止订阅
ALTER SUBSCRIPTION mypg_db01_subscription DISABLE;
--修改订阅的连接信息，执行新的主节点
ALTER SUBSCRIPTION mypg_db01_subscription CONNECTION 'host=192.168.152.137 port=9700 dbname=db01 user=logic_repl_user password=logic_repl_user'
--启动订阅
ALTER SUBSCRIPTION mypg_db01_subscription ENABLE;查看新的订阅信息
oid |subdbid|subskiplsn|subname |subowner|subenabled|subbinary|substream|subtwophasestate|subdisableonerr|subpasswordrequired|subrunasowner|subfailover|subconninfo |subslotname |subsynccommit|subpublications |suborigin|
-----+-------+----------+----------------------+--------+----------+---------+---------+----------------+---------------+-------------------+-------------+-----------+-----------------------------------------------------------------------------------------+-----------------+-------------+-----------------------+---------+
16447| 16388|0/0 |mypg_db01_subscription| 10|true |false |p |d |false |true |false |false |host=192.168.152.137 port=9700 dbname=db01 user=logic_repl_user password=logic_repl_user|logic_repl_slot01|off |{mypg_db01_publication}|any |在新的主库192.168.152.137上，往发布表写入数据，可以正常同步到订阅端。
PostgreSQL 17 已经可以在物理备库上同步逻辑复制槽，实现“逻辑复制 Failover 的基础能力”，本文简单通过一个简单的测试案例来测试逻辑复制槽的failover效果，订阅端可以基于新的主节点做逻辑复制，但订阅端的连接切换仍需人工干预，尚未实现全自动高可用。
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。