I was trying to make pg_dump backup of our database. When I got the error shown below:
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
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;
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