Search code examples
postgresqlpg-dumppg-dumpall

How to pg_dumpall from remote server to local machine and local machine to remote server

I need to do the new installation for which i need to copy my database from current installation. i need the pg_dumpall command for remote server to local machine.

I have already tried this but it works only for dump and doesnt work for dumpall

pg_dump -h hostname -Fc -o -U username database > databaseall.sql

and

pg_dumpall -h hostname -p 5432 -U username > databaseall.sql

gives error

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

and

pg_dumpall -h hostname -p 5432 -U username -c -o -f "databaseall.sql"

gives error

pg_dumpall: query failed: ERROR:  permission denied for relation pg_authid
pg_dumpall: query was: SELECT rolname FROM pg_authid ORDER BY 1
Solution

  • Use the pg_dumpall/psql commands to dump and restore

    pg_dumpall > alldb.sql

    to load in the other intance

    psql -f alldb.sql postgres