---
title: Livesync replication advanced topics | Tiger Data Docs
description: Advanced Livesync replication scenarios for migrating to Tiger Cloud
---

Advanced scenarios for [Livesync replication](/docs/migrate/livesync-replication/index.md) migrations: continuous aggregates, compressed hypertables on older TimescaleDB versions, identity columns, and merging many source tables into one hypertable.

## Migrating continuous aggregates

Livesync replicates the continuous aggregate's underlying materialization hypertable, not the view. The continuous aggregate must already exist with data on the source and be defined (empty) on the target.

1. **Resolve materialization hypertable names on both source and target**

   The `_materialized_hypertable_<N>` suffix differs per environment:

   ```
   SELECT materialization_hypertable_schema AS mat_schema,
          materialization_hypertable_name   AS mat_name
   FROM timescaledb_information.continuous_aggregates
   WHERE view_schema = '<schema>' AND view_name = '<cagg_name>';
   ```

2. **Set replica identity on the source materialization hypertable**

   Required when the publication replicates `UPDATE`/`DELETE` (the default). Continuous aggregate refreshes issue `DELETE`s; without a replica identity the next refresh fails with `cannot delete from table ... because it does not have a replica identity and publishes deletes`.

   ```
   ALTER TABLE _timescaledb_internal._materialized_hypertable_<N>
     REPLICA IDENTITY FULL;
   ```

3. **Add the materialization hypertable to the publication**

   ```
   ALTER PUBLICATION <publication_name>
     ADD TABLE _timescaledb_internal._materialized_hypertable_<N>;
   ```

4. **Map source matview hypertable to target matview hypertable**

   - **Tiger Console:** edit the connector, select the source `_materialized_hypertable_<N>`, and set its target mapping to the target's `_materialized_hypertable_<M>`.

   - **Docker:** add a `--table-map` entry and refresh:

     ```
     --table-map '{
       "source": {"schema": "_timescaledb_internal", "table": "_materialized_hypertable_<N>"},
       "target": {"schema": "_timescaledb_internal", "table": "_materialized_hypertable_<M>"}
     }'
     ```

5. **Do not attach a refresh policy on the target**

   While Livesync is running, the target continuous aggregate is kept current via replication. Attach the refresh policy on the target only after cutover.

## Replica identity on compressed hypertables (older TimescaleDB)

On older TimescaleDB versions, `ALTER TABLE ... REPLICA IDENTITY ...` fails on compressed hypertables:

```
ERROR:  operation not supported on hypertables that have compression enabled
```

Newer versions accept the command directly on the hypertable and propagate it to chunks. If you can upgrade TimescaleDB on the source first, do that and skip this section.

If upgrading isn't an option, wrap the work in `timescaledb_pre_restore()` / `timescaledb_post_restore()` (these disable the catalog hooks that block the operation). Iterate over all chunks of the hypertable, set `REPLICA IDENTITY FULL` on each, and set it on the hypertable itself so new chunks inherit:

Terminal window

```
psql "$SOURCE" -v schema='<schema>' -v hypertable='<hypertable>' <<'EOF'
BEGIN;
SELECT timescaledb_pre_restore();


SELECT format('ALTER TABLE %I.%I REPLICA IDENTITY FULL;',
              chunk_schema, chunk_name)
FROM timescaledb_information.chunks
WHERE hypertable_schema = :'schema'
  AND hypertable_name   = :'hypertable'
\gexec


ALTER TABLE :"schema".:"hypertable" REPLICA IDENTITY FULL;


SELECT timescaledb_post_restore();
COMMIT;
EOF
```

Run as a single transaction. Dry-run on a non-production instance first.

## Tables with `GENERATED ALWAYS AS IDENTITY` columns

CDC `UPDATE` replication currently fails on target tables whose columns are defined as `GENERATED ALWAYS AS IDENTITY`, because the apply worker tries to write the source value into a column that PostgreSQL forbids assigning to. Until Livesync handles this automatically, work around it by relaxing the column to `GENERATED BY DEFAULT` for the duration of the migration. This does not rewrite the column or change runtime behavior for existing sequences.

