How to Enable WAL Log Archiving in PostgreSQL: Step-by-Step Guide
Archiving WAL (Write-Ahead Logging) logs in PostgreSQL is an important aspect for ensuring the reliability and recovery of database data. Enabling and configuring WAL archiving allows you to store changes that have occurred to the data and use them for recovery in the event of a failure. This process is necessary if you need to maintain backup copies of data or organize a system rollback to a certain point in time.
In PostgreSQL, absolutely any transaction that is executed is first written to a WAL log file, and then applied to the actual database data files. As long as you continue to add and change data in the database, WAL files continue to be generated. In PostgreSQL terms, copying the generated WAL logs is called archiving.
In this article, we will look at how to enable WAL log archiving in PostgreSQL.
The article on the page is based on my many years of experience with this technology and is presented to you as a short instruction, but if you want to get acquainted with it in more detail, then I recommend that you go to the official documentation on PostgreSQL.
Contents of the article:
- Checking the status of the archiving mode.
- Create a directory for archived WAL logs.
- Turn on archiving mode.
- Restart the database.
- Checking the result.
1. Checking the status of the archiving mode.
The archive_mode parameter is responsible for archiving WAL logs in PostgreSQL. The parameter can have two values, either on or off. To check whether 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 we can see in both results, the archive_mode parameter value is off, which means the archiving mode is disabled.
2. Create a directory for archived WAL logs.
Before we enable the WAL log archiving mode, we first need to create a directory somewhere where PostgreSQL will place WAL logs. I usually create this directory next to the directory where we have the database deployed. The directory will be called pg_wal_archive.
Create a directory using the mkdir command.
$. mkdir -p /app/postgresql/pg_wal_archive
3. Turn on archiving mode.
After we have created a directory for our WAL logs, we can now enable archiving mode on the database. There are two important parameters responsible for enabling the archiving mode in PostgreSQL, the first parameter is called archive_mode, and the second is archive_command.
The archive_mode parameter needs to be switched from off to on. The archive_command parameter needs to be set to a local command that will be executed to archive the completed WAL segment of the log. These parameters need to be added to the main configuration file called postgresql.conf.
archive_mode = on
archive_command = 'cp -i %p /app/postgresql/pg_wal_archive/%f'
The variables are static, so you must restart the database after making changes.
4. Restart the database.
After we have changed the archiving parameters, we must restart the database for the changes to apply.
$. pg_ctl restart
5. Checking the result.
After restarting the database, we check the result. First, we execute a sql query to check the archiving parameters.
sql> SELECT name, setting FROM pg_settings WHERE name like 'archive%';
As we can see, the archive_mode mode has the value on, which means that archiving of WAL logs is enabled.
Secondly, we will check how we transfer WAL logs to our directory that we created.
$. cd /app/postgresql/pg_wal_archive
As we can see, WAL logs are successfully transferred to our created directory.
In the end, archiving WAL logs is not just a technical process, but an effective way to minimize risks and increase system fault tolerance. By implementing archiving, you can not only improve data protection, but also ensure sustainable development of your PostgreSQL-based infrastructure.
