1

How can I use pg_dumpall with Heroku? The default "database backup" feature from Heroku is pg_dump with the click of a button, which doesn't include roles, so I want to do pg_dumpall ... I'm trying pg_dumpall -h myherokuurl.compute-1.amazonaws.com -l mypassword -U myUser > dump.sql

I'm getting this error:

pg_dumpall: error: query failed: ERROR:  permission denied for table pg_authid
pg_dumpall: error: 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 WHERE rolname !~ '^pg_' ORDER BY 2

My first thought was to create a new user with the correct privileges. So, I logged using heroku pg:psql DATABASE -a my-app-name then tried create user myUser with password 'mypassword' but got the error ERROR: permission denied to create role

I'm honestly not sure what's going on I'm kind of just guessing. Any troubleshooting ideas would be appreciated! (in the meantime I'm just trying to learn more about Postgres)

George Pickett
  • 71
  • 1
  • 1
  • 11
  • If you are using the hobby/shared tier, you cannot use pg_dumpall, as that would obtain other people's data. If you are paying for dedicated server, you are also paying for support, aren't you? – jjanes Nov 18 '20 at 20:04
  • I am on "hobby basic" on Heroku Postgres here: https://devcenter.heroku.com/articles/heroku-postgres-plans it doesn't say anything about the limitation you cited. Any links on that? – George Pickett Nov 18 '20 at 20:17

1 Answers1

6

If your problem is just about the pg_authid catalog, you should be able to use recent versions of pg_dumpall with the --no-role-passwords option.

This commonly works in hosted environments where pg_authid is inaccesible, e.g. on AWS. The only downside is that the passwords of Postgres users will be missing from the dump.

However, you appear to have a more limited, perhaps shared environment, where you can't even create new Postgres users. I am not certain if there is any chance to get pg_dumpall working there.

F30
  • 1,036
  • 1
  • 10
  • 21
  • `--no-role-passwords` is the correct solution to this issue. Thank you!! Note: If `pg_dumpall` gives you this error `pg_dumpall: error: could not connect to database "template1": FATAL: permission denied for database "template1"`, then that means you need to add a `--database` flag (even if you use `--dbname`). – tommyh Dec 14 '21 at 15:54