Migrating AWS RDS to Self‑Hosted PostgreSQL: Full Guide

Moving a production database from Amazon RDS to a self-hosted PostgreSQL instance gives you full control over configuration, extensions, and cost. This guide walks you through each stage, offering commands for both Debian/Ubuntu (apt) and AlmaLinux/Rocky/RHEL (dnf) environments.

1. Assess the Current RDS Setup

Before touching any servers, gather details about your RDS instance. Knowing the exact version, extensions, parameter groups, and storage size lets you replicate the environment on the new host.

• Instance class – CPU, memory, and I/O capacity.
• PostgreSQL version – e.g., 13.9, 14.5.
• Enabled extensions – postgis, pg_stat_statements, etc.
• Parameter group settings – shared_buffers, work_mem, max_connections.
• Security groups – inbound rules that allow your application servers to reach the RDS endpoint.

Export these details with the AWS CLI (or console). The following command prints the key properties you’ll need to match later:

aws rds describe-db-instances \
    --db-instance-identifier my-prod-db \
    --query 'DBInstances[0].{Engine:Engine,Version:EngineVersion,Endpoint:Endpoint.Address,Port:Endpoint.Port}'

Save the output in a checklist; you’ll use it to configure the self-hosted server.

2. Prepare the Destination Server

The target machine must run a compatible PostgreSQL version and have enough resources for the production load. Below are installation steps for the two most common Linux families.

Debian / Ubuntu (apt)

# Update package lists
sudo apt update

# Install PostgreSQL (replace 14 with the major version you need)
sudo apt install -y postgresql-14

# Verify installation
psql --version

AlmaLinux / Rocky / RHEL (dnf)

# Enable the PostgreSQL module (example for version 14)
sudo dnf module enable -y postgresql:14

# Install PostgreSQL server and client
sudo dnf install -y postgresql-server postgresql

# Initialise the database cluster
sudo postgresql-setup --initdb

# Start and enable the service
sudo systemctl enable --now postgresql

# Verify installation
psql --version

After installation, create a dedicated system user (if one does not already exist) and a database superuser that matches the RDS owner.

sudo -u postgres createuser --interactive
# Answer the prompts: give the same username as on RDS, enable superuser if required

3. Export Data from RDS

For a production migration, pg_dump (single database) or pg_dumpall (full cluster) with the --format=custom flag produces a compressed backup that can be restored with pg_restore. Run the command from an EC2 instance or a local workstation that can reach the RDS endpoint.

# Example for a single database named "appdb"
export PGPASSWORD='your_rds_password'
pg_dump -h my-prod-db.abcdefg.us-east-1.rds.amazonaws.com \
        -p 5432 \
        -U rds_admin \
        -d appdb \
        -Fc \
        -f /tmp/appdb.dump

Key options:

• -Fc – creates a custom-format archive, enabling parallel restore.
• -h, -p, -U – specify the RDS host, port, and user.
• PGPASSWORD – supplies the password for non-interactive execution.

Transfer the dump file to the destination server with scp or rsync:

scp /tmp/appdb.dump user@selfhosted.example.com:/var/lib/pgsql/

4. Configure PostgreSQL for Production

Match the RDS configuration as closely as possible. Edit postgresql.conf and pg_hba.conf, which reside in the data directory (/etc/postgresql/14/main/ on Debian/Ubuntu or /var/lib/pgsql/data/ on AlmaLinux).

Common settings to adjust

# postgresql.conf (excerpt)
shared_buffers = 4GB               # 25% of RAM, adjust to your server size
work_mem = 64MB
maintenance_work_mem = 1GB
max_connections = 200
effective_cache_size = 12GB

# Enable required extensions
shared_preload_libraries = 'pg_stat_statements,postgis'

In pg_hba.conf, allow connections from your application servers (replace 10.0.0.0/16 with your CIDR block):

host    all     all     10.0.0.0/16    md5

After editing, reload the service:

# Debian / Ubuntu
sudo systemctl reload postgresql

# AlmaLinux / Rocky / RHEL
sudo systemctl reload postgresql

5. Restore the Dump to the New Instance

Use pg_restore with parallel workers to speed up the process. Adjust the number of workers (-j) based on CPU cores.

# Create the target database (if you used pg_dumpall, this step is not needed)
sudo -u postgres createdb appdb

# Restore with 4 parallel jobs
sudo -u postgres pg_restore -d appdb -j 4 /var/lib/pgsql/appdb.dump

Verify that all objects were restored:

sudo -u postgres psql -d appdb -c "\dt"
sudo -u postgres psql -d appdb -c "SELECT extname FROM pg_extension;"

If any extensions are missing, install them via the package manager (e.g., postgresql-14-postgis on Debian) and then run CREATE EXTENSION inside the database.

6. Test, Cut Over, and Clean Up

Functional testing

• Run application integration tests against the new endpoint.
• Check replication of critical data (e.g., recent orders) by comparing row counts.
• Validate performance metrics (query latency, CPU, I/O).

Read-only switchover

When you are confident, put the RDS instance into read-only mode to prevent further writes. The exact command depends on your RDS version; the key idea is to stop writes.

Final data sync

Perform a second pg_dump of any changes that occurred after the first dump, then restore it on the self-hosted server. Because the window is now short, this step usually finishes quickly.

Update application connection strings

Replace the RDS endpoint with the IP or DNS name of your new PostgreSQL server. Restart the application services to pick up the change.

Decommission RDS (optional)

After a monitoring period (e.g., 48 hours) with no issues, you can delete the RDS instance to avoid unnecessary charges.

7. Ongoing Maintenance on the Self-Hosted Server

Running PostgreSQL yourself adds responsibilities:

• Backups – schedule pg_basebackup or pg_dump to a remote storage location.
• Updates – apply security patches via apt update && apt upgrade or dnf update.
• Monitoring – use tools like pg_stat_activity, pgBadger, or third-party agents.
• Disaster recovery – test point-in-time recovery (PITR) regularly.

Document the configuration and keep the version-specific commands in a repository for future reference.

Conclusion

Migrating a production PostgreSQL database from AWS RDS to a self-hosted server requires thorough inventory, reliable export, precise configuration, and careful cut-over. By following the steps above—assessing the source, installing matching PostgreSQL packages on Debian/Ubuntu or AlmaLinux/RHEL, exporting with pg_dump, restoring with pg_restore, and validating before final switchover—you can achieve a smooth transition while retaining full control over your database environment. Remember to implement regular backups and monitoring to keep the new setup healthy over the long term.