使用 pglogical 迁移 PostgreSQL

# 参考 https://github.com/2ndQuadrant/pglogical#quick-setup

wal_level = 'logical'
max_worker_processes = 16   # one per database needed on provider node
                            # one per node needed on subscriber node
max_replication_slots = 16  # one per node needed on provider node
max_wal_senders = 16        # one per node needed on provider node
shared_preload_libraries = 'pglogical'

track_commit_timestamp = on # needed for last/first update wins conflict resolution
                            # property available in PostgreSQL 9.5+

SHOW wal_level;
SHOW shared_preload_libraries;

-- 给逻辑复制专门创建一个用户
CREATE ROLE logicalrep WITH LOGIN;
ALTER USER logicalrep WITH PASSWORD 'your_pass';

-- 给予 logicalrep 用户必要的权限
GRANT USAGE ON schema public TO logicalrep;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO logicalrep;
GRANT USAGE ON schema pglogical TO logicalrep;
GRANT SELECT ON ALL TABLES IN SCHEMA pglogical TO logicalrep;


-- 创建 pglogical 扩展
CREATE EXTENSION pglogical;


-- dsn 可以留空 (暂时没发现有啥影响)
SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := ''
);

-- 将 public schema 中的所有表加入复制集
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

CREATE EXTENSION pglogical;
SELECT pglogical.create_node(
    node_name := 'sub',
    dsn := 'dbname=order'
);

-- 创建订阅
SELECT pglogical.create_subscription(
    subscription_name := 'sub_order',
    provider_dsn := 'host=your_host port=5432 dbname=order user=logicalrep password=your_pass'
);

-- 检查订阅状态
--
-- 预期状态是 `replicating`.
-- 如果不是, 就要检查目标端或者源端的数据库日志来确定问题.
select * from pglogical.show_subscription_status();

-- 获取 last values
SELECT * FROM pg_sequences;

-- 修改目标端的 last value
ALTER SEQUENCE public.videos_id_seq RESTART new_value;

-- 检查 last value
-- 参考 https://gist.github.com/lbbedendo/449ff46d3baa7838b99ec513c2de92a7
SELECT last_value FROM public.videos_id_seq;

-- 注意修改了 last value 之后, 这个 SQL 查出来的 last value 会是 NULL
SELECT * FROM pg_sequences;

SELECT * FROM pglogical.drop_subscription('sub_order');

Could not open relation with OID

GRANT ALL ON DATABASE mydb TO admin;