--备份所有对象结构

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;