Migrate data to TimescaleDB from the same PostgreSQL instance

You can migrate data into a TimescaleDB hypertable from a regular PostgreSQL table. This method assumes that you have TimescaleDB set up in the same database instance as your existing table.

Prerequisites

Before beginning, make sure you have installed and set up TimescaleDB.

You also need a table with existing data. In this example, the source table is named old_table. Replace the table name with your actual table name. The example also names the destination table new_table, but you might want to use a more descriptive name.

Migrate data

Migrate your data into TimescaleDB from within the same database.

1. Call CREATE TABLE to make a new table based on your existing table

   You can create your indexes at the same time, so you don’t have to recreate them manually. Or you can create the table without indexes, which makes data migration faster.

   With indexes

   CREATE TABLE new_table (
       LIKE old_table INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES
   ) WITH (
       tsdb.hypertable,
       tsdb.partition_column='<the name of the time column>'
   );

   Without indexes

   CREATE TABLE new_table (
       LIKE old_table INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES
   ) WITH (
       tsdb.hypertable,
       tsdb.partition_column='<the name of the time column>'
   );

   When you create a hypertable using CREATE TABLE ... WITH ..., the default partitioning column is automatically the first column with a timestamp data type. Also, TimescaleDB creates a columnstore policy that automatically converts your data to the columnstore, after an interval equal to the value of the chunk interval, defined through compress_after in the policy. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the columnstore conversion, hypertable chunks are compressed by up to 98%, and organized for efficient, large-scale queries.

   You can customize this policy later using alter_job. However, to change after or created_before, the compression settings, or the hypertable the policy is acting on, you must remove the columnstore policy and add a new one.

   You can also manually convert chunks in a hypertable to the columnstore.

2. Insert data from the old table to the new table

   INSERT INTO new_table
     SELECT * FROM old_table;

3. Recreate indexes if needed

   If you created your new table without indexes, recreate your indexes now.