PG16 逻辑复制-CSDN博客

本文链接：https://blog.csdn.net/cqsztech/article/details/138769795

在ORACLE数据库，想近零停机业务实现数据库从低版本比如11G升级到高版本比如19C，采用逻辑复制的方式，是最简单易行的方式,那么PG是否有这种方式，看了一下，PG也有逻辑复制，不过和ORACLE比起来，不太一样，ORACLE的逻辑复制是先创建物理复制，再转为逻辑复制，但PG的逻辑复制和物理复制差距很大，明显就不是一回事，PG的逻辑复制使用的发布订阅模式，可以基于表，非常灵活。物理复制是基于整个实例，使用日志块复制。操作起来，PG逻辑复制比较简单，但健壮性不如ORACLE的逻辑复制，严格说来，PG的逻辑复制，更应该对标ORACLE的流复制。或者ORACLE的CDC

3.3.2 将没有主键的表增加REPLICA IDENTITY FULL

3.4 目标端准备数据结构

3.41 目标端创建对应的数据库及相应用户

3.6.2 查看pg_publication视图

下面我们看看使用逻辑复制来实现两个库的数据同步：

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