Livesync replication

Early access

Migrate your entire PostgreSQL or TimescaleDB database to Tiger Cloud with near-zero downtime using Livesync

Livesync replication vs. source PostgreSQL connector: Livesync replication is a one-time migration to Tiger Cloud with a cutover at the end. For continuous ongoing replication where PostgreSQL stays the primary and Tiger Cloud acts as a logical replica, see Sync from Postgres instead. Both features share the same underlying technology, but the workflows and end states differ.

The Livesync replication workflow is in early access and is not yet supported for production use. For questions or feedback, visit #livesync in the Tiger Cloud Community.

Livesync replication uses the PostgreSQL logical replication protocol to synchronize data from a source database to a Tiger Cloud service, then cut over once the target is caught up. Because it relies on the standard PostgreSQL protocol, you get compatibility, familiarity, and a broad knowledge base.

The same technology powers two distribution channels: the self-hosted Docker image (timescale/live-sync) is commonly referred to as Livesync, and the managed Tiger Cloud Console integration is referred to as the Source PostgreSQL connector. Both produce the same migration result; this page covers both.

This page replaces the older Live migration workflow.

The migration runs in four stages:

Schema migration: export structure from the source, apply it to the target.
Initial data copy: bulk COPY at 300 GB/hr to 2.2 TB/hr depending on --table-sync-workers tuning, target bandwidth, and IOPS. Publication tables are copied in parallel; large individual tables still use a single connection. Foreign key validation is disabled during sync.
CDC: real-time replication of ongoing changes up to 50K operations per second.
Cutover: validate, stop sync, switch the application connection strings.

You can drive Livesync replication from the Tiger Cloud Console UI or from a self-hosted Docker container. The Console supports multiple Livesync instances per service, dropdown-based schema and table selection, and live progress metadata (rows copied during initial data copy, replication lag during CDC).

To follow these steps, you'll need:

A target Tiger Cloud service.

Best practice is to create a Tiger Cloud service with at least 8 CPUs for a smoother experience. A higher-spec instance can significantly reduce the overall migration window.
A migration machine to run the commands that move data from your source database to your target Tiger Cloud service.

Best practice: use an Ubuntu EC2 instance hosted in the same region as your Tiger Cloud service.
An adjusted maintenance window to prevent maintenance from running during migration.

Source: PostgreSQL 13 or later with wal_level = logical. The source must be a primary, not a read replica or standby.
Target: A Tiger Cloud service with real-time analytics enabled. Target spec should be close to the source spec for a full migration. Minimum 4 CPU / 16 GB RAM on both source and target for roughly 300 GB/hr to 2.2 TB/hr transfer. tsdbadmin credentials.
Migration host: Docker, psql, pg_dump, and vacuumdb installed, with network access to both source and target databases.

Environment variables for the source and target connections:

export SOURCE="postgres://<user>:<password>@<source_host>:<port>/<db_name>"
export TARGET="postgres://tsdbadmin:<password>@<target_host>:<port>/tsdb?sslmode=require"

Limitations

TimescaleDB source must be on 2.16.0 or newer if any chunk is ever compressed or decompressed during the migration. Earlier versions don’t emit the decompression-boundary markers that Livesync needs to deduplicate the WAL stream, so the target ends up with duplicate records. This affects all of:
- INSERTs into a chunk that the compression policy later compresses.
- Explicit convert_to_columnstore() or convert_to_rowstore() calls.
- UPDATE or DELETE on a compressed chunk, which decompresses, mutates, then recompresses.
The pre-2.16.0 GUC timescaledb.enable_decompression_logrep_markers is incomplete (it doesn’t cover compression policies or explicit compress/decompress) and requires PostgreSQL 14 or later to do anything.

If you can’t upgrade, the practical mitigations are: keep chunks that may receive writes uncompressed (for example, the last three months), pause compression policies during the migration, or plan to re-sync affected hypertables.

1. Source setup

1.1 Database parameters

Livesync requires logical replication on the source. How you enable it depends on where your source database runs.

Apply the parameters with ALTER SYSTEM. A restart is required afterwards.

psql "$SOURCE" <<'EOF'
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_wal_senders = 30;
ALTER SYSTEM SET max_replication_slots = 30;
ALTER SYSTEM SET wal_sender_timeout = 0;
ALTER SYSTEM SET max_locks_per_transaction = 1200;
EOF

After restart, validate:

psql "$SOURCE" <<'EOF'
SHOW wal_level;
SHOW max_wal_senders;
SHOW max_replication_slots;
SHOW wal_sender_timeout;
SHOW max_locks_per_transaction;
EOF

RDS and Aurora do not permit ALTER SYSTEM. Set the parameters in a custom parameter group:

In the RDS console, create a parameter group for your PostgreSQL major version, or edit the cluster parameter group for Aurora.
Set:
- rds.logical_replication = 1 (enables wal_level=logical)
- max_wal_senders = 30
- max_replication_slots = 30
- wal_sender_timeout = 0
- max_locks_per_transaction = 1200
Attach the parameter group to your instance or cluster.
Reboot the instance or cluster (for Aurora, reboot the writer) to apply.

Validate:

psql "$SOURCE" -c "SHOW rds.logical_replication;"  -- expect: on
psql "$SOURCE" -c "SHOW wal_level;"                -- expect: logical

Neon manages logical replication settings through its console.

Open the Neon Console, go to your project, then click Settings > Logical Replication.
Click Enable.

No restart or ALTER SYSTEM is required. Validate:

psql "$SOURCE" <<'EOF'
SHOW wal_level;            -- expect: logical
SHOW max_wal_senders;      -- expect: 10
SHOW max_replication_slots;-- expect: 10
EOF

Neon computes may auto-suspend on idle. For long initial copies, raise the auto-suspend timeout in Settings > Compute, or pick a plan that disables it.

wal_sender_timeout can only be changed via a Neon support case. Ask support to disable it (set to 0) or set it to a high value such as 12 hours to avoid timeouts during the initial copy of large tables.

Supabase PostgreSQL has logical replication enabled by default; no ALTER SYSTEM or reboot is required.

Validate:

psql "$SOURCE" -c "SHOW wal_level;"  -- expect: logical

Recommended changes (apply via the Supabase custom Postgres config):

max_slot_wal_keep_size = 102400 (100 GB) prevents the replication slot from being dropped during long-running transactions.

Use the direct connection string, not the connection pooler: pgBouncer doesn’t support logical replication. In Project Settings > Database > Connection parameters, disable Display connection pooler.

Azure does not permit ALTER SYSTEM. Set the parameters via the Server parameters blade in the Azure portal, or with az postgres flexible-server parameter set:

Set wal_level = logical.
Set max_wal_senders = 30, max_replication_slots = 30, wal_sender_timeout = 0, max_locks_per_transaction = 1200.
Save. Azure prompts to restart the server. Restart now.

Validate:

psql "$SOURCE" -c "SHOW wal_level;"  -- expect: logical

1.2 Migration user

Create a dedicated migration user on the source:

psql "$SOURCE" <<'EOF'
CREATE USER livesync_migrate PASSWORD '<strong_password>';
ALTER ROLE livesync_migrate WITH SUPERUSER REPLICATION;
GRANT CREATE ON DATABASE <db_name> TO livesync_migrate;
EOF

SUPERUSER is only needed on self-hosted TimescaleDB. New hypertable chunks are created on the fly by the extension, and PostgreSQL requires the role calling ALTER PUBLICATION ... ADD TABLE to either own the table or be a superuser. Without SUPERUSER, chunks created during the migration won’t get added to the publication and their data is skipped. If you can’t grant SUPERUSER, make livesync_migrate the owner of every hypertable in the publication instead.

Azure Database for PostgreSQL: managed Azure instances don’t allow creating a new SUPERUSER. Skip the CREATE USER step and use the existing server administrator account instead, after granting it replication:

ALTER ROLE <admin_user> WITH REPLICATION;

Then set:

export SOURCE="postgres://<admin_user>:<password>@<host>:<port>/<db>"

If the admin lacks ownership of the application’s hypertables, transfer ownership (or use GRANT ... TO <admin_user> WITH ADMIN OPTION) before creating the publication.

Grant the migration user access to all user schemas:

psql "$SOURCE" -t -A <<'EOF' | psql "$SOURCE"
SELECT
  'GRANT USAGE ON SCHEMA ' || quote_ident(nspname) || ' TO livesync_migrate;' || chr(10) ||
  'GRANT SELECT ON ALL TABLES IN SCHEMA ' || quote_ident(nspname) || ' TO livesync_migrate;' || chr(10) ||
  'ALTER DEFAULT PRIVILEGES IN SCHEMA ' || quote_ident(nspname) ||
  ' GRANT SELECT ON TABLES TO livesync_migrate;'
FROM pg_namespace
WHERE nspname NOT IN ('information_schema')
  AND nspname NOT LIKE '_timescale%'
  AND nspname NOT LIKE 'pg\_%' ESCAPE '\'
  AND nspname NOT LIKE 'pg_toast%'
  AND nspname NOT LIKE 'pg_temp_%'
  AND nspname NOT LIKE 'timescaledb%'
  AND nspname NOT LIKE 'toolkit%'