1. **Find affected columns on the source**

   ```
   psql "$SOURCE" <<'EOF'
   SELECT
       n.nspname AS schema,
       c.relname AS table,
       a.attname AS column
   FROM pg_attribute a
   JOIN pg_class c ON c.oid = a.attrelid
   JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE a.attidentity = 'a'  -- 'a' = ALWAYS, 'd' = BY DEFAULT
     AND c.relkind IN ('r', 'p')
     AND n.nspname NOT IN ('pg_catalog', 'information_schema')
   ORDER BY n.nspname, c.relname, a.attnum;
   EOF
   ```

2. **Relax to `BY DEFAULT` before migration**

   For each row returned above, run on **both** source and target:

   ```
   ALTER TABLE <schema>.<table>
     ALTER COLUMN <column> SET GENERATED BY DEFAULT;
   ```

3. **Restore to `ALWAYS` after cutover**

   After the application is running on Tiger Cloud, restore the original constraint on the target:

   ```
   ALTER TABLE <schema>.<table>
     ALTER COLUMN <column> SET GENERATED ALWAYS;
   ```

## Merge many source tables into one hypertable

Some apps partition data themselves, with one table per period: `metrics-001`, `metrics-002`, and so on. Livesync can merge such tables into a single hypertable on the target, because the `table` in a `--table-map` source is a [Go regular expression](https://pkg.go.dev/regexp/syntax). Livesync adds `^` and `$` around a pattern that has neither, so `metrics-[0-9]+` matches `metrics-001` but not `app_metrics-001`. Wrap alternatives in parentheses, as in `(metrics|events)-[0-9]+`, and if you write `^` or `$` yourself, write both. When you pass several `--table-map` flags, the first match wins. Tables that match no mapping sync one-to-one:

Terminal window

```
--table-map '{"source": {"schema": "public", "table": "metrics-[0-9]+"}, "target": {"schema": "public", "table": "metrics"}}'
```

Before you start Livesync, make the three changes below for every table you merge. The rest of the migration follows the [Livesync replication](/docs/migrate/livesync-replication/index.md) steps.

### Make the replica identity unique across the merged tables

Livesync applies `UPDATE` and `DELETE` on the target by matching the source table's [replica identity](/docs/migrate/livesync-replication#replication-identity/index.md) columns, by default the primary key. The target doesn't know which source table a row came from.

Warning

If the replica identity is only unique within each source table, for example an `id` sequence that restarts at 1 in every monthly table, a change to one source row reaches the matching row of **every** merged table:

- `DELETE` removes all of them, with no error.
- `UPDATE` either changes all of them, with no error, or fails and stops replication for the whole subscription.

Choose one option for every merged table on the source, before you start Livesync:

- **Append-only data**: create the publication for these tables `WITH (publish = 'insert')`. Livesync then never receives `UPDATE` or `DELETE` for them.
- **A key that is unique across all the tables**, such as `(device_id, time)` when the tables cover different time ranges: create a unique index on it in each table and run `ALTER TABLE ... REPLICA IDENTITY USING INDEX ...`.
- **Any other case**: `ALTER TABLE ... REPLICA IDENTITY FULL`. Livesync then matches on every column. This needs no new index but increases the write-ahead log volume on the source.

### Create the target hypertable

[Schema migration](/docs/migrate/livesync-replication#schema-migration/index.md) creates the source tables on the target as regular tables. Use one of them as the template, then drop the copied `serial` default, which points at that table's own sequence:

```
CREATE TABLE public.metrics (LIKE public."metrics-001" INCLUDING DEFAULTS)
WITH (
  tsdb.hypertable,
  tsdb.partition_column = 'time',
  tsdb.chunk_interval = '7 days'
);


ALTER TABLE public.metrics ALTER COLUMN id DROP DEFAULT;
```

`LIKE` doesn't copy the primary key, so the per-table key on `id` stays behind. A unique constraint on the hypertable must include the partition column. Only add one that is unique across all the merged tables, such as `(device_id, time)`. Otherwise the initial copy fails with `duplicate key value violates unique constraint`. Finally, drop the merged source tables from the target, since Livesync doesn't write to them.

### Publish the tables and check the mapping

Add every merged table to the [publication](/docs/migrate/livesync-replication#publication/index.md). After you start Livesync, confirm that each one maps to the hypertable:

```
SELECT schemaname, tablename, target_table, state
FROM _ts_live_sync.subscription_rel
ORDER BY tablename;
```

When your app creates a new table during the migration, set its replica identity, add it to the publication, and run `refresh-publication` with the same `--table-map` flags. Without them, the new table maps to a target table with the same name.
