1

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
jagtar
  • 23
  • 8
  • You need to run `pg_dumpall` as the superuser, e.g. `pg_dumpall -U postgres ....` –  Jun 26 '19 at 07:21
  • i ran pg_dumpall as superuser but it dumped all in text format in the terminal but i need it in sql format on local machine. Can u give the full command. – jagtar Jun 26 '19 at 07:30
  • pg_dumpall creates a SQL script (text format), you need to redirect that to a file using `> alldb.sql` or use `-f ...` as you did –  Jun 26 '19 at 07:34
  • pg_dumpall -h hostname -p 5432 -U postgres > databaseall5.sql Password: pg_dumpall: could not connect to database "template1": FATAL: password authentication failed for user "postgres" FATAL: password authentication failed for user "postgres" $ pg_dumpall -h hostname -p 5432 -U postgres > databaseall5.sql Password: pg_dumpall: could not connect to database "template1": fe_sendauth: no password supplied – jagtar Jun 26 '19 at 07:35
  • postgres@iitians:/home/network$ pg_dumpall -h hostname -p 5432 -U postgres -c -o -f "databaseall7.sql" Password: pg_dumpall: could not connect to database "template1": FATAL: password authentication failed for user "postgres" FATAL: password authentication failed for user "postgres" – jagtar Jun 26 '19 at 07:44
  • postgres@iitians:/home/network$ pg_dumpall > alldb.sql bash: alldb.sql: Permission denied – jagtar Jun 26 '19 at 08:01
  • postgres@iitians:/home/network$ pg_dumpall -c -o -f "databaseall.sql" pg_dumpall: could not open the output file "databaseall.sql": Permission denied postgres@iitians:/home/network$ pg_dumpall -c -o -f alldb4.sql pg_dumpall: could not open the output file "alldb4.sql": Permission denied postgres@iitians:/home/network$ pg_dumpall -f alldb4.sql pg_dumpall: could not open the output file "alldb4.sql": Permission denied postgres@iitians:/home/network$ pg_dumpall > alldb4.sql bash: alldb4.sql: Permission denied postgres@iitians:/home/network$ – jagtar Jun 26 '19 at 08:06

1 Answers1

0

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
Anthony Sotolongo
  • 1,395
  • 2
  • 9
  • 17
  • where to add server host. i need to download the database to my local machine from remote server. Then destroy that server and rebuilt that server with same database. Hope u got the query – jagtar Jun 26 '19 at 05:43
  • postgres@iitians:/home/network$ pg_dumpall > alldb.sql bash: alldb.sql: Permission denied – jagtar Jun 26 '19 at 10:50
  • the pg_dumpall/psql commands have options to set host/port/user etc, in this case -h is the host -p the port, to know other options write: pg_dumpall --help/ psql --help and you will see all options about this command – Anthony Sotolongo Jun 26 '19 at 13:39