postgres database with multiple database object owners and pg_dump

Question

with a postgres 11.2 database with multiple database object owners and trying to use the following command as the database owner (not the postgres superuser):

pg_dump -h hostname -p 5432 -U dbownername dbname --file= dbname_20200406.pg_dump --no-owner --no-privileges --format=p

it fails due to object ownership not being all the same. (I have tested that this is the case by changing all ownership to dbownername and at that point pg_dump succeeds)

is the only way to get this to work is to run it as the postgres superuser? or are there postgres roles that I can give dbownername to allow them to back up... with out in effect making them another postgres superuser?

Answer 1

If you grant SELECT privileges to all tables to the database owner you should be able to export at least the tables. If not, please give full error message(s).

• boar: ah, thanks. at least one of the tables didn't have proper permissions on it.