How to Migrate From AWS RDS for PostgreSQL to Timescale

Database migration refers to the process of moving data from one database to another. It's a vital and complex operation requiring careful planning and execution. Unsurprisingly, developers tend to avoid it, as migrating a database involves changing the database architecture or transferring data from one platform or format to another. 

At Timescale, we see many devs looking for an alternative to AWS RDS for PostgreSQL. Drawn by performance and a transparent pricing model (no one has the time to understand RDS pricing), they often land at Timescale. When trying to help them, we couldn’t help but notice how lengthy and complex it is to migrate from RDS.

But what if you had a playbook to assist you along the way? If you’re embarking on a journey through uncharted territory, having a map makes all the difference. That’s what this blog post is: a step-by-step overview of the migration process from RDS to Timescale using live migrations. This migration workflow ensures a smooth transition (even for heavy workloads) while helping you avoid common pitfalls and harness our features. Think of it as your GPS for database migration.

Steps for a Successful Database Migration

Migrating a database is a complex process due to all the moving parts you’ll need to handle to ensure a seamless and safe transition of your data. A successful database migration typically involves several steps:

• Assessment: understand your current database's size, complexity, and dependencies.
• Planning: decide on a migration strategy, schedule downtime if necessary, and prepare a backup plan in case something goes wrong.
• Enable hypertables: to leverage Timescale’s performance optimization, compression, continuous aggregates, and data tiering, you have to enable hypertables on your time-series tables. 
• Data migration: move the old data and incoming data during migration from the old database to the new one with minimal downtime.
• Testing: rigorously test the new database with your applications to ensure everything works as expected.
• Cutover: finally, switch over from the old database to the new one, which usually involves a period of downtime. (If you follow our playbook, we ensure minimal downtime.)

Our playbook covers all the bases for a successful migration and streamlines it, allowing you to skip some of these steps. For example, we devised a way for you to enable one of Timescale’s most popular features, hypertables, in your target database while migrating your data. This means that all the data coming into your target database will be automatically partitioned when it comes into Timescale, saving you precious time.

This helpful aid is already part of our live migrations solution, a database migration workflow for Timescale based on pg_dump/pg_restore (for schema) and PostgreSQL logical decoding (for live data). But now, we have accelerated the process even more, shortening the number of steps and making it even more straightforward. Let’s jump into it.

Steps for Migrating From AWS RDS to Timescale

For a detailed step-by-step guide to migrate your database(s) from Amazon RDS for PostgreSQL to Timescale, we recommend you read our documentation. However, here is a high-level overview of the process. You’ll have to follow these steps:

1. Create a Timescale instance

Sign up or log in to Timescale Cloud and click on "Create service." Choose a Time-series service with your desired CPU and Memory plan. We recommend a minimum of 4 CPUs and 8 GB memory for migration. Click on "Download the cheatsheet" post-service creation to obtain an SQL file that contains the login details for your new service. Alternatively, you can copy the details directly from this page. After copying your password, click "I stored my password, go to service overview" at the bottom of the page. Once your service is ready to use, it will be labeled as "Running" in the Service Overview, displayed in green.

2. Collect information from your AWS RDS instance

The AWS RDS management console is extensive, containing numerous details. Our customers often express that locating requirement details and taking actions in AWS is time-consuming, requiring manual navigation of various screens and documentation to find the relevant information. Additionally, AWS has its own intricacies, such as parameter groups, which require engineers to invest effort in understanding the concepts and gathering the required information.

To prepare an AWS RDS instance for migration, you need the following information about the instance as the first step:

1. Endpoint
2. Port
3. Master username
4. Master password
5. VPC
6. DB instance parameter group

3. Update the following parameters of the AWS RDS for PostgreSQL instance

The live migration offered by Timescale is powered by logical decoding, which consumes Write-Ahead Logs (WAL) to synchronize real-time changes happening on the source database during the migration. Therefore, you need to set the wal_level to logical. 

Additionally, you must set old_snapshot_threshold to -1 to preserve the snapshots until the migration is complete. Not setting this parameter to -1 can cause errors in the logical decoding process, as the database performs periodic maintenance checks. You need to update the following configurations:

1. wal_level: set to logical
2. old_snapshot_threshold: set to -1

4. Prepare the intermediate machine to start the migration process

