PostgreSQL 10.3数据库主从复制

 

PostgreSQL作为一个近年来才在国内开始发展的国外的开源数据库产品，无论是数据库本身的问题还是对数据库使用不当造成的问题，在一段时间内可能不容易找到或者找不到服务提供商，因此高可用性是使用PostgreSQL的一个非常重要的问题。本节介绍PostgreSQL的高可用Synchronous Replication+HOT STANDBY单活双机同步热备方式，这种方式可以保证只有在主备同时奔溃的情况下才会丢失数据，且Standby库可以提供读能力供分担负载。数据保护模式类似Oracle Active DataGuard的最大保护模式。

1 数据库 HA方式简介

　　本节仅对关键参数作出说明，其他有大量PG参数配置无法逐一解释，阅读前最好对PG有一定了解。

　　PostgreSQL数据库本身提供三种HA模式，这里简单介绍：

　　1. 基于日志文件的复制

　　Master库向Standby库异步传输数据库的WAL日志，Standby解析日志并把日志中的操作重新执行，以实现replication功能。缺点在于Master库必须等待每个WAL日志填充完整后才能发给Standby，如果在填充WAL日志的过程中Master库宕机，未发送的日志内的事务操作会全部丢失。

　　2. 异步流复制模式

　　Master库以流模式向Standby库异步传输数据库的WAL日志，Standby解析收到的内容并把其中的操作重新执行，以实现replication功能。这种方式和“基于日志文件的复制”相比不需要等待整个WAL日志填充完毕，大大降低了丢失数据的风险，但在Master库事务提交后，Standby库等待流数据的时刻发生Master宕机，会导致丢失最后一个事务的数据。同时备库可以配置成HOT Standby，可以向外提供查询服务，供分担负载。

　　3. 流同步复制模式（Synchronous Replication）

　　顾名思义，是流复制模式的同步版本。向Master库发出commit命令后，该命令会被阻塞，等待对应的WAL日志流在所有被配置为同步节点的数据库上提交后，才会真正提交。因此只有Master库和Standby库同时宕机才会丢数据。多层事务嵌套时，子事务不受此保护，只有最上层事务受此保护。纯读操作和回滚不受此影响。同时备库可以配置成HOT Standby，可以向外提供查询服务，供分担负载。采用这种模式的性能损耗依据网络情况和系统繁忙程度而定，网络越差越繁忙的系统性能损耗越严重。

　　可以依据实际情况权衡以上三种数据库复制模式的优缺点决定使用哪一种数据库高可用模式。这里推荐使用第二种高可用方式（异步流复制模式）实现数据库高可用。下面以异步流复制模式为例，说明HA环境搭建：

Master：10.19.100.2

Standby：10.19.100.3

　　注意：

　　　　服务器已经安装好PG 10.3。

　　　　下面的例子里为了方便配置和说明，并没有仔细规划归档日志的保存路径，归档日志在主备切换时用完即弃。

2 Master库

　　本节所有操作均在10.19.100.2上进行

2.1创建Replication用户

　　登陆Master库，创建具有用于传递数据的具有replication权限的用户（也可以直接用Super user当作replication用户，但不推荐）

$ psql -U postgres -d postgres
Password for user postgres:
psql.bin (10.3)
Type "help" for help.

postgres=# CREATE ROLE replicator login replication password '123456';

CREATE ROLE

2.2 Master库网络策略

　　修改Master库的pg_hba.conf，把Master库和Standby库的IP地址添加进Master库网络策略白名单中，使Standby库可以连上Master库，同时便于主备切换。

$ cd /PostgreSQL/10/data/
$ vi pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only

local   all             all                                     md5

# IPv4 local connections:

host    all             all             127.0.0.1/32            md5
host    all             all             10.19.100.0/24          md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                md5
host    replication     replicator      10.19.100.2/32            md5
host    replication     replicator      10.19.100.3/32            md5
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

2.3 Master库数据库配置

　　修改Master库的配置文件postgresql.conf，在原配置文件postgresql.conf的基础上修改,修改内容如下：

$ cd /PostgreSQL/10/data/
$ mkdir arch_dir
$ mkdir arch_dir_master
$ vi postgresql.conf

wal_level= logical
max_wal_senders = 10   # at least the number of standby
archive_mode = on
archive_command =  'test ! -f /PostgreSQL/10/data/arch_dir/%f && cp %p /PostgreSQL/10/data/arch_dir/%f'
synchronous_standby_names = ''   #standby application name, in recover.conf
hot_standby=on

