postgresql Postgres：将数据从一个数据库的公共模式移动到另一个数据库的新模式的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24080832/
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
Postgres: Best way to move data from public schema of one DB to new schema of another DB
提问by Mandeep Singh
I am new to Postgres and just discovered that I cannot access data of different databases in one SQL query. And also learned the concept of schema in Postgres.
我是 Postgres 的新手，刚刚发现我无法在一个 SQL 查询中访问不同数据库的数据。并且还学习了 Postgres 中 schema 的概念。
Now, I have two databases
现在，我有两个数据库
db1 and db2
db1 和 db2
Both have tables with same name in their public schema.
两者都在其公共架构中具有相同名称的表。
Now, I want to create a new schema in db1 with name : new_schema
现在，我想在 db1 中创建一个名为 new_schema 的新模式
And move data from db2.public to db1.new_schema
并将数据从 db2.public 移动到 db1.new_schema
What is the easiest way to do this ?
什么是最简单的方法来做到这一点？
采纳答案by klin
The simplest way to do that is to rename schemas. However you must be sure you are a sole user of db1 database.
最简单的方法是重命名模式。但是，您必须确保您是 db1 数据库的唯一用户。
First, hide your schema public in db1:
首先，在 db1 中隐藏您的模式 public：
alter schema public rename to original_public;
create schema public;
Next, do the backup and restore:
接下来，进行备份和恢复：
$ pg_dump --format custom --file "my_backup" --schema "public" "db2"
$ pg_restore --dbname "db1" "my_backup"
Finally, recreate appropriate schema names:
最后，重新创建适当的架构名称：
alter schema public rename to my_schema;
alter schema original_public rename to public;
Another option is to use dblink.It enables accessing data of different databases.
另一种选择是使用dblink。它可以访问不同数据库的数据。
回答by Dmitri
Export "public" from db2 (skipping grants and ownership):
从 db2 导出“public”（跳过授权和所有权）：
pg_dump -xO -n public db2 > db2.sql
The exported file will set up the search path (somewhere near the top):
导出的文件将设置搜索路径（靠近顶部的某处）：
SET search_path = public, pg_catalog;
change it to:
将其更改为：
CREATE SCHEMA IF NOT EXISTS new_schema;
SET search_path = new_schema, pg_catalog;
Import to db1 as usual:
像往常一样导入到 db1：
psql db1 < db2.sql
You'll probably want to move everything from public to a new schema in db1, first.
首先，您可能希望将所有内容从公共模式移动到 db1 中的新模式。
If the schema is already set up in db1, you can do the transfer in one go:
如果 db1 中已经设置了 schema，则可以一次性完成传输：
pg_dump -xO -n public db2 | sed 's/search_path = public/search_path = new_schema/' | psql db1
Wouldn't recommend that without a lot of testing, of course.
当然，如果没有大量测试，不建议这样做。
