如何将PostgreSQL dump文件恢复到Postgres数据库中？

Question

我有一个扩展名为.SQL的转储文件（实际上是纯文本的 SQL 文件）。我想将其还原到我创建的数据库中。我正在使用pgAdmin III，但当我使用其“恢复向导”时，它没有突出显示“恢复”按钮，而是期望.backup文件扩展名。

我尝试使用 shell 命令恢复转储，但仍然不起作用。

我对此很陌生。如果有人能帮助我，我将感激不尽。

编辑

我在新建的TestDB中使用以下命令将其 Shell SQL 窗格。

newTestDB-# \i E:\db-rbl-restore-20120511_Dump-20120514.sql

仍然会出现相同的错误("Permission Denied")。

提升权限后，它只会显示PostgreSQL的默认表：

      List of tablespaces
Name       |  Owner   | Location
-----------+----------+----------
pg_default | postgres |
pg_global  | postgres |

(2 rows)

我不知道如何从一个SQL文件中导入或还原数据库。

- Usman

---

表和表空间不是同一回事。要查看表列表，请使用\dt+而不是\db。通常，恢复SQL转储不会创建新的表空间，除非您已经告诉它这样做。 - dschulz

查看哪些表空间，请看这里。 - dschulz

在 psql 会话中查看可用的 psql \ 命令，请键入 \?。 - dschulz

Answer 1

由于您没有提到备份是如何进行的，所以一般性的答案是：通常使用 psql 工具。

根据 pg_dump 被指示转储的内容不同，SQL 文件可能包含不同的 SQL 命令集合。例如，如果你使用 --clean 和 --schema-only 参数指示 pg_dump 转储数据库，那么不能期望从该转储中恢复数据库，因为其中没有 SQL 命令用于复制（或插入，如果使用了 --inserts）表中的实际数据。这样的一个转储只包含 DDL SQL 命令，能够重新创建架构但不能重新创建实际数据。

典型的 SQL 转储可以使用 psql 进行还原：

psql (connection options here) database  < yourbackup.sql

或者可以从 psql 会话中获取：

psql (connection options here) database
database=# \i /path/to/yourbackup.sql

对于使用pg_dump -Fc（“自定义格式”）创建的备份，它不是普通的SQL文件，而是一个压缩文件，您需要使用pg_restore工具。

如果您正在类Unix系统上工作，请尝试以下操作：

man psql
man pg_dump
man pg_restore

否则，请查看HTML文档。祝你好运！

- dschulz

---

我执行了上述两个命令，但是我不知道在myTestDB\Schemas\Tables(0)中没有任何显示。没有下载或还原表格。我可以在这里上传SQL文件，或者你可以以某种方式进行检查吗？ - Usman

上传到Pastebin或类似的服务，并在此处放置链接。抱歉，我无法上传文件。请提供文本并我将为您翻译。 - dschulz

pastebin 不允许我上传大小为 375MB 的 .SQL 文件，还有其他方法吗？ - Usman

这是一个非常大的文件，大小为363252 KB，加载时间太长了，并且在此期间CPU使用率非常高。我认为它将会被正确加载。 需要一些时间.. - Usman

WordPad不能工作，它试图加载整个文件。你需要一个程序员的文本编辑器，比如Notepad++，可以处理大文件。 - Craig Ringer

Answer 2

通过使用pg_restore命令，您可以恢复Postgres数据库。

首先打开终端并输入

sudo su postgres

创建新数据库

createdb [数据库名称] -O [所有者]

createdb test_db [-O openerp]

pg_restore -d [数据库名称] [转储文件路径]

pg_restore -d test_db /home/sagar/Download/sample_dbump

等待数据库恢复完成。

请记住，转储文件应具有读取、写入、执行访问权限，因此您可以应用chmod命令进行更改。

- Sagar Pise

Answer 3

你在尝试使用psql命令行时遇到的问题是斜杠方向不正确：

newTestDB-# /i E:\db-rbl-restore-20120511_Dump-20120514.sql   # incorrect
newTestDB-# \i E:/db-rbl-restore-20120511_Dump-20120514.sql   # correct

请注意，psql命令以反斜杠开头，因此您应该使用\i而不是\db。由于您的笔误，psql忽略了找到第一个\之前的所有内容，随后是db，而\db恰好是列出表空间的psql命令，因此输出结果为表空间列表，而不是您所说的“PostgreSQL默认表格清单”。

此外，似乎psql希望filepath参数使用正斜杠来分隔目录，而不管操作系统如何（因此在Windows上这可能会令人困惑）。

值得注意的是，您尝试的“提升权限”与您尝试执行的命令的结果无关。另外，您也没有说明导致所谓的“权限被拒绝”错误的原因。

最后，转储文件的扩展名并不重要，事实上您甚至不需要扩展名。确实，在选择备份文件名时，pgAdmin建议使用.backup扩展名，但您实际上可以将其设置为任何您想要的扩展名，甚至可以不使用扩展名。问题在于pgAdmin似乎只允许“自定义或tar”或“目录”格式的转储文件进行还原操作（至少在MAC OS X版本的应用中是这样），因此请使用如上所示的psql \i命令。

