HOW TO FIX POSTGRESQL DATABASE CORRUPTION

Prerequisites

Of course, the majority of individuals do not want their databases to be harmed. These people will benefit from staying away from the strategies mentioned in this article. But some people might find it useful to corrupt a database on purpose, such as when testing a tool or process that will be used to find or fix corrupted data.

Prerequisites 

We require a database with some data in it as well as active activity for several of our studies. The built-in PostgreSQL benchmark pgbench can be used for that. As a result of using a scale factor of 100, the largest table has 10 million rows:

Load will be generated with 5 concurrent client sessions:

Creating a corrupt database by setting fsync = off

Let’s turn off the server while it is under load by setting fsync = off in postgresql.conf.

The AMCHEK plugin allows us to quickly identify data corruption.

What happened? Data were no longer flushed to disk in the correct order, so that data modifications could hit the disk before the WAL did. This leads to data corruption during crash recovery.

Creating a corrupt database from a backup

While pgbench is running, we create a base backup:

The function to start backup mode is pg backup start() rather than pg start backup because I am using PostgreSQL v15. This is due to PostgreSQL v15’s removal of the exclusive backup API, which had been deprecated since PostgreSQL 9.6. Read my revised post on the linked page to learn more.

Let’s determine the database’s and pgbench accounts’ primary key index object IDs:

​We copy the data directory to build a backup. Then, to make sure they are more current than the others, we duplicate the pgbench accounts primary key index and commit log:​

The crucial part: do not create backup_label

Now we exit backup mode, but ignore the contents of the backup_label file returned from pg_backup_stop():

Then, let’s make sure that the last checkpoint in the control file is different:

Great! Let’s start the server:

Now an index scan on pgbench_accounts fails, because the index contains more recent data than the table:

postgres=# SELECT * FROM pgbench_accounts ORDER BY aid;
ERROR:  could not read block 166818 in file "base/5/16422.1": read only 0 of 8192 bytes

What took place? We recovered from the incorrect checkpoint because the backup label file was left out, and as a result, the data in the table and its index were no longer consistent. I merely wanted to underline the significance of the backup label; keep in mind that we can achieve the same result without pg backup start() and pg backup stop(). 

corrupting a database using pg resetwal 

We crash the database while pgbench is loading it.

Then we run pg_resetwal:

Then we start the server and use amcheck like before to check the index for integrity:

What happened? pg_resetwal is only safe to use on a cluster that was shutdown cleanly. The option -fis intended as a last-ditch effort to get a corrupted server to start and salvage some data. Only experts should use it.

Creating a corrupt database with pg_upgrade --link

We create a second cluster with initdb:

Then we edit postgresql.conf and choose a different port number. After shutting down the original cluster, we run an “upgrade” in link mode:

pg_upgrade renamed the control file of the old cluster, so that it cannot get started by accident. We’ll undo that:

Now we can start both clusters and run pgbench on both. Soon we will see error messages like

What happened? Since both clusters share the same data files, we managed to start two servers on the same data files. This leads to data corruption.

Creating a corrupt database by manipulating data files

For that, we figure out the file name that belongs to the table pgbench_accounts:

Now we stop the server and write some garbage into the first data block:

Then we start the server and try to select from the table:

What happened? We tampered with the data files, so it’s unsurprising that the table is corrupted.

Creating a corrupt database with catalog modifications

Who needs ALTER TABLE to drop a table column? We can simply run

After that, an attempt to query the table will result in an error:

What took place? We disregarded the fact that deleting a column in the pg attribute really removes the entry instead of setting the drop attribute to TRUE. Additionally, we failed to lock the table against concurrent access and failed to correctly check for dependencies in pg depend. It is not recommended to modify catalog tables because you get to keep both parts of the database if it fails.

Conclusion 

There are numerous ways to corrupt a PostgreSQL database, as we have seen. Some of these were evident, but others could surprise a novice. A damaged database is not something you want.

• Keep track of the system catalogs.
• Don’t ever make changes to the data directory (with the exception of configuration files).
• Run without FSYNC turned off.
• Avoid calling pg resetwal -f on a wrecked server; instead, use pg upgrade —link to delete the old cluster following an upgrade.
• Never remove or forget the backup label.
• To avoid known software flaws, run PostgreSQL in a known version on dependable hardware.

With this information, hopefully some databases can be saved! Read my post on join strategies if you want to learn more about PostgreSQL performance problems.

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​