Toggle navigation
PostgreSQL中文社区
首页
(current)
社区新闻
中文文档
加入ACE
相关资料
了解PostgreSQL
PostgreSQL相关文档
PostgreSQL软件下载
PostgreSQL中文图书
社区年会PPT资料
关于中文社区
注册
登录
全部
社区活动
技术讨论
新闻综述
强人随笔
会议资料
其他
首页
社区新闻
热点话题
PostgreSQL与 逻辑复制
采编:
doudou586
发布时间:
2017-12-12 19:45:21
欢迎大家踊跃投稿,投稿信箱:
press@postgres.cn
评论:
0
浏览:
26041
# PostgreSQL与 逻辑复制 #### 作者:Pierre-Emmanuel André #### 翻译:PostgreSQL中文社区 / doudou586 #### 发布:2017-12-12 #### 欢迎大家踊跃投稿,投稿信箱: press@postgres.cn ---- ## 逻辑复制 在PostgreSQL10版本以前,数据复制时我们只能是复制整个数据库集群.正如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)
请在
登录
后发表评论,否则无法保存。
发表评论:
您还没有登录,请您登录后再发表评论
© 2010 PostgreSQL中文社区