postgresql11主从流复制

环境

OS: centos 7
postgresql:11.8
数据同步方式：同步流复制

IP地址	版本	主从
192.168.61.130	11.8	主
192.168.61.131	11.8	从

1. 安装postgresql

安装略

配置环境变量

$ sudo vi /etc/profile

增加内容

export PGHOME=/usr/local/pgsql
export PGDATA=/var/lib/pgsql/data
export PGPORT=5432
export PGUSER=postgres
expost PATH=$PGHOME/bin:$PATH

环境变量立即生效

$ source /etc/profile

2. 配置

同步流复制：数据不丢失，但性能差异步流复制：数据可能丢失，性能好

2.1 修改hosts

在主备机上都修改hosts

$ sudo vi /etc/hosts

文件末尾增加如下内容：

192.168.61.130 pg_master
192.168.61.131 pg_slave

2.1 主机配置

【初始化，并配置流复制】

修改pg_hba.conf文件

$ sudo vi /var/lib/pgsql/data/pg_hba.conf

文件末尾增加如下内容：

host replication all pg_master md5
host replication all pg_slave md5

修改postgresql.conf文件

创建归档文件夹

sudo mkdir /var/lib/pgsql/pg_archive
sudo chmod 0700 /var/lib/pgsql/pg_archive

$ sudo vi /var/lib/pgsql/data/postgresql.conf

修改内容：

listen_addresses = '*'

tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10

synchronous_commit = off
wal_write_delay = 10ms
max_wal_senders = 10
wal_level = hot_standby

参考内容

listen_addresses = '*'
wal_level = hot_standby
synchronous_commit = on
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/pg_archive/%f'

max_wal_senders=5
wal_keep_segments = 32

hot_standby = on
restart_after_crash = off
replication_timeout = 5000
wal_receiver_status_interval = 2
max_standby_streaming_delay = -1
max_standby_archive_delay = -1
synchronous_commit = on
restart_after_crash = off
hot_standby_feedback = on

配置recovery文件(主库recovery.done，从库recovery.conf)

sudo vi /var/lib/pgsql/data/recovery.done

standby_mode = on
primary_conninfo = 'host=192.168.61.131 port=5432 user=postgres password=123456'
recovery_target_timeline = 'latest'

修改权限

sudo chown -R postges:postgres /var/lib/pgsql

2.2 从机配置

【不初始化，同步主机数据】

删除原有数据库文件

sudo rm -rf /var/lib/pgsql/*
sudo mkdir /var/lib/pgsql/{data,pg_archive}
sudo chown -R postges:postgres /var/lib/pgsql
sudo chmod 0750 /var/lib/pgsql/data

流复制

su root
su postgres
pg_baseabckup -h 192.168.61.130 -U postgres -D /var/lib/pgsql/data -X stream -P

配置recovery.conf

也可以修改主机已经配置的recovery.done

sudo vi /var/lib/pgsql/data/recovery.conf

standby_mode = on
primary_conninfo = 'host=192.168.61.130 port=5432 user=postgres password=123456'
recovery_target_timeline = 'latest'

sudo chown -R postges:postgres /var/lib/pgsql

重启从库

sudo systemctl restart postgresql

3. 验证

验证主从

ps -ef | grep postgres

主库上查看复制状态

select * from pg_stat_replication;

select pg_is_in_recovery();

f是主，t为从

pg_controldata命令

pg_controldata /var/lib/pgsql/data

# 主库
Database cluster state: in production

# 从库
Database cluster state: in archive recovery

Reference: