Table-Level Resync

Table-level resync allows you to refresh data for individual tables without affecting the entire mirror. This is useful for resolving data drift, corruption, or schema changes on specific tables.

Use Cases

Resync individual tables when:

• Data corruption detected: Destination table has corrupted or inconsistent data
• Schema drift: Manual schema changes on destination caused sync failures
• Missed changes: Replication slot gap or CDC failure caused missing data
• Column exclusion changes: You modified exclude_columns and need fresh data
• Testing: Validate resync behavior on a single table before full mirror resync

Table-Level Resync

Resync a single table within a mirror:

curl -X POST http://localhost:8112/v1/mirrors/prod_to_staging/resync/public.users \
  -H "Authorization: Bearer YOUR_TOKEN"

Response

{
  "message": "Resync started for table 'public.users' in mirror 'prod_to_staging'"
}

Resync Strategies

BunnyDB supports two resync strategies, configured at mirror creation with the resync_strategy parameter.

Truncate Strategy (Default)

Fast but causes downtime for reads on the destination table.

-- Example: Foreign keys dropped
ALTER TABLE orders DROP CONSTRAINT fk_orders_user_id;

TRUNCATE TABLE public.users;

COPY public.users FROM PROGRAM '...' WITH (FORMAT binary);

CREATE INDEX idx_users_email ON public.users(email);
CREATE INDEX idx_users_created_at ON public.users(created_at);

ALTER TABLE orders ADD CONSTRAINT fk_orders_user_id
  FOREIGN KEY (user_id) REFERENCES public.users(id);

Pros:

• Fast execution
• Less disk space required

Cons:

• Destination table is unavailable during resync
• Read queries return empty results or fail

Swap Strategy

Zero-downtime but requires additional disk space.

CREATE TABLE public.users_resync (LIKE public.users INCLUDING ALL);

COPY public.users_resync FROM PROGRAM '...' WITH (FORMAT binary);

CREATE INDEX idx_users_email ON public.users_resync(email);

BEGIN;
ALTER TABLE public.users RENAME TO users_old;
ALTER TABLE public.users_resync RENAME TO users;
DROP TABLE public.users_old;
COMMIT;

ALTER TABLE orders ADD CONSTRAINT fk_orders_user_id
  FOREIGN KEY (user_id) REFERENCES public.users(id);

Pros:

• Zero downtime for reads
• Queries never see empty table

Cons:

• Requires 2x disk space temporarily
• Slower execution due to atomic swap coordination

Full Mirror Resync

To resync all tables in a mirror:

curl -X POST http://localhost:8112/v1/mirrors/prod_to_staging/resync \
  -H "Authorization: Bearer YOUR_TOKEN"

Response

{
  "message": "Full resync started for mirror 'prod_to_staging'"
}

Monitoring Resync Progress

Check mirror status during resync:

curl -X GET http://localhost:8112/v1/mirrors/prod_to_staging \
  -H "Authorization: Bearer YOUR_TOKEN"

Response during resync:

{
  "name": "prod_to_staging",
  "status": "RESYNCING",
  "current_phase": "SNAPSHOT",
  "source_peer": "source_db",
  "destination_peer": "dest_db",
  "tables": [
    {
      "source_table": "public.users",
      "destination_table": "public.users",
      "resync_status": "in_progress"
    }
  ]
}

Response after completion:

{
  "name": "prod_to_staging",
  "status": "RUNNING",
  "current_phase": "CDC"
}

Impact on Other Mirrors

Table-level resync only affects the mirror being resynced. Other mirrors using the same source or destination continue running normally.

Example:

• Mirror prod_to_staging resyncs public.users
• Mirror prod_to_analytics continues CDC for all its tables
• Both mirrors share the source prod_db

Each mirror maintains its own replication slot, so operations are isolated.

Best Practices

Resync During Low-Traffic Periods

Even with swap strategy, resync consumes significant I/O resources. Schedule resyncs during off-peak hours:

# Example: Run resync at 2 AM
curl -X POST http://localhost:8112/v1/mirrors/prod_to_staging/resync/public.users \
  -H "Authorization: Bearer $TOKEN"

Monitor Replication Slot Lag

During resync, CDC is paused and WAL accumulates:

-- Run on source database
SELECT slot_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots
WHERE slot_name = 'bunny_prod_to_staging';

If lag grows too large, source disk may fill up.

Use Swap Strategy for Production

For production workloads where read availability is critical:

{
  "name": "prod_to_staging",
  "resync_strategy": "swap",
  ...
}

Verify Data After Resync

After resync completes, validate row counts:

-- Source
SELECT COUNT(*) FROM public.users;
 
-- Destination
SELECT COUNT(*) FROM public.users;

Or use a checksum for critical tables:

-- Source
SELECT md5(string_agg(md5(t::text), '' ORDER BY id))
FROM public.users t;
 
-- Compare with destination

Troubleshooting

Resync Fails with “Table Not Found”

Cause: Table doesn’t exist on source or destination.

Solution: Verify table exists in both databases:

# List source tables
curl -X GET http://localhost:8112/v1/peers/source_db/tables \
  -H "Authorization: Bearer YOUR_TOKEN"
 
# List destination tables
curl -X GET http://localhost:8112/v1/peers/dest_db/tables \
  -H "Authorization: Bearer YOUR_TOKEN"

Resync Stuck at 0% Progress

Cause: Insufficient permissions on destination or source.

Solution: Check BunnyDB logs for detailed errors:

docker logs bunnydb --tail 50 | grep ERROR

Ensure the destination user has CREATE, INSERT, TRUNCATE privileges.

Swap Strategy Fails with Constraint Violation

Cause: Foreign key references prevent table rename.

Solution: BunnyDB automatically handles FK drops/recreations, but manual constraints may interfere. Check for triggers or custom constraints:

SELECT conname, contype
FROM pg_constraint
WHERE conrelid = 'public.users'::regclass;

Next Steps

• Schema Sync - Sync schema changes from source
• Zero-Downtime Swap Resync - Deep dive into swap strategy
• Pause & Resume - Control mirror execution