PostgreSQL 备份与恢复 | pg_dump, pg_restore 教程

介绍

在这个实验中，你将学习如何备份和恢复 PostgreSQL 数据库。本实验涵盖了保护数据的基本技术，包括使用 pg_dump 导出数据库，使用 pg_restore 恢复数据库，以及备份特定表。

你将首先创建一个名为 mydatabase 的示例数据库。然后，你将使用 pg_dump 命令将此数据库导出到 SQL 文件。接下来，你将探索如何使用 pg_restore 从备份文件恢复数据库。最后，你将学习如何仅备份数据库中的特定表，并验证恢复数据的完整性。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/db_setup("Create New Database") postgresql/PostgreSQLGroup -.-> postgresql/db_access("Connect To Database") postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") postgresql/PostgreSQLGroup -.-> postgresql/row_add("Insert One Row") postgresql/PostgreSQLGroup -.-> postgresql/data_all("Select All Data") postgresql/PostgreSQLGroup -.-> postgresql/row_drop("Delete One Row") subgraph Lab Skills postgresql/db_setup -.-> lab-550949{{"PostgreSQL 备份与恢复"}} postgresql/db_access -.-> lab-550949{{"PostgreSQL 备份与恢复"}} postgresql/table_init -.-> lab-550949{{"PostgreSQL 备份与恢复"}} postgresql/row_add -.-> lab-550949{{"PostgreSQL 备份与恢复"}} postgresql/data_all -.-> lab-550949{{"PostgreSQL 备份与恢复"}} postgresql/row_drop -.-> lab-550949{{"PostgreSQL 备份与恢复"}} end

创建并填充数据库

在此步骤中，你将创建一个名为 mydatabase 的 PostgreSQL 数据库，并用一些示例数据填充它。此数据库将用于后续步骤中的备份和恢复练习。

首先，使用以下命令创建 mydatabase 数据库：

sudo -u postgres psql -c "CREATE DATABASE mydatabase;"

此命令以 postgres 用户身份连接到 PostgreSQL 服务器，并执行 SQL 命令 CREATE DATABASE mydatabase。

接下来，使用 psql 命令连接到 mydatabase 数据库：

sudo -u postgres psql -d mydatabase

现在你应该在 psql shell 中，连接到 mydatabase 数据库。你会看到类似 mydatabase=# 的提示符。

现在，创建一个名为 users 的表，其中包含 id 和 name 列：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

此 SQL 命令创建一个名为 users 的表，其中包含两列：id（自动递增的整数）和 name（字符串）。

接下来，将一些示例数据插入到 users 表中：

INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

这些 SQL 命令将两行插入到 users 表中，名称分别为 'Alice' 和 'Bob'。

最后，创建另一个名为 products 的表并插入一些数据：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

INSERT INTO products (name) VALUES ('Laptop');
INSERT INTO products (name) VALUES ('Tablet');

要验证表和数据是否已成功创建，你可以运行以下 SQL 查询：

SELECT * FROM users;
SELECT * FROM products;

你应该看到你刚刚插入的数据。

通过键入以下内容退出 psql shell：

\q

现在你回到了 Linux 终端。

导出数据库

在此步骤中，你将使用 pg_dump 命令将 mydatabase 数据库导出到 SQL 文件。此文件将包含重新创建数据库所需的 SQL 命令。

运行以下命令以导出数据库：

pg_dump -U postgres -d mydatabase -f ~/project/mydatabase.sql

此命令使用 pg_dump 实用程序以 postgres 用户身份连接到 mydatabase 数据库，并将数据库导出到 ~/project 目录中名为 mydatabase.sql 的文件。

要验证文件是否已成功创建，你可以列出 ~/project 目录的内容：

ls ~/project

你应该在输出中看到 mydatabase.sql。

你还可以使用 head 命令查看备份文件的前几行：

head ~/project/mydatabase.sql

这将显示由 pg_dump 创建的 SQL 脚本的开头。

恢复数据库

在此步骤中，你将从 mydatabase.sql 备份文件将 mydatabase 数据库恢复到名为 newdatabase 的新数据库中。

首先，创建 newdatabase 数据库：

sudo -u postgres psql -c "CREATE DATABASE newdatabase;"

现在，从备份文件恢复数据库：

pg_restore -U postgres -d newdatabase ~/project/mydatabase.sql

此命令使用 pg_restore 实用程序以 postgres 用户身份连接到 newdatabase 数据库，并从 mydatabase.sql 文件恢复数据库。

要验证数据库是否已成功恢复，请使用 psql 连接到 newdatabase 数据库：

sudo -u postgres psql -d newdatabase

然后列出表：

\dt

你应该看到列出的 users 和 products 表。

你还可以查询表中的数据，以验证数据是否已正确恢复：

SELECT * FROM users;
SELECT * FROM products;

你应该看到你在步骤 1 中插入的相同数据。

退出 psql shell：

\q

备份和恢复单个表

在此步骤中，你将学习如何从 PostgreSQL 数据库备份和恢复单个表。如果你只需要备份数据库的特定部分，这将非常有用。

首先，让我们仅备份 mydatabase 数据库中的 users 表：

pg_dump -U postgres -d mydatabase -t users -f ~/project/users_only.sql

此命令使用 -t 选项来指定仅应备份 users 表。

现在，让我们将此表恢复到 newdatabase 数据库中。首先，删除 newdatabase 中现有的 users 表：

sudo -u postgres psql -d newdatabase -c "DROP TABLE IF EXISTS users;"

然后，从 users_only.sql 文件恢复 users 表：

pg_restore -U postgres -d newdatabase ~/project/users_only.sql

要验证表是否已成功恢复，请使用 psql 连接到 newdatabase 数据库：

sudo -u postgres psql -d newdatabase

然后列出表：

\dt

你应该看到列出的 users 表，但没有 products 表（因为我们只恢复了 users 表）。

你还可以查询 users 表中的数据，以验证数据是否已正确恢复：

SELECT * FROM users;

你应该看到与 mydatabase 数据库中 users 表中相同的数据。

退出 psql shell：

\q

总结

在这个实验中，你已经学习了如何使用 pg_dump 和 pg_restore 实用程序备份和恢复 PostgreSQL 数据库。你学习了如何：

创建数据库并使用数据填充它。
使用 pg_dump 将整个数据库导出到 SQL 文件。
使用 pg_restore 从 SQL 文件恢复数据库。
使用 pg_dump 从数据库备份单个表。
使用 pg_restore 将单个表恢复到数据库中。

这些是任何 PostgreSQL 数据库管理员的基本技能。