PG10-235=drop database if exists lottu; NOTICE: database "lottu" does not exist, skipping DROP DATABASE PG10-235=create database lottu owner lottu; CREATE DATABASE
5.2.2、pglogical 插件安装本环境已经安装pglogical；只要到对应数据库创建pglogical插件即可
PG10-235=CREATE EXTENSION pglogical; CREATE EXTENSION PG10-235=\dx List of installed extensions Name | Version | Schema | Description -----------+---------+------------+-------------------------------- pglogical | 2.2.0 | pglogical | PostgreSQL Logical Replication plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows)
5.2.3、配置pglogical发布者节点
这个要根据真实环境来设置；考虑到真实环境数据库中表不一���都有主键约束，可将表放到复制集 "default_insert_only"。
PG96-221=SELECT pglogical.create_node(node_name := 'provider', dsn := 'host=192.168.1.221 port=5432 dbname=lottu'); create_node ------------- 3171898924 (1 row) PG96-221=SELECT pglogical.replication_set_add_all_tables('default_insert_only', ARRAY['public']); replication_set_add_all_tables -------------------------------- t (1 row)
该函数可实现主键和非主键分别放到'default'和'default_insert_only'复制集
CREATE OR REPLACE FUNCTION "public"."pglogical_relhaspkey_repset"() RETURNS "pg_catalog"."void" AS $BODY$ DECLARE obj record; BEGIN FOR obj IN (SELECT n.nspname, c.relname, c.relhaspkey FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1, 2) LOOP IF obj.relhaspkey THEN PERFORM pglogical.replication_set_add_table(set_name := 'default', relation := obj.relname :: regclass); ELSE PERFORM pglogical.replication_set_add_table(set_name := 'default_insert_only', relation := obj.relname :: regclass); END IF; END LOOP; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100
订阅者节点
PG10-235=SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=192.168.1.235 port=5432 dbname=lottu'); create_node ------------- 2941155235
5.2.4、迁移DDLpglogical 可以同步表/序列结构；在创建订阅者 'pglogical.create_subscription' ; 里面参数synchronize_structure - 指定是否将提供者与订阅者之间的结构同步，默认为false。可以同步表/序列/索引。
PG10-235=SELECT pglogical.create_subscription(subscription_name := 'subscription', provider_dsn := 'host=192.168.1.221 port=5432 dbname=lottu', synchronize_structure := true, synchronize_data := false); create_subscription --------------------- 2875150205 (1 row)
5.2.5、业务代码改写优化上一步我们没同步数据。所以参数synchronize_data我们选择false。虽然把表/序列/索引结构同步过来；但是业务代码(函数/插件)没同步过来；还要考虑这些业务代码是否需要改写优化。因为新的版本往往有新特性。
5.2.6、全量复制pglogical有将所有未同步表都在单个操作中同步
语法：
pglogical.alter_subscription_synchronize(subscription_name name, truncate bool)
参数：
subscription_name - 现有订阅的名称
truncate - 如果为true，表将在复制前被截断，默认为false
PG10-235=SELECT pglogical.alter_subscription_synchronize(subscription_name := 'subscription', truncate := false); alter_subscription_synchronize -------------------------------- t (1 row)
5.2.7、比对数据一致经过上一步，两个数据库数据达到一致。
查看表同步状态
PG10-235=select * from pglogical.show_subscription_table(subscription_name := 'subscription', relation := 'tbl_lottu01'::regclass); nspname | relname | status ---------+-------------+-------------- public | tbl_lottu01 | synchronized (1 row)
比对两个数据库表的数据