connecting to your AWS RDS PostgreSQL instance to exporting and importing data into your local PostgreSQL

aws-rds-to-local-db-dump.md

AWS RDS PostgreSQL: Connect, Export & Import Data Locally

This tutorial will guide you step-by-step from connecting to your AWS RDS PostgreSQL instance to exporting and importing data into your local PostgreSQL database.

---

1. Gather Required Information

Before you begin, collect these details:

• RDS Endpoint: e.g., mydb.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com
• Database Name: Name of your PostgreSQL database on RDS
• Database Username: Username with access privileges
• Database Password: Password for the above user
• RDS Port: Default is 5432

---

2. Configure RDS Security Group

Ensure your local IP address is allowed to connect:

1. Go to AWS Console → RDS → Databases.
2. Select your database instance.
3. Navigate to Connectivity & security.
4. Find VPC security groups and click on the group link.
5. In the EC2 Security Groups page, click Inbound rules.
6. Add rule:
   • Type: PostgreSQL
   • Port: 5432
   • Source: Your local IP (e.g., 203.0.113.42/32)
     • Find your IP here: https://www.whatismyip.com/

Tip: For security, avoid using 0.0.0.0/0 as source unless for temporary access.

---

3. Install PostgreSQL Client Tools

Mac:

brew install postgresql

Ubuntu/Debian:

sudo apt-get update
sudo apt-get install postgresql-client

Windows:

• Download and install from PostgreSQL official site.

---

4. Connect Using psql Command

In your terminal, run:

psql -h <RDS-endpoint> -U <username> -d <database> -p 5432

Replace placeholders with your actual values.

Example:

psql -h mydb.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com -U admin -d production -p 5432

When prompted, enter your database password.

---

5. Export Data from AWS RDS PostgreSQL (pg_dump)

Use the command below to export your RDS database:

pg_dump -h <RDS-endpoint> -U <username> -d <database> -Fc -f backup.dump

• -Fc creates a custom-format dump file (backup.dump).

Example:

pg_dump -h mydb.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com -U admin -d production -Fc -f backup.dump

---

6. Create a Local Database

If you haven't already, create a new database locally:

createdb -U <local-username> <local-database>

• <local-username> is often postgres.

Example:

createdb -U postgres staging

---

7. Import Data to Local PostgreSQL (pg_restore)

Restore the exported dump into your local database:

pg_restore -U <local-username> -d <local-database> -Fc backup.dump

Example:

pg_restore -U postgres -d staging -Fc backup.dump

---

8. Common Troubleshooting

• Timeout or connection refused:

  • Verify your IP is whitelisted in the security group.
  • Ensure the RDS instance is in the "available" state.

• Authentication failed:

  • Double-check username and password.

• psql or pg_dump not found:

  • Ensure PostgreSQL client tools are installed.

• Version mismatch:

  • Local PostgreSQL version should be equal or newer than AWS RDS version.

• Extensions:

  • Install any required extensions locally before restoring.

---

Summary Workflow

1. Export from RDS:

   pg_dump -h <RDS-endpoint> -U <username> -d <database> -Fc -f backup.dump

2. Create local DB:

   createdb -U <local-username> <local-database>

3. Import to local:

   pg_restore -U <local-username> -d <local-database> -Fc backup.dump

---

You’re now ready to migrate your AWS RDS PostgreSQL data to your local machine!