Skip to content

Instantly share code, notes, and snippets.

@soumentrivedi
Last active September 27, 2018 13:57
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save soumentrivedi/08b770a8f0c685216859 to your computer and use it in GitHub Desktop.
Save soumentrivedi/08b770a8f0c685216859 to your computer and use it in GitHub Desktop.
Steps to perform pg_dump on Postgresql RDS instance
alter schema schema_name1 owner to rds_superuser;
alter schema schema_name2 owner to rds_superuser;
\dn;
CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$
BEGIN EXECUTE $1; RETURN $1; END; $f$;
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser')
FROM (
SELECT nspname, relname
FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname in ('schema_name1', 'schema_name2') AND
relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;
Update the change_owner.sql script with the relevant "rds_superuser" and "schema_name"
Connect to RDS instance through psql prompt and run the change_owner.sql script
External reference:
http://stackoverflow.com/questions/4363697/copy-a-postgres-database-without-lock-permissions
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.PostGIS
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment