Just yesterday, one of our clients complained that he was making backups in a 1C database based on PostgreSQL. Due to the fact that we only provide them with VPS and nothing more, we did not know what was going on inside them. And as it turned out, backups have not been written for a long time, because they also do not have their own admin and no one is watching this.
They sent us an appeal, looked at what was happening there and see the following when trying to make a backup copy:
ERROR: invalid page in block 2196 of relation base/349583/364737
After spending as much as 15 minutes, it turned out that it’s not so easy to take and press the “Good” button, and there is no regular solution from the word “Absolutely”. There are no ready-made scripts either. As a result, having decided that it would be necessary to publish the found solution for those who use PostgreSQL on Windows.
- We start pgAdmin.
- Launch the Query Tool and:
- The path to the database, I think no one will doubt with which database the problem arose, so we will omit the method of finding the name of the database knowing only base/349583
- Next, to insert text:
SELECT pg_filenode_relation(0, 364737); - as a result, we get the name of the table. We had a table "_document143_vt2652" - Now we need to start its recovery:
SET zero_damaged_pages = on;
VACUUM FULL _document144;
- At the end of the execution, you need to re-index the entire database!
It should be noted that some data may be lost from the table!