Migrating From Amazon RDS to Timescale Cloud

Many Timescale Cloud customers migrated from Amazon Relational Database Service (RDS) for PostgreSQL because Timescale Cloud offers better performance than Amazon RDS without giving up the PostgreSQL developers know and love.

In this post, we cover how to migrate from Amazon RDS for PostgreSQL to Timescale Cloud using our new migration tool, Hypershift.

This migration tool will help ease the pain of migrating your database and your wallet. Hypershift saves you money by allowing you to enable compression on the fly (expect up to a +95 % reduction in storage!), minimize your downtime, and make the migration process smoother and easier, getting you up and running with Timescale Cloud as quickly as possible.

Using Timescale’s Hypershift has a few benefits when migrating from Amazon RDS PostgreSQL to Timescale Cloud:

• Hypershift significantly speeds up migration compared to a standard pg_dump/ pg_restore thanks to parallelization.
• Since it’s built as a Rust wrapper around pg_dump and pg_restore, it provides the same operational guarantees as pg_dump and pg_restore.
• Unlike pg_dump/ pg_restore, Hypeshift doesn’t require an interim place to store your database dump. You can migrate your data in one step.
• Last but certainly not least, Hypershift allows you to enable Timescale features during migration, such as converting PostgreSQL tables to Timescale hypertables and compressing hypertables on the fly to reduce costs immediately. This makes transitioning from PostgreSQL to Timescale quicker, and you can start experiencing the benefits of hypertables and compression right from the start!
• Even if Hypershift is an expert in Timescale, you can use it to migrate your relational PostgreSQL databases to Timescale Cloud. Since time-series data doesn’t live in a vacuum, many customers also store purely relational databases in Timescale (e.g., containing their application’s metadata or customer information). Hypershift can help you migrate your relational databases, too.

Keep reading to learn how to use Hypershift to migrate from your PostgreSQL databases to Timescale!

Migrating From RDS PostgreSQL to Timescale: Hypershift to the Rescue

Even if Hypershift is platform-agnostic, in this blog post we will focus on an Amazon RDS Postgres to Timescale Cloud migration, an increasingly common path for our customers. That said, you should be able to adapt these instructions and use the tool to migrate between other sources and destinations, including to or from a self-hosted PostgreSQL or Timescale instance.

Step 0: Evaluating Timescale Features for Your (Previously) Postgres RDS Databases

Before you start your migration, you need to determine which features of your Timescale database you would like to leverage. Deciding this now can save you additional trouble after completing the migration. Specifically, Hypershift allows you to configure two Timescale features on the fly while moving your data: hypertables and compression.

This last bit is especially interesting: enabling compression will allow you to opt for a smaller storage configuration based on the achieved post-migration compression—this can be incredibly powerful since compression can help reduce the size of your large tables by +95 %.

Hypertables

One of the central features you can make the most of is hypertables. Hypertables are a core Timescale feature: by transforming a PostgreSQL table into a hypertable, you’ll be enabling automatic partitioning by time—your queries and writes will become faster without any manual work. Hypertables are recommended for all time-series tables, and they’re the foundation for features you’ll want to enable later on, such as continuous aggregates.

Migrating to hypertables does not require a schema change, so any queries on regular PostgreSQL tables converted to hypertables will remain fully functional after the migration. For best practices, check our hypertable docs.

Compression

The other main feature you can enable during migration is compression. One massive benefit of using compression during the migration process is that you can start saving money immediately since you’ll require smaller storage than your current one. Read our documentation for more information about compression.

Before moving on with your migration, determine which features you’d like to enable for each one of your tables. Later in the post, we’ll cover how to use Hypershift to enable such features using a YAML configuration file.

Step 1: Plan for Platform Changes Between Postgres on RDS and Timescale Cloud

Now that you know which Timescale features you’d like to enable for each one of your tables, it’s time to consider the differences between each platform. When migrating between two database platforms, the features offered in each one may not match completely—you may have to introduce some changes to your data model and operations as a result. To help you with this process, we will highlight in this section some of the main differences between RDS and Timescale Cloud that can impact your migration.

