CODEWITHSUNDEEP
postgresqlpostgresql-9.1data-corruption
Neeraj Bansal
Neeraj Bansal

Reputation: 390

Could not open file "pg_clog/0000": No such file or directory

I am getting the error like following while accessing a Postgres database

ERROR:  could not access status of transaction 69675
DETAIL:  Could not open file "pg_clog/0000": No such file or directory.

I didn't do anything with the pg_clog folder but the 0000 file is not there. Is there any way to recover that file or in any way to fix this issue? Any help would be appreciated.

Upvotes: 0

Views: 5254

Answers (2)

Theron
Theron

Reputation: 21

As stated in the previous response, you're better off restoring from backup, however, I discovered the metadata for those transaction files are not stored in the same location as the data when we restored the data on a server where we were doing some testing with full vacuum and needed to restore the database to an earlier state before the vacuum. In the event where your data integrity isn't as critical like a test database you can get away with creating empty files for the missing transaction logs like this:

dd if=/dev/zero of=/path/to/db/pg_clog/xxxx bs=256k count=1
chown postgres.postgres /path/to/db/pg_clog/xxxx
chmod go-rwX /path/to/db/pg_clog/xxxx

There may be multiple missing files, but if it's just a few files this is an alternative to consider.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246338

You are experiencing database corruption, and you should restore from a backup. You should try to figure out what happened to the database so you can prevent it in the future.

  • Is your storage reliable?
  • Are you using dangerous settings like fsync = off?
  • Were there any crashes recently?
  • Are you really running 9.1? If yes, you shouldn't do that, as it is out of support.
  • Are there any files in the pg_clog directory? There should be.
  • Did you have an out-of-space problem recently that may have led someone to remove files from a “log” directory?

Upvotes: 2

Related Questions