postgresql 15.2 用pgbackup搭建备库后，主从复制一直无法启动
pgbackup搭建主从复制过程
略
主备检查:
log 日志检查，主从库都正常
从库
2025-03-11 10:18:23.952 CST [15567] LOG: starting PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2025-03-11 10:18:23.952 CST [15567] LOG: listening on IPv4 address "0.0.0.0", port 1922
2025-03-11 10:18:23.952 CST [15567] LOG: listening on IPv6 address "::", port 1922
2025-03-11 10:18:23.954 CST [15567] LOG: listening on Unix socket "/tmp/.s.PGSQL.1922"
2025-03-11 10:18:23.956 CST [15571] LOG: database system was shut down in recovery at 2025-03-11 10:17:43 CST
2025-03-11 10:18:23.957 CST [15571] LOG: entering standby mode
2025-03-11 10:18:23.960 CST [15571] LOG: redo starts at 7/9C000028
2025-03-11 10:18:23.970 CST [15571] LOG: consistent recovery state reached at 7/9C41B8D8
2025-03-11 10:18:23.970 CST [15571] LOG: recovery stopping before commit of transaction 177155, time 2025-03-04 14:48:21.479534+08
2025-03-11 10:18:23.970 CST [15571] LOG: pausing at the end of recovery
2025-03-11 10:18:23.970 CST [15571] HINT: Execute pg_wal_replay_resume() to promote.
2025-03-11 10:18:23.970 CST [15567] LOG: database system is ready to accept read-only connections
主库：
2025-03-11 09:21:41.100 CST [1277] LOG: starting PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2025-03-11 09:21:41.100 CST [1277] LOG: listening on IPv4 address "0.0.0.0", port 1922
2025-03-11 09:21:41.100 CST [1277] LOG: listening on IPv6 address "::", port 1922
2025-03-11 09:21:41.108 CST [1277] LOG: listening on Unix socket "/tmp/.s.PGSQL.1922"
2025-03-11 09:21:41.370 CST [1377] LOG: database system was interrupted; last known up at 2025-03-10 14:26:57 CST
2025-03-11 09:21:51.197 CST [1377] LOG: database system was not properly shut down; automatic recovery in progress
2025-03-11 09:21:51.232 CST [1377] LOG: redo starts at 7/9D0000D8
2025-03-11 09:21:51.232 CST [1377] LOG: invalid record length at 7/9D0001C0: wanted 24, got 0
2025-03-11 09:21:51.232 CST [1377] LOG: redo done at 7/9D000188 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
2025-03-11 09:21:51.398 CST [1375] LOG: checkpoint starting: end-of-recovery immediate wait
2025-03-11 09:21:51.839 CST [1375] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.109 s, total=0.445 s; sync files=2, longest=0.083 s, average=0.055 s; distance=0 kB, estimate=0 kB
2025-03-11 09:21:51.932 CST [1277] LOG: database system is ready to accept connections
主库视图检查
postgres=# SELECT client_addr,sync_state FROM pg_stat_replication;
client_addr | sync_state
-------------+------------
(0 rows)
postgres=# \du
List of roles
Role name | Attributes | Member of
------------+------------------------------------------------------------+-----------
fdw_user | | {}
local_user | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
qn | Create role, Create DB | {}
repl | Replication +| {}
| 32 connections |
replc | Replication +| {}
| 8 connections |
repmgr | Superuser, Replication +| {}
| 5 connections |
test | Superuser, Create DB | {}
testa | | {}
u11 | | {}
u12 | | {}
user2 | | {}
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
host all all ::1/128 md5
host replication repl 0.0.0.0/0 md5
# host all all 127.0.0.1/32 trust
# host all all ::1/128 trust
# local replication all trust
# host replication all 127.0.0.1/32 trust
# host replication all ::1/128 trust
# host all all 0.0.0.0/0 trust
host replication repmgr 0.0.0.0/0 md5
host replication repmgr 0.0.0.0/0 md5
host all all 0.0.0.0/0 md5
[postgres@postgres01 data]$ psql -h 192.168.99.151 -p 1922 -U repl postgres -W
Password:
psql (15.2)
Type "help" for help.
postgres=> exit
都没问题
从库检查：
[postgres@postgres02 data]$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
logging_collector = 'on'
# wal_level = 'archive'
force_parallel_mode = 'on'
synchronous_commit = 'on'
wal_sender_timeout = '6000'
synchronous_standby_names = ''
archive_mode = 'on'
wal_keep_size = '1024'
max_slot_wal_keep_size = '1024'
# Recovery settings generated by pgBackRest restore on 2023-12-13 02:18:46
# restore_command = 'pgbackrest --stanza=test archive-get %f "%p"'
recovery_target_time = '2023-12-13 01:40:52+08'
# recovery_target_action = 'promote'
primary_conninfo = 'user=repl password=111111 channel_binding=disable host=192.168.99.150 port=1922 sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
postgres=# select pg_is_in_recovery() ;
pg_is_in_recovery
-------------------
t
(1 row)
postgres@postgres02 ~]$ ps -ef | grep -i post
root 1658 1590 0 09:22 pts/0 00:00:00 su - postgres
postgres 1659 1658 0 09:22 pts/0 00:00:00 -bash
postgres 1762 1 0 09:22 ? 00:00:00 /usr/local/pgsql15.2/bin/postgres
postgres 1770 1762 0 09:22 ? 00:00:00 postgres: logger
postgres 1771 1762 0 09:22 ? 00:00:00 postgres: checkpointer
postgres 1772 1762 0 09:22 ? 00:00:00 postgres: background writer
postgres 1773 1762 0 09:22 ? 00:00:00 postgres: startup recovering 00000003000000070000009C
postgres 1864 1659 0 09:23 pts/0 00:00:00 ps -ef
postgres 1865 1659 0 09:23 pts/0 00:00:00 -bash
从库看上似乎正常，就是没有启动复制进程
检查检查postgresql.conf 发现，恢复文件自带参数：
recovery_target_time = ‘2023-12-13 01:40:52+08’
将参数屏蔽，然后重启服务器后，复制就正常了
postgres=# select * from pg_stat_wal_receiver ;
-[ RECORD 1 ]---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 24770
status | streaming
receive_start_lsn | 7/9C000000
receive_start_tli | 3
written_lsn | 7/A4000060
flushed_lsn | 7/A4000060
received_tli | 3
last_msg_send_time | 2025-03-11 10:48:52.235255+08
last_msg_receipt_time | 2025-03-11 10:48:50.653625+08
latest_end_lsn | 7/A4000060
latest_end_time | 2025-03-11 10:47:46.071326+08
slot_name |
sender_host | 192.168.99.150
sender_port | 1922
conninfo | user=repl password=******** channel_binding=disable dbname=replication host=192.168.99.150 port=1922 application_name=standby_node fallback_application_name=walreceiver sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any