The main significant in-database difference between RDS PostgreSQL and Timescale Cloud services is that Timescale Cloud services do not allow you to have multiple databases. During migration, we recommend splitting your databases into separate services or using schemas to isolate your data. For more information, see our Timescale Community Forum thread “Does Timescale Cloud support multiple databases?”.

If you are using availability features on RDS PostgreSQL like Multi-AZ Deployments, don’t forget to add a replica to your Timescale Cloud instance for the same level of availability. Timescale Cloud offers rapid recovery for all features, but we highly recommend using a replica for Multi-AZ redundancy.

When migrating your RDS database into Timescale Cloud, there are more factors to consider:

• The PostgreSQL major version on the source database should be lower than or equal to the target database. You can check your Timescale Cloud instance's version when you connect to it or in the Console under the “Maintenance” tab.
• Your existing users and passwords may change, as will the admin role's name  (postgres in RDS and tsdbadmin in Timescale Cloud). Remember to update your code accordingly.
• Your database connection string will also change. You can overcome this by using an intermediary domain name in your connection strings pointing to your RDS or Timescale Cloud instance.

Step 2: Migrate Your Databases From Amazon RDS to Timescale Cloud

With the planning stage over, it’s time to start moving some data!

In this section, we will detail how to use Hypershift to migrate your data from Amazon RDS to Timescale Cloud. But remember that Hypershift is platform-agnostic—you should be able to easily adapt these steps to migrate databases from any other PostgreSQL source.

As we’ve mentioned earlier, Hypershift can be used to migrate both time-series databases and purely relational databases (e.g., pure PostgreSQL databases that you’ll also store in Timescale). Therefore, this section covers both paths: how to migrate time-series databases to Timescale Cloud and how to migrate relational databases.

How to migrate time-series databases from Amazon RDS to Timescale Cloud

In this section, we’ll cover how to migrate your time-series databases in RDS over to Timescale Cloud. To clarify, by “time-series databases,” we’re referring to databases including tables that you’d like to transform into a hypertable. Such databases can also include purely relational databases—this is not a problem for Timescale. If you need to enable at least one hypertable in the database you’re migrating, this section is for you.

If you have a purely relational database instead, jump into the next section.  

1. Connect to your RDS instance: We’ll connect to our RDS instance using psql and insert some time-series data.

psql "postgres://postgres:[email protected]:5432/postgres" -c """
CREATE TABLE sensor (time TIMESTAMPTZ, reading REAL); 
INSERT INTO sensor (time, reading) VALUES (NOW(), 12.40), (NOW() - INTERVAL '1 day', 14.54), (NOW() - INTERVAL '2 days', 16.97);
"""

2. Prepare to define your new database properties manually: When migrating time-series data into Timescale Cloud, we can use Hypershift to take full advantage of Timescale’s time-series features, such as hypertables and compression. To do so, we need to define the properties of our hypertable and compression manually. Keep reading to learn how.

3. Convert standard PostgreSQL tables to hypertables: To convert a regular PostgreSQL table to a hypertable, the most important fields are the name and time_column_name. The name field corresponds to the name of the table you want to convert. The time_column_name field corresponds to the column's name in your table, which holds the timestamp. The chunk_time_interval field tells Timescale at what intervals to create new hypertable chunks.

4. Define compression: Lastly, define compression and when to start compressing data using the after field. Visit our documentation to find out more about configuring Hypershift.

5. Run the command below to create a configuration file called hypershift.yaml in your current directory.

echo """
source: "host=my-database.rds.amazonaws.com port=5432 user=postgres password=<PASSWORD> dbname=my-database"
target: "host=my-database.tsdb.cloud.timescale.com port=38364 user=tsdbadmin password=<PASSWORD> dbname=tsdb"

hypertable_configs:
  - schema: public
    name: sensor
    time_column_name: time
    chunk_time_interval: "12h"
    compress:
      after: "48h"

""" > hypershift.yaml

The example provided in this blog post shows the password stored in a YAML file. You can also provide the password as an environment variable or a CLI flag for added security.

6. Add the YAML file to the root of the Hypershift container: To run the Hypershift container, add the hypershift.yaml file to the root of our container by using the -v flag and tell Hypershift to load it with the --config-file flag.