ORDER BY nspname;
EOF

Switch to this user for all subsequent steps:

export SOURCE="postgres://livesync_migrate:<password>@<source_host>:<port>/<db_name>"

2. Roles

Dump roles from the source and apply to the target:

pg_dumpall --roles-only -d "$SOURCE" > roles.sql

Review and apply:

cat roles.sql
psql "$TARGET" -f roles.sql

pg_dumpall --roles-only includes passwords (from pg_authid), role attributes, and memberships. You may need to edit out postgres, tsdbadmin, and other system roles before applying to the target.

3. Schema migration

Choose the section matching your source database type.

From TimescaleDB
From PostgreSQL

3.1 Dump and restore the schema

pg_dump "$SOURCE" \
  --schema-only \
  --no-privileges \
  --no-owner \
  --no-publications \
  --no-subscriptions \
  --no-table-access-method \
  --no-tablespaces \
  --exclude-schema='_timescaledb_cache' \
  --exclude-schema='_timescaledb_catalog' \
  --exclude-schema='_timescaledb_config' \
  --exclude-schema='_timescaledb_debug' \
  --exclude-schema='_timescaledb_functions' \
  --exclude-schema='_timescaledb_internal' \
  --exclude-schema='timescaledb_experimental' \
  --exclude-schema='timescaledb_information' \
  --file=schema_dump.sql

This dumps hypertables as regular tables. continuous aggregates are included as regular views; they are dropped and recreated properly in step 3.3.

psql "$TARGET" -f schema_dump.sql

3.2 Create hypertables

3.2.1 Primary dimension

psql "$SOURCE" -t -A <<'EOF' > create_hypertables.sql
SELECT format(
  'SELECT create_hypertable(%L, by_range(%L%s), create_default_indexes => false, if_not_exists => true);',
  format('%I.%I', d.hypertable_schema, d.hypertable_name),
  d.column_name,
  CASE WHEN d.time_interval IS NOT NULL
    THEN format(', %L::interval', d.time_interval::text)
    ELSE ''
  END
)
FROM timescaledb_information.dimensions d
JOIN timescaledb_information.hypertables h
  ON d.hypertable_schema = h.hypertable_schema
 AND d.hypertable_name = h.hypertable_name
WHERE d.dimension_number = 1
  AND h.hypertable_name NOT LIKE '_materialized_hypertable_%'
ORDER BY d.hypertable_schema, d.hypertable_name;
EOF

cat create_hypertables.sql
psql "$TARGET" -f create_hypertables.sql

3.2.2 Additional dimensions (optional)

TimescaleDB generally discourages using more than one dimension. Additional dimensions carry a risk of partition explosion and prevent the use of data tiering (tiered storage requires single-dimension hypertables). Only add extra dimensions if the source explicitly uses them and you have validated the need.

psql "$SOURCE" -t -A <<'EOF' > add_dimensions.sql
SELECT format(
  'SELECT add_dimension(%L, %s);',
  format('%I.%I', d.hypertable_schema, d.hypertable_name),
  CASE
    WHEN d.num_partitions IS NOT NULL THEN
      format('by_hash(%L, %s)', d.column_name, d.num_partitions)
    ELSE
      format('by_range(%L%s)', d.column_name,
        CASE WHEN d.time_interval IS NOT NULL
          THEN format(', %L::interval', d.time_interval::text)
          ELSE ''
        END)
  END
)
FROM timescaledb_information.dimensions d
JOIN timescaledb_information.hypertables h
  ON d.hypertable_schema = h.hypertable_schema
 AND d.hypertable_name = h.hypertable_name
WHERE d.dimension_number > 1
  AND h.hypertable_name NOT LIKE '_materialized_hypertable_%'
ORDER BY d.hypertable_schema, d.hypertable_name, d.dimension_number;
EOF

cat add_dimensions.sql
psql "$TARGET" -f add_dimensions.sql

3.3 Continuous aggregates

Drop the views that pg_dump created (they are not proper continuous aggregates), then recreate them correctly:

psql "$SOURCE" -t -A <<'EOF' > continuous_aggregates.sql
SELECT format(
  E'DROP VIEW IF EXISTS %I.%I CASCADE;\nCREATE MATERIALIZED VIEW %I.%I\nWITH (timescaledb.continuous) AS\n%s\nWITH NO DATA;\n',
  view_schema, view_name,
  view_schema, view_name,
  rtrim(view_definition, ';')
)
FROM timescaledb_information.continuous_aggregates
ORDER BY view_schema, view_name;
EOF

