8 Mar 2017

Using pg_dumpall with AWS RDS Postgres

My patch (allowing a non-super-user to dump Globals) just got committed to Postgres 10.0.

Besides the use mentioned above, this patch allows one to dump Globals from their AWS RDS Postgres Database and have a complete backup as a part of their Disaster Recovery (DR) solution. This earlier required an inelegant hack (detailed here in another post of mine) that wasn't very convenient for a regular user.

For those interested, RDS Postgres (by design) doesn't allow you to read pg_authid, which was earlier necessary for pg_dumpall to work. With this patch checked-in, pg_dumpall now uses a workaround (pg_roles) to read the same data (except passwords) and generate the same SQL Script.

With that mentioned, let's get our hands dirty and see a working solution:

# Take RDS Postgres Dump
# We store the pgdb database in pgdb.sql and globals in pgdb_globals.sql
pg_dumpall --globals-only --no-role-passwords \
  -U rdssuperuser -h rdsinstance \
  2>stderr.txt > pgdb_globals.sql
pg_dump -U rdssuperuser -h rdsinstance pgdb 2>stderr.txt > pgdb.sql

# Restore on Local Machine
psql -U postgres -c "CREATE DATABASE pgdb;"

# rdsadmin database (even if empty) is required for the next script
psql -U postgres -c "CREATE DATABASE rdsadmin;" 

psql -U postgres pgdb < pgdb_globals.sql
psql -U postgres pgdb < pgdb.sql

Note: The --no-role-passwords flag is still necessary since AWS RDS doesn't allow you to read Passwords. Therefore, the above generates a script that resets all users to be password-less, but that's still better than having to restore Globals (like 'CREATE USER ... ') manually by hand!

Big thanks to Simon for ironing out a few issues, Stephen, David & Robert for their (critical) reviews.

13 comments:

Unknown said...
This comment has been removed by a blog administrator.
Tim said...

Thank you so much for this. It took me until midnight to find it on Google but it worked perfectly :) Had a 9.6 database in RDS that I needed to move to 10 in order to get improved Row Level Security performance.

One thing that was worth doing was adding -v ON_ERROR_STOP=1 to the psql import command.

Robins Tharakan said...

Thanks Tim... and I agree that ON_ERROR_STOP helps the client to stop early (on error) which makes triaging issues so much easier.

Unknown said...

Hey, maybe I'm not following, how can I get that patched version? cuz we're having the exact same issue.

Robins Tharakan said...

Hi Naim,

If you're okay with the fact that Postgres v10 isn't production ready yet, you could download the Beta binaries and use that:

https://www.enterprisedb.com/products-services-training/pgdevdownload

Further, since Postgres is open source you could always compile Postgres v10 branch yourself from source.

Unknown said...
This comment has been removed by the author.
Unknown said...
This comment has been removed by a blog administrator.
Qutub said...
This comment has been removed by the author.
Qutub said...

While restoring i am facing error.

ERROR: must be superuser to alter superusers

ERROR: must be superuser to set grantor

Users are created but they are not getting an appropriate permissions. I also removed all the system created roles like rds_replication, rds_superuser, etc. from the pg_dumpall script.

I'm using Aurora postgres 9.6

Tzach Shabtay said...

This example might work fine for dumping RDS and restoring locally, but it doesn't work for dumping RDS and restoring to RDS.
You just hit the same problem with the fact you're not really a superuser that you did when dumping.
Got the same errors as reported by Qutub, in addition to a bunch of more errors like "Invalid Command \N".

E Weidl said...

The spelling of the option may have changed since you posted this article.

I got an error when I tried the option:

--no-role-password

I changed the spelling to:

--no-role-passwords

and the command worked.

Farhan.Jee said...
This comment has been removed by a blog administrator.
Ashok said...
This comment has been removed by a blog administrator.

Find Database DNS / Endpoint via SQL

How to get Database identifier using SQL Often there is a need for client programs to find "where am I logged into?". This blog po...