psql数据库从账号A1完整复制到账号A2的便捷方法咨询

前置说明 #

你需要确保操作账号有对应数据库的访问、新建权限，以下两种方案均可以100%保留原库的表结构、关联约束、全量数据，无需手动建表。 #

方案1：同实例快速克隆（推荐，操作最快） #

适合原库DB和目标库DB'在同一个PostgreSQL实例的场景：

• 首先使用超级用户（默认postgres）登录psql：
  psql -U postgres
• 先断开原库DB的所有活跃连接（否则会克隆失败）：

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'DB' AND pid <> pg_backend_pid();

• 直接以原库为模板克隆新库，同时指定所有者为A2：

CREATE DATABASE "DB'" WITH TEMPLATE DB OWNER A2;

• 操作完成后直接用A2登录验证即可：
  psql -U A2 -d "DB'"

方案2：跨实例导出导入（兼容性最强） #

适合需要把库克隆到其他PostgreSQL实例、或者原库无法断开活跃连接的场景：

• 第一步：用A1账号导出原库完整备份：
  pg_dump -U A1 -F c -b -v -f db_full_backup.dump DB
  参数说明：-F c 是压缩自定义格式，-b 包含大对象，-v 显示导出进度
• 第二步：创建新库并指定所有者为A2（用超级用户执行）：

CREATE DATABASE "DB'" OWNER A2;

• 第三步：用A2账号将备份导入新库：
  pg_restore -U A2 -d "DB'" -v db_full_backup.dump

验证操作 #

登录新库后执行以下命令验证完整性：

• 执行\d 查看所有30余张表是否完整存在
• 执行\d 具体表名 查看表的外键、索引等约束是否保留
• 执行SELECT count(*) FROM 任意表名 验证数据是否完整

内容的提问来源于stack exchange，提问作者dragon