postgresql12 主从复制[通俗易懂]

前言

PostgreSQL 12 的一个重要变化是 recovery.conf 配置文件中的参数合并到 postgresql.conf，recovery.conf 不再使用 ；

参见：https://www.postgresql.org/docs/release/12.0/

Move recovery.conf settings into postgresql.conf (Masao Fujii, Simon Riggs, Abhijit Menon-Sen, Sergei Kornilov)

recovery.conf is no longer used, and the server will not start if that file exists. 
recovery.signal and standby.signal files are now used to switch into non-primary mode. 
The trigger_file setting has been renamed to promote_trigger_file. The standby_mode setting has been removed.

代码100分

recovery.conf 配置文件此文件中的参数合并到 postgresql.conf

• recovery.conf 配置文件不再支持，若 recovery.conf 存在，数据库无法启动
• 新增 recovery.signal 标识文件和standby.signal 标识文件用于切换主从模式； recovery.signal表示数据库处于 recovery 模式
• trigger_file 参数更名为 promote_trigger_file
• standby_mode 参数不再支持

一、主从配置

1.安装数据库服务

安装repos库

代码100分pg-master|pg-slave # yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

安装postgresql-server

pg-master|pg-slave # yum install postgresql12-server

在主节点初始化数据库

代码100分pg-master # /usr/pgsql-12/bin/postgresql-12-setup initdb

注： 从节点不需要初始化数据库

2. 主数据库配置

1. 修改主配置文件postgresql.conf

listen_addresses = "*"
port = 5432
shared_buffers = 4GB
wal_level = replica
fsync = on
max_wal_size = 1GB
min_wal_size = 80MB

archive_mode = on
archive_command = "cp %p /var/lib/pgsql/12/archive_log/%f"

max_wal_senders = 5
wal_keep_segments = 0
wal_sender_timeout = 60s
max_replication_slots = 10

wal_level 确定将多少信息写入WAL，默认值是replica。
          replica 将写入足够的数据以支持WAL归档和复制，包括在备用服务器上运行只读查询。
          minimal 会删除所有日志记录，但从崩溃或立即关闭中恢复所需的信息除外。
          logical 添加支持逻辑解码所需的信息。

在 minimal 级别上，可以安全地跳过一些批量操作的WAL日志记录，这可以使这些操作更快（请参见14.4.7节）, 但是 minimal 的WAL没有包含足够的信息来从基本备份和WAL日志中重建数据，因此必须使用replica或更高级别来启用WAL归档（archive_mode）和流复制。

在logical级别，将记录与副本相同的信息，以及允许从WAL中提取逻辑更改集所需的信息。 使用logical级别将增加WAL量，尤其是如果为REPLICA IDENTITY FULL配置了许多表并且执行了许多UPDATE和DELETE语句时，尤其如此。

在9.6之前的版本中，此参数还允许值archive和hot_standby。 这些仍被接受，但映射到replica。

2. 修改pg_hba.conf文件添加认证信息

host    all             all             0.0.0.0/0               md5

host    replication     all             192.168.1.11/32         md5

3. 启动postgresql服务

pg-master # systemctl start portgresql-12

4. 添加复制用户

pg-master # su - postgres
-bash $ psql

postgres=# CREATE USER repluser REPLICATION LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD "replpass";

3. 从数据库配置

1. 从节点执行pg_basebackup初始化数据库

pg-slave # pg_basebackup -R -D /var/lib/pgsql/12/data/ -Fp -Xs -v -P -h 192.168.1.10 -p 5432 -U repluser

$PGDATA 目录下产生了 standby.signa 标识文件，同时 postgresql.auto.conf 配置文件增加了 primary_conninfo 参数信息。

pg-slave # cat postgresql.auto.conf

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = "user=repluser password=replpass host=192.168.1.10 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any"

2. 修改主配置文件postgresql.conf

hot_standby = on
max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
wal_receiver_timeout = 60s
wal_retrieve_retry_interval = 5s

3.启动postgresql服务

pg-slave # systemctl start portgresql-12

二、主从验证

1. 当从节点启动服务后，会有一个“postgres: walreceiver”进程

pg-slave # ps -ef 

postgres  7990  7983  0 13:35 ?        00:00:03 postgres: walreceiver   streaming 0/90020B0

主节点有一个“postgres: walsender”进程

pg-master # ps -ef

postgres  8951  8493  0 13:35 ?        00:00:00 postgres: walsender repluser 192.168.1.11(35822) streaming 0/90020B0

2. 在主节点查询主从复制信息

postgres=# select * from pg_stat_replication;

pid  | usesysid | usename  | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_
lag | sync_priority | sync_state |          reply_time           
------+----------+----------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+--------
----+---------------+------------+-------------------------------
 8951 |    16384 | repluser | walreceiver      | 192.168.1.11 |                 |       35822 | 2020-06-24 13:35:47.923873+08 |              | streaming | 0/90020B0 | 0/90020B0 | 0/90020B0 | 0/90020B0  |           |           |        
    |             0 | async      | 2020-06-24 14:55:17.912174+08

3. 创建一个测试库test

主节点：

postgres=# create database test;

postgres=# l

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

从节点上：

postgres=# l

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

可以看到从节点已将test库的信息同步过来