Postgresql – Use pg_dump with postgis extensions


I'm using Postgres 9.4. I'm trying to dump a database (both the schema and the actual data) with the PostGIS extensions (set up using CREATE EXTENSION):

$ pg_dump prescribing -U prescribing -h localhost -Fc > prescribing.dump

But when I type in the db password, I see this:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for schema topology
pg_dump: [archiver (db)] query was: LOCK TABLE topology.topology IN ACCESS SHARE MODE

How can I dump this database?

UPDATE: it says these tables are owned by postgres:

 public | frontend_sha                               | table             | prescribing
 public | geography_columns                          | view              | postgres
 public | geometry_columns                           | view              | postgres
 public | pg_stat_statements                         | view              | postgres
 public | raster_columns                             | view              | postgres
 public | raster_overviews                           | view              | postgres
 public | spatial_ref_sys                            | table             | postgres
 public | vw_chemical_summary_by_ccg                 | materialized view | prescribing

Maybe I could just dump the other tables, and import them into another database set up in the same way? Or maybe I could run pg_dump as a superuser? But if I try pg_dump prescribing then it still asks me for a password, and I don't know what that should be.

Best Answer

What about using -T modifier for pg_dump to skip those tables?

For example -T topology.*