PostgreSQL流复制-主从切换

主从复制

1. 基于文件的日志传送

创建一个高可用性（HA）集群配置可采用连续归档，集群中主服务器工作在连续归档模式下，备服务器工作在连续恢复模式下（1台或多台可随时接管主服务器），备持续从主服务器读取WAL文件。

连续归档不需要对数据库表做任何改动，可有效降低管理开销，对主服务器的性能影响也相对较低。

直接从一个数据库服务器移动WAL记录到另一台服务器被称为日志传送，PostgreSQL通过一次一文件（WAL段）的WAL记录传输实现了基于文件的日志传送。

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

2. 流复制

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

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

一、主从环境

1. 操作系统

CentOS-7-x86_64

2. PostgresSQL版本

PostgreSQL 9.6

3. 主机

 

主机名	IP	端口	角色
pg_master	192.168.159.150	5432	主库
pg_slave	192.168.159.151	5432	从库

 

二 、主库配置

三．主库配置

1. 使用postgres用户登陆数据库，创建复制用户

#需要一个账号进行主从同步

postgres=#create role replica login replication encrypted password 'replica';

2. 认证文件pg_hba.conf

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

[root@psql_master ~]#vim /var/lib/pgsql/9.6/data/pg_hba.conf

host replication replica 192.168.159.0/24 md5

3. 主库配置文件postgresql.conf

 [root@psql_master ~]# vim /var/lib/pgsql/9.6/data/postgresql.conf

listen_addresses = '*'

max_connections = 1000

wal_level = replica

max_wal_senders = 1

wal_keep_segments = 64

archive_mode = on

archive_command = 'cp %p /var/lib/pgsql/pg_archive/%f'

wal_log_hints = on

full_page_writes = on

hot_standby = on

4.创建pg_archive目录

mkdir -p /var/lib/pgsql/pg_archive

chown -R postgres:postgres  /var/lib/pgsql/pg_archive

5.配置.pgpass

su - postgres

vim ~/.pgpass

192.168.159.150:5432:replication:replica:replica
192.168.159.151:5432:replication:replica:replica

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

vim /var/lib/pgsql/9.6/data/recovery.done

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

7.权限变更

chown -R postgres:postgres /var/lib/pgsql/9.6/data/

8. 重启服务

#同时注意打开防火墙端口打开

[root@psql_master ~]# systemctl restart postgresql-9.6

三、从库配置

先清空data目录（默认/var/lib/pgsql/9.6/data）

cd /var/lib/pgsql/9.6/data

rm -fr *

1. 基础备份(主库需索表，只读不能写)

[root@pg_slave ~]# pg_basebackup -h 192.168.159.150 -p 5432 -U replica -F p -P -D /var/lib/pgsql/9.6/data/

#-h，主库主机，-p，主库服务端口；

#-U，复制用户；

#-F，p是默认输出格式，输出数据目录和表空间相同的布局，t表示tar格式输出；

#-P，同--progress，显示进度；

#-D，输出到指定目录；

#因为主库采用的是md5认证，这里需要密码认证。

2. 备份目录权限

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

[root@pg_slave ~]# chown -R postgres:postgres /var/lib/pgsql/9.6/data/

3.修改recovery文件

mv recovery.done recovery.conf

vim recovery.conf

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

4.创建pg_archive目录

mkdir -p /var/lib/pgsql/pg_archive

chown -R postgres:postgres  /var/lib/pgsql/pg_archive

5.配置.pgpass

su - postgres

vim ~/.pgpass

192.168.159.150:5432:replication:replica:replica
192.168.159.151:5432:replication:replica:replica

6.启动服务

systemctl start postgresql-9.6 

四、验证主从

1.查看进程

1.1主库sender进程

1.2从库receive进程

 2.在主库上查看复制状态

postgres=# x

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为从。

3.建表写入数据测试
主库上建一个测试库，测试表，然后写入数据，在从库上看是否有主库写入的测试数据。

五、主备切换

主数据库是读写的，备数据库是只读的。当主数据库宕机了，可以通过pg_controldata命令将从库提升为主库（将只读模式变成读写），实现一些基本的HA应用。也可以通过建立触发文件提升为主库，不推荐。命令快捷不容易出错。

1.查看/var/lib/pgsql/9.6/data复制状态

主库
[root@pg_master ~]# pg_controldata /var/lib/pgsql/9.6/data/
pg_control version number: 960
Catalog version number: 201608131
Database system identifier: 6362107256088627972
Database cluster state: in production
从库
pg_control version number: 960
Catalog version number: 201608131
Database system identifier: 6362107256088627972
Database cluster state: in archive recovery

2.主库故障

[root@pg_master ~]# systemctl stop postgresql-9.6

[root@pg_master ~]# pg_controldata /var/lib/pgsql/9.6/data

pg_control version number: 960
Catalog version number: 201608131
Database system identifier: 6362107256088627972
Database cluster state: shut down

3.提升从库为主库（在从库上执行）

[root@pg_slave ~]# su - postgres -c "pg_ctl promote"

server promoting

4.查看从库日志，状态及进程

[root@pg_master ~]#  cat /var/lib/pgsql/9.6/data/pg_log/postgresql-Mon.log

[root@pg_master ~]# pg_controldata /var/lib/pgsql/9.6/data

pg_control version number: 960
Catalog version number: 201608131
Database system identifier: 6362107256088627972
Database cluster state: in production

[root@pg_master ~]# ps -ef|grep postgres

postgres  34461   1133  0 Mar05 ?        00:00:00 postgres: wal sender process replica 192.168.159.150(49947) streaming 0/F006670

5.恢复pg_master(192.168.159.150)，然后作为新的从库，在恢复之前可以往新主库写入数据，然后启用服务,数据会同步至新的从库。

[root@pg_master ~]# cd /var/lib/pgsql/9.6/data

[root@pg_master ~]# mv recovery.done reconvery.conf

[root@pg_master ~]# systemctl start postgresql-9.6

[root@pg_master ~]# ps -ef|grep postgres

postgres  32502  24304  0 Mar05 ?        00:00:15 postgres: wal receiver process   streaming 0/F006750