PostgreSQL主备同步详解_pgsql主备

前言：

        PostgreSQL支持物理复制和逻辑复制，物理复制也可以称为流复制（Streaming Replication），通过流复制技术，可以从实例级复制出一个与主库一模一样的从库，主库可读写数据，从库仅能读取数据；另一种复制方式称为逻辑复制（Logical Replication），逻辑复制可以做到基于表级别的复制。生产环境我们常用的是流复制技术。

        流复制又分为同步流复制和异步流复制，如果主节点和备节点不是很忙，通常异步模式下备库和主库的延迟时间能控制在毫秒级。

        流复制和逻辑复制都是基于WAL，但两者有本质不同，流复制是基于WAL物理复制，逻辑复制是基于WAL逻辑解析，将WAL解析成一种清晰、易于理解的格式。

WAL (Write-Ahead Logging) 日志记录数据库的变化，格式为二进制格式，当主机出现异常断电时，如果WAL文件已经写入成功，但还没来得及刷新数据文件，当数据库再次启动时会根据WAL日志文件信息进行事务前滚，从而恢复数据库到一致性状态。

一、实验环境

        本次使用的docker部署postgresql，配置更简单，日常管理和升级更方便。服务器需提前安装docker和docker-compose服务。

二、PG主库配置

1.docker-compose.yml配置文件

#vi docker-compose.yml
version: '3'
services:
  postgres:
    image: postgres:11-alpine                  #PostgreSQL镜像版本
    container_name: postgres-master             #PostgreSQL容器名称
    ports:
      - "5432:5432"
    volumes:
      - "./pgdata:/var/lib/postgresql/data"
    environment:
      POSTGRES_PASSWORD: password              #数据库用户密码
      POSTGRES_USER: dbuser                    #数据库账户
      POSTGRES_DB: example                     #数据库名
      TZ: Asia/Shanghai

2.docker服务管理

#启动PostgreSQL容器
docker-compose up -d
#PostgreSQL容器状态查询
docker-compose ps
#停止PostgreSQL容器
docker-compose stop

3.pg_hba.conf配置

#cd pgdata		//启动PostgreSQL容器后，进入pgdata目录
#vi pg_hba.conf
host    replication     repuser         172.27.1.11/32          md5
host    replication     repuser         172.27.1.12/32          md5

        注：主库和备库的角色不是静止的，它们的角色是可以互换的，比如做一次主备切换后角色就发生了变化，因此建议主库、备库的pg_hba.conf配置完全一致。

4.postgresql.conf配置

#cd pgdata		//启动PostgreSQL容器后，进入pgdata目录
#vi postgresql.conf
listen_addresses = '*'
wal_level = replica
archive_mode = on
archive_command = '/bin/date'
max_wal_senders = 10 
wal_keep_segments = 64
wal_sender_timeout = 60s
hot_standby = on
max_connections = 1000

参数说明：

注：主库和备库的postgresql.conf配置建议完全一致。

5.创建数据库复制用户

# docker exec -it postgres-master /bin/bash				//登录PostgreSQL容器，postgres-master是容器名称
# psql -U dbuser -d example							//在容器内使用数据库用户登录PostgreSQL
# CREATE ROLE repuser login replication encrypted password 'repuser';				//创建拥有登录和复制权限的用户repuser
#postgres=# select * from pg_user;			//查询数据库用户
 usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
---------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 dbuser  |       10 | t           | t        | t       | t            | ******** |          | 
 repuser |    16385 | f           | f        | t       | f            | ******** |          | 
(2 rows)

三、PG备库配置

1.docker-compose.yml配置文件

#vi docker-compose.yml
version: '3'
services:
  postgres:
    image: postgres:11-alpine                  #PostgreSQL镜像版本
    container_name: postgres-salve             #PostgreSQL容器名称
    ports:
      - "5432:5432"
    volumes:
      - "./pgdata:/var/lib/postgresql/data"
      - "./bak:/var/lib/postgresql/bak"        #pg_basebackup备份主库后需注销
    environment:
      POSTGRES_PASSWORD: password              #数据库用户密码
      POSTGRES_USER: dbuser                    #数据库账户
      POSTGRES_DB: example                     #数据库名
      TZ: Asia/Shanghai

2.pg_basebackup备份主库

        pg_basebackup命令行工具支持对主库发起一个在线基准备份，并自动进入备份模式进行数据库基准备份，备份完成后自动从备份模式退出，pg_basebackup工具是对数据库实例级进行的物理备份，因此这个工具通常作为备份工具对据库进行基准备份。

# docker exec -it postgres-salve /bin/bash		//进入PG备库容器内
# pg_basebackup -h 172.27.1.11 -U repuser -Fp -Xs -P -v -D /var/lib/postgresql/bak    
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_46"
31680/31680 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/2000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
# exit						//退出容器

参数说明：

3.数据库同步

# docker-compose stop						//停止备库容器
Stopping postgres-salve ... done
# rm -rf pgdata									//删除备库数据
# mv bak pgdata									//重命名pg_basebackup
# vi docker-compose.yml 
version: '3'
services:
  postgres:
    image: postgres:11-alpine
    container_name: postgres-salve
    ports:
      - "5432:5432"
    volumes:
      - "./pgdata:/var/lib/postgresql/data"
#      - "./bak:/var/lib/postgresql/bak"      #注销此行
    environment:
      POSTGRES_PASSWORD: password
      POSTGRES_USER: dbuser
      POSTGRES_DB: example
      TZ: Asia/Shanghai

4.备库recovery.conf

# cd pgdata
# vi recovery.conf 
standby_mode = on
primary_conninfo = 'host=172.27.1.11 port=5432 user=repuser password=repuser'
recovery_target_timeline = 'latest'

参数说明：

5.启动PG备库

# docker-compose up -d
Recreating postgres-salve ... done

四、主备查询

1.pg_stat_replication系统视图查询

注：在PG主库字典表中是能查到记录，PG备机中是查询不到的。

        pg_stat_replication视图显示主库上WAL发送进程信息，主库上有多少个WAL发送进程，此视图就对应多少条记录。sync state字段值为async，表示主备数据复制使用异步方式。

# docker exec -it postgres-master /bin/bash					//登录PG主库容器
# psql -U dbuser -d example													//登录数据库
psql (11.19)
Type "help" for help.
 
example=# SELECT usename,application_name,client_addr,sync_state  FROM pg_stat_replication;
 usename | application_name | client_addr | sync_state 
---------+------------------+-------------+------------
 repuser | walreceiver      | 172.27.1.12 | async
(1 row)

2.通过系统函数查询

        登录数据库执行以下函数，返回结果：t（备库）、f（主库）

iot=# SELECT pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

3.查询数据库控制信息

        登录PG库容器执行pg_controldata ，PG主库的cluster state是in production，PG备库的cluster state是in archive recovery。

$ pg_controldata
    pg_control version number:            1100
    Catalog version number:               201809051
    Database system identifier:           7223932090703405085
    Database cluster state:               in production
 
$ pg_controldata					
    pg_control version number:            1100
    Catalog version number:               201809051
    Database system identifier:           7223932090703405085
    Database cluster state:               in archive recovery