postgresql 主从配置_Postgresql 10.10 主从配置

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

主数据库配置

创建一个新目录

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

创建流复制帐号

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

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

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

在pg_hba.conf里增加两行：

1

2host all all 192.168.1.111/32 trust #允许从服务器连接到主服务器

host replication replica 192.168.1.111/32 md5 #允许从服务器使用replica用户来复制

修改postgresql.conf1

2

3

4

5

6

7

8

9

10

11max_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

修改完 ，重启主数据库

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

1psql -h 192.168.1.109 -U postgres

从服务器

从主服务器拷贝基础数据

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

1pg_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

6standby_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' # 一定要配置这个，要不然主从切换后不会同步成功

然后重启从服务器

查看同步状态

主库查询1select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

查询备库落后多少字节1select client_addr, pg_wal_lsn_diff(sent_lsn,replay_lsn)/1024/1024 as MB from pg_stat_replication;