为什么要复制数据库

在数据库管理生命周期中，复制数据库是一项基础且关键的操作。它主要服务于以下三个核心场景：

• 测试与开发：快速构建与生产环境数据结构一致的沙箱环境，供开发人员测试新功能或复现 Bug，而无需担心影响线上数据。

• 数据备份：通过创建冗余副本，确保在发生硬件故障或人为误操作时，能够迅速恢复数据，保障业务连续性。

• 数据库迁移：将数据库从旧服务器平滑迁移至新服务器，或在不同云服务商之间转移数据。

同一服务器数据库克隆

当您需要在一台 PostgreSQL 服务器上创建现有数据库的副本时（例如为了测试目的），最高效的方法是使用 CREATE DATABASE 语句配合 TEMPLATE 选项。

核心原理

PostgreSQL 允许将现有的数据库作为“模板”来创建新数据库。这种方式不仅仅是复制数据，还会复制所有的表结构、索引、约束以及存储过程等对象。由于它在文件系统层面进行操作（对于空闲连接），速度通常比逻辑导出导入快得多。

语法结构

CREATE DATABASE 目标数据库名 
WITH TEMPLATE 源数据库名;

实战示例

假设我们有一个名为 dvdrental 的示例数据库（该数据库常用于 PostgreSQL 教学），现在需要为测试团队创建一个名为 dvdrental_test 的副本。

执行命令：

CREATE DATABASE dvdrental_test 
WITH TEMPLATE dvdrental;

执行说明：

• 该命令会立即启动复制过程。

• 耗时取决于源数据库的大小。对于大型数据库，可能需要几分钟时间。

• 重要前提：在执行此命令时，不能有任何用户连接到源数据库（dvdrental）。如果有活动连接，PostgreSQL 会报错并拒绝操作。您需要先终止所有相关会话。

跨服务器数据库迁移

当需要将数据库从一台服务器（源服务器）复制到另一台服务器（目标服务器）时，通常采用“导出 - 传输 - 导入”的流程。这种方法兼容性最好，适用于不同版本的 PostgreSQL 之间迁移，也适合通过网络传输。

标准操作流程

步骤 1：在源服务器创建转储文件

使用 pg_dump 工具将源数据库导出为 SQL 脚本文件。

pg_dump -U postgres -d source_database -f source_database.sql

• -U postgres：指定连接用户（通常为 postgres 超级用户）。

• -d source_database：指定要导出的源数据库名称。

• -f source_database.sql：指定输出文件名。

步骤 2：传输文件到目标服务器

使用安全复制工具（如 scp）或文件共享服务，将生成的 .sql 文件从源服务器复制到目标服务器。

scp source_database.sql user@remote_server:/path/to/destination/

步骤 3：在目标服务器创建空数据库

登录到目标服务器的 psql 终端，创建一个新的空数据库用于接收数据。

CREATE DATABASE target_database;

步骤 4：在目标服务器恢复数据

使用 psql 工具执行之前传输过来的 SQL 脚本，将数据导入新数据库。

psql -U postgres -d target_database -f source_database.sql

案例演示

假设我们要将本地的 dvdrental 数据库迁移到远程服务器。

1. 导出数据：

   pg_dump -U postgres -O dvdrental > dvdrental.sql

   ( 注：-O 表示不输出所有者信息，便于在不同用户环境下恢复 )

2. 创建目标库：
   在远程服务器执行：

   CREATE DATABASE dvdrental;

3. 导入数据：
   在远程服务器执行：

   psql -U postgres -d dvdrental -f dvdrental.sql