Backing up and restoring a Supabase database that Debezium reads from

Debezium reads Postgres through three things that aren’t in your tables: a replication slot on the server, a publication listing the tables, and the offset the connector keeps in Kafka. A backup and restore treats each of them differently, and the connector’s reaction is easy to miss. We restored a Postgres 17 database under a running Debezium 3.7 connector and tried the combinations below. In three of them the connector stayed RUNNING and sent none of the new rows, without logging an error.

Supabase is the example here because its restore options and limits are documented. The Postgres and Debezium behavior is the same on any host.

What a restore brings back

The slot and the publication live in different places. A publication is a catalog object, like a table or a view. A replication slot is server state kept in the pg_replslot directory, and it marks a position in that server’s WAL. pg_dump writes SQL for catalog objects, so it has nothing to say about slots. We dumped a database with one slot and one publication covering two tables, once in full and once with --schema=public:

pg_dump --format=custom --file=full.dump app
pg_dump --format=custom --schema=public --file=public.dump app

pg_restore --list full.dump | grep -iE 'publication|slot'
pg_restore --list public.dump | grep -iE 'publication|slot'

3430; 6104 16405 PUBLICATION - dbz_publication postgres
3431; 6106 16406 PUBLICATION TABLE public dbz_publication customers postgres
3432; 6106 16407 PUBLICATION TABLE public dbz_publication orders postgres

Then we brought the database back four ways and looked at what was there:

pg_basebackup copies pg_replslot as an empty directory, which the PostgreSQL replication protocol docs also state. The PostgreSQL backup docs suggest leaving that directory out of file-level copies too, because a copied slot holds WAL on the new server for a client that will never connect to it. The last row shows why: a raw copy of the data directory still had the source’s debezium slot, at the position it had on the source.

After any restore, run these before the connector or anything else connects:

-- on the restored database, before anything connects to it
select slot_name, plugin, active from pg_replication_slots;
select pubname, puballtables from pg_publication;
select pubname, schemaname, tablename from pg_publication_tables;

If the publication is gone, create it again with the same name and tables. Debezium can create one itself (publication.autocreate.mode defaults to all_tables), but that publishes every table in the database, and FOR ALL TABLES needs a superuser in stock Postgres. Writing it yourself keeps the old definition:

-- same name and table list as before the restore
create publication dbz_publication for table public.customers, public.orders;

What the connector does with the restored database

The test setup: a Postgres 17.11 source with a customers and an orders table, the publication above, and Debezium 3.7.0.Final on Kafka Connect 4.3.1 with this configuration:

{
  "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
  "database.hostname": "dbzlab-pg-old",
  "database.port": "5432",
  "database.user": "postgres",
  "database.password": "lab",
  "database.dbname": "app",
  "topic.prefix": "app",
  "plugin.name": "pgoutput",
  "slot.name": "debezium",
  "publication.name": "dbz_publication",
  "publication.autocreate.mode": "disabled",
  "table.include.list": "public.customers,public.orders",
  "snapshot.mode": "initial"
}

After the initial snapshot (250 read events) and a few streamed inserts, we pushed the source’s WAL forward the way months of traffic would, took a pg_dump, made two more changes, and stopped the source. The connector’s stored offset was 0/D344190. We restored the dump on a fresh Postgres 17 server, whose WAL position afterwards was 0/195B5D0, and pointed the connector at it under the same name, so it kept its offsets.

Creating the slot by hand is not a strange thing to do: Debezium’s upgrade procedure tells you to create it before writes resume. The last two rows ran on a second fresh restore of the same dump, where the when_needed run from row two had already created a slot. We put the pre-crash offset back with Kafka Connect’s PATCH /connectors/app-cdc/offsets and restarted the connector. Any slot with the connector’s name has the same effect, whoever created it.

Slot 'debezium' has restart LSN 'LSN{0/195F978}'
A previous offset indicating a completed snapshot has been found.
According to the connector configuration no snapshot will be executed
Retrieved latest position from stored offset 'LSN{0/D344190}'
Starting replication stream from LSN LSN{0/D344190} with automaticFlush=false (mode=CONNECTOR)
Searching for WAL resume position
Received offset commit request on 'LSN{0/D342140}', but ignoring it. LSN flushing is not allowed yet

Two checks pass when they shouldn’t. Debezium (in PostgresConnection.validateLogPosition, 3.7.0.Final) only asks whether the slot’s restart_lsn is at or before the stored offset. A slot created a minute ago on a fresh server is far before 0/D344190, so the offset looks valid and no snapshot runs, whatever snapshot.mode says. Then Postgres is asked to stream from 0/D344190, and its protocol docs say it starts at the requested location or the slot’s confirmed_flush_lsn, whichever is greater. Every change on the new server sits below that LSN, so none of it was sent while we watched.

offset.mismatch.strategy, a Technology Preview option in 3.7 that defaults to no_validation, doesn’t cover this either. Its trust_offset setting fails only when the slot is ahead of the stored offset; when the offset is ahead, as here, it moves the slot up to the offset when it can. In our run the slot never reached 0/D344190 and the connector kept running.

Debezium’s docs describe the same outcome in their section on upgrading PostgreSQL: with a new slot and old offsets the connector skips the existing change events, which “can lead to silent data loss”. If the new server’s WAL is instead ahead of the stored offset, the slot’s restart_lsn is past it, the check fails, and you should get the first two rows. We read that case from the source code and didn’t run it.

Restoring production with a connector attached

After a real restore, Kafka holds events that the database no longer has: everything between the backup and the failure. In our run that was order 205 (5,555 cents) and a change to order 1. The restored database then gave id 205 to a different order, because the identity sequence came back at its value from backup time. So clear the offset, take a fresh snapshot, and tell the consumers. The order that worked:

1. Stop the connector before anything points at the new database. A stopped connector keeps its configuration and offsets.
2. Restore, then run the slot and publication queries above. Drop any slot you didn’t just create, and recreate the publication if it’s missing.
3. Clear the connector’s offsets. Kafka Connect only allows this while the connector is stopped. Renaming the connector, as Debezium’s upgrade procedure does, has the same effect: the old name’s offsets are simply never read again.
4. Point the configuration at the restored database, leave snapshot.mode at initial (or when_needed), and resume. With no offset, Debezium creates the slot if it isn’t there and snapshots every captured table before it streams.
5. Tell the people who own the consumers. The snapshot arrives as read events (op: r). Changes Kafka saw after the backup aren’t reverted, and no delete events are sent for rows that no longer exist, so sink tables have to be rebuilt from the snapshot or reconciled.

curl -X PUT http://localhost:8083/connectors/app-cdc/stop
curl -X DELETE http://localhost:8083/connectors/app-cdc/offsets

# point the config at the restored database, keep snapshot.mode=initial
curl -X PUT -H 'Content-Type: application/json' \
  --data @connector.json http://localhost:8083/connectors/app-cdc/config

curl -X PUT http://localhost:8083/connectors/app-cdc/resume

The DELETE call answered:

{"message":"The Connect framework-managed offsets for this connector have been reset successfully. However, if this connector manages offsets externally, they will need to be manually reset in the system that the connector uses."}

Debezium keeps its offsets in Kafka Connect, so that message is the whole job. On resume the connector logged No previous offsets found, snapshotted 207 orders including the three written after the restore, and an insert made after that arrived as a normal create event. If you run Debezium Server or the embedded engine instead, clear the offset store it is configured with.

Debezium’s upgrade procedure avoids the snapshot: stop writes, let the connector drain the slot, create the new slot before writes resume, rename the connector and set snapshot.mode to no_data. That only holds when the restored database contains everything the connector already sent, as in a planned migration. It doesn’t after restoring last night’s backup.

Restore drills: leave no slot behind

A drill restores production data into a server you throw away, and the risk there is the opposite one: a slot that nobody reads. The PostgreSQL docs put it plainly: slots “will prevent removal of required resources even when there is no connection using them”.

A pg_dump restore starts with no slots. On a drill server, a slot comes from a copy of the data directory, which brings the production slot along, or from a Debezium connector someone pointed at the drill database to try CDC. We started a server from a copy of the source’s data directory, set max_wal_size to 128MB so the effect shows up quickly, and wrote about 300 MB:

 slot_name | wal_status | retained_wal
-----------+------------+--------------
 debezium  | extended   | 294 MB

 wal_files | pg_wal_size
-----------+-------------
        20 | 320 MB

-- on the drill server, as soon as it is up
select slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained_wal
from pg_replication_slots;

-- then drop every slot it lists
select pg_drop_replication_slot('debezium');

To test CDC against the drill database, give the test connector its own name, its own topic.prefix (on the same Kafka cluster, the production prefix would write straight into the production topics), and slot.drop.on.stop=true, which Debezium says to use only for testing. That setting only drops the slot on a graceful stop, so check pg_replication_slots again before you delete the server.

Newer Postgres versions limit the damage a forgotten slot can do. Since 13, max_slot_wal_keep_size caps the WAL a slot can hold. A slot that falls further behind loses that WAL and ends up lost, which means unusable. 17 added inactive_since and invalidation_reason to pg_replication_slots. 18 adds idle_replication_slot_timeout, which invalidates slots that have been idle for that long. On a drill server, dropping the slot is still simpler than any of these.

Supabase specifics

We didn’t connect Debezium to a hosted Supabase project for this guide. The points below come from Supabase’s docs and from the configuration inside its Postgres image.

• Logical decoding is on. Supabase’s Postgres image ships with wal_level = logical (we checked supabase/postgres:17.6.1.084), and Supabase’s replication guide creates a slot on a project with pg_create_logical_replication_slot.
• Slot limits follow compute size. 5 on Nano through Medium, 8 on Large, 24 on XL, 80 from 2XL up, with the same number of WAL senders. max_replication_slots can be raised with supabase postgres-config update, which restarts the database.
• Slots have a WAL cap. The same image sets max_slot_wal_keep_size to 4096 MB. With that value, a connector that falls more than 4 GB of WAL behind loses its slot and needs a new snapshot. Run show max_slot_wal_keep_size; on your project; the CLI can change it.
• Use the direct connection. Supabase says logical replication doesn’t work through a pooler such as Supavisor. Direct connections are IPv6 only unless the project has the IPv4 add-on, and for this kind of setup Supabase recommends XL compute or larger plus the IPv4 add-on.
• Replication user. On Postgres 17 and newer projects, the postgres role can create a separate user with replication permission. On older ones Supabase says to use postgres itself.
• Restoring a daily backup in place. Supabase asks you to drop subscriptions and replication slots before the restore and re-create them afterwards; it handles the Realtime slot itself. Stop the connector, drop its slot, restore, then follow the production steps above.
• Restore to a new project. Supabase calls this a binary restore of the whole database, so the publication should come along. It doesn’t say whether slots do. WAL-G, which Supabase’s image is configured for, leaves pg_replslot out of its base backups, so expect none, and run the slot query before anything connects.

Restore drill checklist

1. Before the drill, save the publication definition (the CREATE PUBLICATION statement or the output of pg_publication_tables) somewhere outside the database.
2. Write down the connector name, where its offsets live, and your Kafka Connect version, so you know whether the stop and offsets endpoints are there.
3. Restore into a separate server or Supabase project. Never point the production connector at it.
4. Query pg_replication_slots as soon as the server is up and drop whatever is listed.
5. Compare the publication with what you saved. After a --schema=public dump it will be missing.
6. If you test CDC there, use a separate connector name and topic.prefix, with slot.drop.on.stop=true.
7. Time a full Debezium snapshot of the restored tables. After a real restore, consumers wait for the restore and then for this snapshot.
8. Before deleting the drill server, check pg_replication_slots once more, then remove the test connector and its offsets.
9. Keep the production order in the runbook: stop the connector, restore, check slot and publication, clear offsets, resume, tell the consumer owners.

FAQ