两个主机节点
10.0.0.7 maser 10.0.0.8 standby
主库master配置
创建复制账户的用户,并在授权pg_hba.conf文件中授权 编译安装的数据目录自定义,yum安装的默认data目录是在/var/lib/pgsql/14/data/pg_hba.conf。
[postgres@master ~]$ psql
postgres=#create role repluser with replication login password '123456';
修改主配置文件postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;
port = 5432 # (change requires restart)
archive_mode = on # enables archiving; off, on, or always
archive_command = '[ ! -f /archive/%f ] && cp %p /archive/%f' 这条配置要配合建目录授权
logging_collector = on # Enable capturing of stderr and csvlog 开启运行日志
log_destination = 'csvlog' # Valid values are combinations of 日志格式
archive_mode = on # enables archiving; off, on, or always
archive_command = '[ ! -f /archive/%f ] && cp %p /archive/%f' # command to use to archive a logfile segment 开启归档 需要配合建目录 并赋权
archive_command = 'scp %p postgres@10.0.0.200:/pgsql/backup/%f' 也可以用scp远程备份 基于key验证
hot_standby = on #对主库无影响,用于将来可能会成为从库,这台机器不仅仅是用于数据归档,也用于数据查询,在从库上配置此项后为只读
配置完成后重启服务
[postgres@localhost ~]$pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2022-11-21 22:38:57.210 CST [19520] LOG: redirecting log output to logging collector process
2022-11-21 22:38:57.210 CST [19520] HINT: Future log output will appear in directory "log".
done
server started
从节点standby配置
停服 清空数据和归档文件
[postgres@standby ~]$pg_ctl stop
waiting for server to shut down.... done
server stopped
[root@standby ~]#rm -rf /pgsql/data/*
[root@standby ~]#rm -rf /archive/*
最好是用mv把数据移走,万一有问题还能恢复
备份主库数据到从库
[postgres@standby ~]$pg_basebackup -D /backup/ -Ft -Pv -U postgres -h 10.0.0.7 -R -p 5432
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/C000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_19586"
52231/52231 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/C000138
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
还原从主库备份过来的数据
[postgres@standby ~]$tar xf /backup/base.tar -C /pgsql/data/
[postgres@standby ~]$tar xf /backup/pg_wal.tar -C /archive/
修改从节点配置文件 (实际上从节点的配置文件时从主库拷贝过来的,只需要加上流复制账号就行 )
restore_command = 'cp /archive/%f %p'
primary_conninfo = 'host=10.0.0.7 port=5432 user=repluser password=123456'
启动服务
[postgres@standby ~]$pg_ctl restart
查看主库状态
[root@PGmaster ~]#pg_controldata
查看主库状态
select pid,state,client_addr,sync_priority,sync_state frompg_stat_replication;
postgres=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
-[ RECORD 1 ]-+----------
pid | 1935
state | streaming
client_addr | 10.0.0.8
sync_priority | 0
sync_state | async
SELECT pg_current_wal_insert_lsn(),* from pg_stat_replication;
postgres=# SELECT pg_current_wal_insert_lsn(),* from pg_stat_replication;
-[ RECORD 1 ]-------------+------------------------------
pg_current_wal_insert_lsn | 0/5000060
pid | 1935
usesysid | 10
usename | postgres
application_name | walreceiver
client_addr | 10.0.0.8
client_hostname |
client_port | 59834
backend_start | 2022-11-22 22:54:30.055296+08
backend_xmin |
state | streaming
sent_lsn | 0/5000060
write_lsn | 0/5000060
flush_lsn | 0/5000060
replay_lsn | 0/5000060
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2022-11-22 22:56:10.362681+08
查看备库状态