高可用方案之PostgreSQL的逻辑复制和物理复制（二）

1、physical 复制的两种思路

1.1、知识点：什么是归档 archive ？

arhive_mode=on 开启归档进程，服务里会多一个 归档进程

当启用archive_mode时，通过设置archive_command将已完成的WAL段发送到归档存储

注意：触发归档有三种方式： 

1.手动切换wal日志，select pg_switch_wal() 

2.wal日志写满后触发归档，配置文件默认达到16M后就会触发归档，wal_keep_segments = 16 

3.归档超时触发归档，archive_timeout 

archive_mode=on 

archive_command=' test ! cp %p /data/%f'

特别要注意 /data 这个归档文件夹 需要有 postgres的写权限

 

若没有，则 archiver process 会失败；查看pg_log可以知道原因

1.2、什么是pg_basebackup？

pg_basebackup 是一个备份命令 

用法： /opt/PostgreSQL/10/bin/pg_basebackup -h xxx.xx.x.xx -R -D /opt/PostgreSQL/10/data -U postgres -W

-D 指定备份文件将要写入的目录

-h 要备份的主机 

-R  是否写 recovery.conf 

在仅添加 -D 情况下，仅仅相当于复制，除开pid文件等 ；scp 可以在新机器上systemctl restart postgresql-10 也是可以使用的

可以对比 直接 scp data文件夹到新机器上 ，然后启动服务的效果；

（注意文件夹权限，data文件夹必须为postgres可以访问）

1.3、为什么不直接scp，而要pg_basebackup呢？

因为pg_basebackup 除了复制，还将主库进入备份模式，复制，再退出主库的备份模式；同时，被复制出来的data文件夹里也自动删除了 posmaster.pid 。如果有 -R 参数，还会自动传教 recover.conf 。

1.4、什么是 recovery.conf ？

当数据库重启时，会检查自动redo wal日志。 

在将wal日志 redo完之前，都是恢复模式

若data文件下配置了 recovery.conf 会先根据此文件中的内容做一些事情。

当standby_mode=on 时，系统处于恢复模式。

恢复模式下，只可读不可写；可以重放wal日志。

表现一：此时若把新的wal日志copy到 pg_wal 文件夹下，则会自动apply这个日志，实现数据库的更新

表现二：此时若 restore_command 里有内容，则执行resotre_command 里的命令 

restore_command 命令示例：  restore_command ='cp /path/%f %p'

表现三：若此时 

primary_conninfo = 'user=xxxx password=xxxx host=xxx.xx.x.xx port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'

则为流复制，不断执行 流传输过来的wal日志 

2、流复制

配置流复制，主库只要开启 replication 权限（pg_hba.conf)，添加红线一行

我是新装的postgresql-10，安装过程就不再介绍了，安装好后需要修改pg_hba.conf文件,修改内容为下面红线部分：

然后执行命令： systemctl restart postgresql-10 重启数据库就可以了

为了测试，需要建立表，执行：SELECT random() as t_1 into test_1;建立一个test_1的表，查看数据如下：

 

然后再是从库，

1、先形成一个主库的基本备份

2、配置 recover.conf ,开启流复制

3、重新启动

1 和2 可以通过 /opt/PostgreSQL/10/bin/pg_basebackup -h xxx.xx.x.xx -R -D /opt/PostgreSQL/10/data -U postgres -W 一步到位 

我是另一台机子上新装的postgresql-10，把PostgreSQL里面data删了，然后执行 /opt/PostgreSQL/10/bin/pg_basebackup -h xxx.xx.x.xx -R -D /opt/PostgreSQL/10/data -U postgres -W命令，在这之前你会发现你的data为root用户的权限，你需要改成postgres用户的权限，执行chown -R postgres:postgres data/，如下：

 重启数据库systemctl start postgresql-10，查看数据库数据，发现也有一个test_1表，数据和主库一样

查看进程：

主库下的进程：

 

从库下的进程：

 

注意：9.x版本有些不同， 9.x版本需要修改postgresql.conf 的"hot_standby=on"，我的10.6版本是默认为on，如果此项不设置，从库数据库会打不开，因为数据接收不全，导致出错

记住，从库的数据库是只读，不能更改，而且从库的数据会随主库改变而变化，只有主从库的那两个进程还在。

