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

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相关的东西，所以，如果有人能告诉我该怎么做，那就太棒了!

14 个解决方案

#1

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:

您的第一个引用源应该是man page 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.

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

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.

可选择的存档文件格式必须与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 psql otherwise you should probably use pg_restore

这要看它是怎么倒出来的。您可以使用优秀的文件(1)命令来解决这个问题，如果它提到了ASCII文本和/或SQL，那么应该使用psql恢复，否则您应该使用pg_restore。

Restoring is pretty easy:

恢复是非常容易的:

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(如注释中指出的)重新创建它们，这取决于转储是如何生成的。

#2

create backup

创建备份

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

restore from backup

从备份恢复

pg_restore -i -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 -选项很重要。

#3

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

您可能需要以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 -F specify format of backup file:

开关-F指定备份文件格式:

• c will use custom PostgreSQL format which is compressed and results in smallest backup file size
• c将使用自定义的PostgreSQL格式，该格式被压缩，并导致最小的备份文件大小。
• d for directory where each file is one table
• d对于每个文件都是一个表的目录。
• t for TAR archive (bigger than custom format)
• t用于TAR存档(比自定义格式更大)

restore backup:

恢复备份:

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

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

参数-C应该在导入数据之前创建数据库。如果它不工作，你可以创建数据库。使用命令(作为用户postgres或其他拥有创建数据库权限的帐户)createdb db_name -O所有者。

pg_dump/psql

pg_dump / psql

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

如果您没有指定参数-F默认的纯文本SQL格式(或者使用-F p)，那么您就不能使用pg_restore。您可以使用psql导入数据。

backup:

备份:

pg_dump -U username -f backup.sql database_name

restore:

恢复:

psql -d database_name -f backup.sql

#4

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.

输入用户密码并按enter。

RESTORE:

恢复:

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

put the user password and press enter.

输入用户密码并按enter。

#5

Below is my version of pg_dump which 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 -h host, -p port, -u login username, -d name of database

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

#6

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

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

#7

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

resore

resore

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

#8

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——主机“192.168.1.111”——端口5432——用户名“postgres”——无密码——格式定制——blobs——文件“/home/dinesh/db/mydb”。备份mydatabase”

3.restore mydb.backup file into destination.

3所示。恢复mydb。备份文件到目的地。

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 "创建数据库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/ PostgreSQL/ bin/pg_restore——主机“localhost”——端口5432——用户名“postgres”——dbname“mydatabase”——无密码——clean“/home/dinesh/db/mydb.backup”

#9

Restoring a postgres backup file depends on how did you take the backup in the first place.

恢复postgres备份文件取决于您是如何首先进行备份的。

If you used pg_dump with -F c or -F d you need to use pg_restore otherwise you can just use

如果使用- fc或- fd使用pg_dump，则需要使用pg_restore，否则只能使用。

psql -h localhost -p 5432 -U postgres < backupfile

psql -h localhost -p 5432 -U postgres < backupfile。

9 ways to backup and restore postgres databases

9种备份和恢复postgres数据库的方法。

#10

As below link said, you can use psql command for restoring the dump file:

如下链接所示，您可以使用psql命令来恢复转储文件:

https://www.postgresql.org/docs/8.1/static/backup.html#BACKUP-DUMP-RESTORE

https://www.postgresql.org/docs/8.1/static/backup.html BACKUP-DUMP-RESTORE

psql dbname < infile

if you need to set username just add the username after the command like:

如果您需要设置用户名，只需在命令后添加用户名:

psql dbname < infile username

#11

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. 打开命令行窗口
3. Go to Postgres bin folder. For example: cd "C:\ProgramFiles\PostgreSQL\9.5\bin"
4. 进入Postgres bin文件夹。例如:cd C:\ ProgramFiles \ PostgreSQL \ 9.5 \ bin”
5. Enter the command to restore your database. For example: psql.exe -U postgres -d YourDatabase -f D:\Backup\.sql
6. 输入恢复数据库的命令。例如:psql。你的数据库- fd:\备份\。sql。
7. Type password for your postgres user
8. 为您的postgres用户键入密码。
9. Check the restore process
10. 检查恢复过程

#12

I was having authentication problems running pg_dump, so I moved my dump file

我正在运行pg_dump的身份验证问题，所以我移动了我的转储文件。

mv database_dump /tmp

into the temp directory and then ran

进入临时目录，然后运行。

su -u postgres
cd /tmp
pg_restore database_dump

If you have a large database dump, you may just want to create another directory where your current user and the postgres user can access and putting the database dump file into that.

如果您有一个大型的数据库转储，您可能只想创建另一个目录，其中您当前的用户和postgres用户可以访问并将数据库转储文件放入其中。

#13

Try to see if the following commands can help you:

试着看看以下命令是否能帮到你:

sudo su - yourdbuser
psql
\i yourbackupfile

#14

See below example its working

参见下面的示例。

C:/Program Files/PostgreSQL/9.4/bin\pg_restore.exe --host localhost --port 5432 --username "postgres" --dbname "newDatabase" --no-password --verbose

C:/程序文件/ PostgreSQL / 9.4 / bin \ pg_restore。exe——主机localhost——端口5432——用户名“postgres”——dbname“newDatabase”——无密码——verbose。

"C:\Users\Yogesh\Downloads\new Download\DB.backup"

“C:\用户下载\优\ \新下载\ DB.backup”