To migrate a database from a source to a target, an intermediate machine is necessary. This machine is responsible for pulling data from the source database and pushing it to the target database. Specific migration tools are required to perform actions on both the source and target databases to complete the migration. The intermediate machine will host these migration tools throughout the entire migration process.

The recommended steps are the following:

1. Set up an EC2 instance in the same region as your source and target databases.
2. Configure your EC2 instance to connect to your AWS RDS instance. This involves updating the security group of your RDS instance to allow the connection from the EC2 instance.

5. Perform the database migration

Now that the source database (AWS RDS instance) is ready for migration, an intermediate machine is prepared to execute the migration, and the target database (your Timescale instance) is up and running, we can proceed with the actual migration process.

1. Set source database uri (i.e., AWS RDS instance) and target database uri (i.e., Timescale instance).

export TARGET=<target_db_uri>

export SOURCE=<source_db_uri> 

2. Execute the following command to initiate the migration. We have packaged all the necessary tools for live migration in the docker image. Simply run the command below and wait for the migration to complete.

docker run --rm -dit --name live-migration \
  -e PGCOPYDB_SOURCE_PGURI=$SOURCE \
  -e PGCOPYDB_TARGET_PGURI=$TARGET \
  -v ~/live-migration:/opt/timescale/ts_cdc \
  timescale/live-migration:v0.0.1

During this step, the live migration workflow will copy existing data from the source database to the target database, as well as replicate ongoing changes (change data capture) from the source to the target. The copying process will occur sequentially, beginning with the existing data and then applying ongoing changes. The output logs of this step will display the migration progress and provide prompts to verify the target database before transitioning applications to use the target database as the primary.

3. Verify the data consistency between the source and target databases once the live migration is synchronized. Once you are confident that the data consistency checks have been successful, proceed with the application switchover to the target database by promoting it as the primary database.

And you’re set! Welcome to Timescale!

Why Choose Timescale Over RDS

If you’re reading this article because you’re contemplating a migration from RDS to Timescale, know that moving your data will not only be easy but will also come with the following benefits:

44 % faster data ingestion 

During our 16-thread ingestion benchmark, where we inserted nearly one billion rows of data, we observed impressive results when running Timescale and RDS. Timescale outperformed RDS by 32 % with 4 vCPUs and by 44 % with 8 vCPUs. Both systems had the same I/O performance configured on their gp3 disk.

Up to 350x faster queries

Query performance optimization is crucial in a time-series database, especially when powering real-time dashboards. The Time-Series Benchmarking Suite (TSBS), which we use to run our benchmarks, includes a variety of queries, each with its complex description. For the RDS benchmark, we conducted 100 runs of each query on 4 vCPU instance types and recorded the results.

The table demonstrates that Timescale outperforms Amazon RDS consistently, often by more than 100x. In some cases, Timescale performs over 350x better without any degradation for any query type. The table below displays the data for 4 vCPU instances, but similar results were observed across all CPU types tested. With a high instance workload, you can achieve even better results.

95% storage savings

In our benchmark, all data, except for the most recent partition, is compressed into our native columnar format. This format utilizes advanced algorithms to reduce the required storage space for the CPU table significantly. Despite compression, you can still access the data as usual, but with the added advantages of a smaller size and a columnar structure.

Reducing storage usage can result in smaller volumes, lower costs, and faster access. We achieved a 95 % reduction, from 159 GB to 8.6 GB—these numbers are not uncommon for real customer workloads.

And even more features

Check out the Timescale vs. Amazon RDS PostgreSQL benchmark blog post for more details.

Next Steps

If you’re considering migrating from AWS RDS but still want to keep the PostgreSQL you know and love, this playbook provides a good overview of how quickly and easily it is to migrate to Timescale using the live migrations solution. We hope it will help you achieve your desired destination—a high-performance PostgreSQL but faster database with predictable, unambiguous billing. If you’re still on the fence, create a free Timescale account and try it out for 30 days.

We will continue to iterate on our migration strategies—we have more aside from live migrations, targeted at different needs and use cases—to ensure that moving to Timescale is just as easy as using our cloud database platform. Read our documentation for more details:

• Live migrations from AWS RDS for PostgreSQL to Timescale
• Pg_dump/pg_restore from AWS RDS for PostgreSQL to Timescale
• Live migrations for any PostgreSQL to Timescale