PostgreSQL14流复制

条件

搭建步骤

配置主库

开启归档

参见文章PostgreSQL14开启归档

创建复制用户

createuser -U postgres repuser -P -c 5 --replication

密码：replication

修改主机的配置

• 修改postgresql.conf文件

wal_keep_size=128
wal_level=hot_standby ##关键行
archive_timeout='900s'
max_wal_senders=3
archive_mode = 'ON'
archive_command = 'cp %p /var/lib/postgresql/14_bak/archive%f'


log_destination=csvlog
logging_collector=on
log_directory=pg_log
log_rotation_age=120
log_rotation_size='1GB'
log_truncate_on_rotation=on

• 修改pg_hba.conf文件

hba文件的位置

postgres=# SHOW hba_file;
              hba_file               
-------------------------------------
 /etc/postgresql/14/main/pg_hba.conf
(1 row)

添加一行

host     replication     repuser         192.168.0.120/32        md5

重启主服务器使配置生效

postgres@zjarn2021-Qh:~$ /usr/lib/postgresql/14/bin/pg_ctl -D /var/lib/postgresql/14/main restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2022-01-25 09:18:26.085 CST [448611] LOG:  redirecting log output to logging collector process
2022-01-25 09:18:26.085 CST [448611] HINT:  Future log output will appear in directory "pg_log".
 done
server started

创建备份服务器

运行备份程序

备份实用程序 pg_basebackup 会将文件从主服务器上的数据目录复制到备用服务器上的同一目录。

postgres@zjarn2021-Qh:~/14$ pg_basebackup -h 127.0.0.1 -p 5432 -D /var/lib/postgresql/14/standby1 -U repuser -P -v -R -X stream -C -S pgstandby1
Password:
# 密码replication

修改备机的配置

• postgresql.conf

postgres@zjarn2021-Qh:~/14/standby1$ cat /var/lib/postgresql/14/standby1/postgresql.conf 
wal_keep_size='1GB'
wal_level=hot_standby
archive_timeout='900s'
max_wal_senders=3
archive_mode = 'ON'
archive_command = 'cp %p /var/lib/postgresql/14_bak/archive%f'


log_destination=csvlog
logging_collector=on
log_directory=pg_log
log_rotation_age=120
log_rotation_size='1GB'
log_truncate_on_rotation=on

listen_addresses='*'
port=5433

• pg_hba.conf

cp /etc/postgresql/14/main/pg_hba.conf /var/lib/postgresql/14/standby1/pg_hba.conf

启动备机

postgres@zjarn2021-Qh:~/14/standby1$ /usr/lib/postgresql/14/bin/pg_ctl -D /var/lib/postgresql/14/standby1 start
waiting for server to start....2022-01-25 06:03:48.876 GMT [457816] LOG:  redirecting log output to logging collector process
2022-01-25 06:03:48.876 GMT [457816] HINT:  Future log output will appear in directory "pg_log".
 done
server started

验证主备同步

备库验证

postgres@zjarn2021-Qh:~/14/standby1$ psql -p 5433
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=# show transaction_read_only ;
 transaction_read_only 
-----------------------
 on
(1 row)

postgres=# 

验证streaming replication
一旦主从复制链接上，就可以在从服务器上看到一个WAL receiver进程，用如下视图查看

ostgres@zjarn2021-Qh:~/14/standby1$ psql -c "\x" -c "SELECT * FROM pg_stat_wal_receiver;"
Expanded display is on.
(0 rows)

postgres@zjarn2021-Qh:~/14/standby1$ psql -p 5433 -c "\x" -c "SELECT * FROM pg_stat_wal_receiver;"
Expanded display is on.
-[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 457822
status                | streaming
receive_start_lsn     | 0/2F000000
receive_start_tli     | 1
written_lsn           | 0/30000000
flushed_lsn           | 0/30000000
received_tli          | 1
last_msg_send_time    | 2022-01-25 06:54:25.749364+00
last_msg_receipt_time | 2022-01-25 06:54:25.749512+00
latest_end_lsn        | 0/30000000
latest_end_time       | 2022-01-25 06:03:48.976711+00
slot_name             | pgstandby1
sender_host           | 127.0.0.1
sender_port           | 5432
conninfo              | user=repuser password=******** channel_binding=prefer dbname=replication host=127.0.0.1 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

主库验证

在主库上有一个WAL sender进程，state是streaming， sync_state是async,如下：

postgres@zjarn2021-Qh:~/14/main$ psql -c "\x" -c "SELECT * FROM pg_stat_replication;"
Expanded display is on.
-[ RECORD 1 ]----+------------------------------
pid              | 457823
usesysid         | 16468
usename          | repuser
application_name | walreceiver
client_addr      | 127.0.0.1
client_hostname  | 
client_port      | 45518
backend_start    | 2022-01-25 14:03:48.952411+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/30000000
write_lsn        | 0/30000000
flush_lsn        | 0/30000000
replay_lsn       | 0/30000000
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2022-01-25 17:16:24.108442+08

async表示异步复制，后面将会将如何调整为同步复制

参考

pg_receivewal介绍：
NEVER LOSE A POSTGRESQL TRANSACTION WITH PG_RECEIVEWAL

hot_standby官方文档
Hot Standby