如何使用 pg16 逻辑复制实现零宕机升级

郑全

                               
登录/注册后可看大图

1.环境

   源端（发布端）：192.168.133.191 ，端口 5432   

   目的端（订阅端）：192.168.133.192 ，端口5432

2.发布节点配置文件

  postgresql.conf ：

    wal_level = logical

    listen_addresses = '*'

    max_replication_slots = 8  

    max_wal_senders = 10

    pg_hba.conf:  

    host    repuser  all   192.168.133.192/32     trust

   重启配置生效

3.导出表结构

  pg_dump -d zlcost -s >/u01/postgresql/pgdata/zlcost.sql

4.查询迁移的数据库中没有主键的表

  SELECT n.nspname AS "Schema",c.relname AS "TableName"

   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 andi.indisprimary='t');

5.将没有主键的表增加REPLICAIDENTITY FULL

  ALTER TABLE     t        REPLICA IDENTITY FULL;

  ALTER TABLE     toast_t1  REPLICA IDENTITY FULL;

  ALTER TABLE     prod      REPLICAIDENTITY FULL;

  ALTER TABLE     test      REPLICAIDENTITY FULL;

  ALTER TABLE     tt        REPLICAIDENTITY FULL;

6.在目标端创建数据库，并导入表结构

  postgres=# create database zlcost;

  postgres=# create user sztech password 'sztech';

  postgres=# alter user sztech Superuser;

  psql -d zlcost -f /tmp/zlcost.sql

7.发布节点创建复制用户

    复制用户必须有复制角色，或者SUPERUSER角色

   CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 10  ENCRYPTED PASSWORD 'repuser';

8.发布节点为复制用户授权

GRANT USAGE ON SCHEMA public,sztech TO repuser ；

GRANT SELECT ON all tables IN SCHEMA public,sztech TO repuser;

\dp+ ;

9.  发布节点创建发布

  可以给具体的表，也可以给所有表，这里给所有表

  zlcost# CREATE PUBLICATION pub1 FOR ALL TABLES ;

  --FOR ALL TABLES：将当前库中所有表添加到发布中，包括以后在这个库中新建的表。这种模式相当于在全库级别逻辑复制所有表。

10.发布节点上查询pg_publication视图

  zlcost# SELECT * FROM pg_publication;

11.  订阅节点创建接收表及订阅

zlcost#  create subscription sub1connection 'host=192.168.133.191 port=5432 dbname=zlcost user=repuserpassword=repuser' publication pub1;

12.发布节点查看复制槽

  SELECT slot_name,plugin,slot_type,database,active,restart_lsn

   FROM pg_replication_slots

  where slot_name='sub1';

13.订阅端查看订阅信息

select * frompg_stat_subscription;

14.订阅端查看当前的数据位置

  select * from pg_replication_origin_status

看到订阅已经完成

15.  发布节点分别向表中插入数据

16.  订阅节点查看逻辑复制效果

看到目标端数据已经同步过来。

附部分命令：

删除订阅

Drop subscription sub1;

删除发布：

drop publication pub1;

手动刷新

ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION；