In this article I will use
$MY_RDS_SU to denote the RDS user managing the RDS instance.
pg_dumpall is a quick and easy to use tool to export your PostgreSQL database. Unfortunately it does not work well on Amazon RDS.
pg_dumpall -h $MY_RDS_HOST --port $MY_RDS_PORT -U $MY_RDS_SU Password: -- -- PostgreSQL database cluster dump -- SET default_transaction_read_only = off; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; 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, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid ORDER BY 2
pg_dump will run into permission issues if you try to dump the database from a user other than the owner of the particular database.
pg_dump -h $MY_RDS_HOST --port $MY_RDS_PORT -U $MY_RDS_SU $MY_RDS_DB Password: pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation ___________ pg_dump: [archiver (db)] query was: LOCK TABLE ________ IN ACCESS SHARE MODE
Amazon RDS restricts the capabilities of each RDS user such that no true PostgreSQL superuser exists.
For each database we wish to export we will change the owner of each table to
$MY_RDS_SU to work around this issue.
do $$ declare r record; begin for r in select tablename from pg_tables where schemaname = 'public' loop execute format( 'alter table public.%I owner to $MY_RDS_SU', r.tablename ); end loop; end; $$;
pg_dump can be used by
$MY_RDS_SU. I prefer to include
pg_dump if I am going to be importing the database into another PostgreSQL instance with a user other than
pg_dump --no-owner -h $MY_RDS_HOST --port $MY_RDS_PORT -U $MY_RDS_SU $MY_RDS_DB > $MY_RDS_DB.sql