PostgreSQL主从同步配置及切换教程

为了实现PostgreSQL数据库对于应用层的透明化访问，我们可以使用浮动IP地址。这样即使在数据库执行主备切换时，应用层依然能够通过浮动 IP 地址访问数据库。具体来说，后端数据库在主备切换前后，应用系统无需进行任何代码或配置的更改。本文将基于Red Hat 7.9和PostgreSQL 13.2的环境介绍实现此方案的基本思路和流程。

一、环境搭建

1、主库

真实 IP：192.168.1.106；
浮动 IP：192.168.1.126；
主机名：pgprimary；
数据库：安装并初始化PostgreSQL数据库。

2、备库

真实 IP：192.168.1.116；
浮动 IP：192.168.1.126；
主机名：pgstandby；
数据库：仅安装 PostgreSQL 数据库软件。

二、配置流程

通常情况下，浮动 IP 192.168.1.126 会在主库上运行。当主库出现故障时，手动执行数据库的主备切换，备库将成为新主库，并启用浮动 IP。然后原主库将被修复和重新配置为新主库的备库。

主库：

[root@Centos ~]# cp /etc/sysconfig/network-scripts/ifcfg-ens33 /etc/sysconfig/network-scripts/ifcfg-ens33:1
[root@Centos ~]# cat /etc/sysconfig/network-scripts/ifcfg-ens33:1
TYPE="Ethernet" PROXY_METHOD="none" BROWSER_ONLY="no" BOOTPROTO="none" DEFROUTE="yes" IPV4_FAILURE_FATAL="no" IPV6INIT="yes" IPV6_AUTOCONF="yes" IPV6_DEFROUTE="yes" IPV6_FAILURE_FATAL="no" IPV6_ADDR_GEN_MODE="stable-privacy" NAME="ens33" UUID="02bc077a-e6b9-492b-a5a3-91bbd808b4e9" DEVICE="ens33:1" ONBOOT="yes" IPADDR="192.168.1.116" PREFIX="24" NM_CONTROLLED=no DNS1="114.114.114.114" IPV6_PRIVACY="no"

三、执行流复制的主备配置流程

1、主库创建流复制用户

[postgres@pgprimary data]$ psql 
postgres=# CREATE ROLE replica login replication encrypted password 'replica'; CREATE ROLE postgres=#

2、主库修改 “pg_hba.conf” 文件

编辑 “pg_hba.conf” 文件以允许备库 IP 通过复制用户访问数据库：

[postgres@pgprimary data]$ vi pg_hba.conf 
# "local" is for Unix domain socket connections only 
local all all trust 
# IPv4 local connections: 
host all all 127.0.0.1/32 trust 
host all all 0.0.0.0/0 md5 
# IPv6 local connections: 
host all all ::1/128 trust 
# Allow replication connections from localhost, by a user with the 
# replication privilege. 
local replication all trust 
host replication all 127.0.0.1/32 trust 
host replication all ::1/128 trust 
host replication replica 192.168.1.116/32 md5

以上最后一行配置了 “replica” 用户可以从备库 IP 192.168.1.116 访问主库。

3、备库执行主库基础备份

在备库上执行基础备份：

[postgres@pgstandby data]$ pwd 
/data/postgres/13.2/data  
[postgres@pgstandby 13.2]$ pg_basebackup -h 192.168.1.106 -p 5432 -U replica --password -X stream -Fp --progress -D $PGDATA -R 
Password: 
40128/40128 kB (100%), 2/2 tablespaces

注意：备份选项中带有 “-R” 参数。

4、备库启动

完成备份后，备库可以通过以下命令启动 PostgreSQL：

[postgres@pgstandby 13.2]$ pg_ctl start

此时，能看到备库上自动生成了 “standby.signal” 文件，并且在 “$PGDATA” 路径下，数据库自动帮我们配置了关于流复制的主库信息：

[postgres@pgstandby 13.2]$ cat data/postgresql.auto.conf 
Do not edit this file manually! 
It will be overwritten by the ALTER SYSTEM command. 
primary_conninfo = 'user=replica password=replica channel_binding=disable host=192.168.1.106 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

如果没有使用 “-R” 选项进行备份，也可以手动创建 “standby.signal” 文件，并在”postgresql.auto.conf” 中配置主库的信息。

5、备库数据库进程信息

