CODEWITHSUNDEEP
postgresqldatabase-schemadump
dan
dan

Reputation: 45642

Exclude function definitions when dumping a PostgreSQL database

I have a PostgreSQL database with PostGIS functions loaded into it. I would like to dump out the schema of the database, but pg_dump -s dumps out the functions along with the table definitions.

Is there a way to exclude the functions and just dump the table definitions?

Upvotes: 6

Views: 8767

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657982

For all I know, pg_dump and pg_dumpall do not support any such restriction.

You could move all your functions to a dedicated schema which you could exclude from the dump like this:

pg_dump mydb -N function_schema > mydump.sql

If you go that route, you can migrate functions to another schema like this:

ALTER FUNCTION myfunc() SET SCHEMA function_schema;

In this case I would also adapt the search_path in postgresql.conf (and possibly in the defaults for databases and roles)

SET search_path = public,function_schema [,more schemas]

As an alternative, you could leave the functions in their default schema public and not use that schema for anything else. Put your objects in one or more separate schemas. That should make upgrading PostGis easier.

It may be a good idea to not use the public schema for your objects. I usually reserve it for PostGis or other extensions that install into public by default. I like to use a dedicated schema for every application. Makes maintenance easier - including backups and granting permissions.

Upvotes: 8

Related Questions