PostgreSQL: How to enable data checksums - pg_checksums

Hello colleagues.

Today we will talk about such a function in PostgreSQL as checking data checksums - checksum. Data checksumming is a great feature in PostgreSQL that will help you detect any corruption of data that PostgreSQL stores on disk. Disk failure is one of the most important things a database administrator should be aware of. Disk failure is a physical problem with reading or writing files on a disk. A common example of a disk failure would be a disk head failure, which would result in the loss of all files on disk associated with a database vulnerable to disk failure, including data files, wal logs, and control files.

In today's example, we'll take a look at the case where you've already created a database without checksums, and now you want to add checksum verification. To achieve this goal, we will use the  pg_checksums utility , which has appeared in PostgreSQL since version 12.

Article content:

1. Checking whether data checksum checking is enabled in the database.
2. Stopping the database.
3. Enable checksum verification on the database.
4. Turn on the database.
5. Examination.
6. Error checking.
7. Simulate a database file failure.

1. Checking whether the checksum check of data in the database is enabled.

Before we do anything with the database, we first need to make sure that checksum verification is indeed disabled. 

sql> show data_checksums;

sql> SELECT name, setting, category FROM pg_settings WHERE name = 'data_checksums';

In all variants, the value of the data_checksums parameter is off, which means that the checksum check in the PostgreSQL database - checksum is disabled.

Another option for checking is the pg_controldata utility.

$. pg_controldata |grep checksum

The pg_controldata utility can return two values ​​for the checksum parameter, 0 or 1. Values ​​0 mean that checksum checking is disabled, and 1 means that it is enabled.

2. Stop the database.

Before enabling the checksum check on the database, we need to stop it first. If we do not stop the database and run the pg_checksums utility, then nothing will happen and the PostgreSQL DBMS itself will show us messages that it needs to be stopped.

$. pg_checksums -D /app/postgresql/15/pg_dbcluster_1/data --enable --progress --verbose

As a result, we will get error messages - pg_checksums: error: cluster must be shut down

We stop the database.

$. pg_ctl stop

3. Enable checksum verification on the database.

Once we have stopped the database, we can now run the pg_checksums utility which will enable checksums on the database.

$. pg_checksums -D /app/postgresql/15/pg_dbcluster_1/data --enable --progress --verbose

Parameter Descriptions:

• -D - Directory where you have all the database files.
• --enable - Enable checksum verification.
• --progress - Show progress as a percentage.
• --verbose - Show verbose execution of the utility.

As a result, we should see messages - Checksums enabled in cluster

4. Turn on the database.

When the pg_checksums utility has completed all its actions, we can now start the database back.

$. pg_ctl start

5. Verification.

Now let's run back all the checks that we did in the first paragraph to make sure that we really have enabled the checksum check in the database.

sql> show data_checksums;

sql> SELECT name, setting, category FROM pg_settings WHERE name = 'data_checksums';

$. pg_controldata |grep checksum

As you can see, as a result, all values ​​of the checksum parameter are equal to on and 1, which means that checksum verification has been successfully enabled. 

6. Check for errors.

We can now periodically check if we have errors related to the data, for this we execute a query:

sql> SELECT datname, checksum_failures, checksum_last_failure FROM pg_stat_database WHERE datname IS NOT NULL;

If you get 0 values ​​in the result for all databases, then at the moment you have no problems with the data.

7. Simulation of a database file failure.

Now I will show you how the database will behave in cases where the file system is corrupted, for this I will manually corrupt one of the database files in which the table will be stored and look at the result.

YOU DO NOT DO SUCH ACTIONS IN YOUR PRODUCTIVE DATABASE!

I messed up one of the testdb database files and am running a normal select query.

WARNING:  page verification failed, calculated checksum 57417 but expected 44979
WARNING:  invalid page in block 0 of relation base/16388/24620_vm; zeroing out page

As a result, we see that our checksums do not match and the database tells us about an error.

Thank you all, I hope my article was of some help to you.