DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 58P02 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 58P02: duplicate file

PostgreSQL error 58P02 duplicate_file occurs when the database engine attempts to create a physical relation file on disk that already exists. This typically surfaces during crash recovery, flawed backup restores, or filenode conflicts in the system catalog, and it can seriously threaten data integrity if not addressed promptly.

Top 3 Causes

1. Incomplete Crash Recovery After Abnormal Shutdown

When PostgreSQL is killed abruptly (e.g., kill -9 or power loss), WAL recovery may attempt to recreate a relation file that was already flushed to disk, causing a collision.

-- Check current relation file mappings to spot inconsistencies
SELECT relname, relfilenode, relkind, pg_relation_filepath(oid) AS filepath
FROM pg_class
WHERE relfilenode > 0
ORDER BY relfilenode;
Enter fullscreen mode Exit fullscreen mode 
# Reset WAL as a last resort (always backup first!)
pg_resetwal -f $PGDATA
Enter fullscreen mode Exit fullscreen mode

2. Botched Backup Restore Leaving Residual Files

Restoring from pg_basebackup, rsync, or snapshots without properly clearing the old PGDATA directory can leave orphaned files that conflict with newly assigned filenodes.

-- Identify orphaned or duplicate filenode entries
SELECT relfilenode, COUNT(*) AS cnt, array_agg(relname) AS objects
FROM pg_class
WHERE relfilenode > 0
GROUP BY relfilenode
HAVING COUNT(*) > 1;
Enter fullscreen mode Exit fullscreen mode 
-- Verify actual file path for a specific table
SELECT pg_relation_filepath('your_table_name');
Enter fullscreen mode Exit fullscreen mode

3. OID / Filenode Collision in the System Catalog

Direct manipulation of system catalogs or OID sequence drift during replication can cause PostgreSQL to assign an already-used filenode to a new object.

-- Rebuild the table with a fresh filenode
CREATE TABLE your_table_backup AS SELECT * FROM your_table;
DROP TABLE your_table;

CREATE TABLE your_table (
    id   SERIAL PRIMARY KEY,
    col1 TEXT,
    col2 INTEGER
);

INSERT INTO your_table SELECT * FROM your_table_backup;
DROP TABLE your_table_backup;
Enter fullscreen mode Exit fullscreen mode 
-- Force a new filenode via VACUUM FULL
VACUUM FULL your_table;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Check PostgreSQL logs immediately — the log will usually name the exact file causing the conflict.
  2. Move (don't delete) the duplicate file to a safe location, then restart PostgreSQL to let it recreate the file cleanly.
  3. Use pg_resetwal -f only as an absolute last resort after a verified full backup, as it may cause data loss.
  4. Run REINDEX DATABASE if indexes are involved, especially after a crash recovery scenario. 
-- Rebuild all indexes in the current database
REINDEX DATABASE your_database_name;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Use validated backup tools (pgBackRest, Barman, or pg_basebackup) and always verify restores with logical checks (pg_dump + row counts) before going live.
  • Enable checksums at the cluster level (initdb --data-checksums) so file-level corruption and anomalies are caught early, well before they escalate into a 58P02 event. 
# Enable checksums on an existing cluster (PostgreSQL 12+)
pg_checksums --enable -D $PGDATA
Enter fullscreen mode Exit fullscreen mode
  • Schedule periodic restore drills and file consistency checks to ensure your team can handle these failures confidently under pressure.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)