When PostGIS and pg_dump do not play well together

2020-04-07

Cubes in the dark

I was trying to make pg_dump backup of our database. When I got the error.

pg_dump: [archiver (db)] query failed: ERROR: permission denied for schema topology
pg_dump: [archiver (db)] query was: COPY topology.topology  TO stdout;

This error is caused due to the fact that the user that own the topology table might be the postgres user and not the user that You created for the database.

I am assuming that You have a designated user for Your database with certain privileges that are tailored after Your needs. In my case the database user's name is django.

So I ran the following commands:

$ sudo su postgres
$ postgres@ubuntu: psql
psql (9.4.8)
Type "help" for help.

postgres=# GRANT USAGE ON SCHEMA topology to django;
postgres=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA topology TO django;
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA topology TO django;

Try running pg_dump now with the command below and it should execute without errors. It should create a compressed dump of Your database.

faisal@ubuntu:~$ pg_dump -Fc -U yourusername -h localhost yourdatabase --no-owner --no-acl --verbose -f pgdump-20160720-0230.bak