Migrate Postgres 9.6 Inherited Tables to TimeScaleDB

Question

We have a time series table which is partitionied with inheritance. We now want to migrate this to TimeScaleDB within the same database. Documentation says to either use pg_dump/COPY or CREATE with LIKE. We could neither get to work with inherited tables. Should we pg_dump/COPY all inherited tables into CSV files, concatenate then import?

CREATE TABLE events (
  date            TIMESTAMP,
  event           SMALLINT
);

CREATE TABLE events_2018_1 (
  CHECK (date >= '2018-01-01 00:00:00' AND date < '2018-02-01 00:00:00')
)
INHERITS (events);

CREATE INDEX idx_events_date
  ON events
  USING BTREE (date);

score 3 · Accepted Answer · answered Mar 14 '18 at 22:04

The easiest way to migrate data within the same DB is described here: http://docs.timescale.com/latest/getting-started/migrating-data#same-db

Basically you create a new hypertable and then run INSERT INTO new_table SELECT * FROM old_table;

Let me know if you have any trouble.

Migrate Postgres 9.6 Inherited Tables to TimeScaleDB

1 Answers1