PostgreSQL - 加载数据库

PostgreSQL - 加载和恢复数据库

将数据加载到 PostgreSQL 数据库是一项常见任务，无论是从备份恢复、从其他系统迁移还是导入批量数据。本章介绍了实现此目标最有效和现代的方法。

选择正确的方法

最佳工具取决于源数据的格式：

对于自定义格式备份（.dump, .backup）： 使用 pg_restore 工具。这是恢复使用 pg_dump -Fc 创建的备份最稳健的方法。
对于纯 SQL 文件（.sql）： 使用 psql 命令行客户端。这对于重放 SQL 脚本是理想的选择。
对于结构化文本文件（CSV, TSV）： 使用高效的 COPY SQL 命令进行服务器端加载，或使用 psql 中的 \copy 元命令进行客户端加载。

方法 1：使用 `pg_restore` 恢复自定义格式备份

pg_restore 工具旨在从 pg_dump 以自定义 (-Fc)、目录 (-Fd) 或 tar (-Ft) 格式创建的归档中恢复数据库。这种格式通常比纯 SQL 更受青睐，因为它提供了更大的灵活性，例如支持并行恢复。

语法和常用选项

pg_restore --username=your_user --host=your_host --dbname=target_db --jobs=4 --clean backup_file.dump

关键参数：

--username (-U)：用于连接的 PostgreSQL 用户。
--dbname (-d)：要恢复到的目标数据库。该数据库必须已经存在。
--jobs (-j)：指定并发运行的作业数，显著加快恢复过程。
--clean (-c)：指示 pg_restore 在重新创建数据库对象之前删除现有的数据库对象。

方法 2：使用 `psql` 处理纯 SQL 文件

如果您的备份是一个 .sql 文件（纯文本脚本），您可以直接使用 psql 执行它。

psql --username=your_user --host=your_host --dbname=target_db --file=backup_file.sql

最佳实践：对于关键的恢复操作，请将执行包装在一个单独的事务中，以确保整个脚本要么完全成功，要么完全失败，从而使数据库保持不变。

psql --username=your_user --dbname=target_db --single-transaction --file=backup_file.sql

方法 3：使用 `COPY` 进行批量数据导入

COPY 命令是从 CSV 等结构化文本文件加载大量数据的最快方式。理解 COPY（SQL 命令）和 \copy（psql 元命令）之间的区别至关重要。

使用 `COPY` 进行服务器端加载

COPY 在数据库服务器上运行，并从服务器的文件系统中读取文件。这需要超级用户权限或特定的授权，因为它涉及直接的服务器文件访问。

-- 假设 'products.csv' 在数据库服务器的文件系统上
COPY products(name, price, category)
FROM '/var/lib/postgresql/data/imports/products.csv'
WITH (FORMAT CSV, HEADER true, DELIMITER ',');

使用 `\copy` 进行客户端加载

\copy 是一个 psql 命令，它从您的本地（客户端）机器读取文件并将数据流式传输到服务器，执行 COPY FROM STDIN。它不需要超级用户权限。

-- 在 psql 会话中运行此命令
-- 假设 'products.csv' 在您的本地机器上
\copy products(name, price, category) FROM '~/local_data/products.csv' WITH (FORMAT CSV, HEADER true, DELIMITER ',');

方法 4：使用 pgAdmin 等图形用户界面工具

对于喜欢图形界面的用户，pgAdmin 提供了一种用户友好的方式来恢复数据库。在底层，它构建并运行相同的 pg_restore 命令。

步骤 1：打开 pgAdmin 并连接到您的 PostgreSQL 服务器。步骤 2：如果数据库不存在，右键单击“数据库”并选择“创建 -> 数据库…”来创建一个新的空数据库。步骤 3：右键单击新创建的目标数据库并选择“恢复…” 步骤 4：在对话框中，选择自定义格式的备份文件（.dump），并配置“恢复前清理”或作业数量等选项。步骤 5：点击“恢复”按钮开始该过程。您可以在“进程”选项卡中查看命令行输出。

这些方法提供了一个全面的工具集，用于将数据加载到 PostgreSQL 中，从完整的数据库恢复到从外部源批量摄取数据。

PostgreSQL - 加载数据库

PostgreSQL - 加载和恢复数据库

选择正确的方法

方法 1：使用 pg_restore 恢复自定义格式备份

语法和常用选项

方法 2：使用 psql 处理纯 SQL 文件

方法 3：使用 COPY 进行批量数据导入

使用 COPY 进行服务器端加载

使用 \copy 进行客户端加载