PostgreSQL实战之物理复制和逻辑复制（一）

目录

物理复制和逻辑复制（一）

前言

1.1 异步流复制

1.1.1 以拷贝数据文件方式部署流复制

1.1.2 以pg_basebackup方式部署流复制

1.1.3 查看流复制同步方式

---

物理复制和逻辑复制（一）

前言

PostgreSQL早在9.0版本开始支持物理复制，也可称为流复制(Streaming Replication),通过流复制技术，可以从实例级复制出一个与主库一模一样的从库(也称之为备库)。举个简单的例子，在主机pghost1上创建了一个PostgreSQL实例，并在实例上创建多个数据库，通过流复制技术可以在另外一台主机如pghost2上创建一个热备只读PostgreSQL实例，我们通常将pghost1上的数据库称为主库(Primary Database或 Master)，pghost2上的数据库称为备库(Standby Database或Slave)，pghost1称为主节点，pghost2称为备节点。流复制同步方式有同步、异步两种，如果主节点和备节点不是很忙，通常异步模式下备库和主库的延迟时间能控制在毫秒级，将详细介绍两种同步方式的差异以及部署过程。
另一种复制方式为逻辑复制( Logical Replication)，通常也称之为选择性复制，因为逻辑复制可以做到基于表级别的复制，选择需要逻辑复制的表，而不是复制实例上的所有数据库的所有表，物理复制是基于实例级的复制，只能复制整个PostgreSQL实例。PostgreSQL10版本前不支持内置的逻辑复制，通常使用第三方逻辑复制工具，比如Slony-I、Londiste、pglogical等，PostgreSQL10版本开始支持内置的逻辑复制，这一新特性属PostgreSQL 10重量级新特性。
WAL(Write-Ahead Logging）日志记录数据库的变化，格式为二进制格式，当主机出现异常断电时，如果WAL文件已经写入成功，但还没来得及刷新数据文件，当数据库再次启动时会根据WAL日志文件信息进行事务前滚，从而恢复数据库到一致性状态。尽管流复制和逻辑复制都是基于WAL，但两者有本质不同，流复制是基于WAL物理复制，逻辑复制是基于WAL逻辑解析，将 WAL解析成一种清晰、易于理解的格式。
流复制和逻辑复制主要有以下差异:
（1）流复制是物理复制，其核心原理是主库将预写日志WAL日志流发送给备库，备库
接收到WAL日志流后进行重做，因此流复制是基于WAL日志文件的物理复制。逻辑复制核心原理也是基于WAL，逻辑复制会根据预先设置好的规则解析WAL日志，将WAL二进制文件解析成一定格式的逻辑变化信息，比如从WAL中解析指定表上发生的DML逻辑变化信息，之后主库将逻辑变化信息发送给备库，备库收到WAL逻辑解析信息后再应用日志。
（2）流复制只能对PostgreSQL实例级进行复制，而逻辑复制能够对数据库表级进行
复制。
（3）流复制能对DDL操作进行复制，比如主库上新建表、给已有表加减字段时会自动同步到备库，而逻辑复制主库上的DDL操作不会复制到备库。
（4）流复制主库可读写，但从库只允许查询不允许写入，而逻辑复制的从库可读写。
（5）流复制要求PostgreSQL大版本必须一致，逻辑复制支持跨PostgreSQL大版本。

1.1 异步流复制

流复制根据数据同步方式分为异步流复制和同步流复制，异步流复制是指主库上提交事务时不需要等待备库接收WAL日志流并写入到备库WAL日志文件时便返回成功，而同步流复制相反，后面会详细介绍异步流复制、同步流复制的部署过程。
这一小节先介绍PostgreSQL异步流复制的部署，异步流复制部署主要有两种方式，一种方式是拷贝数据文件方式，另一种方式是通过pg_basebackup命令行工具，这两种方式的绝大部分部署步骤都一样，只是数据复制的方式不同而已。
实验环境为两台虚拟机。

其中 pghost1为主节点， pghost2为备节点，以下先介绍以拷贝数据文件方式部署流复制。

1.1.1 以拷贝数据文件方式部署流复制

简单介绍PostgreSQL安装，重点介绍流复制部署，首先在pghost1和 pghost2上编译安装PostgreSQL。

在pghost1和 pghost2上创建操作系统用户和相关目录，如下所示:# groupadd postgres

# useradd postgres -g postgres
# passwd postgres
# mkdir -p /database/pg10/pg_root
# mkdir -p /database/pg10/pg_tbs
# chown -R postgres:postgres /database/pg10

/database/pg10/pg_root目录存储数据库系统数据文件，ldatabase/pg10/pg_tbs存储用户自定义表空间文件。
设置postgres操作系统用户环境变量，/home/postgres/.bash_profile文件添加以下内容:export PGPORT=1921

export PGUSER=postgres
export PGDATA=/database/pg10/pg_rootexport LANG=en_Us.utf8
export PGHOME=/opt/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export PATH=$PGHOME/bin:$PATH: .
export MANPATH=$PGHOME/share/man: $MANPATH
alias rm= 'rm -i'
alias ll= 'ls -lh'

以上内容可根据使用偏好设置环境变量。解压并编译PostgreSQL软件，如下所示:

# tar jxvf postgresql-10.0.tar.bz2
# cd postgresql-10.0
# ./configure --prefix=/opt/pgsql_10.0 --with-pgport=1921

configure过程中依赖操作系统包zlib、readline等，如果configure过程中报缺少相关依赖包，
通过yum install 命令安装相关依赖包即可，例如安装zlib、readline系统包，如下所示:

# yum install zlib readline

之后进行编译安装，如下所示:

# gmake world
# gmake install-world

gmake world表示编译所有能编译的东西，包括文档和附加模块，而gmake命令不会安装这些内容，gmake world相比 gmake编译时间长很多。之后通过gmake install-world命令安装PostgreSQL软件到目录/opt/pgsql_10.0，安装后/opt/pgsql_10.0/share目录下生成了/doc文档目录，并且/opt/pgsql_10.0/share/extension目录下生成了大量的扩展模块文件，这些扩展模块提供的功能能够丰富PostgreSQL特性，在生产环境下推荐gmake world编译安装。

PostgreSQL软件安装在pgsql_10.0目录，为了便于管理，做个软链接，如下所示:

# ln -s /opt/pgsql_10.0 /opt/pgsql

编译安装PostgreSQL软件过程使用的是root操作系账号，之后的部署步骤使用操作系统普通账号postgres即可，在pghost1 上使用postgres系统账号执行initdb命令初始化数据库，如下所示:

$ initdb -D /database/pg10/pg_root -E UTF8 --locale=C -U postgres -w

以上初始化数据库后，ldatabase/pg10/pg_root目录下将产生系统数据文件，之后配置$PGDATA/postgresql.conf，设置以下参数:

wal_level = replica                # minimal, replica, or logical
archive_mode = on                # enables archiving; off, on,or always
archive_command = '/bin/date'    # command to use to archive a logfile segment
max_wal_senders = 10            # max number of walsender processes
wal_keep_segments = 512         # in logfile segments,16MB each; 0 disables
hot_standby = on

以上几个postgresql.conf参数是流复制的主要参数，其他可选参数没有列出。
（1）wal_level参数控制WAL日志信息的输出级别，有minimal、replica、logical三种模式，minimal记录的WAL日志信息最少，除了记录数据库异常关闭需要恢复时的WAL信息外，其他操作信息都不记录;replica记录的WAL信息比 minimal信息多些，会记录支持WAL归档、复制和备库中启用只读查询等操作所需的WAL信息;logical记录的WAL日志信息最多，包含了支持逻辑解析(10版本的新特性，逻辑复制使用这种模式，本章后面会介绍)所需的WAL ; replica模式记录的WAL信息包含了minimal记录的信息，logical模式记录的WAL信息包含了replica记录的信息，此参数默认值为replica，调整此参数需重启数据库生效，开启流复制至少需要设置此参数为replica级别。
（2）archive_mode参数控制是否启用归档，off表示不启用归档，on表示启用归档并使用archive_command参数的配置命令将WAL日志归档到归档存储上，此参数设置后需重启数据库生效，这里通常设置成on。
（3）archive_command参数设置WAL归档命令，可以将WAL归档到本机目录，也可以归档到远程其他主机上，由于流复制的配置并不一定需要依赖配置归档命令，我们将归档命令暂且设置成伪归档命令/bin/date，后期如果需要打开归档直接配置归档命令即可，第13章备份与恢复章节会详细介绍此参数的配置。
（4）max_wal_senders参数控制主库上的最大WAL发送进程数，通过pg_basebackup命令在主库上做基准备份时也会消耗WAL进程，此参数设置不能比max_connections参数值高，默认值为10，一个流复制备库通常只需要消耗流复制主库一个WAL发送进程。
（5）wal_keep_segments参数设置主库pg_wal目录保留的最小WAL日志文件数，以便备库落后主库时可以通过主库保留的WAL进行追回，这个参数设置得越大，理论上备库在异常断开时追平主库的机率越大，如果归档存储空间充足，建议将此参数配置得大些，由于默认情况下每个WAL文件为16MB(编译时可通过--with-wal-segsize参数设置WAL文件大小)，因此pg_wal目录大概占用空间为wal_keep_segments参数值×16MB，这里为512×16MB=8GB，实际情况下pg_wal目录下的WAL文件数会比此参数的值稍大些。
（6）hot_standby参数控制数据库恢复过程中是否启用读操作，这个参数通常用在流复制备库，开启此参数后流复制备库支持只读SQL，但备库不支持写操作，主库上也设置此参数为on。
以上是流复制配置过程中主要的postgresql.conf参数，其他参数没有列出，主库和备库的postgresql.conf配置建议完全一致。
配置主库的pg_hba.conf文件，添加以下内容:

# replication privilege.
host	replication    	repuser	    192.168.28.74/32	md5
host	replication	    repuser	    192.168.28.75/32	md5

这里为什么配置两条pg_hba.conf策略?因为主库和备库的角色不是静止的，它们的角色是可以互换的，比如做一次主备切换后角色就发生了变化，因此建议主库、备库的pg_hba.conf配置完全一致。
之后pghost1启动数据库，如下所示

[postgresepghost1 ~]$ pg_ctl start

使用超级用户 postgres登录到数据库创建流复制用户repuser，流复制用户需要有REPLICATION权限和LOGIN权限，如下所示:

CREATE USER repuser
    REPLICATION
    LOGIN
    cONNECTION LIMIT5
    ENCRYPTED PASSwORD ' re12a345';

建议为流复制创建专门的流复制用户。
以上完成了主库上的配置，接下来热备生成一个备库，制作备库过程中主库仍然可读写，不影响主库上的业务，以 postgres超级用户执行以下命令:

postgres=# SELECT pg_start_backup ('francs_bk1');
  pg_start_backup
--————-—--——-—---
  0 / 4000060
(1 row )

pg_start_backup()函数在主库上发起一个在线备份，命令执行成功后，将数据文件拷贝到备节点pghost2，如下所示:

$ tar czvf pg_root.tar.gz pg_root --exclude=pg_root/pg_wal
$ scp pg_root.tar.gz postgres@192.168.28.75:/database/pg10

pg_wal目录不是必须复制的，如果pg_wal目录下文件比较多，压缩包时可以排除这个目录，以节省数据拷贝时间，数据拷贝到备节点后，备节点的pg_wal目录需要手工创建，以上只是拷贝了pg_root系统数据目录，如果有另外的表空间目录也需要拷贝。
之后在pghost2解压文件，如下所示:

s tar xvf pg_root.tar.gz

文件拷贝到备节点后，在主库上执行以下命令:

postgres=#SELECT pg_stop_backup ( ) ;
NOTICE: pg_stop_backup complete,all required wAL segments have been archived
  pg_stop_backup
----------------
  0/ 2000130
(1 row)

以上命令表示完成在线备份，但备库上仍然需要做一些配置，之后配置pghost2主机上的recovery.conf，此配置文件提供了数据库恢复相关的配置参数，这个文件默认在$PGDATA目录下并不存在，可以在软件目录中找到这个模板，并复制到SPGDATA目录，如下所示:

$ cp $PGHOME/share/recovery.conf.sample $PGDATA/recovery.conf

在recovery.conf中配置以下参数:

recovery_target_timeline = 'latest'
standby mode = on
primary_conninfo = 'host=192.168.28.74 port=1921 user=repuser'

recovery_target_timeline参数设置恢复的时间线( timeline)，默认情况下是恢复到基
准备份生成时的时间线，设置成latest表示从备份中恢复到最近的时间线，通常流复制环境设置此参数为latest，复杂的恢复场景可将此参数设置成其他值。
standby_mode参数设置是否启用数据库为备库，如果设置成on，备库会不停地从主库上获取WAL日志流，直到获取主库上最新的WAL日志流。
primary_conninfo参数设置主库的连接信息，这里设置了主库IP、端口、用户名信
息，但没有配置明文密码，在连接串中给出数据库密码不是好习惯，建议将密码配置在隐藏文件~l.pgpass文件中。
配置~/.pgpass隐藏文件，如下所示:

[postgresepghost2 ~]$ touch .pgpass
[postgres@pghost2 ~]$ chmod 0600 .pgpass

.pgpass文件默认情况下不存在，需要手动创建并设置好0600权限。之后给.pgpass文件添加以下内容:

192.168.28.74:1921 :replication:repuser:re12a345
192.168.28.75:1921 :replication:repuser:re12a345

.pgpass文件内容分五个部分，分别为IP、端口、数据库名、用户名、密码，用冒号分隔，设置后，repuser用户可以免密码直接登录数据库。之后在pghost2上启动从库即可，如下所示:

s pg_ctl start

如果此步没报错，并且主库上可以查看到WAL发送进程，同时备库上可以看到WAL接收进程说明流复制配置成功，查看主库上的WAL发送进程，如下所示:

postgres28575284750 16:41 ?
00:00:00 postgres : wal sender process
repuser 192.168.28.75(57805) streaming 0/3025000

查看备库上的WAL接收进程，如下所示:

postgres 15449 153310 16:41 ?
00:00:00  postgres : wal receiver process
streaming 0/301FC68

接着在主库上创建一个测试表并插入数据，如下所示:

postgres=# CREATE TABLE test_sr (id int4) ;
CREATE TABLE
postgres=# INSERT INTO test_sr VALUES (1);
INSERT 0 1

在备库上验证数据是否已同步，如下所示:

postgres=#SELECT * FROM test_sr;
id
--------
1 
(1 row)

在主库上创建一张表后，在备库上立刻就能查询到了，值得一提的是，备库上postgresql.conf的hot_standby参数需要设置成on才支持查询操作，此参数调整后需重启数据库生效，如下所示。

hot_standby = on                #"off" disallows queries during recovery

如果此参数设置成off，通过psql连接数据库时会抛出以下错误:

[postgres@pghost2 ~]$ psql postgres postgres
psql: FATAL: the database system is starting up

以上信息显示数据库在恢复中，不允许连接数据库也不允许执行查询。
以上是异步流复制部署的所有过程，虽然本小节内容有些多，但总体来说流复制配置并不复杂，读者在配置过程中如遇错误，多查看SPGDATA/pg_log数据库日志，根据数据库日志报错信息进行问题排查。

1.1.2 以pg_basebackup方式部署流复制

部署流复制备库的数据复制环节主要包含三个步骤。
1) pg_start_backup('francs_bk1');
2) 拷贝主节点SPGDATA数据文件和表空间文件到备节点;
3) pg_stop_backup()。
以上三个步骤可以合成一步完成，PostgreSQL提供内置的pg_basebackup命令行工具支持对主库发起一个在线基准备份，并自动进入备份模式进行数据库基准备份，备份完成后自动从备份模式退出，不需要执行额外的pg_start_backup()和pg_stop_backup()命令显式地声明进入备份模式和退出备份模式，pg_basebackup工具是对数据库实例级进行的物理备份，因此这个工具通常作为备份工具对据库进行基准备份。
pg_basebackup工具发起备份需要超级用户权限或REPLICATION权限，注意max_wal_senders参数配置，因为 pg_basebackup工具将消耗至少一个WAL发送进程。本节将演示通过pg_basebackup 工具部署异步流复制，之前已经在pghost2上部署了一个备库，我们先将这个备库删除，之后通过pg_basebackup工具重新做一次备库，删除 pghost2上的备库只需要先停备库之后删除备库数据库数据文件即可，如下所示:

$ pg_ctl stop -m fast
waiting for server to shut down . . .. done
server stopped
$ rm -rf /database/pg10/pg_root
$ rm -rf /database/pg10/pg_tbs

之后在pghost2使用pg_basebackup工具做一个基准备份，如下所示:

$ pg_basebackup -D /database/pg10/pg_root -Fp -Xs -v -p -h 192.168.28.74 -p 1921
-urepuser
pg_basebackup: initiating base backup，waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 1/B9000028 on timeline 1
pg_basebackup: starting background WAL receiver
7791508/7791508 kB (100%),2/2 tablespaces
pg_basebackup: write-ahead log end point: 1/B90039EO
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

从以上日志信息看出pg_basebackup命令首先对数据库做一次checkpoint，之后基于时间点做一个全库基准备份，全备过程中会拷贝$PGDATA数据文件和表空间文件到备库节点对应目录，pg_basebackup主要选项解释如下:
（1） -D参数表示指定备节点用来接收主库数据的目标路径，这里和主库保持一致，依然
是ldatabase/pg10/pg_root目录。
（2）-F参数指定pg_basebackup命令生成的备份数据格式，支持两种格式，p(plain)格
式和 t(tar)格式，p(plain)格式是指生成的备份数据和主库上的数据文件布局一样，也就是说类似于操作系统命令将数据库SPGDATA系统数据文件、表空间文件完全拷贝到备节点;t(tar)格式是指将备份文件打个tar包并存储在指定目录里，系统文件被打包成base.tar，其他表空间文件被打包成oid.tar，其中 OID为表空间的OID。
（3）-X参数设置在备份的过程中产生的WAL日志包含在备份中的方式，有两种可选方式，f(fetch)和s(stream)，f(fetch)是指WAL日志在基准备份完成后被传送到备节点，这时主库上的wal_keep_segments参数需要设置得较大，以免备份过程中产生的WAL还没发送到备节点之前被主库覆盖掉，如果出现这种情况创建基准备份将会失败，f(fetch)方式下主库将会启动一个基准备份WAL发送进程;s(stream)方式中主库上除了启动一个基准备份WAL发送进程外还会额外启动一个WAL发送进程用于发送主库产生的WAL增量日志流，这种方式避免了f(fetch)方式过程中主库的WAL被覆盖掉的情况，生产环境流复制部署推荐这种方式，特别是比较繁忙的库或者是大库。
（4）-v参数表示启用verbose模式，命令执行过程中打印出各阶段的日志，建议启用此参数，了解命令执行到哪个阶段。
（5）-P参数显示数据文件、表空间文件近似传输百分比，由于执行pg_basebackup命令过程中主库数据文件会变化，因此这只是一个估算值;建议启用此选项，了解数据复制的进度。