3、 归档恢复 （archive recover）

一个wal日志 一个wal日志的恢复 

关键在看懂 recover.conf   pg_home/share/下有例子

# When standby_mode is enabled, the PostgreSQL server will work as a

# standby. It will continuously wait for the additional XLOG records, using

# restore_command and/or primary_conninfo.

standby_mode=on 的时候，从数据库不允许其他读写，只允许自己内部的 restore ；

流复制的时候，使用的是 primary_conninfo 自动复制、恢复

日志复制，需要执行 restore_command 命令 ，来达到同样的效果；

PITR恢复是基于文件系统备份和wal文件的备份

若standby_mode=off ,可手动启动恢复，by 重启服务

若 standby_mode=on  , 自动执行restore_command 

3.1、先看如何手动通过 基础备份+wal日志 来恢复一个数据库的

1 通过 pg_basebackup 获得了一  data文件夹；

我这里使用流复制的从库里面的data文件夹来做实验

2 在另外一台机器上（106.12.193.xxx）有 pg的安装文件（不包括data目录或删除掉）

3 将新的wal日志复制到待恢复的机器上

在从库的服务器上执行： scp -r data/ root@106.12.193.xxx:/opt/PostgreSQL/10/ 

4 启动服务

在106.12.193.xxx上执行：systemctl start postgresql-10，查看106.12.193.xxx数据库发现和从库的数据库一样的数据

可在recover.conf 中设置 一些选项，控制恢复行为；比如  recovery_target_time = '2016-04-21 14:49:14' 控制恢复的截止时间

3.2、wal复制，完整操作流程

1 在从库上通过pg_basebackup 获得基础备份,暂时不启动pg

2 在主库中定义 archive_mode= on   

archive_command='cp %p /data/%f'

chown -R postgres:postgres data/

3 从库上编辑 recovery.conf 

standby_mode=on 

restore_command ='scp postgres@xxx.xx.x.x:/opt/postgresql/10/data/%f%p'

standby_mode下的进程

3.3、逻辑复制

物理复制下的进程

 

修改 wal_level=logical 同时 注释掉 archive_mode  和 archive_command后的进程 

在master某张表上创建 发布者

create publication test10_pub for table test10;

在订阅者上 创建 订阅

create subscription test10_sub connection 'host=xxx.xx.x.xx user=postgres password=xxxxx dbname=postgres' publication test10_pub;

此时 在naster上 的进程 

 

subscr上的进程 

注意事项 和概念 

发布者需要设置wal_level=logical，同时开启足够的worker，设置足够大的replication slot，设置足够多的sender。

因为每一个订阅，都要消耗掉一个replication slot，需要消耗一个wal sender，一个worker进程。

订阅者，需要指定发布者的连接信息，以及 publication name，同时指定需要在publication数据库中创建的slot name。

在同一个数据库中，可以创建多个订阅。

订阅者和发布者的角色可以同时出现在同一个实例的同一个数据库中。

注意，订阅者一样需要设置：

max_replication_slots

max_logical_replication_workers

max_worker_processe

1）发布端

1. wal_level=logical

2. max_replication_slots，每一个订阅需要消耗一个slot，每一个指定了slot的流式物理复制也要消耗一个slot。

3. max_wal_senders，每一个slot要使用一个wal sender，每一个流式物理复制也要使用一个wal sender。

4. max_worker_processes，必须大于等于max_wal_senders加并行计算进程，或者其他插件需要fork的进程数。

2）订阅端

1. max_replication_slots，大于等于该实例总共需要创建的订阅数

2. max_logical_replication_workers，大于等于该实例总共需要创建的订阅数

3. max_worker_processes， 大于等于max_logical_replication_workers + 1 + CPU并行计算 + 其他插件需要fork的进程数.

逻辑复制，本质上是事务层级的复制，需要在订阅端执行SQL。

如果订阅端执行SQL失败（或者说引发了任何错误，包括约束等），都会导致该订阅暂停。

注意，update, delete没有匹配的记录时，不会报错，也不会导致订阅暂停。

用户可以在订阅端数据库日志中查看错误原因。

 

注意：逻辑复制，是跨平台的 ，跨版本的；而物理复制限于同平台，同版本