Will & Skill Developers

Will & Skill Developers


Thoughts, snippets and ideas from the team at Will & Skill AB, Stockholm.

Faisal Mahmud
Author

“The mind is not a vessel to be filled, but a fire to be kindled.” ― Plutarch

Share


When PostGIS and pg_dump does not play well together

Faisal MahmudFaisal Mahmud

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 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  
Faisal Mahmud
Author

Faisal Mahmud

“The mind is not a vessel to be filled, but a fire to be kindled.” ― Plutarch

Comments