---
title: Livesync replication | Tiger Data Docs
description: Migrate your entire PostgreSQL or TimescaleDB database to Tiger Cloud with near-zero downtime using Livesync
---

Tips

**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 PostgreSQL](/migrate/livesync-for-postgresql/index.md) instead. Both features share the same underlying technology, but the workflows and end states differ.

Important

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](https://app.slack.com/client/T4GT3N2JK/C086NU9EZ88).

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 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](/migrate/live-migration/index.md) workflow.

The migration runs in four stages:

1. **Schema migration:** export structure from the source, apply it to the target.
2. **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.
3. **CDC:** real-time replication of ongoing changes up to 50K operations per second.
4. **Cutover:** validate, stop sync, switch the application connection strings.

You can drive Livesync replication from the Tiger Console UI or from a self-hosted Docker container. Tiger 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).

## Prerequisites for this procedure

To follow these steps, you'll need:

- **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:** A separate machine to run the migration commands, with Docker, `psql`, `pg_dump`, and `vacuumdb` installed and network access to both source and target databases.

- An [adjusted maintenance window](/deploy/tiger-cloud/tiger-cloud-aws/upgrades#define-your-maintenance-window/index.md) to prevent maintenance from running during migration.

- Environment variables for the source and target connections:

  Terminal window

  ```
  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:

  - `INSERT`s 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.

## Source setup

1. **Database parameters**

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

   - [Self-hosted](#tab-panel-619)
   - [AWS RDS / Aurora](#tab-panel-620)
   - [Neon](#tab-panel-621)
   - [Supabase](#tab-panel-622)
   - [Azure Flexible Server](#tab-panel-623)

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

   Terminal window

   ```
   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:

   Terminal window

   ```
   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:

   1. In the RDS console, create a parameter group for your PostgreSQL major version, or edit the cluster parameter group for Aurora.

   2. 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`

   3. Attach the parameter group to your instance or cluster.

   4. Reboot the instance or cluster (for Aurora, reboot the writer) to apply.

   Validate:

   Terminal window

   ```
   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.

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

   No restart or `ALTER SYSTEM` is required. Validate:

   Terminal window

   ```
   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:

   Terminal window

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

   Recommended changes (apply via the [Supabase custom Postgres config](https://supabase.com/docs/guides/database/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`:

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

   Validate:

   Terminal window

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

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.

   Note

   **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:

   Terminal window

   ```
   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:

   Terminal window

   ```
   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:

   Terminal window

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

## Roles

Dump roles from the source and apply to the target:

Terminal window

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

Review and apply:

Terminal window

```
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.

## Schema migration

Choose the section matching your source database type.

- [From TimescaleDB](#tab-panel-626)
- [From PostgreSQL](#tab-panel-627)

1. **Dump and restore the schema**

   Terminal window

   ```
   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 the continuous aggregates step.

   Terminal window

   ```
   psql "$TARGET" -f schema_dump.sql
   ```

2. **Create hypertables**

   **Primary dimension**

   Terminal window

   ```
   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
   ```

   **Additional dimensions (optional)**

   TimescaleDB generally discourages using more than one dimension. Additional dimensions carry a risk of [partition explosion](/learn/hypertables#partition-by-dimension/index.md) and prevent the use of [data tiering](/learn/data-tiering/about-data-tiering#low-cost-storage/index.md) (tiered storage requires single-dimension hypertables). Only add extra dimensions if the source explicitly uses them and you have validated the need.

   Terminal window

   ```
   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. **Continuous aggregates**

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

   Terminal window

   ```
   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
   ```

4. **Columnstore**

   Terminal window

   ```
   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.

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](#post-sync)).

   Terminal window

   ```
   # 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
   ```

1) **Dump and restore the schema**

   Terminal window

   ```
   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
   ```

2) **Convert tables to hypertables**

   Good candidates for [hypertables](/learn/hypertables/index.md):

   - **Tables with a time-based column:** any column of type `TIMESTAMPTZ`, `TIMESTAMP`, `DATE`, or `INTEGER`/`BIGINT` (epoch). See [supported partition column types](/learn/hypertables/hypertable-crud/index.md).
   - **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) **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)](/learn/columnar-storage/understand-hypercore/index.md) for details on compression methods and configuration.

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;
   ```

5) **Save policies for later**

   Define columnstore, refresh, and retention policies now, but apply them only after data sync (see [Post-sync](#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');
   ```

Whichever source type you migrated from, finish schema migration by applying privileges.

### Apply privileges

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

Terminal window

```
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
```

## Publication

Create a [PostgreSQL `PUBLICATION`](https://www.postgresql.org/docs/current/sql-createpublication.html) 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.

Important

**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:

Terminal window

```
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:

Terminal window

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

## 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`.

Terminal window

```
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.

## Data migration

Choose one of the two approaches below.

- [Self-hosted Docker](#tab-panel-624)
- [Tiger Console](#tab-panel-625)

1. **Start Livesync**

   Best practice is to run as a Docker daemon:

   Terminal window

   ```
   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](https://hub.docker.com/r/timescale/live-sync).

   | Flag                   | Description                                                                                                                                                                                                                  |
   | ---------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
   | `--publication`        | Publication name as created in the publication step. 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`.                                                            |

2. **Monitor**

   Terminal window

   ```
   docker logs -f livesync
   ```

   **Table sync status**

   Terminal window

   ```
   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.

   Terminal window

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

   **Replication lag**

   On the source using `pg_replication_slots`:

   Terminal window

   ```
   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):

   Terminal window

   ```
   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;
   "
   ```

1) **Create the connector**

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

2) **Select tables**

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

   Tiger Console starts the connector and displays progress.

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.

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`.

## Post-sync

After all tables reach state `r` or `s`:

1. **Apply policies**

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

   Terminal window

   ```
   # 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
   ```

2. **Update statistics**

   Terminal window

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

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`):

   Terminal window

   ```
   # 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.

## Cutover

1. **Validate**

   Confirm all tables are in state `r` or `s`:

   Terminal window

   ```
   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:

   Terminal window

   ```
   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';
   "
   ```

2. **Stop write traffic to the source**

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

   Terminal window

   ```
   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.

3. **Reset sequences**

   Important

   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](#tab-panel-617)
   - [Manual SQL](#tab-panel-618)

   Terminal window

   ```
   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
   ```

4. **Stop Livesync**

   Terminal window

   ```
   docker stop livesync
   ```

5. **Connect application**

   Update connection strings to point to Tiger Cloud.

## Cleanup

After the application is fully running on Tiger Cloud:

Terminal window

```
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](/migrate/livesync-replication-troubleshooting/index.md): common issues you may hit during migration and how to resolve them.
- [Advanced topics](/migrate/livesync-replication-advanced/index.md): continuous aggregates, compressed hypertables on older TimescaleDB versions, and `GENERATED ALWAYS AS IDENTITY` columns.