通过 Docker 验证 PostgreSQL 10 的逻辑复制功能

原文地址是 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 演示逻辑复制的基本功能。

1. 使用 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 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=#