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