PostgreSQL 13流复制主从同步配置，切换步骤

SQL数据库运维 2022-08-19

3261

点击蓝色字关注“SQL数据库运维”，回复“SQL”获取2TB学习资源！

本文档详细记录如何一步一步在两台Linux服务器上，搭建PostgreSQL数据库主从模式的高可用数据库环境，以及如何手工切换主备库，及其注意事项。

为了实现数据库对于应用层的透明，配合浮动IP来实现，即使数据库执行主备切换，应用层依然通过浮动IP来访问数据库。即：后端数据库执行主备切换前后，应用系统无需做任何更改。

搭建环境选择在Rehat 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数据库软件；

3 通常情况下，浮动IP 192.168.1.126运行在主库上，当主库出故障时，手工执行数据库的主备切换，备库成为新主库，然后新主库上启用浮动IP；

4 修复并重配原主库，使其成为新主库的备库；

主库

[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"

一、执行stream主备配置流程

1.1 主库创建流复制的用户

[postgres@pgprimary data]$ psql


postgres=# CREATE ROLE replica login replication encrypted password 'replica';
CREATE ROLE
postgres=#

1.2 主库修改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访问主库。

[postgres@pgstandby 13.2]$ ll
total 16
drwxrwxr-x. 2 postgres postgres 4096 Jan  7 14:12 bin
drwxrwxr-x. 6 postgres postgres 4096 Jan  7 14:12 include
drwxrwxr-x. 4 postgres postgres 4096 Jan  7 14:12 lib
drwxrwxr-x. 8 postgres postgres 4096 Jan  7 14:12 share

1.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选项。

1.4 备库就可以执行pg_ctl start启动了

这时，就可以看到备库服务器上自动生成了standby.signal文件。同时，也看到在$PGDATA路径下，数据库自动帮我们配置了关于流复制的主库的信息：

[postgres@pgstandby 13.2]$ pg_ctl start
[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，并在其内容中配置主库的信息。

[postgres@pgstandby 13.2]$ ll
total 20
drwxrwxr-x.  2 postgres postgres 4096 Jan  7 14:12 bin
drwx------. 19 postgres postgres 4096 Jan 10 21:04 data
drwxrwxr-x.  6 postgres postgres 4096 Jan  7 14:12 include
drwxrwxr-x.  4 postgres postgres 4096 Jan  7 14:12 lib
drwxrwxr-x.  8 postgres postgres 4096 Jan  7 14:12 share
[postgres@pgstandby 13.2]$ cd data
[postgres@pgstandby data]$ ll
total 272
-rw-------. 1 postgres postgres    224 Jan 10 21:04 backup_label
-rw-------. 1 postgres postgres 220496 Jan 10 21:04 backup_manifest
drwx------. 7 postgres postgres     67 Jan 10 21:04 base
drwx------. 2 postgres postgres   4096 Jan 10 21:04 global
drwx------. 2 postgres postgres      6 Jan 10 21:04 pg_commit_ts
drwx------. 2 postgres postgres      6 Jan 10 21:04 pg_dynshmem
-rw-------. 1 postgres postgres   4896 Jan 10 21:04 pg_hba.conf
-rw-------. 1 postgres postgres   1636 Jan 10 21:04 pg_ident.conf
drwx------. 4 postgres postgres     68 Jan 10 21:04 pg_logical
drwx------. 4 postgres postgres     36 Jan 10 21:04 pg_multixact
drwx------. 2 postgres postgres      6 Jan 10 21:04 pg_notify
drwx------. 2 postgres postgres      6 Jan 10 21:04 pg_replslot
drwx------. 2 postgres postgres      6 Jan 10 21:04 pg_serial
drwx------. 2 postgres postgres      6 Jan 10 21:04 pg_snapshots
drwx------. 2 postgres postgres      6 Jan 10 21:04 pg_stat
drwx------. 2 postgres postgres      6 Jan 10 21:04 pg_stat_tmp
drwx------. 2 postgres postgres      6 Jan 10 21:04 pg_subtrans
drwx------. 2 postgres postgres     19 Jan 10 21:04 pg_tblspc
drwx------. 2 postgres postgres      6 Jan 10 21:04 pg_twophase
-rw-------. 1 postgres postgres      3 Jan 10 21:04 PG_VERSION
drwx------. 3 postgres postgres     60 Jan 10 21:04 pg_wal
drwx------. 2 postgres postgres     18 Jan 10 21:04 pg_xact
-rw-------. 1 postgres postgres    322 Jan 10 21:04 postgresql.auto.conf
-rw-------. 1 postgres postgres  27981 Jan 10 21:04 postgresql.conf
-rw-------. 1 postgres postgres      0 Jan 10 21:04 standby.signal

1.5 备库数据库进程信息

[postgres@pgstandby data]$ ps -ef|grep postgres
root      19760  19406  0 19:43 pts/1    00:00:00 su - postgres
postgres  19761  19760  0 19:43 pts/1    00:00:00 -bash
root      20509  20400  0 20:41 pts/2    00:00:00 su - postgres
postgres  20510  20509  0 20:41 pts/2    00:00:00 -bash
postgres  20924      1  0 21:18 ?        00:00:00 data/postgres/13.2/bin/postgres
postgres  20925  20924  0 21:18 ?        00:00:00 postgres: startup recovering 000000010000000000000004
postgres  20926  20924  0 21:18 ?        00:00:00 postgres: checkpointer 
postgres  20927  20924  0 21:18 ?        00:00:00 postgres: background writer 
postgres  20928  20924  0 21:18 ?        00:00:00 postgres: stats collector 
postgres  20929  20924  0 21:18 ?        00:00:00 postgres: walreceiver streaming 0/4000148
postgres  20944  20510  0 21:20 pts/2    00:00:00 ps -ef
postgres  20945  20510  0 21:20 pts/2    00:00:00 grep --color=auto postgres
[postgres@pgstandby data]$

备库上，可以看到walreceiver进程，正在读取日志streaming 0/4000148，执行恢复recovering 000000010000000000000004。

1.6 主库数据库进程信息

[postgres@pgprimary data]$ ps -ef|grep postgres
root      20334  19836  0 19:46 pts/2    00:00:00 su - postgres
postgres  20335  20334  0 19:46 pts/2    00:00:00 -bash
postgres  21221      1  0 20:57 ?        00:00:00 data/postgres/13.2/bin/postgres
postgres  21223  21221  0 20:57 ?        00:00:00 postgres: checkpointer 
postgres  21224  21221  0 20:57 ?        00:00:00 postgres: background writer 
postgres  21225  21221  0 20:57 ?        00:00:00 postgres: walwriter 
postgres  21226  21221  0 20:57 ?        00:00:00 postgres: autovacuum launcher 
postgres  21227  21221  0 20:57 ?        00:00:00 postgres: stats collector 
postgres  21228  21221  0 20:57 ?        00:00:00 postgres: logical replication launcher
postgres  21487  21221  0 21:18 ?        00:00:00 postgres: walsender replica 192.168.1.116(43648) streaming 0/4000148
postgres  21537  20335  0 21:22 pts/2    00:00:00 ps -ef
postgres  21538  20335  0 21:22 pts/2    00:00:00 grep --color=auto postgres

主库上看到，后台进程walsender，正在向replica 192.168.1.116(43648) streaming 0/4000148推送日志信息

1.7 主库查看数据库复制信息

[postgres@pgprimary data]$ psql -xc "select * from pg_stat_replication"
-[ RECORD 1 ]----+------------------------------
pid              | 21487
usesysid         | 16404
usename          | replica
application_name | walreceiver
client_addr      | 192.168.1.116
client_hostname  | 
client_port      | 43648
backend_start    | 2022-01-10 21:18:57.112831+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/4000148
write_lsn        | 0/4000148
flush_lsn        | 0/4000148
replay_lsn       | 0/4000148
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2022-01-10 21:23:47.870841+08

二、主备切换及注意事项

如果因为意外或故障导致主库不可用的情况下，可以直接将备库提升为主库对外提供服务。然后视具体情况看原来的主库是否需要重建，或者是否待故障恢复之后，可以直接作为新的备库，然后从新的主库(原备库)同步数据。

下面是模拟切换步骤：

2.1 主库停止，模拟故障

[postgres@pgprimary ~]$ ps -ef|grep postgres
root      18132  18071  0 11:54 pts/0    00:00:00 su - postgres
postgres  18133  18132  0 11:54 pts/0    00:00:00 -bash
postgres  20582      1  0 15:14 ?        00:00:00 data/postgres/13.2/bin/postgres
postgres  20584  20582  0 15:14 ?        00:00:00 postgres: checkpointer
postgres  20585  20582  0 15:14 ?        00:00:00 postgres: background writer
postgres  20586  20582  0 15:14 ?        00:00:00 postgres: walwriter
postgres  20587  20582  0 15:14 ?        00:00:00 postgres: autovacuum launcher
postgres  20588  20582  0 15:14 ?        00:00:00 postgres: stats collector
postgres  20589  20582  0 15:14 ?        00:00:00 postgres: logical replication launcher
root      20930  20816  0 15:32 pts/0    00:00:00 su - postgres
postgres  20931  20930  0 15:32 pts/0    00:00:00 -bash
postgres  21179  20582  0 15:50 ?        00:00:00 postgres: walsender replica 192.168.1.116(41508) streaming 0/8000148
postgres  21330  20931  0 16:03 pts/0    00:00:00 ps -ef
postgres  21331  20931  0 16:03 pts/0    00:00:00 grep --color=auto 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
waiting for server to shut down....2022-05-27 16:03:33.186 CST [20582] LOG:  received fast shutdown request
2022-05-27 16:03:33.198 CST [20582] LOG:  aborting any active transactions
2022-05-27 16:03:33.199 CST [20582] LOG:  background worker "logical replication launcher" (PID 20589) exited with exit code 1
2022-05-27 16:03:33.199 CST [20584] LOG:  shutting down
2022-05-27 16:03:33.220 CST [20582] LOG:  database system is shut down
 done
server stopped


[postgres@pgprimary ~]$ ps -ef|grep postgres
root      18132  18071  0 11:54 pts/0    00:00:00 su - postgres
postgres  18133  18132  0 11:54 pts/0    00:00:00 -bash
root      20930  20816  0 15:32 pts/0    00:00:00 su - postgres
postgres  20931  20930  0 15:32 pts/0    00:00:00 -bash
postgres  21343  20931  0 16:03 pts/0    00:00:00 ps -ef
postgres  21344  20931  0 16:03 pts/0    00:00:00 grep --color=auto postgres
[postgres@pgprimary ~]$

通过pg_ctl stop -m fast停止原来的主库之后，数据库后台进程都没有了。

2.2 备库提升为新主库，对外提供服务

[postgres@pgstandby 13.2]$ ps -ef|grep postgres
root      18284  18060  0 14:44 pts/0    00:00:00 su - postgres
postgres  18285  18284  0 14:44 pts/0    00:00:00 -bash
postgres  19107      1  0 15:50 ?        00:00:00 data/postgres/13.2/bin/postgres
postgres  19108  19107  0 15:50 ?        00:00:00 postgres: startup recovering 000000010000000000000008
postgres  19109  19107  0 15:50 ?        00:00:00 postgres: checkpointer
postgres  19110  19107  0 15:50 ?        00:00:00 postgres: background writer
postgres  19111  19107  0 15:50 ?        00:00:00 postgres: stats collector
postgres  19268  18285  0 16:05 pts/0    00:00:00 ps -ef
postgres  19269  18285  0 16:05 pts/0    00:00:00 grep --color=auto 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
waiting for server to promote....2022-05-27 16:06:25.714 CST [19108] LOG:  received promote request
2022-05-27 16:06:25.715 CST [19108] LOG:  redo done at 0/8000148
2022-05-27 16:06:25.728 CST [19108] LOG:  selected new timeline ID: 2
2022-05-27 16:06:25.979 CST [19108] LOG:  archive recovery complete
2022-05-27 16:06:25.982 CST [19107] LOG:  database system is ready to accept connections
 done
server promoted
[postgres@pgstandby 13.2]$


[postgres@pgstandby 13.2]$ ps -ef|grep postgres
root      18284  18060  0 14:44 pts/0    00:00:00 su - postgres
postgres  18285  18284  0 14:44 pts/0    00:00:00 -bash
postgres  19107      1  0 15:50 ?        00:00:00 data/postgres/13.2/bin/postgres
postgres  19109  19107  0 15:50 ?        00:00:00 postgres: checkpointer
postgres  19110  19107  0 15:50 ?        00:00:00 postgres: background writer
postgres  19111  19107  0 15:50 ?        00:00:00 postgres: stats collector
postgres  19347  19107  0 16:06 ?        00:00:00 postgres: walwriter
postgres  19348  19107  0 16:06 ?        00:00:00 postgres: autovacuum launcher
postgres  19349  19107  0 16:06 ?        00:00:00 postgres: logical replication launcher
postgres  19407  18285  0 16:07 pts/0    00:00:00 ps -ef
postgres  19408  18285  0 16:07 pts/0    00:00:00 grep --color=auto postgres
[postgres@pgstandby 13.2]$

重要1：启动备库为新主库的命令是pg_ctl promote。

提升备库为主库之后，可以看到，后台进程中不再有startup recovering，以及walreceiver streaming进程了。同时，多了postgres: walwriter 写进程。

重要2：$PGDATA/standby.signal文件自动消失了。这是告诉PostgreSQL，我现在不再是备库了，我的身份是主库了

2.3 新主库修改pg_hba.conf文件

修改新主库(原备库192.168.1.116)的$PGDATA/pg_hba.conf文件，在其中添加允许新备库(原主库192.168.1.106)可以通过replica用户访问数据库的条目信息。

host    replication     all             192.168.1.106/32           md5

注意：这里的192.168.1.126是原主库上配置的1个浮动IP地址，绑定在eth0:1设备上。如果主从环境的数据库没有配置浮动IP的话，则这里的IP地址，应该直接填原主库的实际IP地址。

2.4 原主库新建$PGDATA/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

注意：这一步骤非常非常重要，如果不配置该文件的话，那么原来的主库一旦重新启动话，就将成为了1个新的独立主库，脱离了主从数据库环境。

2.5 原主库修改$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'
[postgres@pgprimary data]$

2.6 启动原主库，变为新备库

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


[postgres@pgprimary data]$ ps -ef|grep postgres
root      18132  18071  0 11:54 pts/0    00:00:00 su - postgres
postgres  18133  18132  0 11:54 pts/0    00:00:00 -bash
root      20930  20816  0 15:32 pts/0    00:00:00 su - postgres
postgres  20931  20930  0 15:32 pts/0    00:00:00 -bash
root      22329  22133  0 16:56 pts/0    00:00:00 su - postgres
postgres  22330  22329  0 16:56 pts/0    00:00:00 -bash
postgres  22391      1  0 16:58 ?        00:00:00 data/postgres/13.2/bin/postgres
postgres  22392  22391  0 16:58 ?        00:00:00 postgres: startup recovering 000000020000000000000008
postgres  22393  22391  0 16:58 ?        00:00:00 postgres: checkpointer
postgres  22394  22391  0 16:58 ?        00:00:00 postgres: background writer
postgres  22395  22391  0 16:58 ?        00:00:00 postgres: stats collector
root      22918  22717  0 17:17 pts/0    00:00:00 su - postgres
postgres  22919  22918  0 17:17 pts/0    00:00:00 -bash
postgres  23002  22391  0 17:18 ?        00:00:00 postgres: walreceiver streaming 0/8000798
root      23142  23100  0 17:28 pts/1    00:00:00 su - postgres
postgres  23143  23142  0 17:28 pts/1    00:00:00 -bash
postgres  23193  23143  0 17:28 pts/1    00:00:00 psql
postgres  23194  22391  0 17:28 ?        00:00:00 postgres: postgres postgres [local] idle
postgres  23228  22919  0 17:31 pts/0    00:00:00 ps -ef
postgres  23229  22919  0 17:31 pts/0    00:00:00 grep --color=auto postgres

三、测试同步状态

[postgres@pgstandby ~]$ psql
psql (13.2)
Type "help" for help.


postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)


#查看所有数据库信息
postgres=# \l+




postgres=#  \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 replica   | Replication                                                | {}
 t_user    |                                                            | {}


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
          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+--------
 public | test_table | table | t_user
(1 row)

3.1 新主库（原备库192.168.1.116）插入数据



postgres=# select * from pg_test0524;
 id |  name
----+---------
  1 | beijing
(1 row)


postgres=# insert into pg_test0524 values(2,'shanghai');
INSERT 0 1


postgres=# insert into pg_test0524 values(3,'tianjin');
INSERT 0 1

3.2 同步数据正常（新备库原主库）

Last login: Fri May 27 17:11:44 2022
[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;
 id |   name
----+----------
  1 | beijing
  2 | shanghai
(2 rows)


postgres=# select * from pg_test0524;
 id |   name
----+----------
  1 | beijing
  2 | shanghai
  3 | tianjin
(3 rows)


postgres=#

四、手动切换回原主库

4.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
root      18006  17942  0 11:54 pts/0    00:00:00 su - postgres
postgres  18007  18006  0 11:54 pts/0    00:00:00 -bash
postgres  18137      1  0 12:02 ?        00:00:00 data/postgres/13.2/bin/postgres -D data/postgres/13.2/data
postgres  18139  18137  0 12:02 ?        00:00:00 postgres: checkpointer
postgres  18140  18137  0 12:02 ?        00:00:00 postgres: background writer
postgres  18141  18137  0 12:02 ?        00:00:00 postgres: walwriter
postgres  18142  18137  0 12:02 ?        00:00:00 postgres: autovacuum launcher
postgres  18143  18137  0 12:02 ?        00:00:00 postgres: stats collector
postgres  18144  18137  0 12:02 ?        00:00:00 postgres: logical replication launcher
postgres  19604  18137  0 14:07 ?        00:00:00 postgres: walsender replica 192.168.1.106(41370) streaming 0/9000D80
root      19778  19623  0 14:18 pts/1    00:00:00 su - postgres
postgres  19779  19778  0 14:18 pts/1    00:00:00 -bash
postgres  20222  19779  0 14:53 pts/1    00:00:00 ps -ef
postgres  20223  19779  0 14:53 pts/1    00:00:00 grep --color=auto 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"

4.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
root      18006  17942  0 11:54 pts/0    00:00:00 su - postgres
postgres  18007  18006  0 11:54 pts/0    00:00:00 -bash
root      19778  19623  0 14:18 pts/1    00:00:00 su - postgres
postgres  19779  19778  0 14:18 pts/1    00:00:00 -bash
postgres  20236  19779  0 14:54 pts/1    00:00:00 ps -ef
postgres  20237  19779  0 14:54 pts/1    00:00:00 grep --color=auto postgres

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

查看备库的状态
[postgres@pgprimary data]$ ps -ef|grep postgres
root      18287  18230  0 11:54 pts/0    00:00:00 su - postgres
postgres  18288  18287  0 11:54 pts/0    00:00:00 -bash
postgres  18417      1  0 12:02 ?        00:00:00 /data/postgres/13.2/bin/postgres -D /data/postgres/13.2/data
postgres  18418  18417  0 12:02 ?        00:00:00 postgres: startup recovering 000000020000000000000009
postgres  18419  18417  0 12:02 ?        00:00:00 postgres: checkpointer
postgres  18420  18417  0 12:02 ?        00:00:00 postgres: background writer
postgres  18421  18417  0 12:02 ?        00:00:00 postgres: stats collector
postgres  19326  18288  0 13:34 pts/0    00:00:00 psql
postgres  19336  18417  0 13:34 ?        00:00:00 postgres: postgres postgres [local] idle
root      19779  19654  0 14:18 pts/1    00:00:00 su - postgres
postgres  19780  19779  0 14:18 pts/1    00:00:00 -bash
postgres  20177  19780  0 14:54 pts/1    00:00:00 ps -ef
postgres  20178  19780  0 14:54 pts/1    00:00:00 grep --color=auto 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.3 备注提升为主库pg_ctl promote

[postgres@pgprimary data]$ pg_ctl promote
waiting for server to promote.... done
server promoted
[postgres@pgprimary data]$ ps -ef|grep postgres
root      18287  18230  0 11:54 pts/0    00:00:00 su - postgres
postgres  18288  18287  0 11:54 pts/0    00:00:00 -bash
postgres  18417      1  0 12:02 ?        00:00:00 /data/postgres/13.2/bin/postgres -D /data/postgres/13.2/data
postgres  18419  18417  0 12:02 ?        00:00:00 postgres: checkpointer
postgres  18420  18417  0 12:02 ?        00:00:00 postgres: background writer
postgres  18421  18417  0 12:02 ?        00:00:00 postgres: stats collector
postgres  19326  18288  0 13:34 pts/0    00:00:00 psql
postgres  19336  18417  0 13:34 ?        00:00:00 postgres: postgres postgres [local] idle
root      19779  19654  0 14:18 pts/1    00:00:00 su - postgres
postgres  19780  19779  0 14:18 pts/1    00:00:00 -bash
postgres  20201  18417  0 14:55 ?        00:00:00 postgres: walwriter
postgres  20202  18417  0 14:55 ?        00:00:00 postgres: autovacuum launcher
postgres  20203  18417  0 14:55 ?        00:00:00 postgres: logical replication launcher
postgres  20204  19780  0 14:55 pts/1    00:00:00 ps -ef
postgres  20205  19780  0 14:55 pts/1    00:00:00 grep --color=auto postgres

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

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


# TYPE  DATABASE        USER            ADDRESS                 METHOD


# "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

4.5 新备库创建文件 standby.signal

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


查看连接主库的信息
[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'

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

[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
root      18006  17942  0 11:54 pts/0    00:00:00 su - postgres
postgres  18007  18006  0 11:54 pts/0    00:00:00 -bash
root      19778  19623  0 14:18 pts/1    00:00:00 su - postgres
postgres  19779  19778  0 14:18 pts/1    00:00:00 -bash
postgres  20330      1  0 15:02 ?        00:00:00 /data/postgres/13.2/bin/postgres
postgres  20331  20330  0 15:02 ?        00:00:00 postgres: startup recovering 000000030000000000000009
postgres  20332  20330  0 15:02 ?        00:00:00 postgres: checkpointer
postgres  20333  20330  0 15:02 ?        00:00:00 postgres: background writer
postgres  20334  20330  0 15:02 ?        00:00:00 postgres: stats collector
postgres  20335  20330  2 15:02 ?        00:00:00 postgres: walreceiver streaming 0/9000F10
postgres  20336  19779  0 15:02 pts/1    00:00:00 ps -ef
postgres  20337  19779  0 15:02 pts/1    00:00:00 grep --color=auto postgres

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

4.7 测试数据同步

主库插入数据
[postgres@pgprimary data]$ psql
psql (13.2)
Type "help" for help.


postgres-# \d
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | pg_test     | table | postgres
 public | pg_test0524 | table | postgres
 public | test_tbs    | table | postgres
(3 rows)


postgres-# \c
You are now connected to database "postgres" as user "postgres".
postgres=# select * from pg_test0524;
 id |   name
----+-----------
  1 | beijing
  2 | shanghai
  3 | tianjin
  4 | xian
  5 | guangzhou
  6 | shenzheng
(6 rows)


postgres=# insert into pg_test0524 values(7,'langfang');
INSERT 0 1
postgres=#


备库查看同步情况
[postgres@pgstandby data]$ psql
psql (13.2)
Type "help" for help.


postgres=# \d
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | pg_test     | table | postgres
 public | pg_test0524 | table | postgres
 public | test_tbs    | table | postgres
(3 rows)


postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# select * from pg_test0524;
 id |   name
----+-----------
  1 | beijing
  2 | shanghai
  3 | tianjin
  4 | xian
  5 | guangzhou
  6 | shenzheng
(6 rows)


postgres=# select * from pg_test0524;
 id |   name
----+-----------
  1 | beijing
  2 | shanghai
  3 | tianjin
  4 | xian
  5 | guangzhou
  6 | shenzheng
  7 | langfang
(7 rows)


postgres=#

五小结

1、随着新版本的发行，数据库的配置和使用也越来越简单顺手了。

2、备库提升为主库的命令：pg_ctl promote;

3、新主库(原备库)的pg_hba.conf文件，要开放允许流复制访问数据库的信息给原主库的IP地址；

4、原主库配置为新备库的时候，务必要创建$PGDATA/standby.signal文件；

5、原主库配置为新备库的时候，务必要修改$PGDATA/postgresql.auto.conf文件，添加主库primary_conninfo的信息；

文章来源：开发者社区

原文作者：用户8006012

原文链接：https://cloud.tencent.com/developer/article/2013763

点击关注“SQL数据库运维”，后台或浏览至公众号文章底部点击“发消息”回复关键字：进群，带你进入高手如云的技术交流群。后台回复关键字：SQL，获取学习资料。

动动小手点击加关注呦☟☟☟

data postgresql

文章转载自SQL数据库运维，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。