PostgreSQL: could not open file “/var/lib/pgsql/data/pg_clog/0726”: No such file or directory

when selecting a specific date from a table, at times due to database corruption Postgres might report the file was not found.

hsphere=# select * from TABLE;
ERROR:  could not access status of transaction 1918986094
DETAIL:  could not open file “/var/lib/pgsql/data/pg_clog/0726”: No such file or directory


In Such instances, we will need to re-create the file using the below dd command

 dd if=/dev/zero of=/var/lib/pgsql/data/pg_clog/0726 bs=256k count=1


Note: Ensure the permissions on the file that was re-created are set appropriately. creating the blank file means that the data that was originally on it might actually go missing. The respective records will need to be re-created or its relevant records (constraints etc) will need to be removed from the DB.

Related Posts
%d bloggers like this: