POSTGRESQL 12 流复制主备切换

2022-09-18 10:34:28

1、异步流复制简单配置示例

服务器规划

192.168.0.130(主库)

192.168.0.131(备库)

192.168.0.132(备库)

主节点配置

1、初始化数据集簇

./initdb ../data-primary -U postgres

2、启动服务，创建用户

ALTER USER postgres WITH PASSWORD ‘123456‘;
CREATE ROLE repl WITH PASSWORD ‘123456‘ REPLICATION LOGIN;

3、配置 postgresql.conf

listen_addresses=‘*‘
archive_mode=on
archive_command=‘cp "%p" "/data/pg12/archivedir"‘
max_wal_senders=10
max_replication_slots=10
wal_level=replica
wal_keep_segments=512
hot_standby=on
synchronous_commit=on
synchronous_standby_names=‘any 1(*)‘
logging_collector = on
log_destination = ‘csvlog‘
log_truncate_on_rotation = on
log_filename = ‘postgresql-%w.log‘
log_connections = off
log_disconnections = off
log_error_verbosity = verbose
log_statement = ddl
log_min_duration_statement = 3s
log_checkpoints = on

4、配置 pg_hba.conf

host    replication     all             0.0.0.0/0            trust

5、重启服务

node1备节点配置

1、删除备机data目录

rm -rf /data/pg12/pgdata

2、基础备份

pg_basebackup -h 192.168.0.130 -p 5432 -U repl -D /data/pg12/pgdata/ -v -P -R -X stream -C -S node1

在pgdata路径下会生成：standby.signal 文件

在pgdata路径下： postgresql.auto.conf 文件会添加连接信息

[sun@localhost data-standby01]$ cat postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = ‘user=repl password=123456 host=192.168.0.130 port=5432 sslmode=disable sslcompression=0 gssencmode=disable target_session_attrs=any‘

2、启动备库（同一服务器实验，需要先修改 postgresql.conf 的端口）

node2备节点配置

1、基础备份

pg_basebackup -h 192.168.0.130 -p 5432 -U repl -D /data/pg12/pgdata/ -v -P -R -X stream -C -S node2

在pgdata路径下会生成：standby.signal 文件

在pgdata路径下： postgresql.auto.conf 文件会添加连接信息

[sun@localhost data-standby02]$ cat postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = ‘user=repl password=123456 host=192.168.0.130 port=5432 sslmode=disable sslcompression=0 gssencmode=disable target_session_attrs=any‘

2、启动备库（同一服务器实验，需要先修改 postgresql.conf 的端口）

主库进行查询

2、主备切换

1、关闭主库130

2、在备库131上使用函数进行主备切换，默认为true,等待60秒

3、停止备库131

[postgres@localhost pgdata]$ psql -Upostgres -dpostgres
psql (12.2)
Type "help" for help.

postgres=# SELECT pg_promote(true,60);
pg_promote
------------
t
(1 row)

postgres=# \q
[postgres@localhost pgdata]$ pg_ctl stop -D $PGDATA
waiting for server to shut down.... done
server stopped

4、停132备库，并删除pgdata目录下的standby.signal

5、修改131备机pgdata目录postgresql.auto.conf内容

6、注释131的pgdata目录下postgresql.auto.conf里面的内容

7、130上pgdata目录添加standby.signal

[postgres@localhost pgdata]$ ls
backup_label.old current_logfiles log pg_dynshmem pg_ident.conf pg_multixact pg_replslot pg_snapshots pg_stat_tmp pg_tblspc PG_VERSION pg_xact postgresql.conf
base global pg_commit_ts pg_hba.conf pg_logical pg_notify pg_serial pg_stat pg_subtrans pg_twophase pg_wal postgresql.auto.conf postmaster.opts
[postgres@localhost pgdata]$ touch standby.signal
[postgres@localhost pgdata]$ ls
backup_label.old global pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_wal postgresql.conf
base log pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase pg_xact postmaster.opts
current_logfiles pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans PG_VERSION postgresql.auto.conf standby.signal
[postgres@localhost pgdata]$

8、取消130上postgresql.auto.conf里面的注释

8、分别启动三台服务器，131、130、132

切换成功

注意：如果新主机下select * from pg_stat_repliation查询不到备机内容，则需要在新主机下创建两个备机的复制曹

select * from sys_create_physical_replication_slot(‘node1‘)

select * from sys_create_physical_replication_slot(‘node2‘)

码农公寓