I want to take backup of all functions in my postgres database.How to take backup of functions only in Postgres?
0
Add a comment
|
2 Answers
use pg_getfunctiondef; see system information functions. pg_getfunctiondef was added in PostgreSQL 8.4.
SELECT pg_get_functiondef('proc_name'::regproc);
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';
it's trivial to change the above to say "from all schemas except those beginning with pg_" instead if that's what you want.
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
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.
9 Comments
vmb
Also i want to restore these functions to another db.How can i do that??.Functions only need to restore
Craig Ringer
@vmb The output is SQL function definitions, so pipe them into psql. See updated answer. Even better, create an extension and package them in it.
NumberFour
It should be noted, that
pg_get_functiondef('proc_name'::regproc) DOES NOT dump the function's privileges (GRANT and REVOKE statements) which may be sometimes considered as part of the function definition.
nbburn
This answer is great: by far the best option I looked at. It helped us in dumping our nearly 400 functions into a reliable backup file as well as to use for revision history. To do that, at the end the of the query I added an order by:
ORDER BY pg_get_functiondef(f.oid) Thanks!
cerd
Note this will not dump aggregate functions. It will produce an error (
"ERROR: X is an aggregate function"). You need to add and f.proisagg is false to exclude those. |
You can't tell pg_dump to dump only functions. However, you can make a dump without data (-s or --schema-only) and filter it on restoring. Note the --format=c (also -Fc) part: this will produce a file suitable for pg_restore.
First take the dump:
pg_dump -U username --format=c --schema-only -f dump_test your_database
Then create a list of the functions:
pg_restore --list dump_test | grep FUNCTION > function_list
And finally restore them (-L or --use-list specifies the list file created above):
pg_restore -U username -d your_other_database -L function_list dump_test
2 Comments
Shihe Zhang
Seems you can only get public function this way. see stackoverflow.com/questions/57581710/pg-dump-excluded-functions
András Váczi
@ShiheZhang nope. Laurenz there is using
-n public explicitly to dump the public schema only.