PostgreSQL 搭建主从同步实现读写分离
PostgreSQL About 6,654 words主库配置
设置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 16 编译安装:https://www.zhangbj.com/p/1699.html
PostgreSQL 搭建级联从库:https://www.zhangbj.com/p/1796.html
PostgreSQL 参数调整作为 OLAP 统计数据库:https://www.zhangbj.com/p/1797.html
————        END        ————
Give me a Star, Thanks:)
https://github.com/fendoudebb/LiteNote扫描下方二维码关注公众号和小程序↓↓↓