Table of Contents
主库
- 基本信息
/pgsql/app/bin/psql -U pgsql -d postgres -p 7777 -D /postgresql/data
- 启库
/postgresql/app/bin/postmaster -p 7777 -D /postgresql/data &
- 停库
pg_ctl stop -m fast -D /postgresql/data
从库
- 基本信息
/postgresql/app/bin/psql -p 8888 -U pgsql -d postgres -D /postgresql/data_slave_20240123
- 启库
/postgresql/app/bin/postmaster -p 8888 -D /postgresql/data_slave_20240123 &
- 停库
pg_ctl stop -m fast -D /postgresql/data_slave_20240123 &
检查 PG 状态
ps -ef |grep -v grep |grep postmaster
[pgsql@cncq081298 ~]$ ps -ef |grep -v grep |grep postmaster
pgsql 92003 1 0 2023 ? 00:03:19 ./app/bin/postmaster -p 6666 -D ./data
pgsql 192389 178111 0 19:14 pts/23 00:00:00 /postgresql/app/bin/postmaster -p 7777 -D /postgresql/data
pgsql 202585 178111 0 19:21 pts/23 00:00:00 /postgresql/app/bin/postmaster -p 8888 -D /postgresql/data_slave_20240123
[pgsql@cncq081298 ~]$ psql -p 7777 -c "\x" -c "select * from pg_stat_replication"
Expanded display is on.
-[ RECORD 1 ]----+------------------------------
pid | 202592
usesysid | 16505
usename | repuser
application_name | walreceiver
client_addr |
client_hostname |
client_port | -1
backend_start | 2024-01-30 19:21:13.608083+08
backend_xmin |
state | streaming
sent_lsn | 1/1A0002A8
write_lsn | 1/1A0002A8
flush_lsn | 1/1A0002A8
replay_lsn | 1/1A0002A8
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2024-01-30 19:21:43.695014+08
[pgsql@cncq081298 ~]$ psql -p 8888 -c "\x" -c "select * from pg_stat_wal_receiver"
Expanded display is on.
-[ RECORD 1 ]---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 202591
status | streaming
receive_start_lsn | 1/1A000000
receive_start_tli | 1
written_lsn | 1/1A0002A8
flushed_lsn | 1/1A0002A8
received_tli | 1
last_msg_send_time | 2024-01-30 19:21:43.695051+08
last_msg_receipt_time | 2024-01-30 19:21:43.695066+08
latest_end_lsn | 1/1A0002A8
latest_end_time | 2024-01-30 19:21:13.610073+08
slot_name |
sender_host | /tmp
sender_port | 7777
conninfo | user=repuser passfile=/home/pgsql/.pgpass channel_binding=prefer dbname=replication port=7777 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any
[pgsql@cncq081298 ~]$
停止主库
pg_ctl stop -m fast -D /postgresql/data
查看备库日志
2024-01-30 19:23:54.247 CST [206034] FATAL: could not connect to the primary server: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.7777"?
2024-01-30 19:23:59.253 CST [206308] FATAL: could not connect to the primary server: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.7777"?
2024-01-30 19:24:04.259 CST [206504] FATAL: could not connect to the primary server: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.7777"?
2024-01-30 19:24:09.264 CST [206526] FATAL: could not connect to the primary server: could not connect to server: No such file or directory
Is the server running locally and accepting
可以看到, 每间隔 5 秒, 会连接一次主库
启动主库
/postgresql/app/bin/postmaster -p 7777 -D /postgresql/data &
[pgsql@cncq081298 ~]$ psql -p 8888 -c "\x" -c "select * from pg_stat_wal_receiver"
Expanded display is on.
(0 rows)
/postgresql/app/bin/postmaster -p 7777 -D /postgresql/data &
[pgsql@cncq081298 ~]$ psql -p 7777 -c "\x" -c "select * from pg_stat_replication"
Expanded display is on.
-[ RECORD 1 ]----+------------------------------
pid | 205028
usesysid | 16505
usename | repuser
application_name | walreceiver
client_addr |
client_hostname |
client_port | -1
backend_start | 2024-01-30 19:23:07.59264+08
backend_xmin |
state | streaming
sent_lsn | 1/1A000320
write_lsn | 1/1A000320
flush_lsn | 1/1A000320
replay_lsn | 1/1A000320
write_lag | 00:00:00.000108
flush_lag | 00:00:00.000108
replay_lag | 00:00:00.000108
sync_priority | 0
sync_state | async
reply_time | 2024-01-30 19:23:07.694661+08
[pgsql@cncq081298 ~]$ psql -p 8888 -c "\x" -c "select * from pg_stat_wal_receiver"
Expanded display is on.
-[ RECORD 1 ]---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 205027
status | streaming
receive_start_lsn | 1/1A000000
receive_start_tli | 1
written_lsn | 1/1A000320
flushed_lsn | 1/1A000320
received_tli | 1
last_msg_send_time | 2024-01-30 19:23:07.59427+08
last_msg_receipt_time | 2024-01-30 19:23:07.594319+08
latest_end_lsn | 1/1A000320
latest_end_time | 2024-01-30 19:23:07.591329+08
slot_name |
sender_host | /tmp
sender_port | 7777
conninfo | user=repuser passfile=/home/pgsql/.pgpass channel_binding=prefer dbname=replication port=7777 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any
[pgsql@cncq081298 ~]$
再次查看备库日志
2024-01-30 19:31:19.699 CST [219818] FATAL: could not connect to the primary server: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.7777"?
2024-01-30 19:31:24.705 CST [219832] FATAL: could not connect to the primary server: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.7777"?
2024-01-30 19:31:29.709 CST [220095] FATAL: could not connect to the primary server: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.7777"?
2024-01-30 19:31:34.715 CST [220275] LOG: started streaming WAL from primary at 1/1A000000 on timeline 1
可以看到, 主库停止之后, 流复制断了,
但是从库每次间隔 5 秒会尝试连接主库, 看看是否可以正常连接上主库
当发现主库可以正常连接之后, 会尝试重新建立流复制状态
最后修改时间：2024-01-30 19:53:46
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
