PostgreSQL 搭建主从同步实现读写分离

主库配置

设置listen_addresses，wal_level

alter system set listen_addresses = '*';
alter system set wal_level = replica;
alter system set shared_preload_libraries = pg_stat_statements, auto_explain;
alter system set logging_collector = on;

创建复制用户

CREATE USER rep_user REPLICATION PASSWORD '123456';

查看链接规则

select * from pg_hba_file_rules;

在pg_hba.conf中添加

# 允许从库通过replica用户连接主库
host    replication     rep_user        192.168.1.1/32          md5

重启主库

/home/postgres/16.1/bin/pg_ctl restart

从库配置

启动从库

/home/postgres/16.1/bin/pg_ctl start

从库默认提供读服务

hot_standby = on 
max_standby_streaming_delay = "30s" 数据流备份的最大延迟时
wal_receiver_status_interval = "10s" 多久向主报告一次从的状态
hot_standby_feedback = off 如果有错误的数据复制，是否向主进行反馈

开启反馈机制

alter system set hot_standby_feedback = on;

热加载新配置

select pg_reload_conf();

pg_basebackup拉取基线数据

pg_basebackup --help

使用pg_basebackup拉取192.168.0.1主库的基线数据

/home/postgres/16.1/bin/pg_basebackup -h 192.168.0.1 -U rep_user -Fp -Xs -P -v -R -D /home/postgres/16.1/data -l pgbackup20241105

参数含义

-F: 指定了输出的格式，支持p（原样输出）或者t（tar格式输出）
-X: 表示备份开始后，启动另一个流复制连接从主库接收WAL日志。
-P: 表示允许在备份过程中实时的打印备份的进度。
-R: 表示会在备份结束后自动生成recovery.conf文件，这样也就避免了手动创建。
-D: 指定把备份写到哪个目录，这里尤其要注意一点就是做基础备份之前从库的数据目录（/home/postgres/16.1/data）目录需要手动清空。
-l: 表示指定一个备份的标识，运行命令后看到如下进度提示就说明生成基础备份成功： 

输出

[postgresserver2 16.1]$ /home/postgres/16.1/bin/pg_basebackup -h 192.168.0.1 -U rep_user -Fp -Xs -P -v -R -D /home/postgres/16.1/data -l pgbackup20241105
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/E7000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_31944"
967616/967616 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/E7006130
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

查看主从状态

select pg_is_in_recovery();

查看同步状态

select * from pg_stat_replication;

输出

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 31968
usesysid         | 19040
usename          | rep_user
application_name | walreceiver
client_addr      | 192.168.1.1
client_hostname  | 
client_port      | 53894
backend_start    | 2024-11-04 19:06:11.781684+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/E96C3D70
write_lsn        | 0/E96C3D70
flush_lsn        | 0/E96C3D70
replay_lsn       | 0/E96C3D70
write_lag        | 00:00:00.001384
flush_lag        | 00:00:00.002058
replay_lag       | 00:00:00.00216
sync_priority    | 0
sync_state       | async
reply_time       | 2024-11-04 22:41:15.552766+08

查看状态

查看主库发送wal状态

[postgres@server1 ~]$ ps aux | grep postgres | grep walsender
postgres 31968  0.0  0.0 342748  4244 ?        Ss   19:06   0:00 postgres: walsender rep_user 172.16.0.13(53894) streaming 0/E8068800

查看从库接收wal状态

[postgres@server2 ~]$ ps aux | grep postgres
postgres 16395  0.0  0.1 322104  4860 ?        Ss   19:06   0:00 postgres: startup recovering 0000000100000000000000E8
postgres 16396  0.0  0.0 326180  2868 ?        Ss   19:06   0:00 postgres: walreceiver streaming 0/E80D9C48

查看从库数据目录

[root@server2 data]# ll
total 348
-rw------- 1 postgres postgres    218 Jul 28 19:04 backup_label.old
-rw------- 1 postgres postgres 208491 Jul 28 19:04 backup_manifest
drwx------ 6 postgres postgres   4096 Jul 28 19:04 base
-rw------- 1 postgres postgres     44 Jul 28 19:06 current_logfiles
drwx------ 2 postgres postgres   4096 Jul 28 19:09 global
drwx------ 2 postgres postgres   4096 Jul 28 19:06 log
drwx------ 2 postgres postgres   4096 Jul 28 19:04 pg_commit_ts
drwx------ 2 postgres postgres   4096 Jul 28 19:04 pg_dynshmem
-rw------- 1 postgres postgres   5991 Jul 28 19:04 pg_hba.conf
-rw------- 1 postgres postgres   2640 Jul 28 19:04 pg_ident.conf
drwx------ 4 postgres postgres   4096 Jul 28 22:36 pg_logical
drwx------ 4 postgres postgres   4096 Jul 28 19:04 pg_multixact
drwx------ 2 postgres postgres   4096 Jul 28 19:04 pg_notify
drwx------ 2 postgres postgres   4096 Jul 28 19:04 pg_replslot
drwx------ 2 postgres postgres   4096 Jul 28 19:04 pg_serial
drwx------ 2 postgres postgres   4096 Jul 28 19:04 pg_snapshots
drwx------ 2 postgres postgres   4096 Jul 28 19:04 pg_stat
drwx------ 2 postgres postgres   4096 Jul 28 19:06 pg_stat_tmp
drwx------ 2 postgres postgres   4096 Jul 28 19:11 pg_subtrans
drwx------ 2 postgres postgres   4096 Jul 28 19:04 pg_tblspc
drwx------ 2 postgres postgres   4096 Jul 28 19:04 pg_twophase
-rw------- 1 postgres postgres      3 Jul 28 19:04 PG_VERSION
drwx------ 3 postgres postgres   4096 Jul 28 21:26 pg_wal
drwx------ 2 postgres postgres   4096 Jul 28 19:04 pg_xact
-rw------- 1 postgres postgres    851 Jul 28 19:04 postgresql.auto.conf
-rw------- 1 postgres postgres  29708 Jul 28 19:04 postgresql.conf
-rw------- 1 postgres postgres     33 Jul 28 19:06 postmaster.opts
-rw------- 1 postgres postgres     83 Jul 28 19:06 postmaster.pid
-rw-rw-r-- 1 postgres postgres    185 Jul 28 19:04 serverlog
-rw------- 1 postgres postgres      0 Jul 28 19:04 standby.signal

查看从库配置

[root@server2 data]# cat postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
shared_preload_libraries = 'pg_stat_statements, auto_explain'
logging_collector = 'on'
log_min_duration_statement = '250ms'
log_line_prefix = '%m [%p] %q%u@%d/%a '
log_rotation_age = '30d'
log_rotation_size = '50MB'
log_connections = 'on'
log_disconnections = 'on'
auto_explain.log_min_duration = '250ms'
auto_explain.log_analyze = 'on'
auto_explain.log_verbose = 'on'
listen_addresses = '*'
log_hostname = 'on'
log_statement = 'none'
wal_level = 'replica'
primary_conninfo = 'user=rep_user password=123456 channel_binding=disable host=192.168.0.205 port=5432 sslmode=disable sslcompression=0 sslcertmode=disable sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'

备注

PostgreSQL 16版本中standby.signal文件为空。

参考

https://www.modb.pro/db/618917

https://www.postgresql.org/docs/16/runtime-config-replication.html

PostgreSQL Release Notes: https://www.postgresql.org/about/press/presskit16/zh/

编译安装 PostgreSQL

https://www.zhangbj.com/p/1699.html