How to export PostgreSQL database structure while ignoring objects created by PostGIS

Question

I want to export the database structure from a PostgreSQL database. I want to include all tables, functions, etc., created by me but exclude everything created by database extensions like PostGIS. What is the easiest way how to do that?

The problem is that there are hundreds of functions and procedures in the same (public) schema as my own functions.

Answer 1

You can use the pg_dump utility to export the structure of your PostgreSQL database.

To exclude objects created by database extensions like PostGIS, you can use the --exclude-extension option followed by the name of the extension. For example, to exclude all objects created by the PostGIS extension, you can use the following command:

pg_dump --exclude-extension=postgis -U <username> -s <databasename> > <outputfile>

This command will create a SQL script that includes the structure of all tables, functions, etc., created by you, but excludes everything created by the PostGIS extension. The -s option specifies that only the database schema should be exported, without the data.

You can customize the command further by specifying additional options such as the --schema option to include only specific schemas, or the --table option to include only specific tables. You can also pipe the output of pg_dump to a compression utility such as gzip to create a compressed backup file.