PostgreSQL restore fails with "could not open relation with OID" due to parallel restore race condition

[vikyw89]

Description

The built-in PostgreSQL restore feature fails consistently, even on a completely fresh database with no existing data. The restore process uses parallel workers that race against each other, causing some tables to have stale OIDs by the time other workers attempt to COPY data into them.

This affects databases with TimescaleDB, foreign key constraints, or any moderately complex schema.

To Reproduce

1. Create a PostgreSQL database service in Dokploy with TimescaleDB enabled
2. Set up S3 backup destination and create a backup
3. Delete the database volume (full prune to ensure clean state)
4. Redeploy the database and trigger restore from S3
5. Observe the restore failing repeatedly

Current Behavior

The restore loops indefinitely, with errors like:

ERROR: could not open relation with OID 21066
STATEMENT: COPY public.sessions (id, data, expiry_date) FROM stdin;

ERROR: could not open relation with OID 21066
STATEMENT: COPY public.technologies (id, name, category) FROM stdin;

ERROR: could not open relation with OID 21066
STATEMENT: COPY public.urls (id, domain_id, url, crawl_date, crawl_id, created_at) FROM stdin;

This was reproduced twice — once on a dirty volume and once on a completely fresh volume — confirming the issue is in the restore logic itself, not leftover data.

Root Cause

Dokploy's backup command uses the custom format:

pg_dump -Fc --no-acl --no-owner -h localhost -U ${user} --no-password '${database}' | gzip

The custom format (-Fc) requires pg_restore which by default uses parallel workers. These workers race each other during schema creation — one worker drops and recreates a table while another worker already has its OID cached, resulting in stale OID errors when COPY is attempted.

Expected Behavior

The restore should complete successfully without errors, even for complex schemas with extensions like TimescaleDB.

Proposed Fix

Replace the single-shot pg_restore with a 3-section sequential restore:

gunzip -c backup.sql.gz > /tmp/dump.dump

pg_restore --no-acl --no-owner -d ${database} --section=pre-data /tmp/dump.dump
pg_restore --no-acl --no-owner -d ${database} --section=data /tmp/dump.dump
pg_restore --no-acl --no-owner -d ${database} --section=post-data /tmp/dump.dump

This ensures schema is fully created before data is inserted, and constraints/indexes are applied only after all data is loaded.

Alternatively, switch the backup format to plain SQL and restore via psql pipe, which is fully sequential and requires no intermediate file:

# Backup
pg_dump --no-acl --no-owner '${database}' | gzip

# Restore
gunzip -c backup.sql.gz | psql ${database}

Environment

• Dokploy version: latest
• PostgreSQL version: 18.3
• PostgreSQL image: TimescaleDB (timescale/timescaledb-ha:pg18)
• OS: Ubuntu 24.04
• Deployment: same server

Related Issues

• pg_restore continuously retrying #1730 (closed as wontfix) — same root cause, this issue provides additional reproduction steps and a confirmed workaround

Workaround

Until fixed, the only reliable restore method is a direct pipe bypassing Dokploy entirely:

pg_dump --no-acl --no-owner "postgres://user:pass@source:5432/db" | psql "postgres://user:pass@dest:5432/db"

[dosubot]

This is a well-documented report — and it matches the root cause identified in #1730 [1]. Here's what I found looking at the current code:

The backup command uses custom format (-Fc) [2]:

pg_dump -Fc --no-acl --no-owner -h localhost -U ${user} --no-password '${database}' | gzip

The restore command does not use parallel workers (-j) or --section flags [3]:

pg_restore -U '${databaseUser}' -d ${database} -O --clean --if-exists

So the OID errors you're seeing are likely not from pg_restore's own parallelism (since -j is not used), but rather from the concurrency bug described in #3388 — when a browser or reverse proxy (e.g. Cloudflare, Nginx) times out, Dokploy's restore function in packages/server/src/utils/restore/compose.ts gets re-invoked, spawning multiple overlapping pg_restore processes against the same database. The --clean --if-exists flags cause one process to drop/recreate objects while another is mid-COPY, producing stale OID references.

Your proposed fix of splitting into --section=pre-data, --section=data, --section=post-data was also suggested in #1730 and validated by users there, but it was closed without being implemented. The staged restore would help with constraint ordering, but it wouldn't fully prevent the duplicate-execution race — that requires a concurrency guard in the restore function itself.

For a complete fix, both would likely be needed:

1. Concurrency lock — prevent the restore function from being triggered again while already running
2. Staged restore — use --section flags to ensure schema is fully created before data load, and constraints applied after

Your plain SQL alternative (pg_dump | psql pipe) sidesteps the issue by being fully sequential and less susceptible to the --clean drop/recreate behavior, which is a reasonable workaround.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}
   

[vikyw89]

@dosu does the restore get executed from the browser ? I don't see how cloudflare would cause this. Execution happens server side. And of it's indeed due to cloudflare, my workaround should be failing / timed out too. Or did i miss something ?

[dosubot]

Good point — let me correct my earlier statement. Looking more closely at the code:

The restore is triggered via a tRPC subscription from the browser (api.backup.restoreBackupWithLogs) [1]. The subscription handler on the server side calls the restore function in a fire-and-forget pattern — it does not await the restore function [2]. The actual pg_restore runs in the background while logs are streamed back to the client over the subscription connection.

The frontend explicitly sets retry: false for restore queries [3], so there's no automatic retry from the tRPC client. However, the subscription uses WebSocket connections that have a reconnect delay [4] — if a proxy like Cloudflare drops the WebSocket connection, the reconnection logic could potentially re-trigger the subscription and kick off a second restore. There's no server-side check to prevent a new restore from starting if one is already running [5].

That said — you're right that this may not be what's causing your specific OID errors. If you're only seeing a single restore execution (not duplicates), then the Cloudflare/duplicate-execution theory doesn't apply. In that case, the --clean flag in the restore command [6] could be the culprit — it drops and recreates objects, and even a single pg_restore process using custom format can have internal ordering issues with complex schemas (TimescaleDB, foreign keys, etc.) where objects get dropped and recreated in an order that causes stale OID references during COPY.

Your workaround of piping plain SQL through psql avoids this entirely since it processes statements strictly sequentially without --clean drop/recreate behavior. The --section split you proposed would also help by enforcing a clean ordering within a single execution.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}