Postgres：将数据从一个数据库的 public 模式移动到另一个数据库的新模式的最佳方法

Question

我刚开始接触Postgres，发现无法在一个SQL查询中访问不同数据库的数据。并且学习了在Postgres中的schema概念。

现在，我有两个数据库

db1和db2

它们的public schema中都有相同的表名。

现在，我想在db1中创建一个名为：new_schema的新模式

并将数据从db2.public移动到db1.new_schema

最简单的方法是什么？

- Mandeep Singh

Answer 1

最简单的方法是重新命名模式。然而，您必须确保您是 db1 数据库的唯一用户。

首先，在 db1 中隐藏您的 public 模式：

alter schema public rename to original_public;
create schema public;

接下来，进行备份和恢复操作：

$ pg_dump --format custom --file "my_backup" --schema "public" "db2"
$ pg_restore --dbname "db1" "my_backup"

最后，重新创建合适的模式名称：

alter schema public rename to my_schema;
alter schema original_public rename to public;

另一个选择是使用dblink。它使得可以访问不同数据库的数据。

- klin

---

更新关于 dblink 的内容，最好使用 postgres_fdw。正如指南所说，它“提供了大致相同的功能，使用更现代和符合标准的基础设施”。 - Peter Krauss

Answer 2

从db2导出“public”（跳过授权和所有权）：

pg_dump -xO -n public db2 > db2.sql

导出的文件会设置搜索路径（通常在顶部附近）：

SET search_path = public, pg_catalog;

将其更改为:

CREATE SCHEMA IF NOT EXISTS new_schema;
SET search_path = new_schema, pg_catalog;

像往常一样导入到db1：

psql db1 < db2.sql

您可能想先将所有内容从 public 移动到 db1 的新模式中。

如果 db1 中已经设置好了模式，您可以一次性进行转移：

pg_dump -xO -n public db2 | sed 's/search_path = public/search_path = new_schema/' | psql db1

当然，如果没有经过充分测试，我不建议这样做。

- Dmitri

---

/usr/lib/postgresql/14/bin/pg_dump: invalid option -- '0' - happy_marmoset