PostgreSQL 主从同步

PostgreSQL 9.0 引入了主备流复制机制，流复制每次传输单位是 WAL 日志的 record。通过流复制备库不断的从主库同步相应的数据，并在备库 apply 每个 WAL record 。

NOTES: PostgreSQL 9.0 之前提供的方法是主库写完一个 WAL 日志文件后，才把 WAL 日志文件传送到备库，这样的方式会导致主备延迟特别大。同时 PostgreSQL 9.0 之后提供了 Hot Standby ，备库在应用 WAL record 的同时也能够提供只读服务，大大提升了用户体验。

配置主库

pgsql 通过容器部署，这里使用 docker-compose
主库： 192.168.0.1
从库： 192.168.0.2

启动主库

pgsql:
  image: postgres:9.6
  restart: always
  container_name: pgsql
  ports:
   - 5432:5432
  environment:
   - POSTGRES_PASSWORD=postgres
  volumes:
   - /var/lib/postgresql/data:/var/lib/postgresql/data

新建归档日志目录

docker exec -ti pgsql bash

mkdir -p /var/lib/postgresql/data/pg_archive

新建用户

create role replica login replication encrypted password 'replica';

# 查看是否创建成功
\du

修改 pg_hba.conf 文件 (从库ip)

host replication replica 192.168.0.2/32 trust

修改 postgresql.conf

listen_addresses = '*'     # 监听所有 IP
wal_level = hot_standby    # 开启热备
max_wal_senders = 32       # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
wal_keep_segments = 64     # 设置流复制保留的最多的 xlog 数目，一份是 16M，注意机器磁盘 16M * 64 = 1G
wal_sender_timeout = 60    # 设置流复制主机发送数据的超时时间
max_connections = 500      # 这个设置要注意下，从库的 max_connections 必须要大于主库的
archive_mode = on          # 允许归档
                           # 用该命令来归档logfile segment
archive_command = 'cp %p /var/lib/postgresql/data/pg_archive/%f'

重启容器

docker restart pgsql

配置从库

启动从库

拷贝 docker-compose 文件, 并拉起容器

拷贝主服务器数据

# 清除从库数据
rm -rf /var/lib/postgresql/data/*  

pg_basebackup -h 192.168.0.1 -U replica -D /var/lib/postgresql/data -X stream -P

mkdir -p /var/lib/postgresql/data/pg_archive

添加 recovery.conf 文件

recovery.conf

standby_mode = on    
primary_conninfo = 'host=192.168.0.1 port=5432 user=replica password=replica' 
recovery_target_timeline = 'latest'

修改 postgresql.conf

wal_level = hot_standby
hot_standby_feedback = on             # 如果有错误的数据复制，是否向主进行反馈
max_connections = 1000                # 一般查多于写的应用从库的最大连接数要比较大
hot_standby = on                      # 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s     # 数据流备份的最大延迟时间
wal_receiver_status_interval = 10s    # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间

重启从库

docker restart pgsql-slave

验证主从

select client_addr, sync_state from pg_stat_replication;

ps -ef 查看进程是否存在

查看状态

/usr/lib/postgresql/9.6/bin/pg_ctl -D /var/lib/postgresql/data status

pg_controldata /var/lib/postgresql/data

参考资料

• https://www.runoob.com/postgresql/postgresql-tutorial.html
• http://mysql.taobao.org/monthly/2015/10/04/