- user664833

---

路径中的斜杠方向也不正确，会导致“无效参数”错误。我不得不将路径中所有反斜杠改为正斜杠，就像RonZ在下面所说的那样。 - AlannaRose

@AlannaRose - 你用的是什么操作系统？ - user664833

Windows 2008r2和Windows 7。听起来你正在使用Mac OS X——这是否意味着这里的斜杠方向取决于操作系统？ - AlannaRose

在文件路径上下文中，斜杠取决于操作系统（在Windows上是反斜杠，在类Unix系统上是正斜杠），但是任何给定程序（在本例中为psql）都可以覆盖典型的目录分隔符并强制使用自定义格式。我曾经认为目录分隔符将与底层操作系统一致，但根据其他答案，似乎psql会进行自己的FILENAME解析和重新组装。我已相应地更新了我的答案，谢谢。 - user664833

Answer 4

1. 打开终端。

2. 使用以下命令备份您的数据库：

您的Postgres二进制文件位置 - /opt/PostgreSQL/9.1/bin/

您的源数据库服务器 - 192.168.1.111

您的备份文件位置和名称 - /home/dinesh/db/mydb.backup

您的源数据库名称 - mydatabase

/opt/PostgreSQL/9.1/bin/pg_dump --host '192.168.1.111' --port 5432 --username "postgres" --no-password --format custom --blobs --file "/home/dinesh/db/mydb.backup" "mydatabase"

3. 将`mydb.backup`文件恢复到目标位置。

您的目标服务器 - `localhost`

您的目标数据库名称 - `mydatabase`

4. 为恢复备份创建数据库。

`/opt/PostgreSQL/9.1/bin/psql -h 'localhost' -p 5432 -U postgres -c "CREATE DATABASE mydatabase"`

5. 恢复备份。

/opt/PostgreSQL/9.1/bin/pg_restore --host 'localhost' --port 5432 --username "postgres" --dbname "mydatabase" --no-password --clean "/home/dinesh/db/mydb.backup"

- Dinesh Appuhami

Answer 5

结合MartinP和user664833的建议，我也成功地使其工作。需要注意的是，通过选择插件...PSQL控制台从pgAdmin GUI工具输入psql会话会设置psql会话的凭据和权限级别，因此您必须对表具有管理员或CRUD权限，可能还需要对数据库拥有管理员权限（不确定）。然后在psql控制台中，命令应该采用以下格式：

postgres=# \i driveletter:/folder_path/backupfilename.backup

其中 postgres=# 是 psql 提示符，不是命令的一部分。

.backup 文件将包含用于创建表的命令，因此您可能还会获得像“ALTER TABLE ...”这样的命令，这些命令在文件中被执行但报告为错误。我想你可以在运行还原之前删除这些命令，但最好保留它们，因为这些命令不太可能导致数据恢复失败。但请务必检查所需恢复的数据是否已经成功恢复。(如果这似乎对任何人都是轻视的建议，我很抱歉，但是即使是最资深的专家也有可能因同事、电话等短暂的分心而忘记这一步骤。我在职业生涯早期使用其他数据库时曾经犯过这个错误，然后想“咦，为什么我没有从这个查询中看到任何数据？” 答案是数据实际上没有被还原，我只是浪费了2个小时试图寻找不存在的可疑错误.)

- Matt Campbell

Answer 6

这是我在Windows中恢复Postgres数据库时使用的方法。首先，右键单击命令提示符并选择“以管理员身份运行”，以避免权限被拒绝的问题。执行相同的命令，但进行一些更改：

newTestDB-# \i E:/db-rbl-restore-20120511_Dump-20120514.sql

如果您注意到，命令“newTestDB-# \i E:\db-rbl-restore-20120511_Dump-20120514.sql”已更改为“newTestDB-# \i E：/ db-rbl-restore-20120511_Dump-20120514.sql”，在文件路径中使用/而不是\斜杠。

- Anish Narayan

Answer 7

您可能需要在数据库级别设置权限，允许模式所有者恢复转储。

- Bill Rosmus

Answer 8

我发现在Windows上，psql.exe对斜杠的方向非常挑剔（就像上面看起来的那样）。

这里有一个例子，在cmd窗口中：

C:\Program Files\PostgreSQL\9.2\bin>psql.exe -U postgres
psql (9.2.4)
Type "help" for help.

postgres=# \i c:\temp\try1.sql    
c:: Permission denied
postgres=# \i c:/temp/try1.sql
CREATE TABLE
postgres=#

当我在\i调用中使用“正常”的Windows斜杠时，您会发现它失败了。 然而，如果您将它们作为输入参数传递给psql.exe，则两种斜杠样式都可以工作，例如：

C:\Program Files\PostgreSQL\9.2\bin>psql.exe -U postgres -f c:\TEMP\try1.sql
CREATE TABLE

C:\Program Files\PostgreSQL\9.2\bin>psql.exe -U postgres -f c:/TEMP/try1.sql
CREATE TABLE

C:\Program Files\PostgreSQL\9.2\bin>

- MartinP