postgresql 使用命令行恢复 postgres 备份文件？

Restore a postgres backup file using the command line?

提问by TwixxyKit

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

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

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!

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

回答by Steven Schlansker

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

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

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

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

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.

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.

转储可以以脚本或存档文件格式输出。脚本转储是纯文本文件，其中包含将数据库重建到保存时的状态所需的 SQL 命令。要从这样的脚本恢复，请将其提供给 psql(1)。即使在其他机器和其他架构上，脚本文件也可用于重建数据库；甚至在其他 SQL 数据库产品上也进行了一些修改。

备用存档文件格式必须与 pg_restore(1) 一起使用以重建数据库。它们允许 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 psqlotherwise you should probably use pg_restore

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

Restoring is pretty easy:

恢复非常简单：

psql -U username -d dbname < filename.sql

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

or

或者

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

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.

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

回答by pilot

create backup

创建备份

pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f 
"/usr/local/backup/10.70.0.61.backup" old_db

-F c is custom format (compressed, and able to do in parallel with -j N) -b is including blobs, -v is verbose, -f is the backup file name

-F c 是自定义格式（压缩的，并且能够与 -j N 并行执行） -b 包括 blob，-v 是详细的，-f 是备份文件名

restore from backup

从备份中恢复

pg_restore -h localhost -p 5432 -U postgres -d old_db -v 
"/usr/local/backup/10.70.0.61.backup"

important to set -h localhost- option

设置-h localhost- 选项很重要

回答by Tombart

You might need to be logged in as postgresin order to have full privileges on databases.

您可能需要以 as 登录postgres才能拥有数据库的完全权限。

su - postgres
psql -l                      # will list all databases on Postgres cluster

pg_dump/pg_restore

pg_dump/pg_restore

  pg_dump -U username -f backup.dump database_name -Fc 

switch -Fspecify format of backup file:

switch-F指定备份文件的格式：

• cwill use custom PostgreSQL format which is compressed and results in smallest backup file size
• dfor directory where each file is one table
• tfor TAR archive (bigger than custom format)
• -h/--hostSpecifies the host name of the machine on which the server is running
• -W/--passwordForce pg_dumpto prompt for a password before connecting to a database

• c将使用自定义的 PostgreSQL 格式，该格式被压缩并导致最小的备份文件大小
• d对于每个文件是一张表的目录
• t用于 TAR 存档（大于自定义格式）
• -h/--host指定运行服务器的机器的主机名
• -W/--password强制pg_dump在连接数据库前提示输入密码

restore backup:

恢复备份：

   pg_restore -d database_name -U username -C backup.dump

Parameter -Cshould create database before importing data. If it doesn't work you can always create database eg. with command (as user postgresor other account that has rights to create databases) createdb db_name -O owner

参数-C应在导入数据之前创建数据库。如果它不起作用，您可以随时创建数据库，例如。with 命令（作为postgres有权创建数据库的用户或其他帐户）createdb db_name -O owner

pg_dump/psql

pg_dump/psql

In case that you didn't specify the argument -Fdefault plain text SQL format was used (or with -F p). Then you can't use pg_restore. You can import data with psql.

如果您没有指定参数，-F则使用（或使用-F p）默认纯文本 SQL 格式。那你就不能用了pg_restore。您可以使用psql.

backup:

备份：

pg_dump -U username -f backup.sql database_name

restore:

恢复：

psql -d database_name -f backup.sql

回答by Natan Medeiros

POSTGRESQL 9.1.12

POSTGRESQL 9.1.12

DUMP:

倾倒：

pg_dump -U user db_name > archive_name.sql

put the user password and press enter.

输入用户密码并按回车键。

RESTORE:

恢复：

psql -U user db_name < /directory/archive.sql

put the user password and press enter.

输入用户密码并按回车键。

回答by Yahor M

Below is my version of pg_dumpwhich I use to restore the database:

下面是我pg_dump用来恢复数据库的版本：

pg_restore -h localhost -p 5432 -U postgres -d my_new_database my_old_database.backup

or use psql:

或使用psql：

psql -h localhost -U postgres -p 5432 my_new_database < my_old_database.backup

where -hhost, -pport, -ulogin username, -dname of database

其中-h主机、-p端口、-u登录用户-d名、数据库名称

回答by Sarath Ak

Backup and restore with GZIP

使用 GZIP 备份和恢复

For larger size database this is very good

对于较大的数据库，这是非常好的

backup

备份

pg_dump -U user -d mydb | gzip > mydb.pgsql.gz

restore

恢复

gunzip -c mydb.pgsql.gz | psql dbname -U user

https://www.postgresql.org/docs/9.1/static/backup-dump.html

https://www.postgresql.org/docs/9.1/static/backup-dump.html

回答by Aaron Lelevier

Backup:  $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

Restore: $ psql -U {user-name} -d {desintation_db} -f {dumpfilename.sql}

回答by Franz

This worked for me:

这对我有用：

pg_restore --verbose --clean --no-acl --no-owner --host=localhost --dbname=db_name --username=username latest.dump

回答by Dinesh-SriLanka

1.open the terminal.

1.打开终端。

2.backup your database with following command

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

your postgres bin - /opt/PostgreSQL/9.1/bin/

你的 postgres bin - /opt/PostgreSQL/9.1/bin/

your source database server - 192.168.1.111

您的源数据库服务器 - 192.168.1.111

your backup file location and name - /home/dinesh/db/mydb.backup

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

your source db name - mydatabase

您的源数据库名称 - 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"

/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" "我的数据库"

3.restore mydb.backup file into destination.

3.将 mydb.backup 文件恢复到目的地。

your destination server - localhost

您的目标服务器 - 本地主机

your destination database name - mydatabase

您的目标数据库名称 - mydatabase

create database for restore the backup.

创建数据库以恢复备份。

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

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

restore the backup.

恢复备份。

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

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

回答by Dinesh-SriLanka

If you create a backup using pg_dump you can easily restore it in the following way:

如果您使用 pg_dump 创建备份，您可以通过以下方式轻松恢复它：

1. Open command line window
2. Go to Postgres bin folder. For example: cd "C:\ProgramFiles\PostgreSQL\9.5\bin"
3. Enter the command to restore your database. For example: psql.exe -U postgres -d YourDatabase -f D:\Backup\.sql
4. Type password for your postgres user
5. Check the restore process

1. 打开命令行窗口
2. 转到 Postgres bin 文件夹。例如：cd "C:\ProgramFiles\PostgreSQL\9.5\bin"
3. 输入命令以恢复数据库。 For example: psql.exe -U postgres -d YourDatabase -f D:\Backup\.sql
4. 为您的 postgres 用户键入密码
5. 检查恢复过程