逻辑复制mysql_PostgreSQL与 逻辑复制__在PostgreSQL１０版本以前，数据复制时我们只能是复制整个数据库集群．正如PostgreSQL其他功能一样，复制工作也会一切正常, 但在某...

# PostgreSQL与 逻辑复制

#### 作者：Pierre-Emmanuel André

#### 翻译：PostgreSQL中文社区 / doudou586

#### 发布：2017-12-12

#### 欢迎大家踊跃投稿，投稿信箱： press@postgres.cn

----

## 逻辑复制

在PostgreSQL１０版本以前，数据复制时我们只能是复制整个数据库集群．正如PostgreSQL其他功能一样，复制工作也会一切正常,　但在某些场景下，这并不是最佳的使用方式(比如我们只要系统中一点局部数据，为何要复制所有数据给我？)。

逻辑复制允许你复制指定的表．操作的原理如下：

- 在主数据库上选择拟要复制的指定表，然后创建发布(通道)．

- 在从数据库上我们创建订阅(通道)

#### 警示：数据库的模式和DDL指令是不会复制的．用户需要自行保证它们是相同的．

更多信息请浏览：复制限制

## 用 Docker 开始我们的实验

### 在Docker中创建我们的测试环境

```

% docker network create lab

f77d78a9a512af1afbbc7e5cb00a7e186d82ce1ba122c9c7353be8db344497ab

% docker container run --detach --name pgmaster --network lab -e POSTGRES_PASSWORD=password

postgres:10.1-alpine c5511d8d383fdd4c899dbd1cd03c16b6044168eca78052f9d5ed48e484c257f2

% docker container run --detach --name lab --network lab -e POSTGRES_PASSWORD=password

postgres:10.1-alpine 8f2a1a43f211d45314bf52eadef66d125be5b9ad37ab16f881f039b32bd887d4

```

#### 在主数据库端

修改 WAL 日志级别:

逻辑复制需要 WAL 的级别在 >= logical 以上, 我们可以修改这个设置然后重启容器。

```

% docker container exec -it pgmaster /bin/sh

# psql -U postgrespsql (10.1)

Type "help" for help.

postgres=# ALTER SYSTEM SET wal_level = 'logical';

ALTER SYSTEM

postgres=# exit

% docker container restart pgmaster

```

创建一个数据库pea ，并创建表 hashes :

```

% docker container exec -it pgmaster /bin/sh

# psql -U postgrespsql (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 的自增序列

插入一些数据:

```

pea=# insert into hashes (select generate_series(1,1000),md5(random()::text)) ;

INSERT 0 1000

```

创建一个用户replicate用于复制数据:

```

pea=# CREATE ROLE replicate WITH LOGIN PASSWORD 'Azerty' REPLICATION ;

CREATE ROLE

```

给表hashed 创建一个发布(通道):

```

pea=# CREATE PUBLICATION pubhash FOR TABLE hashes ;

CREATE PUBLICATION

```

修改用户replicate的角色和权限:

```

pea=# GRANT SELECT ON hashes TO replicate ;

GRANT

```

这个replicate 用户必须有读的权限。现在我们的主数据库已准备好了，现在我们去处理从数据库．

#### 在从数据库端

创建数据库:

```

% docker container exec -it pgslave /bin/sh

# psql -U postgrespsql (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=#

```

创建一个订阅(通道):

```

pea_repl=# CREATE SUBSCRIPTION subhash CONNECTION 'host=pgmaster dbname=pea user=replicate password=Azerty'

PUBLICATION pubhash ;

ERROR: relation "public.hashes" does not exist

```

PostgreSQL有意见啦，目标表还不存在．我们现在创建一下

```

pea_repl=# create table hashes (id serial, value char(33), primary key(value) ) ;

CREATE TABLE

```

再次创建订阅(通道):

```

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

```

现在在日志中会有这些内容：

```

2017-11-27 09:16:20.442 UTC [87] LOG: logical replication apply worker for subscription "subhash" has started

2017-11-27 09:16:20.451 UTC [88] LOG: logical replication table synchronization worker for subscription

"subhash", table "hashes" has started

2017-11-27 09:16:20.472 UTC [88] LOG: logical replication table synchronization worker for subscription

"subhash", table "hashes" has finished

```

所有的数据已经复制成功了：

```

pea_repl=# select count(*) from hashes ;

count

-------

1000

(1 row)

```

现在我们的逻辑复制已开始工作了．

#### 接下来，一些其他情况

增加一列字段，再删除数据测试一下：

```

pea=# alter table hashes add column gold boolean default false ;

ALTER TABLEpea=# delete from hashes ;

DELETE 1000

```

现在我们会在从数据库端看见以下错误信息：

```

2017-11-27 09:25:25.886 UTC [87] ERROR: logical replication target relation "public.hashes" is missing some

replicated columns

2017-11-27 09:25:25.887 UTC [1] LOG: worker process: logical replication worker for subscription 16394

(PID 87) exited with exit code 1

```

在主数据库端，系统也有意见啦：

```

2017-11-27 09:27:41.303 UTC [135] LOG: starting logical decoding for slot "subhash"

2017-11-27 09:27:41.303 UTC [135] DETAIL: streaming transactions committing after 0/1617B40,

reading WAL from 0/165E1D8

2017-11-27 09:27:41.303 UTC [135] LOG: logical decoding found consistent point at 0/165E1D8

2017-11-27 09:27:41.303 UTC [135] DETAIL: There are no running transactions.

2017-11-27 09:27:41.309 UTC [135] LOG: could not send data to client: Connection reset by peer

2017-11-27 09:27:41.309 UTC [135] CONTEXT: slot "subhash", output plugin "pgoutput", in the change callback,

associated LSN 0/165F0B0

```

现在我们在从数据库端新增这一列：

```

pea_repl=# alter table hashes add column good boolean default false ;

ALTER TABLE

```

现在逻辑复制就可以正常开始了：

```

2017-11-27 09:28:46.502 UTC [154] LOG: logical replication apply worker for subscription

"subhash" has started

```

再创建一个新表，插入一些数据，然后更新发布(通道)：

```

% docker container exec -it pgmaster /bin/sh

# psql -U postgres peapsql (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_repl=# create table hash2hash (id serial, value char(33), primary key(value) ) ;

CREATE TABLE

```

在从数据库端刷新订阅(通道)：

```

pea_repl=# alter subscription subhash refresh publication ;

ALTER SUBSCRIPTION

```

观察 从数据库端的日志：

```

2017-11-27 10:13:21.097 UTC [244] LOG: logical replication table synchronization worker for subscription "subhash", table "hash2hash" has started

2017-11-27 10:13:21.132 UTC [244] LOG: logical replication table synchronization worker for subscription

"subhash", table "hash2hash" has finished

```

检查从数据库端的数据：

```

pea_repl=# select count(*) from hash2hash ;

- count

-------

1000

(1 row)

```

#### 在从数据库端停止逻辑复制：

```

pea_repl=# alter subscription subhash disable ;

ALTER SUBSCRIPTION

pea_repl=# drop subscription subhash ;NOTICE: dropped replication slot "subhash" on publisher

DROP SUBSCRIPTION

```