Postgresql12主从配置及切换

一、主从安装

1、依据“Centos7安装最新版Postgresql”操作，注意个别细节选择主从配置

2、注意：从服务器只能查询，不能写入

3、主库IP=103，备库IP=104

 

二、主服务器

1、添加pg用于同步的用户名

su - postgres

psql

# 创建具有复制流操作的用户：replica

CREATE ROLE replica login replication encrypted password '123456';

 

1、修改配置文件：pg_hba.conf，信任从服务器访问

1）vi /app/pgsql/data/pg_hba.conf

文件尾部添加信任的从服务器

# replica为postgres用户

# XX.XX.XX.XX为从节点的完整IP，如果为网段配置就不是32了，md5为允许密码验证,trust为免密

# TYPE DATABASE USER ADDRESS METHOD

host replication replica 192.168.100.104/32 trust

 

2、修改配置文件：postgresql.conf

vi /app/pgsql/data/postgresql.conf

# 新增或修改下列属性设置（使用命令“/”来查找，否则眼花）

listen_addresses = '*' # （修改）监听所有IP

max_connections = 100 # （修改）最大连接数，据说从机需要大于或等于该值

 

archive_mode = on # （修改）开启归档

archive_command = 'test ! -f /app/pgsql/pg_archive/%f && cp %p /app/pgsql/pg_archive/%f'

 

wal_level = replica # （添加）

wal_keep_segments = 16 # （修改）暂时还不知道设置为多大合适

wal_sender_timeout = 60s # （修改）

 

3、重启服务

systemctl restart postgresql-12

 

4、在从节点上验证访问

psql -h 192.168.100.103 -U postgres

# 如果发现不知道密码或密码不正确，修改postgres用户密码（数据库）

ALTER USER postgres WITH PASSWORD '你的密码';

 

三、从服务器

1、停止服务

systemctl stop postgresql-12

 

2、切换用户（一定要）

su - postgres

 

3、清空数据文件夹（是这样的，没错）

rm -rf /app/pgsql/data/*

cd /app/pgsql/data

 

4、从主节点获取数据

# 使用replica用户，从主节点获取备份数据（这条命令建议照抄，有兴趣在深究内部参数）

pg_basebackup -h 192.168.100.103 -p 5432 -U replica -Fp -Xs -Pv -R -D /app/pgsql/data

 

5、编辑standby.signal文件（就在数据文件夹内，以此标识从节点，当从节点提升为主节点后会自动删除）

# 添加

standby_mode = 'on'

 

6、修改postgresql.conf文件

vi /app/pgsql/data/postgresql.conf

primary_conninfo = 'host=192.168.100.103 port=5432 user=replica password=123456'

recovery_target_timeline = latest # 默认

max_connections = 120 # 大于等于主节点，正式环境应当重新考虑此值的大小

hot_standby = on

max_standby_streaming_delay = 30s

wal_receiver_status_interval = 10s

hot_standby_feedback = on

 

7、重新启动从节点

systemctl start postgresql-12

 

四、验证主从

方法一：

登录主节点数据库执行如下命令

su postgres

psql

select client_addr,sync_state from pg_stat_replication;

结果如下：

postgres=# select client_addr,sync_state from pg_stat_replication;

client_addr | sync_state

---------------+------------

192.168.100.104 | async

(1 row)

说明104服务器是从节点，在接收流，而且是异步流复制

 

方法二：

在主节点与从节点分别执行如下命令

ps -ef | grep postgres

可以看到主节点有wal receiver进程

postgres 2638 2606 0 17:12 ? 00:00:00 postgres: walsender replica 192.168.100.104(51504) streaming 0/7000660

可以看到从节点有wal receiver进程

postgres 1960 1953 0 17:12 ? 00:00:02 postgres: walreceiver streaming 0/7000660

 

 

五、主备切换（主库出现故障时）

当主库出现故障时，我们需要将备库提升为主库进行读写操作。

0、在pg12之前我们一般使用2种方式：

1）pg_ctl 方式: 在备库主机执行 pg_ctl promote shell 脚本

2）触发器文件方式: 备库配置 recovery.conf 文件的 trigger_file 参数，之后在备库主机上创建触发器文件

 

1、pg12开始新增了一个pg_promote()函数，让我们可以通过SQL命令激活备库。

pg_promote()语法：

pg_promote(wait boolean DEFAULT true, wait_seconds integer DEFAULT 60)

两个参数：

wait: 表示是否等待备库的 promotion 完成或者 wait_seconds 秒之后返回成功，默认值为 true。

wait_seconds: 等待时间，单位秒，默认 60

 

2、切换举例：

1）主库操作：关闭主库，模拟主库故障：

systemctl stop postgresql-12

2）备库操作：激活备库：

su postgres

psql

select pg_promote(true,60);

 

4、验证：

/usr/pgsql-12/bin/pg_controldata /app/pgsql/data

主备库英文显示如下：

Database cluster state: in production

Database cluster state: in archive recovery

主备库中文显示如下：

数据库簇状态: 在运行中

数据库簇状态: 正在归档恢复

 

 

六、原主库修复后降为备库（其实大部分操作与建立备库时一样，只是修改几个IP地址）

1、新主库(104)：

# vi /app/pgsql/data/pg_hba.conf

host replication replica 192.168.100.103/32 trust

# systemctl restart postgresql-12

 

2、新备库(103)

# systemctl stop postgresql-12

# su - postgres

# rm -rf /app/pgsql/data/*

# cd /app/pgsql/data

# pg_basebackup -h 192.168.100.104 -p 5432 -U replica -Fp -Xs -Pv -R -D /app/pgsql/data

# vi standby.signal

# 添加

standby_mode = 'on'

 

vi /app/pgsql/data/postgresql.conf

primary_conninfo = 'host=192.168.100.104 port=5432 user=replica password=123456'

 

# systemctl start postgresql-12

 

七、再次将老主库切回主库

1、当前备库：

> 停止服务

> 删除“/app/pgsql/data/standby.signal”文件

> 启动服务

2、当前主库：

> 停止服务

> 以“postgres”用户创建“/app/pgsql/data/standby.signal”文件，添加内容：standby_mode = 'on'

> 启动服务