In a beautiful day, my PostgreSQL DBMS started to issue this error message as a result of some SQL statements. When I searched for it, I found out it was related to a possible hardware crash, very possibly a power failure that interrupted some running transaction.
Basically, the problem happens because the product is looking for a non-existent transaction file within the pg_clog directory. This transaction should be consumed before retrieving (or altering) data — that’s why PostgreSQL interrupts everything and prints this message. It needs to be solved before others transactions can run.
So, to finally solve it, it is needed to access the server where the DBMS is installed, switch to the ID owner of the database — usually postgres — and find out where the PostgresSQL’s data is located. Especially for my case:
sudo su - postgres;
cd /usr/local/pgsql/data/pg_clog;
Once you get to the directory with the proper credentials, it will be needed to recreate the non-existent file with the following command:
dd if=/dev/zero of=XXXX bs=256k count=1
— Make sure to replace XXXX to the string which is being shown in the logs.
— Run a ls -ltr to make sure file was created with the proper permissions. It is important.
This should be enough at this time but keep in mind others transactions also were affected by the corruption. So, my recommendation at this moment is to run a full backup dump for this database. It will force PostgreSQL to read all the tuples. If any other set of data is depending on a missing transaction, it will be informed by the backup logs. Apply the same procedure to recreate the pg_clog if required.
Besides to creating the pg_clog file, an additional step may be necessary to complete the fix. Basically, it will be needed to identify which table is related to the missed transaction. The backup process is your friend (again) in that step. It will print the table name when it finds the transaction issue. So, for this particular table, run the following statements:
vacuum full table_name;
— This command will completely rewrite the structure of your table. That’s why it will hold any query in this table. Make sure you have a database maintenance window to run it.
reindex table table_name;
— Reindex is required because vacuum full messes with your indexes.
So, at the end of the process, start another full backup do make sure Postgresql is able to read all your tuples.
This should fix it.
amlucasZZ 