如何在Postgres中仅备份函数

Question

我想备份我的Postgres数据库中的所有函数。如何只备份Postgres中的函数？

- vmb

Answer 1

使用pg_getfunctiondef函数; 参见系统信息函数。 pg_getfunctiondef 函数在PostgreSQL 8.4中添加。

SELECT pg_get_functiondef('proc_name'::regproc);

要在架构中转储所有函数，您可以查询pg_catalog中的系统表；例如，如果您想要来自public的所有内容：

要转储模式中的所有函数，可以查询pg_catalog中的系统表；例如，如果您需要public架构中的所有函数：

SELECT pg_get_functiondef(f.oid)
FROM pg_catalog.pg_proc f
INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
WHERE n.nspname = 'public';

如果你想这样说，将上述内容更改为"从所有模式中排除以pg_开头的模式"非常容易。

在psql中，你可以使用以下命令将其转储到文件中：

psql -At dbname > /path/to/output/file.sql <<"__END__"
... the above SQL ...
__END__

要在另一个数据库中运行输出，可以使用类似以下的方法：

psql -1 -v ON_ERROR_STOP -f /path/to/output/file.sql target_db_name

如果您正在像这样在数据库之间复制函数，请考虑将函数定义的权威副本存储为 SQL 脚本，在版本控制系统（如svn或git）中打包为 PostgreSQL 扩展。请参见打包扩展。

- Craig Ringer

---

我还想将这些函数恢复到另一个数据库中。我该怎么做？只需要恢复函数即可。 - vmb

@vmb 输出的是SQL函数定义，所以将它们导入psql中。请查看更新的答案。更好的方法是创建一个扩展并将它们打包在其中。 - Craig Ringer

需要注意的是，pg_get_functiondef('proc_name'::regproc) 不会 转储函数的权限（GRANT 和 REVOKE 语句），这有时可能被认为是函数定义的一部分。 - NumberFour

这个答案很棒：迄今为止我看过的最好的选项。它帮助我们将近400个函数转储到可靠的备份文件中，并用于修订历史记录。为了做到这一点，在查询的末尾，我添加了一个排序：ORDER BY pg_get_functiondef(f.oid) 谢谢！ - nbburn

请注意，此操作不会转储聚合函数。它将产生一个错误（“ERROR: X 是一个聚合函数”）。您需要添加“and f.proisagg is false”来排除这些函数。 - cerd

Answer 2

你不能让 pg_dump 仅转储函数。但是，您可以做一个没有数据的转储 (-s 或 --schema-only) 并在还原时进行过滤。请注意 --format=c (也就是 -Fc) 部分：这将生成一个适用于 pg_restore 的文件。

首先进行转储：

pg_dump -U username --format=c --schema-only -f dump_test your_database

然后创建一个函数列表：

pg_restore --list dump_test | grep FUNCTION > function_list

最后，通过 (-L 或 --use-list 指定上面创建的列表文件) 来恢复它们：

pg_restore -U username -d your_other_database -L function_list dump_test

- dezso