How to fix Duplicate file in PostgreSQL

Look at your PostgreSQL log files to identify which file is causing the duplicate error. The logs will contain a message like "could not create file" or "duplicate file detected":

tail -f /var/log/postgresql/postgresql.log

Note the exact file path and name mentioned in the error.

Check if the problematic file exists in the location mentioned in the error:

ls -la /path/to/postgresql/datadir/
ls -la /path/to/tablespace/directory/

Confirm that the file is indeed present and determine if it is safe to remove.

Before deleting any files, create a backup in case you need to recover it:

cp /path/to/duplicate/file /path/to/duplicate/file.backup

This ensures you can restore it if the removal causes issues.

Delete the conflicting file from the target location:

rm /path/to/duplicate/file

If the file is in a tablespace directory that should be empty, ensure no other files are present:

ls -la /path/to/tablespace/directory/
rm -f /path/to/tablespace/directory/*  # Only if you're certain the directory should be empty

After removing the duplicate file, restart the PostgreSQL service to allow it to complete the pending operation:

sudo systemctl restart postgresql

Or if using a different init system:

sudo service postgresql restart

Check the PostgreSQL logs to confirm the error is resolved and the operation completed successfully:

tail -f /var/log/postgresql/postgresql.log

Connect to the database and verify normal operation:

psql -U postgres -d postgres -c "SELECT version();"