PGLogicalReplication逻辑复制-创新互联
PG10 到 PG11 的逻辑复制
成都创新互联专业为企业提供嘉峪关网站建设、嘉峪关做网站、嘉峪关网站设计、嘉峪关网站制作等企业网站建设、网页设计与制作、嘉峪关企业网站模板建站服务,十余年嘉峪关做网站经验,不只是建网站,更提供有价值的思路和整体网络服务。我下面演示的PG环境是单机多实例的方式部署在同一台物理机上的。部署方式可以参考 上一篇博客。
1、当前老的PG10主库(需要先设置wal_level = logical):
su - postgres cd /usr/local/pgsql-10.10/ ./bin/psql --port 5433 postgres=# create database testdb1; CREATE DATABASE postgres=# create database testdb2; CREATE DATABASE postgres=# \c testdb1 You are now connected to database "testdb1" as user "postgres". testdb1=# create table tb1(a int ,b int, c int ); testdb1=# create table tb2(a int ,b int, c int ); testdb1=# create table tb3(a int ,b int, c int );创建一个复制用的账号
CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 10 ENCRYPTED PASSWORD 'repuser'; 另外,还需要给 repuser 用户对源库、源表、源schmea 赋权 \c testdb1 grant connect on database testdb1 to repuser; grant usage on schema public to repuser; grant select on all tables in schema public to repuser; -- 这个授权有点大,但是问题不算太严重然后,还要去pg_hba.conf 放开 repuser账号的访问地址,如下:
host all repuser 192.168.2.1/24 md5然后,reload下pg的配置。
2、初始化PG11新库
cd /usr/local/pgsql-11.5 mkdir data ./bin/initdb -D data ./bin/pg_ctl -D data/ -o "-p 5434" -l pg.log start ./bin/psql --port 54343、导出PG10的 schema definitions
cd /usr/local/pgsql-10.10 ./bin/pg_dumpall -s --port 5433 --no-subscriptions > ./schemadump.sql4、 将PG10的 导出数据导入到PG11中
su - postgres cd /usr/local/pgsql-11.5 ./bin/psql --port 5434 -d postgres -f /usr/local/pgsql-10.10/schemadump.sql5、在源实例PG10中的每个数据库中,创建一个捕获所有表的发布
注意:逻辑复制在每个数据库中分别工作,因此需要在每个数据库中重复。另一方面,您不必一次升级所有数据库,因此可以一次完成一个数据库,甚至不升级某些数据库。
cd /usr/local/pgsql-10.10 ./bin/psql --port 5433 postgres=# \c testdb1 testdb1=# CREATE PUBLICATION p_upgrade FOR ALL TABLES; testdb1=# \dRp+ Publication p_upgrade Owner | All tables | Inserts | Updates | Deletes ----------+------------+---------+---------+--------- postgres | t | t | t | t (1 row) testdb1=# \c testdb2 testdb2=# CREATE PUBLICATION p_upgrade2 FOR ALL TABLES; testdb2=# \dRp+ Publication p_upgrade2 Owner | All tables | Inserts | Updates | Deletes ----------+------------+---------+---------+--------- postgres | t | t | t | t (1 row)6、 在目标实例 PG11 中的每个数据库中,创建订阅刚刚创建的发布的订阅。确保与源数据库和目标数据库匹配正确。
su - postgres cd /usr/local/pgsql-11.5 ./bin/psql --port 5434testdb2=# \c testdb1 testdb2=# CREATE SUBSCRIPTION s_sub CONNECTION 'host=192.168.2.4 port=5433 dbname=testdb1 user=repuser password=repuser' PUBLICATION p_upgrade; testdb1=# \dRs+ List of subscriptions Name | Owner | Enabled | Publication | Synchronous commit | Conninfo -------+----------+---------+-------------+--------------------+------------------------------------------------------------------------- s_sub | postgres | t | {p_upgrade} | off | host=192.168.2.4 port=5433 dbname=testdb1 user=repuser password=repuser (1 row) testdb2=# \c testdb2 testdb2=# CREATE SUBSCRIPTION s_sub2 CONNECTION 'host=192.168.2.4 port=5433 dbname=testdb2 user=repuser password=repuser' PUBLICATION p_upgrade2; testdb2=# \dRs+ List of subscriptions Name | Owner | Enabled | Publication | Synchronous commit | Conninfo --------+----------+---------+--------------+--------------------+------------------------------------------------------------------------- s_sub2 | postgres | t | {p_upgrade2} | off | host=192.168.2.4 port=5433 dbname=testdb2 user=repuser password=repuser (1 row)7、在PG10上,造些数据:
postgres=# \c testdb1 testdb1=# insert into tb1(a,b,c) values (1,1,1),(2,2,2),(3,3,3); testdb1=# insert into tb2(a,b,c) values (1,1,1);然后,到 PG11上的testdb1库里面,可以看到 数据已同步了。
8、后续如果在PG10上有加表操作,类似如下:
PG10上,我们加一个表 testdb1=# \c testdb1 testdb1=# create table tb_new( a int ,b int ); testdb1=# insert into tb_new values(1,1) ,(2,2) ,(3,3),(4,4); testdb1=# GRANT SELECT ON tb_new to repuser; 需要授权下 因为我们第五步的时候,给了all table做了复制的配置, 因此新加表后这里不需要执行添加到发布者的命令。 testdb1=# \dRp+ p_upgrade -- 查看发布者的详细信息 Publication p_upgrade Owner | All tables | Inserts | Updates | Deletes ----------+------------+---------+---------+--------- postgres | t | t | t | t (1 row)PG10上加完表后,我们可以看到PG11上这个 tb_new 表是不存在的。 需要我们到PG11上手工创建下:
在PG11上执行如下命令:
\c testdb1 create table tb_new( a int ,b int ); select count(*) from tb_new; -- 这时候数据还是为0的 ALTER SUBSCRIPTION s_sub REFRESH PUBLICATION; -- 刷新一下订阅者 select count(*) from tb_new; -- 这时候数据变成了4条了9、 清除复制设置(在PG11新库上执行)
\c testdb1 DROP SUBSCRIPTION s_sub; \c testdb2 DROP SUBSCRIPTION s_sub2;然后, 也可以删除源实例PG10上的发布,但这不是必需的。
10、最后,如果老的PG10上流量都切到PG11后,可以将PG10这个实例下线。
原生logical复制的限制【非常关键】:
1、只支持普通表生效,不支持序列、视图、物化视图、外部表、分区表和大对象
关于逻辑复制不支持的事项的变通方法的一些附加注释。如果您正在使用大型对象,则可以使用pg_dump移动它们,当然只要它们在升级过程中不会更改。这是一个重要的限制,因此如果您是大型对象的重度用户,那么此方法可能不适合您。如果您的应用程序在升级过程中发出TRUNCATE,则不会复制这些操作。也许您可以调整应用程序以防止它在升级时执行此操作,或者您可以替换DELETE。PostgreSQL 11将支持复制TRUNCATE,但这只有在源和目标实例都是PostgreSQL 11或更新版本时才有效。
2、只支持普通表的DML(INSERT、UPDATE、DELETE)操作,不支持truncate、DDL操作
3、需要同步的表必须设置 REPLICA IDENTITY 不能为noting(默认值是default),同时表中必须包含主键,否则delete和update报错
4、一个publisher可以包含一张或多张表,一张表可以有一个或多个publishers
5、一个发布者可以有多个订阅者订阅,一个订阅者也可以同时订阅多个发布者,在同一个数据库下订阅者不能对同一个发布者的表重复订阅(避免数据冲突)
6、逻辑复制不同于流复制,不是严格的主从关系,订阅者端的普通表依然可以进行增删改操作
7、同步表的表结构需要在发布者和订阅者两边保持一致(列的顺序允许不一样,但是列对应的数据类型必须一致)
8、如果订阅者端的数据被误删,想要从发布者重新copy同步表的数据,只能以重建同步表所在的订阅者的方式来实现
其它注意事项:
publication - 发布者
逻辑复制的前提是将数据库 wal_level 参数设置成 logical;
源库上逻辑复制的用户必须具有 replicatoin 或 superuser 角色;
逻辑复制目前仅支持数据库表逻辑复制,其它对象例如函数、视图不支持;
逻辑复制支持DML(UPDATE、INSERT、DELETE)操作,TRUNCATE 和 DDL 操作不支持;
需要发布逻辑复制的表,须配置表的 REPLICA IDENTITY 特性;
一个数据库中可以有多个publication,通过 pg_publication 查看;
允许一次发布所有表,语法: CREATE PUBLICATION alltables FOR ALL TABLES;
subscription - 订阅者
订阅节点需要指定发布者的连接信息;
一个数据库中可以有多个订阅者;
可以使用enable/disable启用/暂停该订阅;
发布节点和订阅节点表的模式名、表名必须一致,订阅节点允许表有额外字段;
发布节点增加表名,订阅节点需要执行: ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION
参考资料:
https://www.postgresql.org/docs/10/sql-createpublication.html
https://www.postgresql.org/docs/10/sql-createsubscription.html
https://www.postgresql.org/docs/10/sql-altersubscription.html
https://yq.aliyun.com/articles/585446?spm=a2c4e.11153940.0.0.48e86e272CVXQp
https://postgres.fun/20170528142004.html
另外有需要云服务器可以了解下创新互联cdcxhl.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。
分享名称:PGLogicalReplication逻辑复制-创新互联
文章出自:http://scyanting.com/article/dedesg.html