如何仅在Postgres中进行功能备份 [英] How to take backup of functions only in Postgres
问题描述
我要备份postgres数据库中的所有功能。如何仅备份Postgres中的功能?
I want to take backup of all functions in my postgres database.How to take backup of functions only in Postgres?
推荐答案
使用 pg_getfunctiondef
;请参阅系统信息功能。 PostgreSQL 8.4中添加了 pg_getfunctiondef
。
use pg_getfunctiondef
; see system information functions. pg_getfunctiondef
was added in PostgreSQL 8.4.
SELECT pg_get_functiondef('proc_name'::regproc);
要转储模式中的所有功能,可以在 pg_catalog中查询系统表
;假设您是否要从 public
中获取所有内容:
To dump all functions in a schema you can query the system tables in pg_catalog
; say if you wanted everything from 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';
从所有模式中以更改以上内容以表示是很简单的c $ c> pg _ ,如果那是您想要的。
it's trivial to change the above to say "from all schemas except those beginning with pg_
" instead if that's what you want.
在 psql
,您可以使用以下命令将其转储到文件中:
In psql
you can dump this to a file with:
psql -At dbname > /path/to/output/file.sql <<"__END__"
... the above SQL ...
__END__
要在另一个数据库中运行输出,请使用以下命令:
To run the output in another DB, use something like:
psql -1 -v ON_ERROR_STOP -f /path/to/output/file.sql target_db_name
不过,在这样的数据库之间复制功能时,请考虑将功能定义的授权副本作为SQL脚本存储在版本控制系统(如svn或git)中,最好打包为PostgreSQL扩展。请参见打包扩展名。
If you're replicating functions between DBs like this, though, consider storing the authorative copy of the function definitions as a SQL script in a revision control system like svn or git, preferably packaged as a PostgreSQL extension. See packaging extensions.
这篇关于如何仅在Postgres中进行功能备份的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!