postgresql 输入文件似乎是文本格式转储。请使用 psql

input file appears to be a text format dump. Please use psql

提问by Haseeb Ahmad

I take backup using

我使用备份

pg_dump db_production > postgres_db.dump

and then I copy it to localhost using scp.

然后我使用 scp 将它复制到本地主机。

Now when I import on my local db it gives an error

现在，当我在本地数据库上导入时，会出现错误

pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

by using commad line

通过使用命令行

pg_restore -d db_development postgres_db.dump

回答by Зелёный

From the pg_dumpdocumentation:

从pg_dump文档：

Examples

例子

To dump a database called mydb into a SQL-script file:

将名为 mydb 的数据库转储到 SQL 脚本文件中：

$ pg_dump mydb > db.sql

To reload such a script into a (freshly created) database named newdb:

要将这样的脚本重新加载到名为 newdb 的（新创建的）数据库中：

$ psql -d newdb -f db.sql

To dump a database into a custom-format archive file:

要将数据库转储到自定义格式的存档文件中：

$ pg_dump -Fc mydb > db.dump

To dump a database into a directory-format archive:

要将数据库转储到目录格式的存档中：

$ pg_dump -Fd mydb -f dumpdir

To reload an archive file into a (freshly created) database named newdb:

要将存档文件重新加载到名为 newdb 的（新创建的）数据库中：

$ pg_restore -d newdb db.dump

From the pg_restoredocumentation:

从pg_restore文档：

Examples

例子

Assume we have dumped a database called mydb into a custom-format dump file:

假设我们已将名为 mydb 的数据库转储到自定义格式的转储文件中：

$ pg_dump -Fc mydb > db.dump

To drop the database and recreate it from the dump:

要删除数据库并从转储中重新创建它：

$ dropdb mydb
$ pg_restore -C -d postgres db.dump

回答by Uziel Valdez

The answer above didn't work for me, this worked:

上面的答案对我不起作用，这有效：

psql db_development < postgres_db.dump

psql db_development < postgres_db.dump

回答by Tserenjamts

For me when i try to dump to remote host i used

对我来说，当我尝试转储到我使用的远程主机时

psql -U username -p 5432 -h 10.10.10.1 -d database < db.dump

worked fine. And if not remote just following command worked.

工作正常。如果不是远程，只需遵循以下命令即可。

psql -d database < db.dump

回答by Tim Fletcher

In order to create a backup using pg_dumpthat is compatible with pg_restoreyou must use the --format=custom/ -Fcwhen creating your dump.

为了使用pg_dump兼容的方式创建备份，pg_restore您必须在创建转储时使用--format=custom/ -Fc。

From the docs:

从文档：

Output a custom-format archive suitable for input into pg_restore.

输出适合输入到 pg_restore 的自定义格式存档。

So your pg_dumpcommand might look like:

所以你的pg_dump命令可能看起来像：

pg_dump --file /tmp/db.dump --format=custom --host localhost --dbname my-source-database --username my-username --password

And your pg_restorecommand:

还有你的pg_restore命令：

pg_restore --verbose --clean --no-acl --no-owner --host localhost --dbname my-destination-database /tmp/db.dump

回答by RAJNISH YADAV

For me, It's working like this one. C:\Program Files\PostgreSQL\12\bin> psql -U postgres -p 5432 -d dummy -f C:\Users\Downloads\d2cm_test.sql

对我来说，它就像这样工作。 C:\Program Files\PostgreSQL\12\bin> psql -U postgres -p 5432 -d dummy -f C:\Users\Downloads\d2cm_test.sql

回答by VasiliNovikov

If you have a full DB dump:

如果您有完整的数据库转储：

PGPASSWORD="your_pass" psql -h "your_host" -U "your_user" -d "your_database" -f backup.sql

If you have schemas kept separately, however, that won't work. Then you'll need to disable triggers for data insertion, akin to pg_restore --disable-triggers. You can then use this:

但是，如果您将模式分开保存，那将不起作用。然后您需要禁用数据插入触发器，类似于pg_restore --disable-triggers. 然后你可以使用这个：

cat database_data_only.gzip | gunzip | PGPASSWORD="your_pass" psql -h "your_host" -U root "your_database" -c 'SET session_replication_role = replica;' -f /dev/stdin

On a side note, it is a very unfortunate downside of postgres, I think. The default way of creating a dump in pg_dumpis incompatible with pg_restore. With some additional keys, however, it is. WTF?

另一方面，我认为这是 postgres 一个非常不幸的缺点。创建转储的默认方式pg_dump与pg_restore. 然而，有了一些额外的键，它是。跆拳道？

回答by M2E67

if you use pg_dump with -Fp to backup in plain text format, use following command:

如果使用带有 -Fp 的 pg_dump 以纯文本格式备份，请使用以下命令：

cat db.txt | psql dbname

to copy all data to your database with name dbname

将所有数据复制到名为 dbname 的数据库中