docker run -it -v $(pwd)/hypershift.yaml:/hypershift.yaml \
timescale/hypershift:0.4 clone \
--config-file /hypershift.yaml

7. Verify the hypertable creation: Once the Hypershift container has exited successfully, connect to the Timescale Cloud database and verify that the TimescaleDB hypertable has been successfully created.

psql "postgres://postgres:[email protected]:5432/tsdb" -c """
SELECT hypertable_schema, hypertable_name, num_chunks, compression_enabled FROM timescaledb_information.hypertables;
"""
 hypertable_schema | hypertable_name | num_chunks | compression_enabled
-------------------+-----------------+------------+---------------------
 public        	| sensor      	|      	3 | t
(1 row)

How to migrate relational databases to Timescale from RDS

The following steps will teach you how to use Hypershift to migrate purely relational databases from to Timescale Cloud: this is, databases without time-series data.

1. Run Hypershift: At the time of release, the only way to run Hypershift is by using the Timescale-provided Docker container on Docker Hub. You can pull this container by executing the following command:

docker pull timescale/hypershift:0.3

2. Start the Amazon RDS to Timescale Cloud migration: To follow along, ensure you can access an RDS and Timescale Cloud instance. For purely relational databases, Timescale Cloud offers PostgreSQL services, which have a lower price point than Timescale databases. Make sure you pick this type of service!

3. Find your RDS instance's host/endpoint and port: You will find these in the "Connectivity & security" tab. Depending on how you configured your RDS instance, you may need to enable public access and allow traffic from your network in the assigned security group. You could also follow this tutorial on an EC2 instance in the same VPC as your RDS instance.

4. Find your host/endpoint and port in Timescale Cloud: For your Timescale Cloud instance, you can find the host, port, and username in your instance's "Connection info" panel.

5. Connect to the source RDS database: You can connect to it using psql to create a table and insert some sample data. If you don’t have psql installed, you can learn how in our documentation.

psql "postgres://postgres:[email protected]:5432/postgres" -c """
CREATE TABLE person (name TEXT, age SMALLINT); 
INSERT INTO person (name, age) VALUES ('Lauren', 31), ('Oliver', 12), ('Bill', 73);
"""

6. Use the Hypershift Docker container: It will allow you to migrate the data from the source RDS instance to the target Timescale Cloud instance. In this example, we used the short -s and -t flags for the source and target connection strings, respectively, but you can also use --source-dsn and --target-dsn. It’s important to note that the target database (Timescale) cannot contain any preexisting data. Otherwise, Hypershift will not function properly.

docker run -it timescale/hypershift:0.3 clone \
-s "host=my-database.rds.amazonaws.com port=5432 user=postgres password=password" \
-t "host=my-database.tsdb.cloud.timescale.com port=38364 user=tsdbadmin password=password dbname=tsdb"

7. Check the migration’s final status: When Hypershift has migrated all the data, it will report its completion status. If all goes well, you should see Cloning finished successfully printed near the bottom of your terminal. To verify that the contents of the database were migrated successfully, we can query the rows in our target database.

psql "postgres://tsdbadmin:[email protected]:38364/tsdb" -c "SELECT * FROM person;"

#  name  | age
#--------+-----
# Lauren |  31
# Oliver |  12
# Bill   |  74
(3 rows)

Step 3: Pat Yourself on the Back, You've Migrated Your Database

That is it! You’ve successfully migrated your data from Amazon RDS to Timescale Cloud using Hypershift.

Enjoy Your New High-Performance PostgreSQL

If you’re maxing out your vanilla PostgreSQL database in terms of performance, you’re not alone. This is why many customers migrate to Timescale from platforms like Amazon RDS for PostgreSQL, and that’s why we’ve built Hypershift—a migration tool that saves you time and money in your migration process.

We hope this blog post was a useful resource to guide you in your database migration. Don’t forget to check our documentation to learn our best practices and discover more Hypershift features. And remember, if you need further assistance, you can always reach out to us—our top-notch Support team will be happy to help you plan and execute your migration plan.

If you’re not yet a Timescale Cloud user, you can start a free trial here to get full access to the platform for 30 days, no credit card required.