Livesync replication advanced topics

Advanced scenarios for Livesync replication migrations: continuous aggregates, compressed hypertables on older TimescaleDB versions, and identity columns.

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 DELETEs; 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.

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

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;