-h、-p、-U参数为数据库连接通用参数，不再解释，以上只是pg_basebackup命令主要选项，其他选项读者可查看手册https://www.postgresql.org/docs/10/static/app-pgbasebackup.htmlo
pg_basebackup命令执行成功后，配置备库recovery.conf，之前已将此文件备份到家目录，从家目录将此文件复制到$SPGDATA目录下即可，如下所示:

$ cp ~/recovery.conf $PGDATA

之后在 pghost2上启动备库，如下所示:

$ pg_ctl start

这时备节点上已经有了WAL接收进程，同时主节点上已经有了WAL发送进程，表示流复制工作正常。

1.1.3 查看流复制同步方式

异步流复制部署完成后，可通过pg_stat_replication系统视图的sync_state字段查看流复制同步方式，如下所示:

postgres=#SELECT usename,application_name, client_addr ,sync_state
          FROM pg_stat_replication ;
usename | application_name | client_addr   | sync_state
--------＋----------------―+---------------+------------
repuser | walreceiver      | 192.168.28.75 | async
(l row )

pg_stat_replication视图显示主库上 WAL发送进程信息，主库上有多少个WAL 发送进程，此视图就对应多少条记录，这里主要看sync_state字段，sync_state字段的可选项包括:
（1）async:表示备库为异步同步方式。
（2）potential :表示备库当前为异步同步方式，如果当前的同步备库宕机后，异步备库
可升级成为同步备库。
（3）sync:当前备库为同步方式。
（4）quorum:此特性为PostgreSQL10版本新增特性，表示备库为quorum standbys的候选。
以上查询结果sync_state字段值为async，表示主备数据复制使用异步方式。