PostgreSQL与逻辑复制　　

doudou586 发布于2017-12-12 19:45:21　　

评论: 2　　

顶: 9749　

踩: 9751　

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

# PostgreSQL与 逻辑复制

#### 作者：Pierre-Emmanuel André
#### 翻译：PostgreSQL中文社区 / doudou586
#### 发布：2017-12-12
#### 欢迎大家踊跃投稿，投稿信箱： press@postgres.cn

----
## 逻辑复制

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

- 在主数据库上选择拟要复制的指定表，然后创建发布（通道）．
- 在从数据库上我们创建订阅（通道）

#### 警示：数据库的模式和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)

评论:2　　

顶: 9749　

踩: 9751　

请在登录后发表评论，否则无法保存。

1# __ xcvxcvsdf 回答于 2024-10-18 22:41:35+08

https://fenlei.tiancebbs.cn/sdrz/ http://gx.lztcxxw.cn/xining/ http://ruanwen.xztcxxw.cn/jjyb/ http://yz.cqtcxxw.cn/twpd/ http://gx.lztcxxw.cn/xilinguolemeng/ http://shenghuo.china-bbs.com/hcq/ http://ruanwen.xztcxxw.cn/bijie/ http://wogao.ahtcbmw.cn/ghua/ https://shiwanzhen.tiancebbs.cn/ http://jingren.hftcbmw.cn/liaoning/ http://yz.cqtcxxw.cn/jyx/ http://huaguang.jxtcbmw.cn/cyq/ http://huaguang.jxtcbmw.cn/sfc/ http://cf.lstcxxw.cn/jjzr/ http://fuyang.tjtcbmw.cn/lpq/ https://shijigongyuan.tiancebbs.cn/ http://huilong.sctcbmw.cn/neijiang/

2# __ xiaowu 回答于 2024-09-05 07:37:26+08

我眼中的缤纷世界作文:https://www.deipei.com/zuowen/1011.html 跳水教案:https://www.deipei.com/jiaoxue/757.html 好人好事记录:https://www.deipei.com/shijicailiao/803.html 小学一年级语文教学总结:https://www.deipei.com/zongjie/1034.html 孝心作文:https://www.deipei.com/zuowen/953.html 推荐信模板:https://www.deipei.com/fanwen/647.html 汤姆索亚读后感:https://www.deipei.com/duhougan/861.html 公务员转正申请:https://www.deipei.com/fanwen/811.html 移步换景的作文:https://www.deipei.com/zuowen/737.html 入学申请:https://www.deipei.com/fanwen/893.html 仓储合同:https://www.deipei.com/hetong/954.html 信息技术论文:https://www.deipei.com/xuexi/868.html 听证申请:https://www.deipei.com/fanwen/995.html 英语学习方法总结:https://www.deipei.com/xuexi/646.html 初中英语论文:https://www.deipei.com/xuexi/843.html 青春向上作文:https://www.deipei.com/zuowen/603.html 散文随笔:https://www.deipei.com/yuedu/688.html 汽车厂实习报告:https://www.deipei.com/shixibaogao/725.html 离婚起诉书范文:https://www.deipei.com/jihua/535.html 项目立项报告书模板:https://www.deipei.com/fanwen/1020.html 心理健康主题班会:https://www.deipei.com/jiaoxue/897.html 生产管理制度:https://www.deipei.com/gongzuo/546.html 认识实习报告:https://www.deipei.com/shixibaogao/667.html 拓展感想:https://www.deipei.com/xindetihui/1015.html 雷锋电影观后感:https://www.deipei.com/guanhougan/941.html 生活是美好的作文:https://www.deipei.com/zuowen/942.html 教务处工作总结:https://www.deipei.com/zongjie/801.html 校本教研活动记录:https://www.deipei.com/gongzuo/797.html 领导讲话:https://www.deipei.com/yanjianggao/956.html 相互批评意见:https://www.deipei.com/gongzuo/1033.html