　　说明：

　　synchronous_standby_names参数对应的参数为同步复制保障节点，如果该参数非空，则任何一个最上层的事务都会等待被同步到该参数指明的节点后才会在Master库提交，如果Standby库无响应Master库会被hung住。如果该参数为空，则表示采用异步复制方式。该参数可以配置多个保障节点，以逗号分隔，PG会从第一个开始尝试。开启同步复制存虽然能最大限度保证数据安全，但是会影响应用可用性。同步模式下，如果Master和Standby之间的网络状况很糟糕，那么同步复制会极大的拉低整个系统的性能；如果Standby宕机，主库会被hang住，虽然这里只记录异步复制，但具体采用何种复制请按自身业务场景选择。

　　修改后的postgresql.conf配置文件既能作为Master库的配置文件使用，也能作为Standby库的配置文件使用。

　　

　　创建切换为Standby库时的同步配置文件recovery.done

$ cd /PostgreSQL/10/data/
$ vi recovery.done

standby_mode=on
restore_command = 'cp /PostgreSQL/10/data/arch_dir_master/%f %p'
primary_conninfo='application_name=pg2 host=10.19.100.3 port=5432 user=replicator password=123456'
archive_cleanup_command ='pg_archivecleanup /PostgreSQL/10/data/arch_dir_master %r'
recovery_target_timeline = 'latest'

　　重启数据库

$ pg_ctl restart -D /PostgreSQL/10/data/ -l /PostgreSQL/10/data/pglog.log

3 Standby库

　　Standby库需要以Master库的完整备份+归档日志恢复而来，如果Master库尚未对外提供服务，也可以直接复制Master库的数据文件目录，这里采用第一种方法，更贴近实际环境。

　　本节所有操作均在10.19.100.3上进行。100.3上仅安装好数据库软件，没有启动数据库。

3.1创建Standby数据库

　　使用主库的热备创建standby库

 

$ psql -h 10.19.100.2 -p 5432 -U postgres -d postgres

Password for user postgres:
psql.bin (10.3)
Type "help" for help.

postgres=# select pg_start_Backup('backuptag',true);
pg_start_backup
-----------------
 0/3000060
(1 row)

　　复制主库数据目录

$ scp -r postgres@10.19.100.2:/PostgreSQL/10/data /PostgreSQL/10/data

　　停止主库的热备锁定

 

$ psql -h 10.19.100.2 -p 5432 -U postgres -d postgres

Password for user postgres:
psql.bin (10.3)
Type "help" for help.

postgres=# select pg_stop_backup();
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup
----------------
 0/3000168
(1 row)

　　清理复制过来的主库文件

$ rm -rf /PostgreSQL/10/data/pg_wal
$ rm -rf /PostgreSQL/10/data/postmaster.pid
$ rm –rf /PostgreSQL/10/data/arch_dir/*

　　修改备库的recovery文件

$ cd /PostgreSQL/10/data/
$ mv recovery.done recovery.conf
$ vi recovery.conf
standby_mode=on
restore_command = 'cp /PostgreSQL/10/data/arch_dir_master/%f %p'
primary_conninfo='application_name=pg3 host=10.19.100.2 port=5432 user=replicator password=123456'
archive_cleanup_command ='pg_archivecleanup /PostgreSQL/10/data/arch_dir_master %r'
recovery_target_timeline = 'latest'

　　准备恢复需要的完整的归档文件和wal文件

$ scp -r postgres@10.19.100.2:/PostgreSQL/10/data/pg_wal /PostgreSQL/10/data/
$ scp -r 10.19.100.2:/PostgreSQL/10/data/arch_dir /PostgreSQL/10/data/arch_dir_master

　　启动备库，观察备库日志

 

$ pg_ctl start -D /PostgreSQL/10/data -l /PostgreSQL/10/data/pglog.log
waiting for server to start..... done
server started

$ more /PostgreSQL/10/data/log/postgresql-2018-03-08_142945.log
2018-03-08 14:29:47.685 CST [21790] LOG:  started streaming WAL from primary at
0/4000000 on timeline 1

4 备库提升为主库的方法

　　流复制搭建完成后，备库是只读的，可以利用它进行读写分离均衡。如果主库失效需要提升备库为主库可以通过下面的命令。

$ pg_ctl promote -D /PostgreSQL/10/data/

　　观察日志可以看到，数据库结束recovery模式，开始提供服务

LOG:  archive recovery complete
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

　　并且可以看到recovery.conf文件被数据库自动更名为recovery.done。