postgresql 我想用不同的模式恢复数据库

I want to restore the database with a different schema

提问by abubacker

I have taken a dump of a database named temp1, by using the follwing command

我temp1使用以下命令转储了一个名为 的数据库

$  pg_dump -i -h localhost  -U postgres -F c -b -v -f pub.backup temp1 

Now I want to restore the dump in a different database called "db_temp" , but in that I just want that all the tables should be created in a "temp_schema" ( not the default schema which is in the fms temp1 database ) which is in the "db_temp" database.

现在我想在一个名为“db_temp”的不同数据库中恢复转储，但我只想在“temp_schema”（不是 fms temp1 数据库中的默认架构）中创建所有表“db_temp”数据库​​。

Is there any way to do this using pg_restorecommand?

有没有办法使用pg_restore命令来做到这一点？

Any other method also be appreciated!

任何其他方法也将不胜感激！

采纳答案by Magnus Hagander

There's no way in pg_restore itself. What you can do is use pg_restore to generate SQL output, and then send this through for example a sed script to change it. You need to be careful about how you write that sed script though, so it doesn't match and change things inside your data.

pg_restore 本身没有办法。您可以做的是使用 pg_restore 生成 SQL 输出，然后通过例如 sed 脚本发送它来更改它。但是，您需要小心编写该 sed 脚本的方式，因此它不会匹配并更改数据中的内容。

回答by fraber

There is a simple solution:

有一个简单的解决方案：

• Create your backup dump in plain SQL format (format "p" using the parameter --format=por -F p)
• Edit your pub.backup.sql dump with your favorite editor and add the following two lines at the top of your file:

• 以纯 SQL 格式创建备份转储（使用参数--format=por格式“p” -F p）
• 使用您喜欢的编辑器编辑 pub.backup.sql 转储，并在文件顶部添加以下两行：

create schema myschema;

SET search_path TO myschema;

create schema myschema;

SET search_path TO myschema;

Now you can restore your backup dump with the command

现在您可以使用以下命令恢复备份转储

psql -f pub.backup.sql

psql -f pub.backup.sql

The set search_path to <schema>command will set myschema as the default, so that new tables and other objects are created in this schema, independently of the "default" schema where they lived before.

该set search_path to <schema>命令将 myschema 设置为默认模式，以便在此模式中创建新表和其他对象，独立于它们之前所在的“默认”模式。

回答by shaunc

A quick and dirty way:

一种快速而肮脏的方式：

1) rename default schema:

1）重命名默认架构：

alter schema public rename to public_save;

2) create new schema as default schema:

2）创建新架构作为默认架构：

create schema public;

3) restore data

3) 恢复数据

pg_restore -f pub.backup db_temp [and whatever other options]

4) rename schemas according to need:

4）根据需要重命名模式：

alter schema public rename to temp_schema;
alter schema public_save rename to public;

回答by Hamish

Probably the easiest method would be to simply rename the schema after restore, ie with the following SQL:

可能最简单的方法是在恢复后简单地重命名架构，即使用以下 SQL：

ALTER SCHEMA my_schema RENAME TO temp_schema

I believe that because you're using the compressed archive format for the output of pg_dump you can't alter it before restoring. The option would be to use the default output and do a search and replace on the schema name, but that would be risky and could perhaps cause data to be corrupted if you were not careful.

我相信，因为您对 pg_dump 的输出使用了压缩存档格式，所以在恢复之前您无法更改它。选项是使用默认输出并在模式名称上进行搜索和替换，但这会有风险，如果您不小心，可能会导致数据损坏。

回答by mu is too short

If you only have a few tables then you can restore one table at a time, pg_restoreaccepts -d databasewhen you specify -t tablename. Of course, you'll have to set up the schema before restoring the tables and then sort out the indexes and constraints when you're done restoring the tables.

如果您只有几张表，那么您可以一次恢复一张表，当您指定时pg_restore接受。当然，您必须在还原表之前设置架构，然后在完成还原表后整理索引和约束。-d database-t tablename

Alternatively, set up another server on a different port, restore using the new PostgreSQL server, rename the schema, dump it, and restore into your original database. This is a bit of a kludge of course but it will get the job done.

或者，在不同的端口上设置另一台服务器，使用新的 PostgreSQL 服务器恢复，重命名架构，转储它，然后恢复到原始数据库。这当然有点麻烦，但它会完成工作。

If you're adventurous you might be able to change the database name in the dump file using a hex editor. I think it is only mentioned in one place in the dump and as long as the new and old database names are the same it should work. YMMV, don't do anything like this in a production environment, don't blame me if this blows up and levels your home town, and all the rest of the usual disclaimers.

如果您喜欢冒险，您可以使用十六进制编辑器更改转储文件中的数据库名称。我认为它只在转储中的一个地方提到，只要新旧数据库名称相同，它就可以工作。YMMV，不要在生产环境中做这样的事情，如果这炸毁了你的家乡，以及所有其他常见的免责声明，请不要责怪我。

回答by Sico

Rename the schema in a temporary database.

重命名临时数据库中的架构。

Export the schema:

导出架构：

pg_dump --schema-only --schema=prod > prod.sql

Create a new database. Restore the export:

创建一个新的数据库。恢复导出：

psql -f prod.sql

ALTER SCHEMA prod RENAME TO somethingelse;

pg_dump --schema-only --schema=somethingelse > somethingelse.sql

(delete the database)

（删除数据库）

For the data you can just modify the set search_pathat the top.

对于数据，您只需修改search_path顶部的设置即可。

回答by Steve Bennett

As noted, there's no direct support in pg_dump, psql or pg_restore to change the schema name during a dump/restore process. But it's fairly straightforward to export using "plain" format then modify the .sql file. This Bash script does the basics:

如前所述，pg_dump、psql 或 pg_restore 中没有直接支持在转储/恢复过程中更改模式名称。但是使用“普通”格式导出然后修改 .sql 文件相当简单。这个 Bash 脚本执行基本操作：

rename_schema () {

  # Change search path so by default everything will go into the specified schema
  perl -pi -e "s/SET search_path = , pg_catalog/SET search_path = , pg_catalog, ;/" ""

  # Change 'ALTER FUNCTION foo.' to 'ALTER FUNCTION bar.'
  perl -pi -e 's/^([A-Z]+ [A-Z]+) ''\./ ''./' ""

  # Change the final GRANT ALL ON SCHEMA foo TO PUBLIC
  perl -pi -e 's/SCHEMA ''/SCHEMA ''/' ""

}

Usage:

用法：

pg_dump --format plain --schema=foo --file dump.sql MYDB
rename_schema dump.sql foo bar
psql -d MYDB -c 'CREATE SCHEMA bar;'
psql -d MYDB -f dumpsql