高效安全迁移：PG高可用集群实战方案深度解析

--备份所有对象结构
pg_dumpall -s -U{user_name} -p{port} -f "/xxx/all_object.sql"


--备份指定schema数据
pg_dump -Fd -v -a -U{user_name} -p{port} -d{db_name} -n{schema_name} -j2 -f "/xxx/{schema_name}.dump"


--恢复所有对象结构
psql -U{user_name} -p{port} -d{db_name} -f xxx/all_object.sql >> /xxx/all_object.log 2>&1


--恢复指定schema数据
pg_restore -p{port} -d{db_name} -U{user_name} -j2 xxx/schema_name.dump >> /xxx/{schema_name}.log 2>&1

--操作步骤如下
a.stop PG;
b.修改postgres.auto.conf，注释掉synchronous_standby_names、primary_conninfo、primary_slot_name;
c.删掉pg data目录下的standby.signal文件;
d.start PG

--新报表库部分命令如下
create subscription {sub_name} connection 'host={master_ip} port={port} dbname={db_name} user={user_name} password={password}' publication {pub_name};


alter subscription {sub_name} refresh publication ;

--新主库部分命令如下
CREATE EXTENSION pgl_ddl_deploy;
INSERT INTO pgl_ddl_deploy.set_configs (set_name,include_schema_regex,driver) VALUES('{pub_name}','{schema_name}','native'::pgl_ddl_deploy.driver);
SELECT pgl_ddl_deploy.deploy('{pub_name}') from pgl_ddl_deploy.set_configs;
SELECT pgl_ddl_deploy.add_role(oid) from pg_roles where rolname='{user_name}';
select * from pgl_ddl_deploy.set_configs;


--新报表库部分命令如下
CREATE EXTENSION pgl_ddl_deploy;
SELECT pgl_ddl_deploy.add_role(oid) from pg_roles where rolname='{user_name}';
alter subscription {sub_name} refresh publication;