基于流复制的postgresql高可用

两个主机节点 

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

查看备库状态