Postgresql 10.10 主从配置

本步骤基于Ubuntu 18.04和 Postgresql 10.10 假设主数据库为 192.168.1.109 从数据库为192.168.1.111

主数据库配置

创建一个新目录

1
mkdir /var/lib/postgresql/10/main/pgarchive

创建流复制帐号

首先需要创建一个数据库用户进行主从同步。创建用户replica,并赋予登录和复制的权限

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

修改pg_hba.conf,允许replica用户同步

在pg_hba.conf里增加两行:

1
2
host     all             all          192.168.1.111/32          trust   #允许从服务器连接到主服务器
host replication replica 192.168.1.111/32 md5 #允许从服务器使用replica用户来复制

修改postgresql.conf

1
2
3
4
5
6
7
8
9
10
11
max_connections = 100 # 这个设置要注意下,从库的max_connections必须要大于主库的
max_wal_senders = 1 # 这个设置了可以最多有几个流复制连接,差不多有几个从,就设置几个 但是,还需要注意,pg_basebackup也会用到一个。
wal_level = replica
archive_mode = on # 设置自动归档
hot_standby = on
archive_command = ‘test ! -f /var/lib/postgresql/10/main/pgarchive/%f && /usr/bin/rsync -a %p /var/lib/postgresql/10/main/pgarchive/%f’ # 设置归档命令
wal_keep_segments = 128 # 设置流复制保留的最多的wal文件
full_page_writes = on # 开启全页写入 pg_rewind 前提条件 如果不将整个page写入WAL中,在介质恢复的时候WAL中记录的数据不足以实现完整的恢复
wal_log_hints = on # pg_rewind 前提条件
min_wal_size = 512MB #最小的wal 空间
max_wal_size = 2GB # max_wal_size = (3 * checkpoint_segments) * 16MB CheckPointSegments得到的值范围是 max_wal_size 的 1/3 ~ 1/2,最小为1

修改完 ,重启主数据库

在测试服务器上测试是否能够正常连接:

1
psql -h 192.168.1.109 -U postgres

从服务器

从主服务器拷贝基础数据

首先需要删除从数据库下所有数据:

1
pg_basebackup -h 192.168.1.109 -U replica -F p -X stream -P -R -D /var/lib/postgresql/10/main/

pg_basebackup参数说明如下:

  • -h: 指定连接的数据库的主机名或IP地址
  • -U: 指定连接的用户名
  • -F: 指定了输出的格式,支持p(原样输出)或者t(tar格式输出)
  • -X: 表示备份开始后,启动另一个流复制连接从主库接收WAL日志
  • -P: 表示允许在备份过程中实时的打印备份的进度
  • -R: 表示会在备份结束后自动生成recovery.conf文件
  • -D: 指定备份写入的数据目录,需要与数据库配置的数据库目录一致,初次备份之前从库的数据目录需要手动清空
  • -l: 表示指定一个备份的标识

配置从库recovery.conf

修改recovery.conf 如下内容:

1
2
3
4
5
6
standby_mode = 'on'
primary_conninfo = 'user=replica password=123456 host=db1 port=5432 sslmode=disable sslcompression=1 target_session_attrs=any'
restore_command = '/usr/bin/rsync -a %f /var/lib/postgresql/10/main/pgarchive%p'
# recovery_target_timeline = 'latest' #测试采用默认即可
archive_cleanup_command = 'pg_archivecleanup /var/lib/postgresql/10/main/pgarchive %r'
recovery_target_timeline = 'latest' # 一定要配置这个,要不然主从切换后不会同步成功

然后重启从服务器

查看同步状态

主库查询

1
select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

查询备库落后多少字节

1
select client_addr, pg_wal_lsn_diff(sent_lsn,replay_lsn)/1024/1024 as MB from pg_stat_replication;
你的支持我的动力