postgresql 主从复制

> 本文由 [简悦 SimpRead](http://ksria.com/simpread/) 转码， 原文地址 [blog.csdn.net](https://blog.csdn.net/m0_52544877/article/details/125449202)

## 一、部署和配置环境

| 主机 | IP 地址 | 版本 |
| --- | --- | --- |
| psql-master（主库） | 10.10.140.113 | psql-14 |
| psql-slave（备库） | 10.10.140.112 |  psql-14 |

#### 1 修改主机名

```
[root@localhost ~]# hostnamectl  set-hostname pgsql-master
[root@localhost ~]# bash

[root@localhost ~]# hostnamectl  set-hostname pgsql-slave
[root@localhost ~]# bash
```

#### 2 安装 postgresql-14

```
#安装pgsql
apt install postgresql

#查看服务是否开启
[root@pgsql-master ~]# ss -antl
State      Recv-Q     Send-Q         Local Address:Port           Peer Address:Port     Process
LISTEN     0          244                127.0.0.1:5432                0.0.0.0:*
```

#### 3 修改数据存放路径

```
# 建目录/赋权限
root@node1:/home/user# mkdir -p /data/postgres_data
root@node1:/home/user# chown -R postgres:postgres /data/postgres_data
root@node1:/home/user# ll /data/
total 12
drwxr-xr-x  3 root     root     4096 Oct 12 01:46 ./
drwxr-xr-x 20 root     root     4096 Oct 12 01:46 ../
drwxr-xr-x  2 postgres postgres 4096 Oct 12 01:46 postgres_data/
# 修改数据库配置
root@node1:/home/user# vim /etc/postgresql/14/main/postgresql.conf
data_directory = '/data/postgres_data'
# 停止数据库服务
root@node1:/home/user# systemctl stop postgresql
root@node1:/home/user# systemctl status postgresql
# 同步数据
root@node1:/home/user# rsync -av /var/lib/postgresql/14/main/ /data/postgres_data/
 #重启数据库
root@node1:/home/user# systemctl start postgresql
```

**也可以使用默认数据存放路径：/var/lib/postgresql/14/main/**

## 二、执行 stream 主备配置流程

主体思路跟 PostgreSQL 11 及以前版本的配置流程大同小异，甚至是更简单一些了。

### 1 主库修改监听地址

```
root@node1:/home/user# vim /etc/postgresql/14/main/postgresql.conf
···
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'
···
```

### 2 主库创建流复制的用户

连接主库后, 创建复制用户replicator。
```
root@node1:/home/user# su postgres
postgres@node1:/home/user$ psql
psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1))
Type "help" for help.

postgres=# CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD '******';
CREATE ROLE
```

设置 pg_hba.conf，添加以下内容，允许 replicaor 用户从备库 IP 10.10.140.112 访问主库。

```
host   replication     repuser          10.10.140.112/32         scram-sha-256
```

### 4 备库删掉旧数据

```
root@node2:/var/lib/postgresql/14# cd /var/lib/postgresql/14/main
root@node2:/var/lib/postgresql/14# rm -rf *
```

### 5 备库执行对于主库的基础备份

[pg_basebackup 文档](http://www.postgres.cn/docs/14/app-pgbasebackup.html "pg_basebackup 文档")

> 注意，备份选项上带有 - R 选项。

```
-bash-4.2$ pg_basebackup -D /data/postgres_data -h 10.10.140.113 -U replicator -P -v -R -X stream -C -S pgstandby1

口令:
pg_basebackup: 开始基础备份，等待检查点完成
pg_basebackup: 已完成检查点
pg_basebackup: 预写日志起始于时间点: 0/2000028, 基于时间轴1
pg_basebackup: 启动后台 WAL 接收进程
pg_basebackup: 已创建复制槽"pgstandby1"
26952/26952 kB (100%), 1/1 表空间
pg_basebackup: 预写日志结束点: 0/2000100
pg_basebackup: 等待后台进程结束流操作...
pg_basebackup: 同步数据到磁盘...
pg_basebackup: 将backup_manifest.tmp重命名为backup_manifest
pg_basebackup: 基础备份已完成
```

### 6 备库启动 postgresql 服务

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

```
root@node2:/data# service postgresql start
root@node2:/data# cat /data/postgres_data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replicator password=replicatorPWD channel_binding=prefer host=10.10.140.113 port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
primary_slot_name = 'pgstandby1'
```

当然了，如果我们没有使用 - R 来备份主库的话。我们完全可以在备库上手工创建 standby.signal 文件，然后手工编辑 postgresql.auto.conf，并在其内容中配置主库的信息。

## 三、信息查看

##### 1 备库数据库进程信息

```
-bash-4.2$  ps -ef|grep postgres
postgres  8911     1  0 Oct20 ?        00:00:00 /postgres/pg12.8/bin/postgres
postgres  8912  8911  0 Oct20 ?        00:00:00 postgres: startup   recovering 000000010000000000000003
postgres  8913  8911  0 Oct20 ?        00:00:00 postgres: checkpointer 
postgres  8914  8911  0 Oct20 ?        00:00:00 postgres: background writer 
postgres  8915  8911  0 Oct20 ?        00:00:00 postgres: stats collector 
postgres  8916  8911  0 Oct20 ?        00:00:32 postgres: walreceiver   streaming 0/3000148
root     18015 18014  0 16:13 pts/0    00:00:00 su - postgres
postgres 18016 18015  0 16:13 pts/0    00:00:00 -bash
postgres 18055 18016  0 16:13 pts/0    00:00:00 ps -ef
postgres 18056 18016  0 16:13 pts/0    00:00:00 grep --color=auto postgres
```

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

##### 2 主库数据库进程信息

```
-bash-4.2$  ps -ef|grep postgres
postgres  3708     1  0 Oct20 ?        00:00:00 /postgres/pg12.8/bin/postgres
postgres  3710  3708  0 Oct20 ?        00:00:00 postgres: checkpointer 
postgres  3711  3708  0 Oct20 ?        00:00:00 postgres: background writer 
postgres  3712  3708  0 Oct20 ?        00:00:00 postgres: walwriter 
postgres  3713  3708  0 Oct20 ?        00:00:00 postgres: autovacuum launcher 
postgres  3714  3708  0 Oct20 ?        00:00:00 postgres: stats collector 
postgres  3715  3708  0 Oct20 ?        00:00:00 postgres: logical replication launcher 
postgres  4174  3708  0 Oct20 ?        00:00:00 postgres: walsender replicator 192.168.40.147(57926) streaming 0/3000148
root     30410 30409  0 16:11 pts/1    00:00:00 su - postgres
postgres 30411 30410  0 16:11 pts/1    00:00:00 -bash
postgres 30868 30411  0 16:13 pts/1    00:00:00 ps -ef
postgres 30869 30411  0 16:13 pts/1    00:00:00 grep --color=auto postgres
```

主库上看到，后台进程 walsender，正在向 replicator 192.168.40.147(57926) streaming 0/3000148 推送日志信息。

##### 3 主库查看数据库复制信息

## 六、 主备切换及注意事项

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

下面是模拟切换步骤：

##### 1 主库停止，模拟故障

```
-bash-4.2$  ps -ef|grep postgres
postgres  3708     1  0 Oct20 ?        00:00:00 /postgres/pg12.8/bin/postgres
postgres  3710  3708  0 Oct20 ?        00:00:00 postgres: checkpointer 
postgres  3711  3708  0 Oct20 ?        00:00:00 postgres: background writer 
postgres  3712  3708  0 Oct20 ?        00:00:00 postgres: walwriter 
postgres  3713  3708  0 Oct20 ?        00:00:00 postgres: autovacuum launcher 
postgres  3714  3708  0 Oct20 ?        00:00:00 postgres: stats collector 
postgres  3715  3708  0 Oct20 ?        00:00:00 postgres: logical replication launcher 
postgres  4174  3708  0 Oct20 ?        00:00:00 postgres: walsender replicator 192.168.40.147(57926) streaming 0/3000148
postgres  4747 30411  0 16:42 pts/1    00:00:00 ps -ef
postgres  4748 30411  0 16:42 pts/1    00:00:00 grep --color=auto postgres
root     30410 30409  0 16:11 pts/1    00:00:00 su - postgres
postgres 30411 30410  0 16:11 pts/1    00:00:00 -bash

-bash-4.2$  pg_ctl status
pg_ctl: server is running (PID: 3708)
/postgres/pg12.8/bin/postgres

-bash-4.2$  pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped

-bash-4.2$  ps -ef|grep postgres
postgres  4843 30411  0 16:43 pts/1    00:00:00 ps -ef
postgres  4844 30411  0 16:43 pts/1    00:00:00 grep --color=auto postgres
root     30410 30409  0 16:11 pts/1    00:00:00 su - postgres
postgres 30411 30410  0 16:11 pts/1    00:00:00 -bash

-bash-4.2$  pg_ctl status
pg_ctl: no server running
```

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

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

```
-bash-4.2$  ps -ef|grep postgres
postgres  8911     1  0 Oct20 ?        00:00:00 /postgres/pg12.8/bin/postgres
postgres  8912  8911  0 Oct20 ?        00:00:00 postgres: startup   recovering 000000010000000000000003
postgres  8913  8911  0 Oct20 ?        00:00:00 postgres: checkpointer 
postgres  8914  8911  0 Oct20 ?        00:00:00 postgres: background writer 
postgres  8915  8911  0 Oct20 ?        00:00:00 postgres: stats collector 
root     18015 18014  0 16:13 pts/0    00:00:00 su - postgres
postgres 18016 18015  0 16:13 pts/0    00:00:00 -bash
postgres 25404 18016  0 16:43 pts/0    00:00:00 ps -ef
postgres 25405 18016  0 16:43 pts/0    00:00:00 grep --color=auto postgres
-bash-4.2$  pg_ctl status
pg_ctl: server is running (PID: 8911)
/postgres/pg12.8/bin/postgres
-bash-4.2$  pg_ctl promote
waiting for server to promote.... done
server promoted
-bash-4.2$  ps -ef|grep postgres
postgres  8911     1  0 Oct20 ?        00:00:00 /postgres/pg12.8/bin/postgres
postgres  8913  8911  0 Oct20 ?        00:00:00 postgres: checkpointer 
postgres  8914  8911  0 Oct20 ?        00:00:00 postgres: background writer 
postgres  8915  8911  0 Oct20 ?        00:00:00 postgres: stats collector 
root     18015 18014  0 16:13 pts/0    00:00:00 su - postgres
postgres 18016 18015  0 16:13 pts/0    00:00:00 -bash
postgres 25622  8911  0 16:44 ?        00:00:00 postgres: walwriter 
postgres 25623  8911  0 16:44 ?        00:00:00 postgres: autovacuum launcher 
postgres 25624  8911  0 16:44 ?        00:00:00 postgres: logical replication launcher 
postgres 25628 18016  0 16:44 pts/0    00:00:00 ps -ef
postgres 25629 18016  0 16:44 pts/0    00:00:00 grep --color=auto postgres
-bash-4.2$
```

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

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

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

##### 3 新主库修改 pg_hba.conf 文件

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

```
host    replication     replicaotr             192.168.40.133/24           md5
```

如果不做这一步配置的话，将来启动原主库为新备库的时候，可能会遇到下述错误。

```
2021-10-21 17:13:20.464 CST [11394] FATAL:  could not connect to the primary server: FATAL:  no pg_hba.conf entry for replication connection from host "192.168.40.133", user "replica", SSL off
2021-10-21 17:13:20.466 CST [11395] FATAL:  could not connect to the primary server: FATAL:  no pg_hba.conf entry for replication connection from host "192.168.40.133", user "replica", SSL off
```

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

##### 4 原主库新建 $PGDATA/standby.signal 文件

```
-bash-4.2$  pwd
/var/lib/pgsql/14/data
-bash-4.2$  touch standby.signal

-bash-4.2$  ll standby.signal 
-rw-rw-r-- 1 postgres postgres 0 Oct 21 16:54 standby.signal
```

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

##### 5 原主库修改 $PGDATA/postgresql.auto.conf 文件

注意，应该用单引号，而不是双引号。否则遇到下述错误。

```
-bash-4.2$  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.40.147 port=5432"
-bash-4.2$  
-bash-4.2$  pg_ctl start -l ~/pg.log 
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.
-bash-4.2$  tailf ~/pg.log
数据库启动日志
2021-10-21 09:07:06.512 GMT [10045] LOG:  syntax error in file "/postgres/pg12.8/data/postgresql.auto.conf" line 3, near token """
2021-10-21 09:07:06.512 GMT [10045] FATAL:  configuration file "postgresql.auto.conf" contains errors
```

修改 $PGDATA/postgresql.auto.conf 配置文件为下述正确的格式：

```
-bash-4.2$  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.40.147 port=5432'
```

##### 6 启动原主库，变为新备库

```
-bash-4.2$  pg_ctl start -l ~/pg.log 
waiting for server to start.... done
server started
-bash-4.2$  ps -ef|grep postgres
root      8116  8115  0 16:58 pts/0    00:00:00 su - postgres
postgres  8118  8116  0 16:58 pts/0    00:00:00 -bash
root      8598  8597  0 17:00 pts/2    00:00:00 su - postgres
postgres  8600  8598  0 17:00 pts/2    00:00:00 -bash
postgres 11368  8118  0 17:13 pts/0    00:00:00 tailf pg.log
postgres 11389     1  0 17:13 ?        00:00:00 /postgres/pg12.8/bin/postgres
postgres 11390 11389  0 17:13 ?        00:00:00 postgres: startup   recovering 000000020000000000000003
postgres 11391 11389  0 17:13 ?        00:00:00 postgres: checkpointer   
postgres 11392 11389  0 17:13 ?        00:00:00 postgres: background writer  
postgres 11393 11389  0 17:13 ?        00:00:00 postgres: stats collector   
postgres 11440 11389  0 17:13 ?        00:00:00 postgres: walreceiver   streaming 0/3013AC8
postgres 12545 30411  0 17:18 pts/1    00:00:00 ps -ef
postgres 12546 30411  0 17:18 pts/1    00:00:00 grep --color=auto postgres
root     30410 30409  0 16:11 pts/1    00:00:00 su - postgres
postgres 30411 30410  0 16:11 pts/1    00:00:00 -bash
-bash-4.2$  tailf pg.log 
2021-10-21 17:13:45.488 CST [11440] LOG:  fetching timeline history file for timeline 2 from primary server
2021-10-21 17:13:45.493 CST [11440] LOG:  started streaming WAL from primary at 0/3000000 on timeline 1
2021-10-21 17:13:45.493 CST [11440] LOG:  replication terminated by primary server
2021-10-21 17:13:45.493 CST [11440] DETAIL:  End of WAL reached on timeline 1 at 0/30001C0.
2021-10-21 17:13:45.494 CST [11390] LOG:  new target timeline is 2
2021-10-21 17:13:45.494 CST [11440] LOG:  restarted WAL streaming at 0/3000000 on timeline 2
2021-10-21 17:13:45.539 CST [11390] LOG:  redo starts at 0/30001C0
```

这样，就完成了一次主从数据库环境的切换操作了。

#### 七、小结

1.  随着新版本的发行，数据库的配置和使用也越来越简单顺手了。
2.  备库提升为主库的命令：pg_ctl promote;
3.  新主库 (原备库) 的 pg_hba.conf 文件，要开放允许流复制访问数据库的信息给原主库的 IP 地址；
4.  原主库配置为新备库的时候，务必要创建 $PGDATA/standby.signal 文件；
5.  原主库配置为新备库的时候，务必要修改 $PGDATA/postgresql.auto.conf 文件，添加主库 primary_conninfo 的信息；