How to Take Backup of Functions Only in Postgres

How to take backup of functions only in Postgres

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.

How to take functions backup in postgresql

This is a shell problem.

<< is a feature of Unix-like shells, while you seem to be using Windows.

Just save the text of the query into a file and run it with the -f flag.

How to get dump only function creation & Stored Process script via command line in PostgreSQL

This will do:

pg_dump -Fc -s -n <schemaname> -f temp.dump <database name>
pg_restore -l temp.dump | grep FUNCTION > functionalist
pg_restore -L functionalist temp.dump > yourfunctions.sql

How to do a pg_dump for only tables only and not triggers and functions?

I would dump everything with a custom format schema-only dump (-F c -s) and run pg_restore -l on the resulting dump. That gives you a table of contents. Delete everything except the tables from that file and use it as input to pg_restore -L to restore exactly those items from the archive that you need.

This may not be as simple as you have hoped for, but it is certainly simpler than writing tons of -t options, and you may be able to automatize it.

Backup database through function in PostgreSQL

No, it is not.

pg_dump must be used.

It's often requested, but it's not supported at present, and nobody who requests it ever goes on to do any work to actually make it happen.

pg_dump excluded functions

It should dump functions (and all other objects) in the public schema.

The functions that are not dumped are those that are part of an extension, like the crosstab in your case. Such objects are not dumped individually, they are included in the CREATE EXTENSION.

Unfortunately extensions are not dumped with a schema dump (they belong to the database).

You should create the extensions manually on the destination database before restoring the dump:

CREATE EXTENSION crosstab;

---

Related Topics