A PostgreSQL replica holds invoices (invoices) and their lines (invoice_lines, with a foreign key to invoices). It is fed by a consumer that reads a queue, Kafka or RabbitMQ: one message per invoice, one message per line. The data comes from a SQL Server database, which stays the reference.
Some messages were lost. Now the replica has two kinds of hole, and they look different:
- Lost invoice messages. The invoice is missing. When its lines arrived, the foreign key rejected them, because the parent was not there. So the lines are missing too.
- Lost line messages. The invoice is there, but one of its lines is missing. The parent exists, not all of its children.
You want to find both, fill them without reloading the tables, and prove the replica is complete. From a script, so it can run in cron or CI.
This post shows how, on Linux and on Windows, with dbSlice by Thinwork. Every output below comes from a real run in a lab.
The sample data
Two generic tables, no real data. Every value is computed from the ids.
- SQL Server (source): 10,000 invoices with 3 lines each, 30,000 lines. The line key is
line_id = invoice_id * 10 + line number, so invoice 7 has lines 71, 72 and 73. See01-sqlserver-source.sql. - PostgreSQL (replica): the same tables, with the same foreign key. See
02-postgres-target.sql.
There was no broker in the lab. We simulated the lost messages directly in the database: 02-postgres-target.sql builds the replica as the consumer would have left it.
- 25 invoice messages lost (
invoice_id % 400 = 7). The 25 invoices are missing, and their 75 lines are missing too (rejected by the foreign key when they arrived). - 40 line messages lost (line 2 of every invoice with
invoice_id % 250 = 3). The 40 invoices are there, each with one line missing.
In the replica: 9,975 invoices and 29,885 lines. 40 invoices have fewer than 3 lines.
All the files are in one download: fix-broken-foreign-keys-after-lost-messages.zip. It has the two SQL scripts, the four job files and a ready-made script for Linux (run-linux.sh) and for Windows (run-windows.ps1).
The four job files
A job is a small JSON file. It holds the source, the target, the key and the type. It never holds a password: it only names a connection profile you saved in dbSlice.
MASTER and Server-dbPostg are the profile names in our lab. Replace them with the names of your own profiles.
Two jobs compare and only read, one per table. fk-invoices-check.json:
{
"nome": "fk-invoices-check",
"tipo": "status",
"origem": {
"environment": "article",
"db": "MASTER",
"type": "TABLE",
"object": "invoices",
"key": "invoice_id"
},
"destino": {
"environment": "article",
"db": "Server-dbPostg",
"type": "TABLE",
"object": "invoices",
"key": "invoice_id"
},
"pageSize": 10000
}
fk-lines-check.json is the same, with "object": "invoice_lines" and "key": "line_id" on both sides.
Two jobs load, one per table: fk-invoices-load.json and fk-lines-load.json. They have the same source and target as the checks. Only the type changes:
{
"nome": "fk-lines-load",
"tipo": "insert",
...
}
insert copies only the rows whose key is missing in the target. It does not update and it does not delete. That is all this case needs: the check will show that nothing diverged, only rows are missing.
There is no where in any of them. The checks read the whole table, so every hole shows up.
Run it: Linux and Windows
| Linux (Docker edition, jobs API) | Windows (CLI) |
|---|---|
Needs curl and jq. Create an API token in the API tab of the web edition. Scopes: jobs:run and runs:read. Pick the jobs it may run and an expiry. Save it to a file. Copy the four job files into the jobs/ folder of the instance's data volume. The API runs a job by its name. | Save the two profiles in dbSlice. Nothing else to set up. |
The token goes only in the Authorization: Bearer header. | The job takes no token and no password. |
Linux, one job:
URL=http://127.0.0.1:8098
TOKEN="$(cat ./token)" # dbs_xxxxxxxx_...
curl -sS -X POST "$URL/api/v1/jobs/fk-invoices-check/run" \
-H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
-d '{"runLabel": "after-lost-messages"}'
The API answers with a runId. Poll the run until status leaves queued and running, then read the log:
curl -sS -H "Authorization: Bearer $TOKEN" "$URL/api/v1/runs/<runId>"
curl -sS -H "Authorization: Bearer $TOKEN" "$URL/api/v1/runs/<runId>/log"
Windows, one job:
dbSlice.exe fk-invoices-check.json check-invoices.log
The CLI prints the result and writes the log to the file you passed.
The sequence: check both tables, load the lines first (on purpose, to show what happens), load the invoices, load the lines, check both tables again.
Step 1. Check both tables
Windows output, invoices:
OK Comparison completed in 0.4s: 9,975 matched, 25 missing in destination, 0 missing in source. 0 with different column values.
Missing in destination (25): 7, 407, 807, 1207, 1607, 2007, 2407, 2807, 3207, 3607, 4007, 4407, 4807, 5207, 5607, 6007, 6407, 6807, 7207, 7607, 8007, 8407, 8807, 9207, 9607
Lines:
OK Comparison completed in 0.8s: 29,885 matched, 115 missing in destination, 0 missing in source. 0 with different column values.
Missing in destination (115): 32, 71, 72, 73, 2532, 4071, 4072, 4073, 5032, 7532, 8071, 8072, 8073, ...
Read the line keys with the formula invoice_id * 10 + line number:
- 71, 72, 73: all three lines of invoice 7, one of the 25 missing invoices. These are the lines the foreign key rejected. 25 invoices × 3 lines = 75.
- 32: line 2 of invoice 3. Invoice 3 is in the replica. This is a lost line message. There are 40 of them.
- 75 + 40 = 115.
- 0 missing in source and 0 with different column values: nothing exists only in the replica, and the rows that are there are right. Only rows are missing.
The Linux run returns the same result. The lab instance runs in Portuguese, so its log is in Portuguese: 9.975 iguais, 25 faltando no destino and 29.885 iguais, 115 faltando no destino.
Step 2. Load the lines first (it fails)
We loaded the lines before the invoices, on purpose. Windows output:
target: Server-dbPostg [PostgreSQL] · invoice_lines · mode: InsertNew
impact: writes to the target (INSERT only)
FAILED 23503: insert or update on table "invoice_lines" violates foreign key constraint "invoice_lines_invoice_id_fkey"
...
inserted : 0
updated : 0
Of the 115 missing lines, 75 belong to invoices that are not in the replica yet. PostgreSQL refuses them with error 23503, the foreign key violation. This is the same thing that happened to the consumer when the invoice messages were lost.
Three things to notice:
- It wrote nothing.
inserted: 0. Not even the 40 lines whose invoices exist. The next load of the lines still found all 115 missing. - The exit code is non-zero on Windows, and on Linux the API reports
"status": "failed". A script or cron stops at the first error instead of carrying on. - The error has no
DETAIL. The log saysDETAIL: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.That is the PostgreSQL driver (Npgsql) hiding the row values by default. The constraint name,invoice_lines_invoice_id_fkey, already tells you which parent table is missing rows.
Step 3. Parents first, then the children
Load the invoices:
OK Done: 10,000 read, 25 inserted, 0 upsert/update in 0.6s
Then the lines:
OK Done: 30,000 read, 115 inserted, 0 upsert/update in 0.7s
The insert job reads the whole source and copies only the keys missing in the replica. Here that is 25 of 10,000 invoices and 115 of 30,000 lines. 0 upsert/update: it did not rewrite the rows that were already there.
That is also why you can run it again without fear. It never inserts a key that already exists, so a second run does not duplicate anything. It only copies what is still missing.
Step 4. The proof: check again
Invoices:
OK Comparison completed in 0.4s: 10,000 matched, 0 missing in destination, 0 missing in source. 0 with different column values.
Lines:
OK Comparison completed in 0.6s: 30,000 matched, 0 missing in destination, 0 missing in source. 0 with different column values.
Linux gave the same counts at every step.
We also counted on both sides, before and after:
before SQL Server invoices / lines: 10000 / 30000 PostgreSQL invoices / lines / invoices with a line missing: 9975 / 29885 / 40
after SQL Server invoices / lines: 10000 / 30000 PostgreSQL invoices / lines / invoices with a line missing: 10000 / 30000 / 0
"Invoices with a line missing" is a plain query on the replica:
SELECT count(*) FROM invoices i
WHERE (SELECT count(*) FROM invoice_lines l WHERE l.invoice_id = i.invoice_id) < 3;
From 40 to 0. The check is what proves the job is done, not the load counters.
The script
On Linux, the four job files run from one shell script with set -euo pipefail. Each run waits for the result and returns non-zero when the status is not succeeded. In our test script the lines are loaded first on purpose, so that one failure is expected and handled:
run_job fk-invoices-check
run_job fk-lines-check
run_job fk-lines-load || echo "(failed as expected: the parents are not there yet)"
run_job fk-invoices-load
run_job fk-lines-load
run_job fk-invoices-check
run_job fk-lines-check
In your own script, drop the early fk-lines-load and the || echo. Keep the order: invoices, then lines, then the checks. Any failure then stops the script, and cron sees it.
On Windows, put the same sequence in a .ps1 and check $LASTEXITCODE after each dbSlice.exe call. Task Scheduler reads the exit code, so a failed run shows up as a failed task.
The series
"dbSlice CLI in daily work", four cases from the command line:
- Reconcile a Big SQL Server to PostgreSQL Migration from the Command Line
- Catch Up a PostgreSQL Replica After a Network Outage, from the Command Line
- Fix Broken Foreign Keys After Lost Queue Messages, from the Command Line (this post)
- Build a Masked QA Database from a Production Sample, from the Command Line
Also from the command line: Copy Only the Missing Rows from SQL Server to PostgreSQL from the Command Line.
Wrap up
Lost messages leave two kinds of hole in a parent/child replica: parents missing with all their children, and parents missing some children. A check per table shows both, and the child keys tell you which is which. Then:
- Parents before children. Load the lines first and the foreign key refuses them, and nothing is written.
- The insert job copies only missing keys. Running it again does not duplicate.
- A non-zero exit code lets a script or cron stop at the first error.
- The second check, with 0 everywhere, is the proof.
dbSlice is our own product, so take this with that in mind. Try it:
- Windows: dbSlice on the Microsoft Store. See what the Windows app does.
- Linux / Docker edition: it is a corporate edition, available by contact. Write to contact@dbslice.net. See what the Linux edition does.


