使用pg_dump和psql迁移数据库-U postgres db_name < ...会出现“ERROR：relation”table_name“不存在”的错误。

Question

我使用以下方法将我的PostgresQL数据库从一个硬盘移动到另一个硬盘:

pg_dump -U postgres db_name > db_name.dump

之后

psql -U postgres db_name < db_name.dump

我在两个实例中同样创建了名为db_name的数据库。但是当我在新的数据库上运行带有JPA查询（或JDBC查询）的Java程序时，出现以下错误：

"ERROR: relation "table1" does not exist"

查询语句是:

select count(0) from table1

我知道我的连接是正常的，因为如果我在连接参数中更改密码，就会出现错误。

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

如果我将查询更改为

select count(0) from myschema.table1

然后它会抱怨权限问题：

"ERROR: permission denied for schema myschema"

权限为什么会不同？

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

当我从Java执行查询时，pg_dump和psql的组合就出现了问题。

我需要做什么来解决这个问题？

- Dean Schulze

Answer 1

你是否要迁移到同一个版本的PostgreSQL？如果你使用pg_dump 8.3做了一次备份并试图在Postgresql 8.4中进行还原，可能会出现问题。不过，假设是相同版本，请尝试以下操作：

备份所有全局对象，例如用户和组（不知道它们是否包含在你的备份中）：

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

这是我的个人意见。希望能帮到你。

- John P

---

我正在从8.3升级到8.4。我将更改我的备份脚本，以使用您更全面的方法。感谢您的回复。 - Dean Schulze

我会为大型数据库在pg_restore中添加详细标志(-v)。 pg_restore -a -v -d dbname -Fc full.dump - green0range

如果你最终要使用 pg_restore -a，那么为什么要将模式以明文和压缩的形式都转储一遍呢？为什么不使用 pg_dump -Fc -a -v -f data.dump 呢？或者干脆跳过模式步骤，分别在两个步骤中处理全局变量和其他所有内容呢？ - Air

在恢复块中，第二行，我假设文件名应该是'db-schema.sql'？ - Dave Munger

到目前为止，最好的答案是，我在恢复数据库时遇到了许多问题，但这个答案解决了我的困扰。 - Kenny Aires

Answer 2

我遇到了这个问题。然后我意识到我忘记安装 postgis 扩展。

不要忘记安装你使用的扩展。

- Ali Davut

---

非常感谢你，这是我苦苦挣扎了几个小时的问题。我甚至不知道 postgis 的存在，因为我是新手在 postgre 的世界里。我只是从仓库中安装了 postgis，一切都正常工作了。 - Doctor Coder

Answer 3

我通过将数据库权限更改为公共连接和公共以及postgres的模式权限更改为USAGE和CREATE，成功解决了这个问题。我的备份脚本显然没有保留权限，至少在从8.3迁移到8.4时没有保留。

- Dean Schulze