AWS, pg dumps and you!

Recently we came across a scenario where we needed the latest dump from one of our postgres databases hosted on AWS.

Simple stuff right?

Actually not really.

With limited to no documentation and any assumption we usually have in doing a pg_dump rendered useless we had to investigate this.

Well, if there is a will then there is a way and I've decided to share it with you!

If you need to do a pg_dump from an AWS RDS database:

Make sure that there is a security group for RDS DB for the endpoint.

Add the following rule if it doesn't exist:
* Type: PostgresSQL
* Protocol: TCP
* Port Range: <port> (This should autocomplete from the port number of the endpoint)
* Source: Anywhere (leave the auto filled field as 0.0.0.0/0)

You can now run the following command in your terminal with the required fields (filled in with your server details).

pg_dump -h <endpoint> -p <endpoint port> -U <username> <db name> > database_copy_name

It will prompt for the AWS server's password as per usual.

Don't forget! Remove the rule added above once the copy has been done!

And thats it! You did it! You now have the latest dump of the database and can psql that fella into your local database for testing with larger datasets and/or happy debugging.