可以通过以下命令查看备库上数据库进程的信息：

[postgres@pgstandby data]$ ps -ef|grep postgres

在备库上可以看到 “walreceiver” 进程，正在读取日志并执行恢复。

6、主库数据库进程信息

在主库上执行以下命令，以查看数据库进程信息：

[postgres@pgprimary data]$ ps -ef | grep postgres

主库上将看到后台进程 “walsender”，正在向 “replica 192.168.1.116” 推送日志信息。

7、主库查看数据库复制信息

执行以下 SQL 查询以查看主库的数据库复制状态：

[postgres@pgprimary data]$ psql -xc "SELECT * FROM pg_stat_replication"

四、主备切换及注意事项

如果由于意外或故障导致主库不可用，可以将备库提升为主库并提供服务。此后，根据具体情况，可以选择重建原主库，或者在故障恢复后将其重新作为备库同步数据。

1、停止主库并模拟故障

通过以下命令停止原主库：

[postgres@pgprimary ~]$ ps -ef|grep postgres
[postgres@pgprimary ~]$ pg_ctl status 
pg_ctl: server is running (PID: 20582) 
/data/postgres/13.2/bin/postgres  
[postgres@pgprimary ~]$ pg_ctl stop -m fast 
[postgres@pgprimary ~]$ ps -ef|grep postgres

在执行 “pg_ctl stop -m fast” 之后，所有数据库后台进程将退出。

PostgreSQL 主备切换与数据库配置教程

2、备库提升为新主库并对外提供服务

首先，我们需要通过以下命令确认备库的状态，并将其提升为新的主库：

[postgres@pgstandby 13.2]$ ps -ef|grep postgres 
[postgres@pgstandby 13.2]$ pg_ctl status 
pg_ctl: server is running (PID: 19107) 
/data/postgres/13.2/bin/postgres  
[postgres@pgstandby 13.2]$ pg_ctl promote
[postgres@pgstandby 13.2]$ ps -ef|grep postgres

注意：

提升备库为新主库的命令是 “pg_ctl promote”。提升完成后，在后台进程中，我们将不再看到 “startup recovering” 和 “walreceiver streaming” 进程，取而代之的是 “walwriter” 写进程；
“$PGDATA/standby.signal” 文件将自动消失，这表明 PostgreSQL 现在识别为新主库。

3、新主库修改 “pg_hba.conf” 文件

在新主库（原备库，IP 为 192.168.1.116）的 “$PGDATA/pg_hba.conf” 文件中，添加允许原主库（192.168.1.106）通过 “replica” 用户访问数据库的条目：

host replication all 192.168.1.106/32 md5

注意：如果主从环境的数据库没有配置浮动 IP，这里的 IP 地址应直接填写原主库的实际 IP 地址。

4、为原主库创建 “$PGDATA/standby.signal” 文件

接下来，在原主库上创建 “standby.signal” 文件，以确保该节点在启动时将作为备库运行：

[postgres@pgprimary ~]$ cd $PGDATA 
[postgres@pgprimary data]$ touch standby.signal  
[postgres@pgprimary data]$ pwd /data/postgres/13.2/data 
[postgres@pgprimary data]$ ll standby.signal 
-rw-rw-r--. 1 postgres postgres 0 May 27 16:17 standby.signal

注意：这一操作极为重要。如果不创建该文件，原主库在重新启动时将成为独立的主库，脱离主从数据库环境。

5、修改 “$PGDATA/postgresql.auto.conf” 文件

在原主库的 “$PGDATA/postgresql.auto.conf” 文件中，添加主库连接信息：

[postgres@pgprimary data]$ cat postgresql.auto.conf 
# Do not edit this file manually! 
# It will be overwritten by the ALTER SYSTEM command. 
[postgres@pgprimary data]$ vim postgresql.auto.conf 
[postgres@pgprimary data]$ cat postgresql.auto.conf 
# Do not edit this file manually! 
# It will be overwritten by the ALTER SYSTEM command. 
primary_conninfo='user=replica password=replica host=192.168.1.116 port=5432'

6、启动原主库，成为新的备库

最后，通过以下命令启动原主库，使其成为新的备库：

[postgres@pgprimary data]$ pg_ctl start -l /home/postgres/startup.log 
waiting for server to start.... done 
server started 
[postgres@pgprimary data]$ ps -ef|grep postgres

五、测试数据同步状态

[postgres@pgstandby ~]$ psql postgres=# select version(); 
#查看所有数据库信息 postgres=# \l+   
postgres=# \du 
postgres=# \c postgres 
You are now connected to database "postgres" as user "postgres". 
postgres=# \c testdb 
You are now connected to database "testdb" as user "postgres". 
testdb=# \d

在新主库（原备库，192.168.1.116）上插入数据以验证同步：

postgres=# select * from pg_test0524; 
postgres=# insert into pg_test0524 values(2,'shanghai'); 
INSERT 0 1 
postgres=# insert into pg_test0524 values(3,'tianjin'); 
INSERT 0 1

在原主库上检查同步状态：

[root@pgprimary ~]# su - postgres 
Last login: Fri May 27 17:17:10 CST 2022 on pts/0 
[postgres@pgprimary ~]$ psql 
psql (13.2) Type "help" for help.  
postgres=# select * from pg_test0524; 
postgres=#

六、手动切换回原主库

1、查看主库状态

在备库（192.168.1.116）上检查状态：

[postgres@pgstandby data]$ pg_ctl status 
pg_ctl: server is running (PID: 18137) 
/data/postgres/13.2/bin/postgres "-D" "/data/postgres/13.2/data" 
[postgres@pgstandby data]$ echo $PGDATA 
/data/postgres/13.2/data 
[postgres@pgstandby data]$ ps -ef|grep postgres 
[postgres@pgstandby data]$ pg_ctl status 
pg_ctl: server is running (PID: 18137) 
/data/postgres/13.2/bin/postgres "-D" "/data/postgres/13.2/data"

2、停止主库，模拟故障

[postgres@pgstandby data]$ pg_ctl stop -m fast 
waiting for server to shut down.... done 
server stopped 
[postgres@pgstandby data]$ ps -ef|grep postgres

通过pg_ctl stop -m fast停止主库(192.168.30.116)之后，数据库后台进程都没有了

3、查看备库的状态

[postgres@pgprimary data]$ ps -ef|grep postgres 
[postgres@pgprimary data]$ pg_ctl status 
pg_ctl: server is running (PID: 18417) 
/data/postgres/13.2/bin/postgres "-D" "/data/postgres/13.2/data"

4、备注提升为主库pg_ctl promote

[postgres@pgprimary data]$ pg_ctl promote 
waiting for server to promote.... done 
server promoted 
[postgres@pgprimary data]$ ps -ef|grep postgres

5、新主库修改pg_hba.conf文件，加入备库访问

[postgres@pgprimary data]$ cat $PGDATA/pg_hba.conf 
# PostgreSQL Client Authentication Configuration File

6、新备库创建文件 standby.signal

[postgres@pgstandby data]$ cd $PGDATA 
[postgres@pgstandby data]$ pwd 
/data/postgres/13.2/data 
[postgres@pgstandby data]$ touch standby.signal

7、查看连接主库的信息

[postgres@pgstandby data]$ vim postgresql.auto.conf 
[postgres@pgstandby data]$ cat postgresql.auto.conf 
# Do not edit this file manually! 
# It will be overwritten by the ALTER SYSTEM command. 
primary_conninfo = 'user=replica password=replica channel_binding=disable host=192.168.1.106 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

8、启动原主库（第一次切换后的主库），变为新备库

[postgres@pgstandby data]$ pg_ctl start -l /home/postgres/startup.log 
waiting for server to start.... done 
server started 
[postgres@pgstandby data]$ ps -ef|grep postgres

可以看到恢复进程开始工作，wal 进程 postgres: walreceiver streaming 0/9000F10

9、测试数据同步

主库插入数据：

[postgres@pgprimary data]$ psql 
psql (13.2) 
Type "help" for help. 
postgres-# \d 
postgres-# \c 
You are now connected to database "postgres" as user "postgres". 
postgres=# select * from pg_test0524; 
postgres=# insert into pg_test0524 values(7,'langfang'); 
INSERT 0 1

备库查看同步情况：

[postgres@pgstandby data]$ psql 
psql (13.2) 
Type "help" for help. 
postgres=# \d 
postgres=# \c 
You are now connected to database "postgres" as user "postgres". 
postgres=# select * from pg_test0524;