9 Dec 2016

Patched pg_dumpall works with AWS RDS Postgres

UPDATE: This patch is now a part of Native Postgres!! Read more here.

While trying to work with AWS RDS Postgres, it was interesting to see pg_dumpall failing to dump databases at all!

Delving further, for obvious reasons a managed service like this hides some global-information, which pg_dump(all) needs ... and no, this post is NOT about circumventing that.

This branch, gives a 'near' workaround for those who are okay with being able to extract all databases + (almost*) all Global information (Users etc.) in a single command. The *only* Global that can't be dumped are the User-Passwords, which for some people is okay, considering that they still get a scriptable way of taking a dump of all databases at one go, along with all User information. 

Note: For those keen to know, the script just resets all users to be password-less, but that's better than having to do 'CREATE USER ... ' commands for your users by hand!

Down the line, I'll try to push this to the core, but for now, this works!

UPDATE: This patch is now a part of Native Postgres!! Read more here.

Go Play :) !!

9 comments:

bithead said...
This comment has been removed by the author.
bithead said...
This comment has been removed by the author.
bithead said...

Thanks for this piece of code. It needs to be updated a little for more modern versions of postgresql, but i was able to quickly adapt it.

Robins Tharakan said...

Thanks for that.. Have submitted a request to pgsql-hackers to see if they accommodate this in the mainline.. If not, I'll try to keep this fork up to date as much as possible.

Unknown said...

Good to have all RDS postgresql users

Robins Tharakan said...

This is now (in Postgres v10+) a part of Postgres! So no Custom patching required any more.

meghanasmiley03 said...
This comment has been removed by a blog administrator.
venkat said...
This comment has been removed by a blog administrator.
venkat 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...