ozwiz
By:
ozwiz

Unable to take backup of Postgres server from my local machine

May 31, 2017 579 views
PostgreSQL Backups Ubuntu 16.04

I have Django instance with Postgres SQL server running on Ubuntu 16.04. When i tried to take backup of the whole Postgres Server using pgadmin 3 (from Windows 7), I am getting following error:

C:\Program Files (x86)\pgAdmin III\1.22\pg_dumpall.exe --host 45.55.241.167 --port 5432 --username "overiq_user" --no-password  --verbose --file "C:\Users\X\Documents\overiq_db.sql"
pg_dumpall: executing SET search_path = pg_catalog
pg_dumpall: executing SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, rolbypassrls, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid ORDER BY 2
pg_dumpall: query failed: ERROR:  permission denied for relation pg_authid
pg_dumpall: query was: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, rolbypassrls, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid ORDER BY 2

Process returned exit code 1.
2 Answers
hansen May 31, 2017
Accepted Answer

Hi @ozwiz

It says ERROR: permission denied for relation pg_authid, so I guess the user you're using overiq_user doesn't have rights to the pg_authid database.

So, how do i assign required permission to this user ?

  • In this case you are trying to dump objects owned by default by "postgres" superuser, and you need to be logged in as superuser to do this.

    If you dont want to backup as "postgres" user, you can create a backup user like this:

    (logged as administrative database user)

    CREATE USER backupman SUPERUSER  password '<my_pass>';
    ALTER USER backupman set default_transaction_read_only = on;
    
    

    Or grant as superuser your other database user:

    ALTER USER otheruser WITH SUPERUSER;
    ALTER USER otheruser set default_transaction_read_only = on;
    
Have another answer? Share your knowledge.