---
title: Livesync replication troubleshooting | Tiger Data Docs
description: Troubleshoot Livesync replication when migrating to Tiger Cloud
---

Common issues you may hit while running a [Livesync replication](/migrate/livesync-replication/index.md) 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:

Terminal window

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

## IP allow-listing for the source database

If your source database accepts connections only from allow-listed IPs, route the [source PostgreSQL connector](/integrate/connectors/source/sync-from-postgres/index.md) 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](/integrate/connectors/source/sync-from-postgres/index.md), 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.

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

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](#restart-a-single-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](/reference/timescaledb/data-retention/add_retention_policy/index.md) on the target. For details, see [Limitations](/migrate/livesync-replication#limitations/index.md).

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

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