Guide to Migrating AWS Postgres to a Self-Hosted Database

Unleash the Power of pg_dump and pg_restore: PostgreSQL's Dynamic Duo for Data Migration!

The pg_dump and pg_restore utility is suitable for the following use cases if:

1. Your database size is less than 100 GB.
2. You plan to migrate database metadata as well as table data.
3. You have a relatively large number of tables to migrate.

Note: If you want to avoid downtime, refrain from using these utilities.

Export Data (Step-1)

You can create dump files for your source database using the following command.

pg_dump -h <hostname> -p 5432 -U <username> -Fc -b -v -f <dumpfilelocation.sql> -d  <database_name>

-h is the source server name where you would like to migrate your database.
-U is the name of the user present on the source server
-Fc: Sets the output as a custom-format archive suitable for input into pg_restore.
-b: Include large objects in the dump.
-v: Specifies verbose mode
-f: Dump file path

Import Dump Files (Step-2)

You can use the following command to import the dump file into your Amazon RDS instance.

pg_restore -v -h <hostname> -U <username> -d <database_name> -j 2 <dumpfilelocation.sql>

-h is the name of the target server where you would like to migrate your database.
-U is the name of the user present on the target server.
-d is the name of the database name that was created in step 2.
<dumpfilelocation.sql> is the dump file that was created to generate the script of the database using pg_dump

Example:

Here are the existing/old AWS RDS credentials as an example:

• hostname: example.ap-south-1.rds.amazonaws.com
• username: oldusername
• dumpfilelocation.sql: aws_dump_9th_july_2023.sql
• database_name: old_dbname

pg_dump -h example.ap-south-1.rds.amazonaws.com -p 5432 -U oldusername -Fc -b -v -f aws_dump_9th_july_2023.sql -d old_dbname

This command prompts for the database password and connects to the specified RDS instance using the provided hostname and username. It creates a dump file named aws_dump_9th_july_2023.sql, including both the database schema (-b) and data (-Fc) in a custom format. The verbose (-v) option displays progress information.

Let's say you have created a new database in neon.tech Your credentials would typically appear as follows:

• hostname: example.ap-southeast-1.aws.neon.tech
• username: profile_name
• database_name: new_dbname

To restore the database using these credentials, you can utilize the following command:

pg_restore -v -h example.ap-southeast-1.aws.neon.tech -U profile_name -d new_dbname -j 2 aws_dump_9th_july_2023.sql

After a successful migration, you can easily verify the data on the new database by running normal SQL queries using a PostgreSQL client like psql. You can examine the tables, perform data validations, and ensure the accuracy of the migrated data.

For Django projects, you can use the command python manage.py showmigrations to check the migration status. This command displays the list of applied and pending migrations, allowing you to confirm if the database migration process was executed successfully.

For any doubts or assistance, you can reach out to me at ratrey.in

Reference: https://docs.aws.amazon.com/dms/latest/sbs/chap-manageddatabases.postgresql-rds-postgresql-full-load-pd_dump.html