如何将PostgreSQL转储文件还原到Postgres数据库中？ [英] How to restore PostgreSQL dump file into Postgres databases?

问题描述

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

I have a dump file with a .SQL extension (in fact it is a plain-text SQL file). I want to restore it into my created databases. I am using pgAdmin III, and when I use its "Restore Wizard" it does not highlight the button "Restore". Instead it is expecting a .backup file extension.

我尝试使用shell命令来恢复转储，但是

I tried using shell the commands for restoring the dump, but it still didn't work.

我是这个新手。如果有人可以帮助我，我有义务。

I am a newbie at this. If anybody could help me I would be obliged.

我在Shell SQL窗格中使用了以下命令

I used following command to the Shell SQL Pane of PostGres while sitting at the newTestDB.

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

它仍然给出相同的错误（权限被拒绝）。

It still gave the same error ("Permission Denied").

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

After elevating permissions it just shows me the default tables of PostgreSQL:

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

(2 rows)

我不知道该怎么做从SQL文件导入/还原数据库。

I don't know what to do for importing/restoring database from an SQL file.

推荐答案

您没有提到如何进行备份，因此通用答案是：通常使用 psql 工具。

You didn't mention how your backup was made, so the generic answer is: Usually with the psql tool.

取决于 pg_dump 转储，该SQL文件可以具有不同的SQL命令集。
例如，如果您指示 pg_dump 使用-clean 和<$ c $转储数据库c>-仅模式，您将无法从该转储中还原数据库，因为将没有用于COPYing的SQL命令（如果，则不会插入- -inserts ）中的实际数据。这样的转储将仅包含DDL SQL命令，并且能够重新创建架构，但不能重新创建实际数据。

Depending on what pg_dump was instructed to dump, the SQL file can have different sets of SQL commands. For example, if you instruct pg_dump to dump a database using --clean and --schema-only, you can't expect to be able to restore the database from that dump as there will be no SQL commands for COPYing (or INSERTing if --inserts is used ) the actual data in the tables. A dump like that will contain only DDL SQL commands, and will be able to recreate the schema but not the actual data.

使用<$ c恢复典型的SQL转储$ c> 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 工具。

In the case of backups made with pg_dump -Fc ("custom format"), which is not a plain SQL file but a compressed file, you need to use the pg_restore tool.

如果您正在使用类似Unix的计算机，请尝试以下操作：

If you're working on a unix-like, try this:

man psql
man pg_dump
man pg_restore

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

otherwise, take a look at the html docs. Good luck!

这篇关于如何将PostgreSQL转储文件还原到Postgres数据库中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！