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

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命令来实现此操作？

也欢迎任何其他方法！

- abubacker

---

我将尽力为您翻译：我就是无法接受在2017年这种事仍在发生，而解决办法是黑掉转储文件。 - Sharky

2018年，我的朋友！ - nackjicholson

2020年，我的朋友！@user1880957的问题是关于自动内置方式，而不是依赖于某些GUI工具的手动方式。 - Ameba Brain

2021 现在该死 x) - Nicoowr

2022年了，仍然没有解决这个问题。 - Bklyn

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;

- shaunc

Answer 2

有一个简单的解决方案：

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

create schema myschema;

SET search_path TO myschema;

现在，您可以使用以下命令还原备份转储：

psql -f pub.backup.sql

set search_path to <schema>命令将myschema设置为默认值，使得新表和其他对象在该模式下创建，而不是它们之前所属的“默认”模式中创建。

- fraber

---

有没有一种简洁的方法只编辑大型转储文件的头部？即使像这样的事情也涉及到复制它：http://superuser.com/questions/246837/how-do-i-add-text-to-the-beginning-of-a-file-in-bash - Peter Ehrlich

@PeterEhrlich 根据您的文件大小和可用RAM，您可以使用vim直接打开gzip'd dump文件。它仍然会在内存中扩展，但我能够打开、编辑和保存一个750MB（.gz）的文件，只有在保存时短暂停顿。您的情况可能会有所不同。 - David

@PeterEhrlich 您可以使用子进程在转储内容之前将某些内容写入文件中。例如：( echo "CREATE SCHEMA myschema; SET search_path TO myschema;" ; pg_dump -F p ... ) | gzip -9 > dump.sql.gz - gldnspud

@gldnspud 但据我所知，这只意味着文件中有两个“SET search_path”，第二个是我们不想要的。 - Andy Smith

这在Postgres 9.6.8 / 10.3中行不通：pg_dump不再使用SET search_path，而是在转储中为所有命令添加模式名称前缀。 - user2384183

Answer 3

在pg_restore本身中不存在该方法。您可以使用pg_restore生成SQL输出，然后将其通过sed脚本发送以进行更改。但是，您需要注意编写sed脚本的方式，以避免匹配和更改数据中的内容。

- Magnus Hagander

---

我知道这个答案很老了，但对于那些只想使用这个被接受的答案的人来说： @shaunc 更安全、更健壮，并且可以与压缩转储一起使用。 - agoldev

Answer 4

最简单的方法可能是在还原后直接重命名模式，即使用以下SQL：

ALTER SCHEMA my_schema RENAME TO temp_schema

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

- Hamish

---

这个解决方案可能会造成很多混乱，不是吗？ - abubacker

怎样才能改变架构名称是唯一安全的方法就是使用SQL语句。在另一个回答中，我们也提出了另外一个解决方案，并且都解释了这种方法是有风险的。对于这种不当的投票表示遗憾。 - Hamish

我已经更改了模式名称，但是在那之后，我无法更改搜索路径！用户在重命名后无法搜索任何表！ - Andrea Girardi

Answer 5

如果您只有几个表格，则可以逐个还原表格，pg_restore在指定-t tablename时接受-d database。当然，在还原表格之前，您必须设置模式，并在完成还原表格后解决索引和约束问题。

或者，在不同端口上设置另一个服务器，使用新的PostgreSQL服务器进行还原，重命名模式，将其转储，然后恢复到原始数据库中。当然这有点拙劣但是它可以完成工作。

如果您富有冒险精神，可以尝试使用十六进制编辑器更改转储文件中的数据库名称。我认为它仅在一处提到，并且只要新旧数据库名称相同，就应该有效。 YMMV，请勿在生产环境中执行此类操作，如果出现问题导致级联故障，概不负责。

- mu is too short

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设置即可。

- Sico

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

- Steve Bennett

---

请随意更新此答案以改进Bash。 - Steve Bennett

我会用sed命令完成这个操作，命令为s/\b$OLD_SCHEMA/$NEW_SCHEMA/g。 - Pipo

正如其他答案中提到的那样，如果模式名称在其他地方（例如数据中）存在，则需要小心。他的Perl脚本比纯粹替换所有$OLD_SCHEMA出现略微更安全。 - Sakari Cajanus

Answer 8

这个问题很久了，但也许可以帮助一些人。

将pg_restore的输出流传输给sed并替换模式以将转储导入到不同的模式中。

类似于以下内容：

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} 

- Dwhitz

---

你的意思是第一个命令应该是 pg_dump 而不是 pg_restore 吗？ - John Smith

@JohnSmith 不，我的意思是pg_restore。您应该在导入目标数据库的转储时执行替换模式之前先进行转储。 - Dwhitz

@Dwhitz，有一个小的拼写错误；应该是{"source_schema"/"target_schema"/}。 - snackbar