使用pg_dump和psql -U postgres移动数据库db_name< ...导致“错误:关系"table_name"不存在" [英] Moving a database with pg_dump and psql -U postgres db_name < ... results in "ERROR: relation "table_name" does not exist"

问题描述

我使用

pg_dump -U postgres db_name > db_name.dump

然后

psql -U postgres db_name < db_name.dump

我在两个实例中都以相同的方式创建了数据库db_name.在新数据库中，当我使用JPA查询(或JDBC查询)运行Java程序时，出现此错误:

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:

"ERROR: relation "table1" does not exist"

查询是:

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.

出于某种原因，在新的PostgresQL实例中，它认为table1在导入的模式中不存在.

For some reason in the new PostgresQL instance it thinks that table1 does not exist in the imported schema.

如果我将查询更改为

从myschema.table1中选择count(0)

select count(0) from myschema.table1

然后它抱怨权限:

"ERROR: permission denied for schema myschema"

权限为何不同?

表table1存在于myschema中，因为我可以在pgAdmin工具中看到它.所有行均导入到新的PostgresQL实例中.

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.

当我从Java中进行查询时，pg_dump和psql的组合会产生问题.

When I do a query from Java the combination of pg_dump and psql created a problem.

我需要怎么做才能解决此问题?

What do I need to do to solve this issue?

谢谢.

推荐答案

您要迁移到相同版本的PostgreSQL吗?如果您使用pg_dump 8.3进行转储并尝试在Postgresql 8.4中还原它，则可能会出现问题.无论如何，假设它是相同的版本，请尝试以下操作:

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:

转储所有全局对象，例如用户和组(不知道它们是否在转储中丢失):

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

转储数据库模式:

pg_dump -Fp -s -v -f db-schema.sql -U postgres dbname

转储数据库内容:

pg_dump -Fc -v -f full.dump -U postgres dbname

现在还原.

psql -f globals.sql
psql -f db-schema.sql dbname
pg_restore -a -d dbname -Fc full.dump

那是我的0.02美元.希望对您有所帮助.

That is my $0.02. Hope it helps.

这篇关于使用pg_dump和psql -U postgres移动数据库db_name< ...导致“错误:关系"table_name"不存在"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！