PostgreSQL Duplicate File (SQLSTATE 58P02)

PostgreSQL raises ERROR: duplicate file with SQLSTATE 58P02 (condition name duplicate_file) when the database engine attempts to create a file on disk that already exists. This is an operating system-level conflict: PostgreSQL expected to create a new file but found one already occupying that path.

What This Error Means

SQLSTATE 58P02 belongs to error class 58, which PostgreSQL designates as "System Error (errors external to PostgreSQL itself)". Class 58 errors indicate problems at the OS or file system layer — not SQL logic or constraint violations — which makes them relatively rare in normal application operation and typically points to environmental or administrative issues.

PostgreSQL uses a flat-file storage layout under its data directory (PGDATA). Each relation (table, index, sequence) maps to one or more files identified by a numeric filenode. When PostgreSQL creates a new database object, it allocates a fresh filenode and attempts to create the corresponding file. If a file with that name already exists — due to a prior incomplete operation, manual file copying, or a bug in cleanup after a failed DDL — PostgreSQL raises duplicate_file rather than silently overwriting data.

After this error is raised, the current transaction is aborted. Because it reflects a file system state inconsistency rather than a transient condition, simply retrying the operation will reproduce the same error until the underlying file conflict is resolved. The server process itself continues running; the error is isolated to the session that triggered it.

Common Causes

1. Incomplete tablespace or database creation: A previous CREATE TABLESPACE or CREATE DATABASE command failed partway through, leaving partially created files on disk. A subsequent attempt to create the same object finds those leftover files.

2. Manual file system operations: A DBA or backup restore process copied files into the PostgreSQL data directory or a tablespace directory without going through PostgreSQL APIs, resulting in a file that PostgreSQL does not know about but whose name collides with a newly allocated filenode.

3. Restored data directory with stale files: Restoring a PGDATA directory from a partial or inconsistent backup can leave files that belong to objects no longer tracked in pg_class, causing conflicts when PostgreSQL tries to reuse those filenodes.

4. Crash during CREATE INDEX or CLUSTER: These operations create new physical files. A server crash mid-operation followed by incomplete crash recovery (or manual intervention) can leave orphaned files that block future operations.

5. Tablespace directory reuse: Reusing a tablespace directory path that still contains files from a previously dropped tablespace, especially if the directory was not cleaned up properly before CREATE TABLESPACE was run again.

How to Fix duplicate_file

1. Identify the conflicting file: Note the full path from the error message. PostgreSQL usually includes the file path in the detail:

   ERROR:  duplicate file "/var/lib/postgresql/16/main/base/16384/1259"
   

   Verify the file exists and check its age and size:

   ls -lh /var/lib/postgresql/16/main/base/16384/1259
   

2. Check if PostgreSQL tracks the file: Query system catalogs to determine whether any live relation owns this filenode:

   SELECT relname, relkind, reltablespace
   FROM pg_class
   WHERE relfilenode = 1259;
   

   If no rows are returned, the file is an orphan and can be safely removed after confirming the server is idle.

3. Remove the orphaned file (only after confirming it is untracked): Stop any activity on the affected database, then remove the file:

   # Verify PostgreSQL has no open handles to the file first
   sudo lsof /var/lib/postgresql/16/main/base/16384/<filenode>
   
   # Remove after confirming it is safe
   sudo rm /var/lib/postgresql/16/main/base/16384/<filenode>
   

   After removal, retry the original DDL operation.

4. Clean up a failed tablespace directory: If the error occurred during CREATE TABLESPACE, remove all files from the tablespace directory that were created by the failed attempt, then retry:

   # List what's in the tablespace directory
   ls -la /path/to/tablespace/PG_16_202307071/
   
   # Remove stale subdirectories/files left by the failed CREATE
   rm -rf /path/to/tablespace/PG_16_202307071/<oid>/
   

5. Use pg_basebackup or a verified backup for restores: When restoring from backup, always use pg_basebackup or a WAL-consistent restore rather than manual file copying. This ensures the catalog and file system remain in sync.

6. Run VACUUM or REINDEX carefully: If the error appears after index creation failures, running REINDEX on the affected relation (after removing the orphaned file) will recreate the index cleanly.

Additional Information

• SQLSTATE 58P02 is a PostgreSQL-specific extension within class 58; the base SQL standard defines class 58 but not the P02 sub-code specifically.
• Related codes in the same class: 58000 (system error, generic), 58030 (io_error — file I/O failure), 58P01 (undefined_file — expected file not found, the mirror image of this error).
• This error is almost never seen in application-layer code. It typically surfaces only during DBA operations (tablespace management, bulk restores, manual file system work) or after a server crash with incomplete recovery.
• Client drivers (libpq, JDBC, psycopg, etc.) will surface this as a PSQLException or equivalent with SQLSTATE 58P02. There is no meaningful retry logic drivers can apply — the error requires manual intervention.
• PostgreSQL's crash recovery and WAL replay are designed to handle incomplete DDL operations cleanly in normal circumstances. This error most often indicates out-of-band file system manipulation rather than a PostgreSQL bug.

Frequently Asked Questions

Why does PostgreSQL raise this error instead of just overwriting the existing file? PostgreSQL uses file creation flags that explicitly fail if the file already exists (O_CREAT | O_EXCL). This is intentional: silently overwriting an existing file could destroy data belonging to an object that PostgreSQL's catalog does not currently know about (e.g., one from a different OID namespace or a backup artifact). Failing loudly preserves data safety.

Can this error occur during normal INSERT or UPDATE operations? Extremely rarely. Normal DML does not create new physical files. The error almost exclusively occurs during DDL operations that create new relations or storage structures: CREATE TABLE, CREATE INDEX, CREATE TABLESPACE, CREATE DATABASE, CLUSTER, or REINDEX.

Is it safe to just delete the conflicting file? Only after verifying it is not tracked by pg_class (see the fix steps above) and that no running transaction has it open. Deleting a file that belongs to a live relation would corrupt that relation. Always check with pg_class and lsof before removing anything.

How do I prevent this from happening after a failed DDL operation? PostgreSQL normally handles cleanup of partially created files during crash recovery. The risk arises when administrators intervene manually — copying files, restoring partial backups, or manipulating the data directory while the server is running. Keep all file system operations under PostgreSQL's control (via SQL DDL and pg_basebackup) to avoid leaving orphaned files.