PostgreSQL主从模式配置

主从复制简介

基于文件的日志传送

创建一个高可用性（HA）集群配置可采用连续归档，集群中主服务器工作在连续归档模式下，备服务器工作在连续恢复模式下（1台或多台可随时接管主服务器），备持续从主服务器读取WAL文件。连续归档不需要对数据库表做任何改动，可有效降低管理开销，对主服务器的性能影响也相对较低。直接从一个数据库服务器移动WAL记录到另一台服务器被称为日志传送，PostgreSQL通过一次一文件（WAL段）的WAL记录传输实现了基于文件的日志传送。

日志传送所需的带宽取根据主服务器的事务率而变化；日志传送是异步的，即WAL记录是在事务提交后才被传送，那么在一个窗口期内如果主服务器发生灾难性的失效则会导致数据丢失，还没有被传送的事务将会被丢失；数据丢失窗口可以通过使用参数archive_timeout进行限制，可以低至数秒，但同时会增加文件传送所需的带宽。

流复制

PostgreSQL在9.x之后引入了主从的流复制机制，所谓流复制，就是备服务器通过tcp流从主服务器中同步相应的数据，主服务器在tcp记录产生时即将它们以流式传送给备服务器，而不必等到WAL文件被填充。

默认情况下流复制是异步的，这种情况下主服务器上提交一个事务与该变化在备服务器上变得可见之间客观上存在短暂的延迟，但这种延迟相比基于文件的日志传送方式依然要小得多，在备服务器的能力满足负载的前提下延迟通常低于一秒；在流复制中，备服务器比使用基于文件的日志传送具有更小的数据丢失窗口，不需要采用archive_timeout来缩减数据丢失窗口；将一个备服务器从基于文件日志传送转变成基于流复制的步骤是：把recovery.conf文件中的primary_conninfo设置指向主服务器；设置主服务器配置文件的listen_addresses参数与认证文件即可。

环境准备

  • 系统环境：CentOS 7.6
  • 软件版本：PostgreSQL 9.5
  • 主机信息：
ip 角色
10.211.55.8 主库
10.22.55.11 从库

主从配置

主库配置

1、创建复制用户

1
postgres=# create role repl login replication encrypted password 'repl';

2、认证文件pg_hba.conf

配置从库可以采用repl账号进行同步

1
2
3
$ vim /var/lib/pgsql/9.5/data/pg_hba.conf 
# 新增
host replication repl 10.211.55.11/32 md5

3、主库配置文件postgresql.conf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$ vim /var/lib/pgsql/9.6/data/postgresql.conf


#监听端口
listen_addresses = '*'
 
#主从设置为热备模式，流复制必选参数
wal_level = hot_standby
 
#流复制允许的连接进程，一般同standby数量一致
max_wal_senders = 2
 
#流复制在没有基于文件的连续归档时，主服务器可能在备机收到WAL日志前回收这些旧的WAL，此时备机需要重新从一个新的基础备份初始化；可设置wal_keep_segments为一个足够高的值来确保旧的WAL段不会被太早重用；1个WAL日志为16MB，所以在设置wal_keep_segments时，在满足空间的前提下可以尽量设置大一些
wal_keep_segments = 64
 
#默认参数，非主从配置相关参数，表示到数据库的连接数，一般从库做主要的读服务时，设置值需要高于主库
max_connections = 100

4、重启服务

1
2
#同时注意打开防火墙端口打开
$ systemctl restart postgresql-9.5

从库配置

从库安装postgresql后，暂不初始化，如果从库已初始化，可以清空其data目录（默认安装是/ /var/lib/pgsql/9.5/data/目录）。

基础备份

1
2
3
4
5
6
7
8
$ pg_basebackup -h10.211.55.8 -p 5432 -U repl -F p -P -D /var/lib/pgsql/9.6/data/

#-h，主库主机，-p，主库服务端口；
#-U，复制用户；
#-F，p是默认输出格式，输出数据目录和表空间相同的布局，t表示tar格式输出；
#-P，同--progress，显示进度；
#-D，输出到指定目录；
#因为主库采用的是md5认证，这里需要密码认证。

修改相关权限

基于root账号做的基础备份，需要将相关目录文件的权限变更

1
$ chown -R postgres:postgres /var/lib/pgsql/9.5/data/

从库配置文件postgresql.conf

在基础备份时，初始化文件是从主库复制来的，所以配置文件一致，可将wal_level，max_wal_senders与wal_keep_segments等参数注释，以下是新增或修改的参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ vim /var/lib/pgsql/9.5/data/postgresql.conf

#在备份的同时允许查询
hot_standby = on
 
#可选，流复制最大延迟
max_standby_streaming_delay = 30s
 
#可选，从向主报告状态的最大间隔时间
wal_receiver_status_interval = 10s
 
#可选，查询冲突时向主反馈
hot_standby_feedback = on
 
#默认参数，非主从配置相关参数，表示到数据库的连接数，一般从库做主要的读服务时，设置值需要高于主库
max_connections = 1000

恢复文件recovery.conf

