6

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
dan
  • 43,914
  • 47
  • 153
  • 254

1 Answers1

8

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 3
    Or, you can use pg_restore's --list related options and manually trim the items from the list generated. – Richard Huxton Mar 09 '12 at 14:07
  • If you are coming here having problems with duplicate functions after restoring an old dump of a PostGIS DB you could investigate the upgrading choices of PostGIS https://postgis.net/docs/postgis_installation.html#upgrading. – bennos Nov 07 '19 at 09:35