postgresql 无法在远程 Linux VM 上 pg_restore SQL 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40292202/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Unable to pg_restore SQL file on remote Linux VM
提问by amitection
I am using PostgreSQL as my DB.
我使用 PostgreSQL 作为我的数据库。
I have SCPeda .sqlfile on my remote Ubuntu VM.
我在远程 Ubuntu 虚拟机上SCP了一个.sql文件。
I did sudo su - postgresand create a DB.
我做了sudo su - postgres并创建了一个数据库。
then I switch backed to my original account and tried this:
然后我切换回我的原始帐户并尝试了这个：
sudo -su postgres pg_restore < temp.sql
sudo -su postgres pg_restore < temp.sql
The command ran successfully.
命令运行成功。
But when I again switched back to postgresuser and checked the db for the list of tables using \dtI found no tables.
但是当我再次切换回postgres用户并使用\dt检查表列表的数据库时，我发现没有表。
What am I doing wrong?
我究竟做错了什么？
回答by lavajumper
'pg_restore' is meant to restore files generated by 'pg_dump'.
'pg_restore' 用于恢复由 'pg_dump' 生成的文件。
From the man page
从手册页
pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump(1) in one of the non-plain-text formats.
pg_restore 是一个实用程序，用于从 pg_dump(1) 创建的非纯文本格式之一的存档中恢复 PostgreSQL 数据库。
https://www.postgresql.org/docs/9.2/static/app-pgrestore.html
https://www.postgresql.org/docs/9.2/static/app-pgrestore.html
If your file was generated by pg_dump you probably need to at least tell it which database to dump into:
如果您的文件是由 pg_dump 生成的，您可能至少需要告诉它要转储到哪个数据库：
pg_restore -d my_new_database temp.sql
My own experience with pg_restore across different flavors shows that many times I need to specify the format of the dump file, even if it was in 'native' format, despite the manpage indicating that it would detect the format.
我自己在不同风格中使用 pg_restore 的经验表明，很多时候我需要指定转储文件的格式，即使它是“本机”格式，尽管联机帮助页表明它会检测格式。
pg_restore -d my_new_database -Fc temp.dump
This is only a guess, but I'm thinking if the tables actually restored, without specifying the db, they got dumped into the default database. You can check this by listing the tables in the 'postgres' database (there should be none).
这只是一个猜测，但我在想如果表实际上恢复了，而没有指定数据库，它们会被转储到默认数据库中。您可以通过列出“postgres”数据库中的表（应该没有）来检查这一点。
postgres=#\c postgres
You are now connected to database "postgres" as user "postgres".
postgres=#\dt
No relations found.
If your tables did restore into the default database they will be listed.
如果您的表确实恢复到默认数据库中，它们将被列出。
Plain text SQL files need to be treated differently, usually executed via SQL commands using psql.
纯文本 SQL 文件需要区别对待，通常使用 psql 通过 SQL 命令执行。
psql -d my_database < temp.sql
回答by d1jhoni1b
Assuming you generated your database backup similar to this:
假设您生成了类似于以下内容的数据库备份：
pg_dump -a --inserts databasename > exportfilename.sql
Try restoring your file like this:
尝试像这样恢复您的文件：
psql databasename -f exportfilename.sql
Postgres pg_restoreas mentioned above is only meant to be used with dumpfiles, you can't restore it like that. Check for this answer in the official postgres.org website
pg_restore上面提到的Postgres仅用于dump文件，您不能像那样恢复它。在官方 postgres.org 网站上查看此答案
Basically you don'tuse pg_restorewith sqlfiles --->
https://www.postgresql.org/message-id/AANLkTi%3DAqmWrUR4f8%2BEfCHzP%2BQrL1%3DunRLZp_jX7SoqF%40mail.gmail.com
基本上你不要使用pg_restore与sql文件--->
https://www.postgresql.org/message-id/AANLkTi%3DAqmWrUR4f8%2BEfCHzP%2BQrL1%3DunRLZp_jX7SoqF%40mail.gmail.com
