Jai Kumar Rajput Jai Kumar Rajput - 2 months ago 26
Ruby Question

pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation abouts

I'm trying to dump my pg db but got these errors please suggest

pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation abouts
pg_dump: [archiver (db)] query was: LOCK TABLE public.abouts IN ACCESS SHARE MODE

Answer

This can be a common error, when using a ROLE (user) that could not open the objects to dump them.

Like said before, you can grant to the specific schema that you want to dump, or even use a ROLE with SUPERUSER* attribute.

Note that when you are dealing with some cloud database providers, like AWS/RDS you will not receive an user with SUPERUSER attribute, so you will need to manage to make sure that the one used to dump will have all access needed.

https://www.postgresql.org/docs/current/static/sql-grant.html will show how give grant to many objects on your database, but also remember that when restoring you will need to create the database first. Only if you are using pg_dumpall that is not necessary, but you also need to dump the ROLES.