PostgreSQL10.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.2Standby：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 postgresPassword 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 onlylocal all all md5# IPv4 local connections:

host all all 127.0.0.1/32 md5host 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 md5host 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 standbyarchive_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.confhot_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=32 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 32 -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 32 -U postgres -d postgresPassword 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.confstandby_mode=on

restore_command = 'cp /PostgreSQL/10/data/arch_dir_master/%f %p'

primary_conninfo='application_name=pg3 host=10.19.100.2 port=32 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.logwaiting for server to start..... doneserver 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 at0/4000000 on timeline 1

4 备库提升为主库的⽅法

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

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

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

LOG: archive recovery complete

LOG: database system is ready to accept connectionsLOG: autovacuum launcher started

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