Migrate data to self-hosted TimescaleDB from the same Postgres instance

URL: llms-txt#migrate-data-to-self-hosted-timescaledb-from-the-same-postgres-instance

Contents:

• Prerequisites
• Migrate data
• Migrating data

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

Before beginning, make sure you have [installed and set up][install] 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 your data into TimescaleDB from within the same database.

1. Call [CREATE TABLE][hypertable-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.

If you are self-hosting TimescaleDB v2.19.3 and below, create a [Postgres relational table][pg-create-table], then convert it using [create_hypertable][create_hypertable]. You then enable hypercore with a call to [ALTER TABLE][alter_table_hypercore].

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

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

===== PAGE: https://docs.tigerdata.com/_troubleshooting/mst/corrupt-index-duplicate/ =====

Examples:

Example 1 (sql):

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>'
    );

Example 2 (sql):

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>'
    );

Example 3 (sql):

INSERT INTO new_table
      SELECT * FROM old_table;