0

I want to export a postgresql database named "kd" with all roles, tablespaces, etc. I run the command pg_dumpall -U sce -h localhost -p 5450 -d kd > /tmp/db.sql I get the error

 pg_dumpall: missing "=" after "kd5" in connection info string

I run the command pg_dumpall -U sce -h localhost -p 5450 > /tmp/db.sql I get the error

pg_dumpall: query was: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid ORDER BY 2

How can fix this problem ?

mourad semi
  • 47
  • 5
  • `pg_dumpall` will dump the entire cluster so you don't need the `-d` . If you only want the `kd` database then `pg_dump -U sce -h localhost -p 5450 -d kd > /tmp/db.sql` and then `pg_dumpall -U sce -h localhost -p 5450 -g > pg_globals.sql` to get the cluster globals e.g. roles and tablespaces. – Adrian Klaver Jun 08 '22 at 21:25
  • the same problem – mourad semi Jun 08 '22 at 21:26
  • Is `-U sce` a superuser? The user dumping the database(s) has to have sufficient privileges to access all the objects in the database(s). – Adrian Klaver Jun 08 '22 at 21:31
  • I get the message "pg_dumpall: query was: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplicat ion, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = cur rent_user AS is_current_user FROM pg_authid ORDER BY 2" – mourad semi Jun 08 '22 at 21:34
  • sce is the user that have access to database – mourad semi Jun 08 '22 at 21:36
  • I get the error pg_dumpall: query failed: ERROR: permission denied for relationship pg_authid – mourad semi Jun 08 '22 at 21:37
  • And the error messages are telling you that the user does not have the necessary privileges to dump the database. Having access to a database is not the same as having the privileges to dump. Select a user that does, in other words a superuser. – Adrian Klaver Jun 08 '22 at 21:45
  • I want to use only pg_dumpall to export all databases with roles – mourad semi Jun 08 '22 at 21:51
  • 1) That is not what your code shows and your text says: "I want to export a postgresql database named "kd". 2) The issue you are having is with permissions, you need to deal with that first. – Adrian Klaver Jun 08 '22 at 21:55
  • I fixed the problem with permission – mourad semi Jun 08 '22 at 22:07

0 Answers0