When moving from development to production, one of the biggest steps is migrating your database to the cloud. Recently, I migrated a local PostgreSQL database to AWS RDS and connected it with my EC2 server.

This guide will take you through every step, along with real issues you may face and how to fix them.

Step 1: Create Your RDS PostgreSQL Database

1. Go to AWS Console → RDS → Databases → Create database
2. Choose:
   • Engine: PostgreSQL
   • Version: Match your local PostgreSQL version (to avoid compatibility issues)
   • Instance class: db.t3.micro (for testing, upgrade later)
3. Credentials:
   • DB instance identifier: <dbname>
   • Master username: <username>
   • Master password: <password>
4. Connectivity:
   • Public access: Yes (only for testing; in production choose No and connect via EC2 security groups)
   • VPC Security Group: Allow inbound traffic on port 5432 from:
     • Your EC2 instance security group, or
     • Your personal IP (if connecting locally).
5. Launch and wait until status is Available.

Step 2: Export Your Local Database

On your local machine, create a dump of your PostgreSQL database:

pg_dump -U <username> -h localhost <dbname> > <dbname>_backup.sql

This creates <dbname>_backup.sql which contains your database schema and data.

Step 3: Upload Backup File to EC2

Transfer the backup file to your EC2 instance:

scp -i <your-key.pem> <dbname>_backup.sql ubuntu@<your-ec2-public-ip>:/home/ubuntu/

Step 4: Install PostgreSQL Client on EC2

SSH into EC2:

ssh -i <your-key.pem> ubuntu@<your-ec2-public-ip>

Install PostgreSQL client:

# For Ubuntu
sudo apt-get install postgresql-client -y

# For Amazon Linux
sudo yum install postgresql -y

Step 5: Test Connection to RDS

Try connecting to your RDS:

psql -h <your-rds-endpoint> -U <username> -d postgres

If successful, create a new database:

CREATE DATABASE <dbname>;
\q

Step 6: Import Backup into RDS

Run this command to import your local dump:

psql -h <your-rds-endpoint> -U <username> -d <dbname> -f /home/ubuntu/<dbname>_backup.sql

Step 7: Verify the Migration

Connect again:

psql -h <your-rds-endpoint> -U <username> -d <dbname>

Inside psql, check tables:

\dt

If you see your tables → ✅ Migration successful!

Step 8: Update Node.js Configuration

Update your config.json (for Sequelize):

{
  "development": {
    "username": "<username>",
    "password": "<password>",
    "database": "<dbname>",
    "host": "localhost",
    "dialect": "postgres"
  },
  "production": {
    "username": "<username>",
    "password": "<password>",
    "database": "<dbname>",
    "host": "<your-rds-endpoint>",
    "dialect": "postgres",
    "dialectOptions": {
      "ssl": {
        "require": true,
        "rejectUnauthorized": false
      }
    },
    "logging": false
  }
}

🔍 Common Issues & Fixes

Here are the real-world problems I faced during migration and how I solved them:

1. ❌ Connection Timeout

Error:

psql: error: connection to server at "<your-rds-endpoint>" failed: Connection timed out

Cause:
Security group rules didn’t allow access.

Fix:

• Go to AWS Console → RDS → Connectivity & security → VPC security groups
• Add inbound rule:
  • Type: PostgreSQL
  • Port: 5432
  • Source: My IP (for local) or EC2 security group (for server).

2. ❌ Must be owner of schema

Error:

pg_restore: error: must be owner of schema public

Cause: The dump file had permissions tied to the local DB user.

Fix:

• Ensure the RDS database was created by <username> you’re connecting with.
• Or recreate the database: DROP DATABASE <dbname>; CREATE DATABASE <dbname>;

3. ❌ SSL Error in Node.js (Sequelize)

Error:

SequelizeConnectionError: no pg_hba.conf entry

Cause: AWS RDS often enforces SSL.

Fix: Add dialectOptions in Sequelize config:

dialectOptions: {
  ssl: {
    require: true,
    rejectUnauthorized: false
  }
}

4. ❌ Wrong Endpoint or Password

Error:

FATAL: password authentication failed for user "<username>"

Fix:

• Double-check <your-rds-endpoint> (AWS RDS → Connectivity & Security → Endpoint).
• Reset DB password in RDS console if forgotten.

5. ❌ Import Too Slow

If your dump is large, importing via psql may take too long.

Fix:

• Use EC2 in the same region as RDS (faster upload).
• Compress before transfer: pg_dump -U <username> <dbname> | gzip > backup.sql.gz scp backup.sql.gz ... gunzip backup.sql.gz

🎯 Conclusion

Migrating PostgreSQL from local to AWS RDS might look intimidating at first, but once broken into steps, it’s very doable.

✅ Create RDS instance
✅ Export local DB
✅ Upload & import via EC2
✅ Fix security group/SSL issues
✅ Update Node.js configs

With this setup, you not only get better performance but also scalability, backups, and high availability — features that make AWS RDS a must-have for production systems.