Migrating from a different PostgreSQL database

tip

First make sure that you have properly installed AND setup TimescaleDB within your PostgreSQL instance.

To migrate your database from PostgreSQL to TimescaleDB, you need pg_dump for exporting your schema and data.

Migration falls into three main steps:

  1. Copy over the database schema and choose which tables will become hypertables (i.e., those that currently have time-series data).
  2. Backup data to comma-separated values (CSV).
  3. Import the data into TimescaleDB

For this example we'll assume you have a PostgreSQL instance with a database called old_db that contains a single table called conditions that you want to convert into a hypertable in a new database called new_db.

1. Copying Schema & Setting up Hypertables

Copying over your database schema is easily done with pg_dump:

pg_dump --schema-only -f old_db.bak old_db

This creates a backup file called old_db.bak that contains only the SQL commands to recreate all the tables in old_db, which in this case is just conditions.

To create those tables in new_db:

psql -d new_db < old_db.bak

Now that we have the schema, we want to convert tables into hypertables where appropriate. So let's connect with the client:

psql -d new_db

Then use the create_hypertable function on the tables to make hypertables. Due to a current limitation, this must be run on a table while it is empty, so we do this before importing data. In this case, our hypertable target is conditions (using column time as the time partitioning column):

SELECT create_hypertable('conditions', 'time');

Your new database is now ready for data.

2. Backing up Data to CSV

To backup your data to CSV, we can run a COPY:

# The following ensures 'conditions' outputs to a comma-separated .csv file
psql -d old_db -c "\COPY (SELECT * FROM conditions) TO old_db.csv DELIMITER ',' CSV"

Your data is now stored in a file called old_db.csv.

3. Import Data into TimescaleDB

Follow these instructions to insert data into your hypertable.

Now check out some common hypertable commands for exploring your data.

Found an issue on this page?

Report an issue!

Keywords

Related Content