cat continuous_aggregates.sql
psql "$TARGET" -f continuous_aggregates.sql

3.4 Columnstore

psql "$SOURCE" -t -A <<'EOF' > enable_columnstore.sql
SELECT format(
  E'ALTER TABLE %s SET (\n  timescaledb.enable_columnstore = true%s%s\n);\n',
  hypertable::text,
  CASE WHEN segmentby IS NOT NULL AND segmentby <> ''
    THEN E',\n  timescaledb.segmentby = ''' || segmentby || ''''
    ELSE '' END,
  CASE WHEN orderby IS NOT NULL AND orderby <> ''
    THEN E',\n  timescaledb.orderby = ''' || orderby || ''''
    ELSE '' END
)
FROM timescaledb_information.hypertable_columnstore_settings
WHERE hypertable::text NOT LIKE '\_timescaledb%' ESCAPE '\'
ORDER BY hypertable::text;
EOF

cat enable_columnstore.sql
psql "$TARGET" -f enable_columnstore.sql

timescaledb_information.hypertable_columnstore_settings requires TimescaleDB 2.18.0 or later on the source.

3.5 Save policies for later

Generate the columnstore, refresh, and retention policy scripts now, but apply them only after data sync completes (see Post-sync).

# Columnstore policies
psql "$SOURCE" -t -A <<'EOF' > columnstore_policies.sql
SELECT format(
  E'CALL add_columnstore_policy(%L, after => INTERVAL %L);\n',
  format('%I.%I', j.hypertable_schema, j.hypertable_name),
  j.config->>'compress_after'
)
FROM timescaledb_information.jobs j
WHERE j.proc_name = 'policy_compression'
ORDER BY j.hypertable_schema, j.hypertable_name;
EOF

# Continuous aggregate refresh policies
psql "$SOURCE" -t -A <<'EOF' > refresh_policies.sql
SELECT format(
  E'SELECT add_continuous_aggregate_policy(%L,\n  start_offset => INTERVAL %L,\n  end_offset => INTERVAL %L,\n  schedule_interval => INTERVAL %L\n);\n',
  format('%I.%I', ca.view_schema, ca.view_name),
  coalesce(j.config->>'start_offset', '30 days'),
  coalesce(j.config->>'end_offset', '1 hour'),
  j.schedule_interval::text
)
FROM timescaledb_information.jobs j
JOIN timescaledb_information.continuous_aggregates ca
  ON j.hypertable_schema = ca.view_schema
 AND j.hypertable_name = ca.view_name
WHERE j.proc_name = 'policy_refresh_continuous_aggregate'
ORDER BY ca.view_schema, ca.view_name;
EOF

# Retention policies
psql "$SOURCE" -t -A <<'EOF' > retention_policies.sql
SELECT format(
  E'SELECT add_retention_policy(%L, drop_after => INTERVAL %L);\n',
  format('%I.%I', j.hypertable_schema, j.hypertable_name),
  j.config->>'drop_after'
)
FROM timescaledb_information.jobs j
WHERE j.proc_name = 'policy_retention'
ORDER BY j.hypertable_schema, j.hypertable_name;
EOF

3.1 Dump and restore the schema

pg_dump "$SOURCE" \
  --schema-only \
  --no-privileges \
  --no-owner \
  --no-publications \
  --no-subscriptions \
  --no-table-access-method \
  --no-tablespaces \
  --file=schema_dump.sql

psql "$TARGET" -f schema_dump.sql

3.2 Convert tables to hypertables

Good candidates for hypertables:

Tables with a time-based column: any column of type TIMESTAMPTZ, TIMESTAMP, DATE, or INTEGER/BIGINT (epoch). See supported partition column types.
Append-mostly tables: sensor readings, metrics, events, logs, or any table where rows are predominantly inserted in time order and rarely updated.
PostgreSQL declarative partitioned tables: these can be converted directly to hypertables. Rename the partitioned table, create a regular table with the same name, then convert (see example below).
Large tables with time-range queries: tables frequently queried with WHERE ts > now() - interval '...' benefit most from chunk exclusion.

Convert on the target:

-- Regular table
SELECT create_hypertable(
  '<schema>.<table>',
  by_range('<time_column>', INTERVAL '7 days'),
  create_default_indexes => false,
  if_not_exists => true
);

-- PostgreSQL declarative partitioned table
BEGIN;
ALTER TABLE <schema>.<table> RENAME TO <table>_part;
CREATE TABLE <schema>.<table>(LIKE <schema>.<table>_part INCLUDING ALL);
SELECT create_hypertable('<schema>.<table>', by_range('<time_column>', INTERVAL '7 days'));
COMMIT;

Primary key and unique constraints must include the partition column.

3.3 Enable columnstore

ALTER TABLE <schema>.<table> SET (
  timescaledb.enable_columnstore = true,
  timescaledb.segmentby = '<segment_column>',
  timescaledb.orderby = '<time_column> DESC'
);

Columnstore policies are added after data sync. See Hypercore (columnstore) for details on compression methods and configuration.

3.4 Create continuous aggregates (optional)

CREATE MATERIALIZED VIEW <schema>.<cagg_name>
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', <time_column>) AS bucket,
  <group_column>,
  avg(<value_column>) AS avg_value,
  count(*) AS cnt
FROM <schema>.<table>
GROUP BY 1, 2
WITH NO DATA;

3.5 Save policies for later

Define columnstore, refresh, and retention policies now, but apply them only after data sync (see Post-sync):

-- Columnstore policy
CALL add_columnstore_policy('<schema>.<table>', after => INTERVAL '7 days');

-- Refresh policy
SELECT add_continuous_aggregate_policy('<schema>.<cagg>',
  start_offset => INTERVAL '3 days',
  end_offset   => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour'
);

-- Retention (optional)
SELECT add_retention_policy('<schema>.<table>', drop_after => INTERVAL '90 days');

3.6 Apply privileges

After restoring the schema on the target, export privileges from the source and apply them to the migrated objects:

psql "$SOURCE" -t -A <<'EOF' > privileges.sql
-- Schema privileges
SELECT 'GRANT ' || acl.privilege_type ||
  ' ON SCHEMA ' || quote_ident(nspname) ||
  ' TO ' || CASE WHEN acl.grantee = 0 THEN 'PUBLIC'
    ELSE acl.grantee::regrole::text END ||
  CASE WHEN acl.is_grantable THEN ' WITH GRANT OPTION' ELSE '' END || ';'
FROM pg_namespace, LATERAL aclexplode(nspacl) AS acl
WHERE nspacl IS NOT NULL
  AND nspname NOT IN ('pg_catalog', 'information_schema')
  AND nspname NOT LIKE '_timescale%'
  AND nspname NOT LIKE 'timescaledb%'
  AND nspname NOT LIKE 'toolkit%'
  AND acl.grantee::regrole::text NOT LIKE 'pg_%'
  AND acl.grantee::regrole::text NOT LIKE 'timescaledb%'
  AND acl.grantee::regrole::text NOT IN ('postgres')
ORDER BY nspname;

-- Table privileges
SELECT 'GRANT ' || acl.privilege_type ||
  ' ON TABLE ' || format('%I.%I', n.nspname, c.relname) ||
  ' TO ' || CASE WHEN acl.grantee = 0 THEN 'PUBLIC'
    ELSE acl.grantee::regrole::text END ||
  CASE WHEN acl.is_grantable THEN ' WITH GRANT OPTION' ELSE '' END || ';'
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid,
LATERAL aclexplode(relacl) AS acl
WHERE relacl IS NOT NULL
  AND relkind IN ('r', 'v', 'm', 'f', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE '_timescale%'
  AND n.nspname NOT LIKE 'timescaledb%'
  AND n.nspname NOT LIKE 'toolkit%'
  AND acl.grantee::regrole::text NOT LIKE 'pg_%'
  AND acl.grantee::regrole::text NOT LIKE 'timescaledb%'
  AND acl.grantee::regrole::text NOT IN ('postgres')
ORDER BY n.nspname, c.relname;
EOF

cat privileges.sql
psql "$TARGET" -f privileges.sql

4. Publication

Create a PostgreSQL PUBLICATION on the source. This defines what Livesync syncs. You can create more than one publication (for example, to group tables by domain), but avoid creating one per table.

Do not use FOR ALL TABLES. It includes the _timescaledb_catalog.* tables, and replicating those onto the target corrupts the TimescaleDB catalog there. Always list tables explicitly (FOR TABLE ...) or scope to user schemas (FOR TABLES IN SCHEMA <user_schema>, ...).

psql "$SOURCE" <<'EOF'
CREATE PUBLICATION <publication_name> FOR TABLE <table_name>, <table_name>;
EOF

To sync every table across one or more user schemas (PostgreSQL 15 or later only), list the schemas explicitly and exclude the TimescaleDB ones:

psql "$SOURCE" <<'EOF'
CREATE PUBLICATION <publication_name>
  FOR TABLES IN SCHEMA <user_schema_1>, <user_schema_2>;
EOF

To add or remove tables after creating the publication:

ALTER PUBLICATION <publication_name> ADD TABLE <table_name>;
ALTER PUBLICATION <publication_name> DROP TABLE <table_name>;

Publication changes are picked up only after a Livesync stop/start, or by running the refresh-publication command:

docker run -it --rm --name livesync-refresh \
  timescale/live-sync:<version-tag> refresh-publication \
  --subscription <subscription_name> \
  --source "$SOURCE" \
  --target "$TARGET"

If you have PostgreSQL declarative partitioned tables in the publication:

ALTER PUBLICATION <publication_name> SET (publish_via_partition_root = true);

To selectively replicate only specific DML events (by default all INSERT, UPDATE, DELETE, and TRUNCATE are replicated):

CREATE PUBLICATION <publication_name> FOR TABLE <table_name>
  WITH (publish = 'insert, update');

Validate:

psql "$SOURCE" -c "SELECT * FROM pg_publication_tables WHERE pubname = '<publication_name>';"

5. Replication identity

Skip this section for INSERT-only workloads.

Tables without a primary key need an explicit replica identity for UPDATE and DELETE replication. The query below finds such tables and generates the appropriate ALTER TABLE statement, using a unique index if one exists, otherwise falling back to REPLICA IDENTITY FULL.

psql "$SOURCE" -t -A <<'EOF'
SELECT
  CASE
    WHEN i.indexrelid IS NOT NULL THEN
      format('ALTER TABLE %I.%I REPLICA IDENTITY USING INDEX %I;',
        n.nspname, c.relname, ic.relname)
    ELSE
      format('ALTER TABLE %I.%I REPLICA IDENTITY FULL;',
        n.nspname, c.relname)
  END
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_constraint pk ON pk.conrelid = c.oid AND pk.contype = 'p'
LEFT JOIN LATERAL (
  SELECT i.indrelid, i.indexrelid
  FROM pg_index i
  WHERE i.indrelid = c.oid
    AND i.indisunique AND i.indisvalid AND i.indpred IS NULL
    AND NOT EXISTS (
      SELECT 1 FROM unnest(i.indkey) k
      JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = k
      WHERE NOT a.attnotnull
    )
  LIMIT 1
) i ON true
LEFT JOIN pg_class ic ON ic.oid = i.indexrelid
WHERE c.relkind = 'r'
  AND pk.oid IS NULL
  AND n.nspname NOT IN ('information_schema')
  AND n.nspname NOT LIKE '_timescale%'
  AND n.nspname NOT LIKE 'pg\_%' ESCAPE '\'
ORDER BY n.nspname, c.relname;
EOF

Review the output, then pipe it to psql "$SOURCE" to apply.

6. Data migration

Choose one of the two approaches below.

Self-hosted Docker
Tiger Cloud Console

6.1 Start Livesync

Best practice is to run as a Docker daemon:

docker run -d --rm --name livesync timescale/live-sync:<version-tag> run \
  --publication <publication_name> \
  --subscription <subscription_name> \
  --source "$SOURCE" \
  --target "$TARGET"

<version-tag> is the latest available version. See Docker Hub.

Flag	Description
`--publication`	Publication name as created in step 4. Repeat the flag for multiple publications.
`--subscription`	Name that identifies the subscription on the target.
`--source`	Connection string to the source PostgreSQL database.
`--target`	Connection string to the target Tiger Cloud service.
`--table-map`	(Optional) JSON mapping source tables to target tables. Repeat for multiple mappings. Example: `--table-map '{"source": {"schema": "public", "table": "metrics"}, "target": {"schema": "public", "table": "metrics_data"}}'`
`--table-sync-workers`	(Optional) Number of parallel workers for initial table sync. Default: 4.
`--copy-data`	(Optional) Set to `false` to skip initial data copy and only replicate changes made after replication slot creation. Useful for dry-run testing. Default: `true`.

6.2 Monitor

docker logs -f livesync

Table sync status

psql "$TARGET" -c "
  SELECT state, last_error, count(*)
  FROM _ts_live_sync.subscription_rel
  GROUP BY 1, 2 ORDER BY 1, 2;
"

Tables with errors appear as separate rows grouped by last_error.

State	Meaning
`i`	Initial state; table data sync not started.
`d`	Initial table data sync is in progress.
`f`	Initial table data sync completed; catching up with incremental changes.
`s`	Synchronized; waiting for the main apply worker to take over.
`r`	Table is ready; applying changes in real time.

COPY progress during initial sync

Monitor that the initial data copy is progressing as expected. The number of rows returned should usually match the --table-sync-workers value.

psql "$SOURCE" -c "SELECT * FROM pg_stat_progress_copy;"

Replication lag

On the source using pg_replication_slots:

psql "$SOURCE" -c "
  SELECT slot_name,
    pg_size_pretty(pg_current_wal_flush_lsn() - confirmed_flush_lsn) AS lag
  FROM pg_replication_slots
  WHERE slot_name LIKE 'live_sync_%' AND slot_type = 'logical';
"

On the target using _ts_live_sync.subscription (also shows last_error if any):

psql "$TARGET" -c "
  SELECT
    pg_size_pretty(source_flush_lsn - last_replicated_lsn) AS lag_bytes,
    (metrics_updated_at - last_replicated_txn_time) AS lag_duration,
    *
  FROM _ts_live_sync.subscription;
"

6.1 Create the connector

In Tiger Console, select the target service.
Click Connectors > Source Postgres Connector.
Set a name for the connector (click the pencil icon).
Check Set wal_level to logical and Update your credentials, then click Continue.
Enter the source database credentials or a PostgreSQL connection string, then click Connect to database. Tiger Console connects and retrieves the schema.

6.2 Select tables

In the Select table dropdown, choose the tables to sync.
Click Select tables +.
Tiger Console checks each table schema and suggests a time-dimension column for hypertable conversion where possible.
Click Create Connector.

Tiger Console starts the connector and displays progress.

6.3 Monitor

Data flow: click Connectors to view the overall diagram showing connector status and data replicated.
Per-table progress: click Connectors > Source connectors, then select your connector name.

6.4 Manage

Edit (rename, add or remove tables): click Connectors > Source connectors, then select the connector name.
Pause: open the three-dot menu on the right > Pause.
Delete: pause first, then three-dot menu > Delete.

7. Post-sync

After all tables reach state r or s:

7.1 Apply policies

Apply the columnstore, refresh, and retention policies saved earlier:

# From TimescaleDB source
psql "$TARGET" -f columnstore_policies.sql
psql "$TARGET" -f refresh_policies.sql
psql "$TARGET" -f retention_policies.sql

For continuous aggregates, do a full initial refresh first:

SELECT add_continuous_aggregate_policy('<schema>.<cagg>',
  start_offset => NULL,
  end_offset   => INTERVAL '30 seconds',
  schedule_interval => INTERVAL '1 hour'
);

Wait for completion, then switch to production intervals:

SELECT remove_continuous_aggregate_policy('<schema>.<cagg>');
-- Apply the saved refresh policy

7.2 Update statistics

vacuumdb --analyze --verbose --jobs=8 --dbname="$TARGET"

7.3 Apply database and role settings

Settings applied on the source via ALTER DATABASE ... SET or ALTER ROLE ... IN DATABASE ... SET (for example, search_path, timezone, statement_timeout, default_transaction_isolation) are not included in pg_dump --schema-only or pg_dumpall --roles-only. If your application relies on them, generate equivalent statements from the source catalogs and apply them on the target.

Both ALTER DATABASE ... SET and ALTER ROLE ... IN DATABASE ... SET settings live in pg_db_role_setting, distinguished by setrole = 0 (database-level) versus setrole != 0 (role-specific). The query below reads settings from the source and generates statements that reference the target database name. Source and target database names typically differ (for example, source migrate, target tsdb):

# Resolve the target database name
TGT_DB=$(psql "$TARGET" -t -A -c "SELECT current_database();")

psql "$SOURCE" -t -A -v tgt_db="$TGT_DB" <<'EOF' > db_settings.sql
WITH pairs AS (
  SELECT
    drs.setrole,
    r.rolname,
    split_part(s, '=', 1) AS guc,
    CASE
      WHEN substring(s FROM position('=' IN s) + 1) LIKE '"%"' THEN
        replace(
          substring(s FROM position('=' IN s) + 2 FOR length(s) - position('=' IN s) - 2),
          '""', '"')
      ELSE
        substring(s FROM position('=' IN s) + 1)
    END AS val
  FROM pg_db_role_setting drs
  JOIN pg_database d ON d.oid = drs.setdatabase
  LEFT JOIN pg_roles r ON r.oid = drs.setrole,
  LATERAL unnest(drs.setconfig) s
  WHERE d.datname = current_database()
    AND (drs.setrole = 0
         OR (r.rolname NOT LIKE 'pg_%'
             AND r.rolname NOT IN ('postgres', 'tsdbadmin')))
)
SELECT
  CASE
    WHEN setrole = 0 THEN
      format('ALTER DATABASE %I SET %I = %L;', :'tgt_db', guc, val)
    ELSE
      format('ALTER ROLE %I IN DATABASE %I SET %I = %L;', rolname, :'tgt_db', guc, val)
  END
FROM pairs
ORDER BY setrole, guc;
EOF

cat db_settings.sql
psql "$TARGET" -f db_settings.sql

Review the generated statements before applying. Some settings (for example, shared library paths, filesystem paths, replication-specific parameters) may not apply to a managed Tiger Cloud service and should be removed.

8. Cutover

8.1 Validate

Confirm all tables are in state r or s:

psql "$TARGET" -c "
  SELECT state, last_error, count(*)
  FROM _ts_live_sync.subscription_rel
  GROUP BY 1, 2 ORDER BY 1, 2;
"

Confirm replication lag is near zero:

psql "$SOURCE" -c "
  SELECT slot_name,
    pg_size_pretty(pg_current_wal_flush_lsn() - confirmed_flush_lsn) AS lag
  FROM pg_replication_slots
  WHERE slot_name LIKE 'live_sync_%' AND slot_type = 'logical';
"

8.2 Stop write traffic to the source

Stop all application writes to the source database. Then wait for replication lag to reach near zero:

psql "$SOURCE" -c "
  SELECT slot_name,
    pg_size_pretty(pg_current_wal_flush_lsn() - confirmed_flush_lsn) AS lag
  FROM pg_replication_slots
  WHERE slot_name LIKE 'live_sync_%' AND slot_type = 'logical';
"

Re-run until lag is near 0 bytes.

8.3 Reset sequences

Livesync does not replicate sequence values. Skipping this step causes serial or identity columns to reuse already-existing values after cutover, resulting in primary key or unique constraint violations.

copy-sequences command
Manual SQL

docker run -it --rm --name livesync-copy-sequences \
  timescale/live-sync:<version-tag> copy-sequences \
  --subscription <subscription_name> \
  --source "$SOURCE" \
  --target "$TARGET"

psql "$TARGET" <<'EOF'
DO $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN (
    SELECT
      sr.target_schema AS table_schema,
      sr.target_table AS table_name,
      col.column_name,
      pg_get_serial_sequence(
        format('%I.%I', sr.target_schema, sr.target_table),
        col.column_name
      ) AS seqname
    FROM _ts_live_sync.subscription_rel AS sr
    JOIN information_schema.columns AS col
      ON col.table_schema = sr.target_schema
     AND col.table_name = sr.target_table
    WHERE col.column_default LIKE 'nextval(%'
  ) LOOP
    EXECUTE format(
      'SELECT setval(%L, COALESCE((SELECT MAX(%I) FROM %I.%I), 0) + 1, false);',
      rec.seqname, rec.column_name, rec.table_schema, rec.table_name
    );
  END LOOP;
END;
$$ LANGUAGE plpgsql;
EOF

8.4 Stop Livesync

docker stop livesync

8.5 Connect application

Update connection strings to point to Tiger Cloud.

9. Cleanup

After the application is fully running on Tiger Cloud:

docker run -it --rm --name livesync-cleanup \
  timescale/live-sync:<version-tag> drop \
  --subscription <subscription_name> \
  --source "$SOURCE" \
  --target "$TARGET"

Then drop the migration user on the source.

Next steps

Troubleshooting: common issues you may hit during migration and how to resolve them.
Advanced topics: continuous aggregates, compressed hypertables on older TimescaleDB versions, and GENERATED ALWAYS AS IDENTITY columns.

Livesync replication

Prerequisites for this procedure

Limitations

1. Source setup

1.1 Database parameters

1.2 Migration user

2. Roles

3. Schema migration

3.1 Dump and restore the schema

3.2 Create hypertables

3.2.1 Primary dimension

3.2.2 Additional dimensions (optional)

3.3 Continuous aggregates

3.4 Columnstore

3.5 Save policies for later

3.1 Dump and restore the schema

3.2 Convert tables to hypertables

3.3 Enable columnstore

3.4 Create continuous aggregates (optional)

3.5 Save policies for later

3.6 Apply privileges

4. Publication

5. Replication identity

6. Data migration

6.1 Start Livesync

6.2 Monitor

Table sync status

COPY progress during initial sync

Replication lag

6.1 Create the connector

6.2 Select tables

6.3 Monitor

6.4 Manage

7. Post-sync

7.1 Apply policies

7.2 Update statistics

7.3 Apply database and role settings

8. Cutover

8.1 Validate

8.2 Stop write traffic to the source

8.3 Reset sequences

8.4 Stop Livesync

8.5 Connect application

9. Cleanup

Next steps