postgresql 使用 pg_dump 和 psql -U postgres db_name < ... 移动数据库导致“错误：关系“table_name”不存在”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3518218/
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
Moving a database with pg_dump and psql -U postgres db_name < ... results in "ERROR: relation "table_name" does not exist"
提问by Dean Schulze
I moved my PostgresQL database from one hard drive to another using
我将 PostgresQL 数据库从一个硬盘驱动器移动到另一个使用
pg_dump -U postgres db_name > db_name.dump
and then
进而
psql -U postgres db_name < db_name.dump
I created the database db_name the same way in both instances. In the new database when I run my Java program with a JPA query (or a JDBC query) I get this error:
我在两个实例中都以相同的方式创建了数据库 db_name。在新数据库中，当我使用 JPA 查询（或 JDBC 查询）运行 Java 程序时，出现此错误：
"ERROR: relation "table1" does not exist"
The query is:
查询是：
select count(0) from table1
I know I've got a connection because if I change the password in the connection parameters I get an error.
我知道我有一个连接，因为如果我更改连接参数中的密码，我会收到错误消息。
For some reason in the new PostgresQL instance it thinks that table1 does not exist in the imported schema.
出于某种原因，在新的 PostgresQL 实例中，它认为 table1 不存在于导入的模式中。
If I change the query to
如果我将查询更改为
select count(0) from myschema.table1
从 myschema.table1 中选择 count(0)
Then it complains about permissions:
然后它抱怨权限：
"ERROR: permission denied for schema myschema"
Why would the permissions be different?
为什么权限会不同？
The table table1 exists in myschema because I can see it in the pgAdmin tool. All the rows were imported into the new PostgresQL instance.
表 table1 存在于 myschema 中，因为我可以在 pgAdmin 工具中看到它。所有的行都被导入到新的 PostgresQL 实例中。
When I do a query from Java the combination of pg_dump and psql created a problem.
当我从 Java 进行查询时，pg_dump 和 psql 的组合产生了问题。
What do I need to do to solve this issue?
我需要做什么来解决这个问题？
Thanks in advance.
提前致谢。
回答by John P
Are you moving to the same version of PostgreSQL? There might be issues if you make a dump with pg_dump 8.3 and try to restore it in Postgresql 8.4. Anyway, assuming that it is the same version try the following:
您要迁移到相同版本的 PostgreSQL 吗？如果您使用 pg_dump 8.3 进行转储并尝试在 Postgresql 8.4 中恢复它，则可能会出现问题。无论如何，假设它是相同的版本，请尝试以下操作：
Dump all global objects, such as users and groups (don't know if they were missing in your dump):
转储所有全局对象，例如用户和组（不知道转储中是否缺少它们）：
pg_dumpall -g -U postgres > globals.sql
Dump schema of database:
数据库转储模式：
pg_dump -Fp -s -v -f db-schema.sql -U postgres dbname
Dump contents of database:
转储数据库内容：
pg_dump -Fc -v -f full.dump -U postgres dbname
Now restore.
现在恢复。
psql -f globals.sql
psql -f db-schema.sql dbname
pg_restore -a -d dbname -Fc full.dump
That is my $0.02. Hope it helps.
那是我的 0.02 美元。希望能帮助到你。
回答by Ali Davut
I encountered this problem. Then I realized that I forgot to install postgisextension.
我遇到了这个问题。然后我意识到我忘记安装postgis扩展。
Don't forget to install the extensions you use.
不要忘记安装您使用的扩展。
回答by Dean Schulze
I was able to solve it by changing the database privileges to public CONNECT and the schema privileges for public and postgres = USAGE and CREATE.
我能够通过将数据库权限更改为 public CONNECT 以及 public 和 postgres = USAGE 和 CREATE 的模式权限来解决它。
My backup scripts apparently didn't preserve the privileges, at least not when moving from 8.3 to 8.4.
我的备份脚本显然没有保留特权，至少在从 8.3 移动到 8.4 时没有。
