Livesync replication troubleshooting
Troubleshoot Livesync replication when migrating to Tiger Cloud
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
Section titled “_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 livesyncReplay not keeping up
Section titled “Replay not keeping up”Add indexes on UPDATE/DELETE WHERE-clause columns on the target. Ensure REPLICA IDENTITY includes the partition column on hypertables.
IP allow-listing for the source database
Section titled “IP allow-listing for the source database”If your source database accepts connections only from allow-listed IPs, route the source PostgreSQL connector connection through a bastion host over an SSH tunnel, and allow-list the bastion host's IP address on the source.
- Create a bastion host
The host must be able to reach the source database, for example an EC2 instance with an Elastic IP.
- Allow-list the bastion host
Add the Elastic IP to the source firewall or security group.
- Enable SSH tunneling
In the connector wizard, toggle
Enable SSH tunnelingand enter the bastion host details.
The connector manages the SSH tunnel for you. Keep the connection string pointed at the source database.
Tables missing a replica identity
Section titled “Tables missing a replica identity”For UPDATE and DELETE statements to replicate, the source tables must have either a PRIMARY KEY or a REPLICA IDENTITY set. Without one, logical replication cannot locate the rows to change, and you see errors such as:
ERROR: cannot update table "..." because it does not have a replica identity and publishes updatesERROR: cannot delete from table "..." because it does not have a replica identity and publishes deletesSet a replica identity on tables that receive updates or deletes:
ALTER TABLE <schema>.<table> REPLICA IDENTITY DEFAULT; -- uses the primary keyALTER TABLE <schema>.<table> REPLICA IDENTITY USING INDEX <name>; -- a unique, NOT NULL indexALTER TABLE <schema>.<table> REPLICA IDENTITY FULL; -- all columns (slower, last resort)If your PostgreSQL tables use native partitioning, setting REPLICA IDENTITY on the root (parent) table does not automatically apply it to the partitioned child tables. Set REPLICA IDENTITY on each partitioned child table individually.
Duplicate key error after rows are removed on the source
Section titled “Duplicate key error after rows are removed on the source”Livesync does not sync a partition detach or drop, or a TRUNCATE, from the source. This includes pg_partman retention, and TimescaleDB retention policies that drop chunks. The target keeps the removed rows. If the source writes rows with the same keys as the removed ones, the sync stops with:
ERROR: duplicate key value violates unique constraint "<constraint>"Livesync does not skip duplicate inserts by default. Do not use --cdc.skip-duplicate-inserts to fix this error: it keeps the old row on the target and ignores the new row from the source.
If the source truncated the table, restart the table. If the source detached or dropped a partition or chunk:
- Delete the rows of the removed partition on the targetDELETE FROM <schema>.<table>WHERE <partition_column> >= '<lower_bound>'AND <partition_column> < '<upper_bound>';
- Restart Livesync and wait for it to catch up
Livesync applies the failed transaction and all later changes.
- Copy back the rows that the source wrote before the error
The
DELETEalso removes the rows that the source wrote into this range after the detach and before the error. Livesync applied these rows before, so it does not apply them again. Copy the range from the source, and insert only the rows that the target does not have:Terminal window psql "$SOURCE" -c "\copy (SELECT * FROM <schema>.<table> WHERE <partition_column> >= '<lower_bound>' AND <partition_column> < '<upper_bound>') TO 'removed_range.csv' WITH (FORMAT csv)"psql "$TARGET" <<'EOF'CREATE TEMP TABLE removed_range (LIKE <schema>.<table>);\copy removed_range FROM 'removed_range.csv' WITH (FORMAT csv)INSERT INTO <schema>.<table> SELECT * FROM removed_range ON CONFLICT DO NOTHING;EOFRun this step when the replication lag is near zero. If Livesync applies a row from this range after the copy, the same error occurs again.
To prevent this error, repeat each detach, drop, and TRUNCATE on the target, or use a matching retention policy on the target. For details, see Limitations.
Lag growing
Section titled “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, queryFROM pg_stat_activityWHERE backend_xmin IS NOT NULLORDER BY xmin_age DESC;Initial data copy is slow or target IOPS saturated
Section titled “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 to find the rows for UPDATE and DELETE, and they reject duplicate rows.
-- 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 indexAfter 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
Section titled “Restart a single table”- Remove the table from the publication on the sourceALTER PUBLICATION <publication_name> DROP TABLE <schema>.<table>;
- Stop and restart Livesync to remove catalog objects for the dropped table
- Confirm removal (this should return empty)psql "$TARGET" -c "SELECT * FROM _ts_live_sync.subscription_relWHERE tablename = '<table>';"
- Truncate the table on the targetTRUNCATE <schema>.<table>;
- Add the table back to the publicationALTER PUBLICATION <publication_name> ADD TABLE <schema>.<table>;
- Stop and restart Livesync to begin syncing the re-added table
Restart everything
Section titled “Restart everything”Stop Livesync, drop the subscription, truncate all target tables, then restart the container.
Failing to truncate target tables leads to duplicate-key violation errors or duplicate records on the target.