在ORACLE数据库,想近零停机业务实现数据库从低版本比如11G升级到高版本比如19C,采用逻辑复制的方式,是最简单易行的方式,那么PG是否有这种方式,看了一下,PG也有逻辑复制,不过和ORACLE比起来,不太一样,ORACLE的逻辑复制是先创建物理复制,再转为逻辑复制,但PG的逻辑复制和物理复制差距很大,明显就不是一回事,PG的逻辑复制使用的发布订阅模式,可以基于表,非常灵活。物理复制是基于整个实例,使用日志块复制。操作起来,PG逻辑复制比较简单,但健壮性不如ORACLE的逻辑复制,严格说来,PG的逻辑复制,更应该对标ORACLE的流复制。或者ORACLE的CDC
目录
3.3.2 将没有主键的表增加REPLICA IDENTITY FULL
下面我们看看使用逻辑复制来实现两个库的数据同步:
1.环境
源端(发布端):192.168.133.191 ,端口 5432 PG16.2
目的端(订阅端):192.168.133.192 ,端口5432 PG16.2
2.目的
通过PG逻辑复制,把PG源端的数据全部同步到目标PG。
3.操作步骤
3.1 调整源端配置文件
postgresql.conf :
wal_level = logical
listen_addresses = '*'
pg_hba.conf:
host repuser all 192.168.133.192/32 trust
重启PG配置生效
3.2 导出表结构
导出要同步库的所有表的结构,比如我这里要同步 ZLCOST
pg_dump -d zlcost -s >/u01/postgresql/pgdata/zlcost.sql
3.3 处理没有主键的表
3.3.1 查询复制数据库中没有主键的表
SELECT n.nspname AS "Schema",c.relname AS "Table Name"
FROM pg_catalog.pg_class c, pg_namespace n
where c.relnamespace = n.oid
AND n.nspname NOT IN ('information_schema', 'pg_catalog')
AND c.relkind='r'
AND not exists (select 1 from pg_index i where c.oid=i.indrelid and i.indisprimary='t');
3.3.2 将没有主键的表增加REPLICA IDENTITY FULL
ALTER TABLE t REPLICA IDENTITY FULL;
ALTER TABLE toast_t1 REPLICA IDENTITY FULL;
ALTER TABLE prod REPLICA IDENTITY FULL;
ALTER TABLE test REPLICA IDENTITY FULL;
ALTER TABLE tt REPLICA IDENTITY FULL;
3.4 目标端准备数据结构
3.41 目标端创建对应的数据库及相应用户
postgres=# create database zlcost;
postgres=# create user sztech password 'sztech';
postgres=# alter user sztech Superuser;
3.4.2 目标端恢复表结构
使用源端导出的表结构,在目标端恢复对应的表结构
psql -d zlcost -f /tmp/zlcost.sql
3.5 源端为发布准备复制用户
3.5.1 创建复制用户
复制用户必须有复制角色,或者SUPERUSER角色
CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 10
ENCRYPTED PASSWORD 'repuser';
3.5.2 为复制用户授权
GRANT USAGE ON SCHEMA public,sztech TO repuser ;
GRANT SELECT ON all tables IN SCHEMA public,sztech TO repuser;
3.6 源端创建发布
3.6.1 创建发布
可以给具体的表,也可以给所有表,我这里给所有表
zlcost# CREATE PUBLICATION pub1 FOR ALL TABLES ;
-- FOR ALL TABLES:将当前库中所有表添加到发布中,
包括以后在这个库中新建的表。这种模式相当于在全库级别逻辑复制所有表。
3.6.2 查看pg_publication视图
SELECT * FROM pg_publication;
3.7 目标端创建订阅
zlcost# create subscription sub1 connection 'host=192.168.133.191 port=5432 dbname=zlcost user=repuser password=repuser' publication pub1;
3.8 查看复制相关信息
3.8.1 源端查看复制槽
SELECT slot_name,plugin,slot_type,database,active,restart_lsn
FROM pg_replication_slots
where slot_name='sub1';
3.8.2 目标端查看订阅信息
select * from pg_stat_subscription;
3.8.3 目标端查看当前的数据位置
select * from pg_replication_origin_status;
看日志,可以看到订阅已经开始
3.9 验证数据同步
等一会,目标端查询与源端对应的表,发现数据都过来了
3.9.1 源端新插入数据
3.9.2 目标端查看逻辑复制效果
到此,逻辑复制已经完成
3.10 附部分命令
目标端删除订阅
Drop subscription sub1;
源端删除发布:
drop publication pub1;
目标端手动刷新
ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
-------------------------------------------
为了方便交流,我建立了一个微信群:水煮数据库,主要交流日常运维中用到的数据库相关问题,包含但不限于:ORACLE,PG,MYSQL,SQLSERVER,OB,TIDB,达梦,TDSQL,OPENGAUSS,人大金仓,GBASE等等,如果有兴趣交流,可以加我微信:zq24803366, 我可以拉你入群。
————————————————
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
原文链接:https://blog.csdn.net/cqsztech/article/details/138546381