在做基础备份时，也可通过-R参数在备份结束后自动生产一个recovery.conf文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 先拷贝一份过来
$ cp /usr/pgsql-9.5/share/recovery.conf.sample /var/lib/pgsql/9.5/data/recovery.conf
# 赋予权限
$ chown postgres:postgres /var/lib/pgsql/9.5/data/recovery.conf
#修改部分内容
$ vim /var/lib/pgsql/9.5/data/recovery.conf

#指明从库身份
standby_mode = on
 
#连接到主库信息
primary_conninfo = 'host=10.211.55.8 port=5432 user=repl password=repl'
 
#同步到最新数据
recovery_target_timeline = 'latest'
 
#指定触发文件，文件存在时，将触发从库提升为主库，前提是必须设置”standby_mode = on”；如果不设置此参数，也可采用”pg_ctl promote“触发从库切换成主库 这个文件一般是不存在的
#trigger_file = ‘/var/lib/pgsql/9.5/data/trigger_activestandby’

重启相关服务

1
$ systemctl restart postgresql-9.5

验证

查看进程

1
2
3
4
5
# 查看主库是否有sender进程
$ ps -ef | grep postgres|grep sender

# 查看备库是否receiver进程
$ ps -ef | grep postgres|grep receiver

查看主备详情

在主库中查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
postgres=# select * from pg_stat_replication; 

#各字段详情
#pid，sender进程； 
#usesysid，复制用户id； 
#usename，复制用户名； 
#application_name，复制进程名； 
#client_addr，从库客户端地址； 
#client_hostname，从库客户端名； 
#client_port，从库客户端port； 
#backend_start，主从复制开始时间； 
#backend_xmin，当前后端的xmin范围，由备机提供；
#state，同步状态，startup：连接中；catchup：同步中；streaming：同步；
#sent_location，主传送wal的位置；
#write_location，从接收wal的位置；
#flush_location，从刷盘的wal位置；
#replay_location，从同步到数据库的wal位置；
#sync_priority，同步优先级，0表示异步；1～？表示同步，数字越小优先级越高； 
#sync_state， async：异步；sync：同步；potential；当前是异步，但可能升级到同步模式； 
#另外，”select pg_is_in_recovery();“命令也可以查看主从状态，false是主，true为从

测试数据

主库操作

1
2
3
4
postgres=# create table postgrestb(id int primary key,name VARCHAR(20),salary real); 
postgres=# insert into postgrestb values(10, 'Messi', 10000.00);
postgres=# insert into postgrestb values(6, 'Xavi', 10000.00); 
postgres=# select * from postgrestb;

从库操作

1
2
3
4
postgres=# \d postgrestb
# select * from postgrestb;
#从库只读，不能写入数据 
postgres=# insert into postgrestb values(8, 'Iniesta', 10000.00);

停服测试

1
2
3
4
5
6
7
8
9
10
11
12
# 主库中操作
$ pg_controldata /var/lib/pgsql/9.5/data/

#从库中操作
$ pg_controldata /var/lib/pgsql/9.6/data/

#以postgres账户停止主库  主库中操作
$ su - postgres -c "pg_ctl stop -m fast"

$ pg_controldata /var/lib/pgsql/9.5/data/
#日志已经明确是不能连接到主库 从库中操作
$ tail -f /var/lib/pgsql/9.5/data/pg_log/postgresql-Tue.log

主备库切换

使用pg_ctl promote命令切换从库为主库，切换后，从库的recovery.conf文件名字变成了recovery.done

1
2
3
4
# 从库中操作
$ su - postgres -c "pg_ctl promote"
$ tail -f /var/lib/pgsql/9.5/data/pg_log/postgresql-Tue.log
$ pg_controldata /var/lib/pgsql/9.6/data/

切换成功后查看数据目录可以看到producting。

其他

同步流复制

  1. 与异步流复制的区别 同步复制必须等待主库与从库都写完wal后才能commit事务，在一定程度上会增加事务的响应时间； 配置同步复制步骤： 在主库postgresql.conf文件中设置参数synchronous_standby_names为1个字符串或”*“，存在多个从库时使用逗号分隔； 在主库postgresql.conf文件中设置参数synchronous_commit参数设置为”on“，控制是否等待wal日志buffer刷入磁盘再返回用户事务状态信息，同步流复制需要打开； 从库的recovery.conf中primary_conninfo参数需要指明”application_name“。
  2. 注意事项 当只有1个从做同步流复制时，如果从库故障，则主库的写也会挂起（可以看到postgres下会有数据操作的waiting进程），此时的方案建议采用1+1+n的方式，即1 master+1 slave(同步)+n slave(异步)，做同步的slave故障后，可从n个异步slave中选举1个切换成同步模式； 设置synchronous_commit = off 后，即使同步复制模式的从库故障，主库的事务也不会出现等待挂起的现象。

备份与恢复

pgsql的连接

psql -h 127.0.0.1 -p 5432 -U postgres

备份数据库

pg_dump -h localhost -p 5432 -U postgres -W -F c -b -v -f "/home/postgres/hzz1116-0426.backup" hzz1116

恢复数据库

pg_restore -h localhost -p 5432 -U postgres -W -d hzz1116 -v "/home/postgres/hzz1116-0426.backup"

Linux
#postgresql #主从
PostgreSQL主从模式配置
https://randzz.cn/2ed53ac1859e/postgresql主从模式配置/
作者
Ezreal Rao
发布于
2020年6月10日
许可协议