原文地址是 PostgreSQL and the logical replication ，主要介绍了 PostgreSQL 10 的逻辑复制功能，并使用 Docker 搭建环境演示了该功能的简单使用。本文算是中文翻译版本，所有命令都是重新执行过的，有些地方可能略做补充说明。
本文我将简单测试 PostgreSQL 10 新的复制模式——逻辑复制（Logical Replication）。其实官方给出了详细的说明，请查看 Logical Replication。
逻辑复制 – Logical Replication
在 PostgreSQL 10 之前，我们只能复制整个 Cluster，即所有的数据库表。通常情况下，这种复制模式被人们所接受；但在一些场景下却不是那么的高效，比如我只想在备库上读某个表，为什么要把主库的所有表都复制过来呢。
逻辑复制解决了这一痛点，允许我们只复制特定的表。它的操作原则如下：
- 在主库选择需要复制的表，并创建 publication
- 在备库创建 subscription
注意：数据库模版和 DDL 命令无法被复制，必须手动在备库执行相应的操作以保持一致性。更多有关逻辑复制的限制请查阅 Restrictions。
通过 Docker 搭建环境
接下来，我们将使用 Docker 演示逻辑复制的基本功能。
- 使用 Docker 建立演示环境
$ docker network create lab
26c093e0c39d5e5d02133f69acc2c00f4bc47868f4008e1efb3f35c3c2356978
$ docker container run --detach --name pgmaster --network lab -e POSTGRES_PASSWORD=password postgres:10.1-alpine
Unable to find image 'postgres:10.1-alpine' locally
10.1-alpine: Pulling from library/postgres
605ce1bd3f31: Pull complete
8d2abe692710: Pull complete
a18eca080102: Pull complete
91cfdfa52a99: Pull complete
452cbf05af6d: Pull complete
4a256b03e5bd: Pull complete
379c8298095d: Pull complete
12207b3814c9: Pull complete
373a2b3fdfa1: Pull complete
Digest: sha256:d6150452877361e26d0fd178d278af8f80d59c75cbb5c2413cfb4fdb5ed4f750
Status: Downloaded newer image for postgres:10.1-alpine
2932a6d81218c7c0548a0f005d230ba46c3f23d40571d40fdb97c8c7ce354cce
$ docker container run --detach --name pgslave --network lab -e POSTGRES_PASSWORD=password postgres:10.1-alpine
ad0c4c67710757922904bfcf471f9a8bb6351e5fca3aa37b50805349b4bfcbfd
主库设置
更改 WAL 级别并重启 container：逻辑复制需要 “logical” 级别
$ docker container exec -it pgmaster /bin/sh
/ # psql -U postgres
psql (10.1)
Type "help" for help.
postgres=# ALTER SYSTEM SET wal_level = 'logical'; ALTER SYSTEM
postgres=# \q
/ # exit
$ docker container restart pgmaster
pgmaster
创建数据库和表
$ docker container exec -it pgmaster /bin/sh
/ # psql -U postgres
psql (10.1)
Type "help" for help.
postgres=# create database pea;
CREATE DATABASE
postgres=# \c pea
You are now connected to database "pea" as user "postgres".
pea=# create table hashes(id serial, value char(33), primary key(value));
CREATE TABLE
pea=#
注意：创建的表必须设置主键或唯一性索引，否则需要定义 REPLICA IDENTITY 才能实现逻辑复制。
向表里插入一些数据
pea=# insert into hashes (select generate_series(1,1000),md5(random()::text)) ;
INSERT 0 1000
创建用于逻辑复制的用户
pea=# CREATE ROLE replicate WITH LOGIN PASSWORD 'Azerty' REPLICATION ;
CREATE ROLE
为该表创建 publication
pea=# CREATE PUBLICATION pubhash FOR TABLE hashes ;
CREATE PUBLICATION
设置逻辑复制用户对该表的权限，至少有读权限才能做逻辑复制
pea=# GRANT SELECT ON hashes TO replicate;
GRANT
开启主库的日志记录，打开 logging_collector 开关并设置日志文件位置和目录等，保存后重启备库。
$ docker container exec -it pgmaster /bin/sh
/ # vi /var/lib/postgresql/data/postgresql.conf
/ # exit
$ docker container restart pgmaster
注：可使用 “docker logs -f pgmaster” 查看 PostgreSQL 的日志输出，这样的话就不需要设置 logging_collector 了。
至此，主库的设置完毕，下面来看备库的设置。
备库设置
首先，按照主库的设置方法，打开备库的日志记录功能。
创建数据库
$ docker container exec -it pgslave /bin/sh
/ # psql -U postgres
psql (10.1)
Type "help" for help.
postgres=# create database pea_repl;
CREATE DATABASE
postgres=# \c pea_repl
You are now connected to database "pea_repl" as user "postgres".
pea_repl=#
创建一个 subscription
pea_repl=# CREATE SUBSCRIPTION subhash CONNECTION 'host=pgmaster dbname=pea user=replicate password=Azerty' PUBLICATION pubhash;
ERROR: relation "public.hashes" does not exist
pea_repl=#
注意这里 PostgreSQL 报错了，提示 hashes 这个表不存在，这是逻辑复制的 “缺点”：需要手动创建要复制的表。
pea_repl=# create table hashes (id serial, value char(33), primary key(value));
CREATE TABLE
再次创建 subscription
pea_repl=# CREATE SUBSCRIPTION subhash CONNECTION 'host=pgmaster dbname=pea user=replicate password=Azerty' PUBLICATION pubhash;
NOTICE: created replication slot "subhash" on publisher
CREATE SUBSCRIPTION
pea_repl=#
现在，逻辑复制已经生效，hashes 表的内容已经被复制到了备库。
pea_repl=# select count(*) from hashes;
count
-------
1000
(1 row)
逻辑复制功能已经正常工作了。
如何查看日志
逻辑复制已经生效，查看主库和备库的日志。
主库日志
$ docker container exec -it pgmaster /bin/sh
/ # tail -f /var/lib/postgresql/data/log/postgresql-2019-02-28_012727.log |tee
2019-02-28 01:32:41.096 UTC [49] LOG: logical decoding found consistent point at 0/161F308
2019-02-28 01:32:41.096 UTC [49] DETAIL: There are no running transactions.
2019-02-28 01:32:41.105 UTC [50] LOG: starting logical decoding for slot "subhash"
2019-02-28 01:32:41.105 UTC [50] DETAIL: streaming transactions committing after 0/161F340, reading WAL from 0/161F308
2019-02-28 01:32:41.106 UTC [50] LOG: logical decoding found consistent point at 0/161F308
2019-02-28 01:32:41.106 UTC [50] DETAIL: There are no running transactions.
2019-02-28 01:32:41.130 UTC [51] LOG: logical decoding found consistent point at 0/161F340
2019-02-28 01:32:41.130 UTC [51] DETAIL: There are no running transactions.
备库日志
$ docker container exec -it pgslave /bin/sh
/ # tail -f /var/lib/postgresql/data/log/postgresql-2019-02-28_012828.log |tee
2019-02-28 01:28:28.136 UTC [19] LOG: database system was shut down at 2019-02-28 01:28:27 UTC
2019-02-28 01:28:28.142 UTC [1] LOG: database system is ready to accept connections
2019-02-28 01:32:28.294 UTC [36] ERROR: relation "public.hashes" does not exist
2019-02-28 01:32:28.294 UTC [36] STATEMENT: CREATE SUBSCRIPTION subhash CONNECTION 'host=pgmaster dbname=pea user=replicate password=Azerty' PUBLICATION pubhash ;
2019-02-28 01:32:41.103 UTC [38] LOG: logical replication apply worker for subscription "subhash" has started
2019-02-28 01:32:41.107 UTC [39] LOG: logical replication table synchronization worker for subscription "subhash", table "hashes" has started
2019-02-28 01:32:41.139 UTC [39] LOG: logical replication table synchronization worker for subscription "subhash", table "hashes" has finished
其他测试
为 hashes 表添加一列，然后删除所有数据
/ # psql -U postgres
psql (10.1)
Type "help" for help.
postgres=# \c pea
You are now connected to database "pea" as user "postgres".
pea=# alter table hashes add column gold boolean default false ;
ALTER TABLE
pea=# delete from hashes ;
DELETE 1000
pea=#
这时，我们会在备库收到错误信息。
$ docker container exec -it pgslave /bin/sh
/ # tail -f /var/lib/postgresql/data/log/postgresql-2019-02-28_012828.log |tee
2019-02-28 01:46:18.339 UTC [38] ERROR: logical replication target relation "public.hashes" is missing some replicated columns
2019-02-28 01:46:18.340 UTC [1] LOG: worker process: logical replication worker for subscription 16394 (PID 38) exited with exit code 1
2019-02-28 01:46:18.341 UTC [71] LOG: logical replication apply worker for subscription "subhash" has started
2019-02-28 01:46:18.354 UTC [71] ERROR: logical replication target relation "public.hashes" is missing some replicated columns
2019-02-28 01:46:18.354 UTC [1] LOG: worker process: logical replication worker for subscription 16394 (PID 71) exited with exit code 1
同时，主库也会提示逻辑复制出错的信息。
$ docker container exec -it pgmaster /bin/sh
/ # tail -f /var/lib/postgresql/data/log/postgresql-2019-02-28_012727.log |tee
2019-02-28 01:50:19.310 UTC [138] LOG: logical decoding found consistent point at 0/161F420
2019-02-28 01:50:19.310 UTC [138] DETAIL: There are no running transactions.
2019-02-28 01:50:19.321 UTC [138] LOG: could not send data to client: Connection reset by peer
2019-02-28 01:50:19.321 UTC [138] CONTEXT: slot "subhash", output plugin "pgoutput", in the change callback, associated LSN 0/166F778
2019-02-28 01:50:24.330 UTC [139] LOG: starting logical decoding for slot "subhash"
2019-02-28 01:50:24.330 UTC [139] DETAIL: streaming transactions committing after 0/1661690, reading WAL from 0/161F420
2019-02-28 01:50:24.330 UTC [139] LOG: logical decoding found consistent point at 0/161F420
2019-02-28 01:50:24.330 UTC [139] DETAIL: There are no running transactions.
2019-02-28 01:50:24.350 UTC [139] LOG: could not send data to client: Connection reset by peer
2019-02-28 01:50:24.350 UTC [139] CONTEXT: slot "subhash", output plugin "pgoutput", in the change callback, associated LSN 0/1668950
2019-02-28 01:50:29.360 UTC [143] LOG: starting logical decoding for slot "subhash"
2019-02-28 01:50:29.360 UTC [143] DETAIL: streaming transactions committing after 0/1661690, reading WAL from 0/161F420
2019-02-28 01:50:29.360 UTC [143] LOG: logical decoding found consistent point at 0/161F420
2019-02-28 01:50:29.360 UTC [143] DETAIL: There are no running transactions.
2019-02-28 01:50:29.371 UTC [143] LOG: could not send data to client: Connection reset by peer
2019-02-28 01:50:29.371 UTC [143] CONTEXT: slot "subhash", output plugin "pgoutput", in the change callback, associated LSN 0/166E158
2019-02-28 01:50:34.381 UTC [144] LOG: starting logical decoding for slot "subhash"
2019-02-28 01:50:34.381 UTC [144] DETAIL: streaming transactions committing after 0/1661690, reading WAL from 0/161F420
2019-02-28 01:50:34.381 UTC [144] LOG: logical decoding found consistent point at 0/161F420
2019-02-28 01:50:34.381 UTC [144] DETAIL: There are no running transactions.
2019-02-28 01:50:34.391 UTC [144] LOG: could not send data to client: Connection reset by peer
2019-02-28 01:50:34.391 UTC [144] CONTEXT: slot "subhash", output plugin "pgoutput", in the change callback, associated LSN 0/166A5E8
解决这个问题就需要将备库的数据库表修改成与主库表的样子，修改之后逻辑复制就恢复正常了。
$ docker container exec -it pgslave /bin/sh
/ # psql -U postgres
psql (10.1)
Type "help" for help.
postgres=# \c pea_repl
You are now connected to database "pea_repl" as user "postgres".
pea_repl=# alter table hashes add column gold boolean default false ;
ALTER TABLE
pea_repl=# \q
/ # tail -f /var/lib/postgresql/data/log/postgresql-2019-02-28_012828.log |tee
2019-02-28 01:53:20.053 UTC [169] LOG: logical replication apply worker for subscription "subhash" has started
2019-02-28 01:53:20.067 UTC [169] ERROR: logical replication target relation "public.hashes" is missing some replicated columns
2019-02-28 01:53:20.067 UTC [1] LOG: worker process: logical replication worker for subscription 16394 (PID 169) exited with exit code 1
2019-02-28 01:53:25.073 UTC [173] LOG: logical replication apply worker for subscription "subhash" has started
2019-02-28 01:53:25.088 UTC [173] ERROR: logical replication target relation "public.hashes" is missing some replicated columns
2019-02-28 01:53:25.088 UTC [1] LOG: worker process: logical replication worker for subscription 16394 (PID 173) exited with exit code 1
2019-02-28 01:53:30.090 UTC [175] LOG: logical replication apply worker for subscription "subhash" has started
2019-02-28 01:53:30.107 UTC [175] ERROR: logical replication target relation "public.hashes" is missing some replicated columns
2019-02-28 01:53:30.107 UTC [1] LOG: worker process: logical replication worker for subscription 16394 (PID 175) exited with exit code 1
2019-02-28 01:53:35.114 UTC [176] LOG: logical replication apply worker for subscription "subhash" has started
添加新表并填充数据，更新 publication
$ docker container exec -it pgmaster /bin/sh
/ # psql -U postgres pea
psql (10.1)
Type "help" for help.
pea=# create table hash2hash (id serial, value char(33), primary key(value) ) ;
CREATE TABLE
pea=# grant select on hash2hash to replicate ;
GRANT
pea=# insert into hash2hash (select generate_series(1,1000),md5(md5(random()::text))) ;
INSERT 0 1000
pea=# alter publication pubhash add table hash2hash ;
ALTER PUBLICATION
pea=#
同时，在备库创建 hash2hash 表，刷新 subscription，查看日志确认逻辑复制已经生效。
$ docker container exec -it pgslave /bin/sh
/ # psql -U postgres pea_repl
psql (10.1)
Type "help" for help.
pea_repl=# create table hash2hash (id serial, value char(33), primary key(value) ) ;
CREATE TABLE
pea_repl=# alter subscription subhash refresh publication ;
ALTER SUBSCRIPTION
pea_repl=# \q
/ # tail -f /var/lib/postgresql/data/log/postgresql-2019-02-28_012828.log |tee
2019-02-28 01:53:20.067 UTC [1] LOG: worker process: logical replication worker for subscription 16394 (PID 169) exited with exit code 1
2019-02-28 01:53:25.073 UTC [173] LOG: logical replication apply worker for subscription "subhash" has started
2019-02-28 01:53:25.088 UTC [173] ERROR: logical replication target relation "public.hashes" is missing some replicated columns
2019-02-28 01:53:25.088 UTC [1] LOG: worker process: logical replication worker for subscription 16394 (PID 173) exited with exit code 1
2019-02-28 01:53:30.090 UTC [175] LOG: logical replication apply worker for subscription "subhash" has started
2019-02-28 01:53:30.107 UTC [175] ERROR: logical replication target relation "public.hashes" is missing some replicated columns
2019-02-28 01:53:30.107 UTC [1] LOG: worker process: logical replication worker for subscription 16394 (PID 175) exited with exit code 1
2019-02-28 01:53:35.114 UTC [176] LOG: logical replication apply worker for subscription "subhash" has started
2019-02-28 02:16:01.200 UTC [226] LOG: logical replication table synchronization worker for subscription "subhash", table "hash2hash" has started
2019-02-28 02:16:01.228 UTC [226] LOG: logical replication table synchronization worker for subscription "subhash", table "hash2hash" has finished
同时，可以在备库上查到新表的数据。
$ docker container exec -it pgslave /bin/sh
/ # psql -U postgres pea_repl
psql (10.1)
Type "help" for help.
pea_repl=# select count(*) from hash2hash ;
count
-------
1000
(1 row)
pea_repl=#
在备库停止逻辑复制
停止逻辑复制后，之前复制到备库的数据不会丢失。
$ docker container exec -it pgslave /bin/sh
/ # psql -U postgres pea_repl
psql (10.1)
Type "help" for help.
pea_repl=# alter subscription subhash disable ;
ALTER SUBSCRIPTION
pea_repl=# drop subscription subhash ;
NOTICE: dropped replication slot "subhash" on publisher
DROP SUBSCRIPTION
pea_repl=#
pea_repl=# select count(*) from hash2hash ;
count
-------
1000
(1 row)
pea_repl=#
分类：PostgreSQL
