---
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 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, except PostgreSQL declarative partitioned tables published with `publish_via_partition_root = false`, which Livesync (v0.23.0 and later) copies leaf partition by leaf partition in parallel, the same way hypertable chunks are copied. 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. 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"
  ```

Note

When using the `postgres://user:pass@host:port/db` URI format, URL-encode special characters in the password to avoid URL-parsing errors.

PasswordPaste your password

Enter your password to get the URL-encoded version. Characters that are safe in a URI (like `*`, `~`, `.`, `-`, `_`) are left as-is.

Nothing is sent or stored anywhere; both the tool above and the terminal commands below run locally in your browser or shell.

If you prefer not to paste your password into a form, URL-encode it in your terminal instead:

- [Python](#tab-panel-758)
- [Node.js](#tab-panel-759)
- [jq](#tab-panel-760)

Terminal window

```
python3 -c "import sys, urllib.parse; print(urllib.parse.quote(sys.argv[1]))" '<password>'
```

Terminal window

```
node -e "console.log(encodeURIComponent(process.argv[1]))" '<password>'
```

Terminal window

```
jq -rn '@uri "<password>"'
```

Alternatively, use the `keyword=value` parameter format with single-quoted values, which lets you include special characters without encoding:

Terminal window

```
export SOURCE="host=example.com port=5432 dbname=mydb user=postgres password='%^/20'"
```

See the [libpq connection strings reference](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING) for the full list of parameters.

## Limitations

- **Compressed hypertable chunks are copied uncompressed.** Livesync copies logical data by decompressing compressed chunks on the fly during the table copy. This removes the TimescaleDB extension-version coupling between source and target, but the target starts with columnstore data in row form. Copying compressed chunks as-is is on the roadmap but not yet available; re-enable columnstore on the target after the sync (see [Columnstore](#columnstore) / [Post-sync](#post-sync)).

- **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-763)
   - [AWS RDS / Aurora](#tab-panel-764)
   - [Neon](#tab-panel-765)
   - [Supabase](#tab-panel-766)
   - [Azure Flexible Server](#tab-panel-767)

   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.

   Note

   **`pg_hba.conf`:** if the source PostgreSQL instance restricts which users can connect via `pg_hba.conf`, add a `host` rule permitting the `livesync_migrate` user (or your chosen connector user) to connect from the migration/connector host. Managed providers (RDS/Aurora, Neon, Supabase, Azure) handle this through their own auth and network controls and do not use `pg_hba.conf`.

   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-768)
- [From PostgreSQL](#tab-panel-769)

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_*' \
     --exclude-schema='timescaledb_*' \
     --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/partitioning-hypertables#space-partitioning-hash-dimension/index.md) and prevent the use of [data tiering](/learn/data-lifecycle/storage/about-storage-tiers#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/understand-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/creating-and-configuring-hypertables/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:latest refresh-publication \
  --subscription <subscription_name> \
  --source "$SOURCE" \
  --target "$TARGET"
```

If you have PostgreSQL declarative partitioned tables in the publication, leave `publish_via_partition_root` at its PostgreSQL default of `false`. Since Livesync v0.23.0, this makes the initial copy of the partitioned table both parallel (each leaf partition is copied by its own worker, like hypertable chunks) and resumable (already-copied leaves aren't recopied if the sync is interrupted):

```
ALTER PUBLICATION <publication_name> SET (publish_via_partition_root = false);
```

Set `publish_via_partition_root = true` only if you need the older serial, whole-table copy, for example for compatibility with a Livesync version older than v0.23.0:

```
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-770)
- [Tiger Console](#tab-panel-771)

1. **Start Livesync**

   Best practice is to run as a Docker daemon:

   Terminal window

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

   `latest` 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) **Connect to your Tiger Cloud service**

   In [Tiger Console](https://console.cloud.tigerdata.com/dashboard/services), select the service to sync live data to.

2) **Prepare the source database**

   ![PostgreSQL connector wizard in Tiger Console](/_astro/pg-connector-wizard-tiger-console.BPVoP0DK_Z1yPPjw.webp)

   1. Click `Connectors` > `Postgres`.
   2. Set the name for the new connector by clicking the pencil icon.
   3. Check the boxes for `Set wal_level to logical` and `Update your credentials`, then click `Continue`.

3) **Connect the source database and the target service**

   ![PostgreSQL connector connection string in Tiger Console](/_astro/pg-connector-connection-string.DUeVjYuI_ZcLCmf.webp)

   1. Enter your database credentials or a PostgreSQL connection string. This is the connection string for the migration user you created during source setup.

   2. **(Recommended for private databases)** If the source database is not reachable from the public Internet, toggle `Enable SSH tunneling` and route the connector through a bastion host:

      1. Copy the public key shown in the wizard and append it to the `authorized_keys` file of the user the connector will log in as on the bastion host:

         Terminal window

         ```
         echo "<public key from the connector wizard>" >> ~/.ssh/authorized_keys


         # Verify
         cat ~/.ssh/authorized_keys
         ```

      2. Enter the bastion `Hostname`, `User`, and `Port` in the wizard.

   3. Click `Connect to database`. Tiger Console connects to the source database and retrieves the schema information.

4) **Select the data to synchronize**

   ![Starting the PostgreSQL connector in Tiger Console](/_astro/pg-connector-start-tiger-console.DPSXCOf0_jl7mV.webp)

   Choose where the connector picks tables from:

   - **From an existing publication**: select a PostgreSQL `PUBLICATION` name, then pick schemas and tables from those it includes. Use this when you cannot grant the connector user `CREATE` privilege on the source and your DBA can pre-create the publication, or when you want to apply [row or column filters](https://www.postgresql.org/docs/current/logical-replication-row-filter.html) on published tables.
   - **Directly from the source**: select schemas, then pick the tables to sync from them. Tiger Console creates the publication for you.

   Click `Select tables +`. For each selected table, Tiger Console checks the schema and, if possible, suggests the column to use as the time dimension in a hypertable.

5) **Configure Initial Data Copy workers**

   ![Configure Initial Data Copy worker count in Tiger Console](/_astro/pg-connector-configure-idc-worker-count.D6aj0UEK_2ev3xi.webp)

   Specify the number of parallel connections (Initial Data Copy, or IDC, workers) used to process the initial data copy. Higher values can speed up the initial copy but may increase load on the source database. Defaults to `4`.

   Tune this based on the resources available on the source database and the number of tables being synced. Large individual tables still copy through a single connection, so higher worker counts help most when you have many small to medium tables.

   Click `Create Connector`. Tiger Console starts the source PostgreSQL connector between the source database and the target service and displays the progress.

6) **Monitor and manage the connector**

   ![Editing a PostgreSQL connector in Tiger Console](/_astro/edit-pg-connector-tiger-console.CAiEG08p_Z1hkIxV.webp)

   1. To review the syncing progress for each table, click `Connectors` > `Source connectors`, then select the name of your connector in the table.

   2. To edit the connector, click `Connectors` > `Source connectors`, then select the name of your connector in the table. You can rename the connector, delete or add new tables for syncing.

   3. To pause a connector, click `Connectors` > `Source connectors`, then open the three-dot menu on the right and select `Pause`.

   4. To delete a connector, click `Connectors` > `Source connectors`, then open the three-dot menu on the right and select `Delete`. You must pause the connector before deleting it.

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

   If you have a large table, you can run `ANALYZE` on the target Tiger Cloud service to update the table statistics after the initial sync is complete. This helps the query planner make better decisions for query execution plans.

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

   Terminal window

   ```
   docker run -it --rm --name livesync-copy-sequences \
     timescale/live-sync:latest 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:

When you are permanently done syncing (for example, you have fully cut over to Tiger Cloud, or you want to restart the sync from scratch), use the `drop` sub-command to remove the replication slot from the source and all subscription state from the target:

Terminal window

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

`drop` removes:

- The replication slot (`live_sync_<subscription_name>`) on the **source** database.
- The subscription and internal tracking tables (`_ts_live_sync.*`) on the **target** Tiger Cloud service.

Warning

Run `drop` only when you intend to permanently stop or fully restart the sync. Dropping the replication slot while the source is still active causes WAL to be retained until the slot is recreated, which can grow disk usage on the source.

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.