Migrating AWS RDS Postgres Instance with Python
Step-by-step instructions to migrate the RDS Postgres database with python
I was working on a project where we had a bunch of over-provisioned RDS Postgres database instances running across multiple AWS accounts footing bills to the customer. We were looking to cut down on cloud spending.
I wrote a script to pull the report of allocated and used storage by all RDS instances. I found TBs of unused disk space across all environments.
Eliminating the TBs of disk wastage across 100+ databases across multiple environments was the obvious choice.
You might wonder why I am making a big deal about it. I can easily update the disk space for RDS instances.
Well, no. AWS RDS does not allow you to reduce the allocated storage, although you can increase it. You also cannot restore it from snapshot backup as it will not allow you to reduce the allocated disk space.
To achieve this, one has to create a duplicated RDS instance with a reduced disk size and restore the database from the backup.
I build a detailed automation using Python to achieve this task. After writing a detailed Python script, I deployed it as a docker container in ECS and triggered it with the help of a Lambda function.
For this blog post, I will build a simpler Python CLI that you can run from a VM.
Automating the AWS RDS Postgres database migration with boto3
I will use AWS Python SDK boto3 to implement the automation. As part of the database migration, I will follow the below steps.
- Use boto3 to pull the RDS instance detail and its storage utilization.
- Use the Postgres utility pg_dump to take the backup of the database.
- Duplicate the RDS instance with a new storage size, and name it with
-newsuffix. - Wait for the new database to be available.
- Restore the new database from the database dump taken in the previous step with the pg_restore utility.
- Swap the original database and the duplicated one by renaming them.
- Stop the old DB instance and manually delete it after a few days.
I created the RDS Postgres database instance in the last blog post, Deploying Containers with AWS ECS. I will use the same RDS instant for this blog.
I used the parameter store parameter to store the DB link for all databases. I use the /database/<DB instance ID> format to store the DB link parameter. DB links can be used to access the database. I will create the DB link using the database details below.
postgresql://{db_user}:{db_password}@{db_host_endpoint}:{db_port}/{database_name}
Let’s start building the automation, one function at a time. I will explain each step
Note: I have kept the complete script and readme file with instructions to run in my public GitHub Repo.
1. Get RDS details of the original DB instance
2. Get the storage utilization for the DB from CloudWatch monitoring
3. Compare the allocated and free disk space
Since we had stored the DB user, password, etc on a parameter store, we will pull that data.
4. DB details from the parameter store
5. Duplicate the RDS instance with the new storage value
This function will duplicate the RDS instance with the new value for allocated disk space. The rest of the values will be the same as the original RDS instance.
Note: If you are using gp2 disks and want to migrate to gp3, you can update that value in the “StorageType” key.
6. Check if RDS is ready
7. Take the backup of the Postgres database with the pg_dump utility.
8. Restore the new RDS instance from the backup we took in the last step.
In this example, I used the backup from local storage to restore. You can add additional logic to upload the backup to an S3 bucket after taking a backup and downloading the backup from S3 before restoring if you have a special requirement.
9. Rename the RDS instance
This function will rename the database. I used the sleep from time module to wait instead of boto3 waiter as it is broken.
10. Swap the RDS instance.
11. Stop the RDS instance
This function will stop the original RDS instance and we will use the new one.
We call all the above functions to migrate the Postgres RDS instance.
I have pasted the above code snippets to explain the logic I used. I have kept the complete script and instructions in my public GitHub repo.
Running the script from the EC2 machine
Since I used an RDS instance with a private endpoint( i.e. no public IP to access from outside the network).
I launched an EC2 Ubuntu machine on the same VPC where my RDS instance exists. As my script will be using pg_dump and pg_restore commands for backup/restore, I will also need to install Postgres and other dependencies.
Installing dependencies on the Ubuntu machine.
sudo apt-get update
sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update
sudo apt-get install postgresql-client-16
pg_dump --version
psql --version
# install pip
apt install python3-pip
# install aws cli
sudo apt install awscli -y- The security group attached to the Ubuntu machine allows inbound/outbound connection to RDS on the DB port(5432).
- The security group attached to RDS allows inbound connection on DB port(5432).
- The IAM role attached to EC2 allows relevant permissions. You can attach the admin role if you are testing it.
Instructions to run the script
Copy the script to your machine.
git clone https://github.com/akhileshmishrabiz/rds-migration.git
cd cd rds-migration/migrtate-with-pg-dump-restore
ls
Install python dependencies
pip install -r requirements.txtRun the script
# Evaluate the status of 'my-rds-instance-name'
python main.py evaluate my-rds-instance-name
# Migrate 'my-rds-instance-name' with a new storage size(in GB)
python main.py migrate my-rds-instance-name < Allocated Storage> Final results
Calculating the storage usage of the RDS instance.
You can get the complete script from my Github Repo.
python3 main.py evaluate wordpress
As you can see most of the allocated storage is not used for this instance. I will reduce the RDS instance to half its size, 10 GB.
python3 main.py migrate wordpress 10
Here is the RDS console output
Pitfalls
While testing my database migration automation I found that Pg_dump and Pg_restore might take significantly longer for large database migrations. My lead suggested I use, a great tool for live migration for Postgres databases. It was faster as it did not do backup/restore, instead it directly synced both databases.
What next?
In the next blog post, I will show how to use pgsync for a live database migration. I will dockerize the script, deploy that in AWS ECS, and trigger the migration from a Lambda function.
Read this blog post to know more about pgsync.
Thanks for reading, see you on the next one.
If you found this blog post useful then clap, comment, and share.
Don’t forget to follow, and subscribe so you won’t miss any of that content.
Connect with me on LinkedIn: https://www.linkedin.com/in/akhilesh-mishra-0ab886124/
