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
```

![PostgreSQL_Community](/images/news/2016/pg_bot_banner.jpg)

PostgreSQL与 逻辑复制

PostgreSQL与逻辑复制