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

Question

我已经使用以下命令对名为 temp1 的数据库进行了转储

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

现在我想在名为“db_temp”的不同数据库中恢复转储，但我只希望所有表都应该在“db_temp”数据库​​中的“temp_schema”（不是 fms temp1 数据库中的默认模式）中创建。

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

任何其他方法也将受到赞赏！

Answer 1

一种快速而肮脏的方法：

1）重命名默认模式：

alter schema public rename to public_save;

2）创建新模式作为默认模式：

create schema public;

3）恢复数据

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

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

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

Answer 2

有一个简单的解决方案：

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

创建模式 myschema;

将 search_path 设置为 myschema；

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

psql -f pub.backup.sql

set search_path to 命令会将 myschema 设置为默认值，以便在此架构中创建新表和其他对象，独立于他们之前居住的“默认”模式。

Answer 3

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

Answer 4

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

ALTER SCHEMA my_schema RENAME TO temp_schema

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

Answer 5

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

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

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

Answer 6

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

导出架构：

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

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

psql -f prod.sql

ALTER SCHEMA prod RENAME TO somethingelse;

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

删除数据库）

数据只需修改顶部设置的search_path即可。

Answer 7

如前所述，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 = $2, pg_catalog/SET search_path = $3, pg_catalog, $2;/" "$1"

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

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

}

用法：

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

Answer 8

这个问题很老了，但也许可以帮助某人。

将 pg_restore 的输出流式传输到 sed 并替换架构名称，以便将转储导入到不同的架构。

例如：

v.15.6 之前的 Pg 恢复

pg_restore ${dumpfile} | \
    sed -e "s/OWNER TO ${source_owner}/OWNER TO ${target_owner}/" \
        -e "s/${source_schema}/${target_schema}/" | \
       psql -h ${pgserver} -d ${dbname} -U ${pguser} 

从 v.15.6 开始的 Pg 恢复

pg_restore ${dumpfile} -f - | \
    sed -e "s/OWNER TO ${source_owner}/OWNER TO ${target_owner}/" \
        -e "s/${source_schema}/${target_schema}/" | \
       psql -h ${pgserver} -d ${dbname} -U ${pguser}