3

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
Password:

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.

  • The quick fix, as you say would be to "run pg_dump as a superuser": but that means doing pg_dump -U postgres prescribing, the -U postgres bit is important (unless your current shell user is postgres, or you specify $PGUSER, or something like that). The more involved fix will be to ensure that your prescribing user has appropriate privileges (SELECT, USAGE) on all schemas and tables in your prescribing database. – Josh Kupershmidt Aug 14 '15 at 1:52
  • Thanks! pg_dump -U postgres prescribing still asks me for a password, and as far as I know the postgres user doesn't have a password, so that's confusing. However switching to the shell user postgres (sudo su postgres) fixed the problem. – Richard Aug 14 '15 at 8:16
  • Sounds like you are using "peer" authentication in your pg_hba.conf file. That's fine, but if you want to learn more about how to configure authentication, see the docs. – Josh Kupershmidt Aug 14 '15 at 12:08
-2

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

For example -T topology.*

  • What does the -T modifier do? How will it help? Edit your answer and add little explanation and you may improve your credibility. – Thronk Oct 14 '15 at 3:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.