postgresql - How to pg_dump an RDS Postgres database?

Question

How can I connect to my RDS instance using pg_dump?

This is the instance's endpoint:

<long public dns thing>:5432

So I'm running this command:

pg_dump -h <long public dns thing> -p 5432 -f dump.sql

And getting this:

pg_dump: [archiver (db)] connection to database "brendan" failed:
could not connect to server: Connection refused
Is the server running on host "<long public dns thing>"
(<IP address>) and accepting TCP/IP connections on port 5432?

This is Amazon's troubleshooting advice:

Cannot Connect to Amazon RDS PostgreSQL DB Instance

The most common problem when attempting to connect to a PostgreSQL DB instance is that the security group assigned to the DB instance has incorrect access rules. By default, DB instances do not allow access; access is granted through a security group. To grant access, you must create your own security group with specific ingress and egress rules for your situation. For more information about creating a security group for your DB instance, see Create a Security Group.

The most common error is could not connect to server: Connection timed out. If you receive this error, check that the host name is the DB instance endpoint and that the port number is correct. Check that the security group assigned to the DB instance has the necessary rules to allow access through your local firewall.

Is there a way to specify my security group from pg_dump? If so, do I have to get a local copy of that the way that I need an ssh key when ssh'ing?

Is it a mistake to even try to use pg_dump remotely? Should I be trying to just ssh onto the instance instead, or doing something else entirely?

Answer 1

Step 1: Create a security group on AWS that has your computer's IP address white listed.

Step 2: Add that security group to the database instance you want to connect to.

Step 3: Run pg_dump. Make sure to specify your user name (thanks @LHWizard) with the -U command. In this case mine wasn't 'postgres', so I guess generally you'll have to look in aws to find it. Also make sure to specify your database's name: in some command line tools there's a -d switch for that, but if you check pg_dump's usage:

Usage:
  pg_dump [OPTION]... [DBNAME]

you can see that it's a formal argument. So the whole command (in my case) was:

pg_dump -h <public dns> -U <my username> -f dump.sql <name of my database>

Notice that specifying the port number wasn't necessary -- I think because port 5432 is THE port for postgres.