Livesync replication troubleshooting

Common issues you may hit while running a Livesync replication migration to Tiger Cloud, and how to resolve them.

_ts_live_sync.subscription_rel empty

It may take up to 30 minutes for large publications before _ts_live_sync.subscription_rel is populated. Check Docker logs:

docker logs -f livesync

Replay not keeping up

Add indexes on UPDATE/DELETE WHERE-clause columns on the target. Ensure REPLICA IDENTITY includes the partition column on hypertables.

Lag growing

Scale up the target. Check for long-running transactions on the source that hold xmin and prevent WAL cleanup:

SELECT pid, age(backend_xmin) AS xmin_age, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC;

Initial data copy is slow or target IOPS saturated

If the target’s IOPS or disk throughput is pinned during the initial data copy, the most common cause is index maintenance on the destination tables. Each non-PK index has to be updated for every COPY’d row, which multiplies the I/O cost.

Drop the non-PK indexes on the target before the copy and recreate them after all tables reach state r or s. Primary-key and unique-constraint indexes must stay; Livesync uses them for INSERT ... ON CONFLICT deduplication.

-- On the TARGET, before initial copy:
DROP INDEX IF EXISTS <schema>.<index_1>;
DROP INDEX IF EXISTS <schema>.<index_2>;
-- ... repeat for each non-PK index

After the initial copy completes, recreate the indexes (preferably in parallel sessions for large tables):

SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;

CREATE INDEX <index_1> ON <schema>.<table> (...);
-- ... etc.

Other knobs to consider: lower --table-sync-workers, scale up target IOPS or CPU, or stagger heavy tables.

Restart a single table

1. Remove the table from the publication on the source:

   ALTER PUBLICATION <publication_name> DROP TABLE <schema>.<table>;

2. Stop and restart Livesync to remove catalog objects for the dropped table.

3. Confirm removal (this should return empty):

   psql "$TARGET" -c "
     SELECT * FROM _ts_live_sync.subscription_rel
     WHERE tablename = '<table>';
   "

4. Truncate the table on the target:

   TRUNCATE <schema>.<table>;

5. Add the table back to the publication:

   ALTER PUBLICATION <publication_name> ADD TABLE <schema>.<table>;

6. Stop and restart Livesync to begin syncing the re-added table.

Restart everything

Stop Livesync, drop the subscription, truncate all target tables, then restart the container.

Important

Failing to truncate target tables leads to duplicate-key violation errors or duplicate records on the target.