Skip to content

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.

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

Terminal window
docker logs -f livesync

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

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.

  1. Create a bastion host

    The host must be able to reach the source database, for example an EC2 instance with an Elastic IP.

  2. Allow-list the bastion host

    Add the Elastic IP to the source firewall or security group.

  3. Enable SSH tunneling

    In the connector wizard, toggle Enable SSH tunneling and enter the bastion host details.

The connector manages the SSH tunnel for you. Keep the connection string pointed at the source database.

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 updates
ERROR: cannot delete from table "..." because it does not have a replica identity and publishes deletes

Set a replica identity on tables that receive updates or deletes:

ALTER TABLE <schema>.<table> REPLICA IDENTITY DEFAULT; -- uses the primary key
ALTER TABLE <schema>.<table> REPLICA IDENTITY USING INDEX <name>; -- a unique, NOT NULL index
ALTER 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:

  1. Delete the rows of the removed partition on the target
    DELETE FROM <schema>.<table>
    WHERE <partition_column> >= '<lower_bound>'
    AND <partition_column> < '<upper_bound>';
  2. Restart Livesync and wait for it to catch up

    Livesync applies the failed transaction and all later changes.

  3. Copy back the rows that the source wrote before the error

    The DELETE also 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;
    EOF

    Run 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.

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

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

  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

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.