使用命令行恢复 postgres 备份文件? [英] Restore a postgres backup file using the command line?

问题描述

我是 postgresql 的新手，在本地，我使用 pgadmin3.然而，在远程服务器上，我没有这样的奢侈.

I'm new to postgresql, and locally, I use pgadmin3. On the remote server, however, I have no such luxury.

我已经创建了数据库的备份并复制了它，但是，有没有办法从命令行恢复备份?我只看到与 GUI 或 pg_dumps 相关的东西，所以，如果有人能告诉我如何去做，那就太棒了！

I've already created the backup of the database and copied it over, but, is there a way to restore a backup from the command line? I only see things related to GUI or to pg_dumps, so, if someone can tell me how to go about this, that'd be terrific!

推荐答案

有两种工具可供查看，具体取决于您创建转储文件的方式.

There are two tools to look at, depending on how you created the dump file.

您的第一个参考源应该是手册页 pg_dump(1)，因为它是创建转储本身的原因.它说:

Your first source of reference should be the man page pg_dump(1) as that is what creates the dump itself. It says:

转储可以在脚本或存档文件格式.脚本转储是包含 SQL 的纯文本文件重建所需的命令数据库恢复到原来的状态在它被保存的时候.到从这样的脚本恢复，将其提供给psql(1).可以使用脚本文件甚至重建数据库在其他机器和其他架构；有一些修改甚至在其他 SQL 数据库产品上也是如此.

Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql(1). Script files can be used to reconstruct the database even on other machines and other architectures; with some modifications even on other SQL database products.

替代存档文件格式必须与 pg_restore(1) 一起使用才能重建数据库.他们允许pg_restore 对什么有选择性被恢复，甚至重新排序恢复之前的项目.这存档文件格式旨在可跨架构移植.

The alternative archive file formats must be used with pg_restore(1) to rebuild the database. They allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive file formats are designed to be portable across architectures.

所以取决于它被倾倒的方式.您可能可以使用出色的 file(1) 命令来解决它 - 如果它提到 ASCII 文本和/或 SQL，则应使用 psql 将其恢复，否则您可能应该使用pg_restore

So depends on the way it was dumped out. You can probably figure it out using the excellent file(1) command - if it mentions ASCII text and/or SQL, it should be restored with psql otherwise you should probably use pg_restore

恢复非常简单:

psql -U username -d dbname < filename.sql

-- For Postgres versions 9.0 or earlier
psql -U username -d dbname -1 -f filename.sql

或

pg_restore -U username -d dbname -1 filename.dump

查看他们各自的联机帮助页 - 有很多选项会影响还原的工作方式.根据转储的生成方式，您可能需要在恢复之前清除实时"数据库或从 template0 重新创建它们(如评论中指出的那样).

Check out their respective manpages - there's quite a few options that affect how the restore works. You may have to clean out your "live" databases or recreate them from template0 (as pointed out in a comment) before restoring, depending on how the dumps were generated.

这篇关于使用命令行恢复 postgres 备份文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！