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;
|