Database administration
Refresh a PostgreSQL source from a snapshot
Replace a non-production PostgreSQL database with a copy of another database, then reload the Artie destination so it matches that copy.
Use this when you restore staging, sandbox, or another non-production database from a production snapshot. An in-place upgrade of the same database follows Database migrations.
Prerequisites
- The pipeline reads the PostgreSQL database you are about to replace.
- You have permission to pause that pipeline and to start a backfill.
- You have permission to drop a replication slot on the replacement server.
- The replacement server has
wal_levelset tological. See PostgreSQL. - Production has its own pipeline. Pause only the pipeline that reads the database you replace.
- If other pipelines share this source reader, follow Pausing and resuming before you pause.
Pause the pipeline
On the pipeline overview, select Pause pipeline…. Select Pause pipeline, not Pause delivery only. Pause delivery only keeps reading the source, so the reader stays attached to the database you are about to replace. Select Pause pipeline to confirm.
The pipeline shows Paused.
To pause from code, call Update pipeline status with status set to paused. A scoped key needs the pipelines:write scope. transfer paused is Pause delivery only, and it keeps reading the source.
curl -X POST "https://api.artie.com/pipelines/PIPELINE_UUID/status" \
-H "Authorization: Bearer YOUR_API_KEY" \
-H "Content-Type: application/json" \
-d '{"status": "paused"}'PIPELINE_UUID is the pipeline’s UUID. Replace YOUR_API_KEY with your API key secret. The response field pipeline.status is paused.
Replace the database
Restore the non-production database from the production snapshot, clone, or dump. If the replacement uses a new address, update Host on the source connector before you resume.
Check wal_level
Run this on the replacement:
SHOW wal_level;The result is logical.
Check the publication
The default publication name is dbz_publication. If Publication is set, use that name in the query.
SELECT pubname
FROM pg_publication
WHERE pubname = 'dbz_publication';One row means the publication is present. With Publication mode set to All tables, Artie creates dbz_publication for all tables when the query returns no rows. With Specific tables, Artie does not create the publication. Resume then fails with failed to verify publications: publication "dbz_publication" not found. Create the publication before you resume.
Drop a copied replication slot
Artie resets its saved log sequence number (LSN) only when its replication slot is missing. It then creates a new slot at the current write-ahead log (WAL) position. A slot copied onto the replacement keeps the previous LSN, and Artie tries to read from that position.
Run this on the replacement server, not on production. The default slot name is artie. If Replication slot is set, use that name. A separate production server keeps its own slot.
SELECT slot_name, active
FROM pg_replication_slots
WHERE slot_name = 'artie';No rows means the slot is already gone. When a row comes back, drop the slot only if active is false:
SELECT pg_drop_replication_slot('artie');Run the slot query again. It returns no rows.
Resume the pipeline
Select Resume pipeline. Artie creates the slot and starts change data capture (CDC) at that slot’s position. That stream does not contain rows that differ between the old destination and the replacement.
To resume from code, call Update pipeline status with status set to running. A scoped key needs the pipelines:write scope.
curl -X POST "https://api.artie.com/pipelines/PIPELINE_UUID/status" \
-H "Authorization: Bearer YOUR_API_KEY" \
-H "Content-Type: application/json" \
-d '{"status": "running"}'PIPELINE_UUID is the pipeline’s UUID. Replace YOUR_API_KEY with your API key secret. The response field pipeline.status is running, which the dashboard shows as Active.
Run this on the replacement:
SELECT
slot_name,
active,
pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS behind_bytes
FROM pg_replication_slots
WHERE slot_name = 'artie';active is true. behind_bytes is zero or greater. A negative value means Artie is still using a position from before the replacement. Pause the pipeline and contact Artie.
Backfill the destination tables
Start the backfill after the new slot is active. The backfill copies the replacement’s rows. CDC from the new slot covers later changes. See Backfills.
On the pipeline overview, open More options and select Backfill tables…. Select the replicated tables. Under Existing rows, choose Empty tables first, then continue through Start backfill.
Artie empties each destination table, then reloads it. Queries see partial data until that table’s backfill finishes. Keep existing rows leaves rows that are gone from the source, so the destination still has rows from before the refresh.
On Snowflake and Databricks, Rebuild and swap loads a new table and swaps it in when the load finishes. Queries keep reading the old rows until then. It is in early access. See Rebuild and swap backfills.
A workspace starts backfills for at most 2,500 tables per hour. Split a larger selection across hours.
A Kafka destination cannot empty, drop, or swap a table before a backfill. The topic keeps the messages it already has.
History mode cannot use Rebuild and swap. To reload those tables, select Backfill history tables… and choose Empty tables first. That removes history rows from before the refresh.
Verify the destination
Wait until the backfill progress card finishes. Insert a row with a new primary key into a small table the pipeline replicates.
INSERT INTO public.refresh_checks (check_id, note)
VALUES ('refresh-2026-11-17', 'post-refresh check');Query the same key in the destination.
SELECT check_id, note
FROM analytics.refresh_checks
WHERE check_id = 'refresh-2026-11-17';The query returns that row. Also pick a primary key that existed only in the destination before the refresh. After Empty tables first, that key is gone.
Next steps
- Backfills covers the other Existing rows choices, including Drop and recreate.
- Database migrations covers an upgrade of the same database.
Was this page helpful?
- Need help? Message us on Slack or email [email protected].
- Want a walkthrough? Book a demo.
- LLM? Read llms.txt.