PostgreSQL - How to enable archiving of WAL logs - archive_mode

Colleagues hello to all.

In today's article, we'll talk about how to enable WAL log archiving in PostgreSQL.

WAL logs are translated as - Write Ahead Log. Enabling wal log archiving is a very important step if you want to protect the data of any critical database. In simple words, archiving is the process of creating a backup copy of all transactions that have occurred in the database, so that later it will be possible to restore the database to any point in time in case of a serious failure.

In PostgreSQL, absolutely any transaction that is executed is first written to the WAL log file, and then applied to the actual database data files. While you continue to add and modify data in the database, the WAL files continue be generated. In PostgreSQL terms, copying WAL-generated logs is called archiving, which is used for backup and restore, point-in-time restore, and database replication to a backup server in case of emergency.

Article content:

1. Checking the status of archive mode.
2. Creating a directory for archival WAL logs.
3. Enable archiving mode.
4. Restarting the database.
5. Check.
6. Compress WAL logs before archiving.

1. Checking the status of the archive mode.

Archiving WAL logs in PostgreSQL is controlled by the archive_mode parameter. The parameter can have two values, either on or off. To check if archiving is enabled, we can use sql queries.

sql> SHOW archive_mode;

Or

sql> SELECT name, setting FROM pg_settings WHERE name like 'archive%';

As you can see in both results, the archiving mode is turned off for us.

2. Creating a directory for archived WAL logs.

Before we enable the archiving of WAL logs, we first need to create a directory somewhere where our PostgreSQL will put WAL logs. I usually create this directory next to the directory where we have our database deployed. The directory will be called pg_wal_archive.

$. mkdir -p /app/postgresql/pg_wal_archive

3. Enable archiving mode.

Once we've created a directory for our WAL logs, we can now enable database-based archiving mode. There are two important parameters for enabling archive mode in PostgreSQL, the first parameter is called  archive_mode, and the second is  archive_command. 

The archive_mode parameter needs to be changed from off to on. The  archive_command parameter must be set to a local command which will be executed to archive the completed WAL segment of the log. You need to add these parameters to the main configuration file called postgresql.conf.

archive_mode = on
archive_command = 'cp -i %p /app/postgresql/pg_wal_archive/%f'

Variables are static, so you must restart the database after making changes.

4. Restarting the database.

After we have changed the archiving parameters, we must restart the database for the changes to take effect.

$. pg_ctl restart

5. Check.

After restarting the database, check the result. The first is to execute an sql query to check the archiving parameters.

sql> SELECT name, setting FROM pg_settings WHERE name like 'archive%';

As you can see, the archive_mode is set to on, which means archiving WAL logs is enabled.

Second, we will check how we are transferring WAL logs to our directory that we have created.

$. cd /app/postgresql/pg_wal_archive

As you can see, the WAL logs are successfully transferred to our created directory.

6. Compressing WAL logs before archiving.

Each WAL log is 16 megabytes in size, and if there are a lot of logs, your disk space will quickly run out. We can solve this problem by gzipping each WAL log before it is sent to our directory. In order to do this, we need to change the values ​​of the archive_command.

with:

cp -i %p /app/postgresql/pg_wal_archive/%f

to:

gzip < %p > /app/postgresql/pg_wal_archive/%f

After making changes, do not forget to restart the database.

Thank you all, I hope that my article helped you in some way.