archive_mode on postgresql 12.4

Almost all transactional databases has these transactional logs. In postgresql world we call these transactional logs as wals (write ahead logs). wal is a mechanism to ensure the data integrity. For detailed information about wal in postgres sql you can check the postgresql online documentation Write-Ahead Logging (WAL) .

In this post we are going to cover the wal archiving. For consistent backups and/or replication configuration we definitely need the archives of the online transactional logs. In this purpose postgresql has parameters to set.

archive_mode

archive_mode option declares if postgresql engine is going to archive he wal files or not. Available options are off (default), on and always. As the default is off which is pretty much self explanatory, we are going to cover the on option which enables archiving the wals.

alter system set archive_mode=on;

archive_command

archive_command simply the way that postgresql handles the archiving. By the default this parameter is empty, when archive_mode=on, then you should enter a valid shell command to archive the online wal. Two parameters can be useful here;

%p : the path of the wal file to archive

%f : the name of the wal file to archive

Why i gave these two parameters? Because we are going to use them within out archive_command bash command. Lets give an example;

alter system set archive_command="cp %p /var/lib/pgsql/12/wal_archive/%f";

This means, for every %p wal file to be archived, the file will be copied to /var/lib/pgsql/12/wal_archive directory with the %f name which is the original name of the wal.

Cool :) There is one more parameter for this;

archive_timeout

This is the timeout parameter as the name implies, but for what purpose we are using this. Imagine if your database is not doing too much operation, to fill the wal file for the last 6 hours. The problem is, if your wal files are not switched, then they are not going to be archived.

So we use archive_timeout parameter to archive the wal file even it is not full to guarantee the integrity. By default the value is 900 seconds (15 minutes) and it is enough for the most cases.

alter system set archive_timeout=900; 

Ready to restart our postgresql database.

pg_ctl stop
pg_ctl start

You can switch the wal explicitly by calling the pg_switch_wal() function as follows.

postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/21000160
(1 row)

postgres=# 

You should find the archived wal file under the /var/lib/pgsql/12/wal_archive directory as indicated by the archive_command.

-bash-4.2$ cd /var/lib/pgsql/12/wal_archive/

-bash-4.2$ ls -l
total 196616
-rw-------. 1 postgres postgres 16777216 Jan 25 12:30 000000010000000000000016
-rw-------. 1 postgres postgres 16777216 Jan 25 12:30 000000010000000000000017
-rw-------. 1 postgres postgres      326 Jan 25 12:30 000000010000000000000017.00000028.backup
-rw-------. 1 postgres postgres 16777216 Jan 25 14:59 000000010000000000000018
-rw-------. 1 postgres postgres 16777216 Jan 25 14:59 000000010000000000000019
-rw-------. 1 postgres postgres 16777216 Jan 25 15:16 00000001000000000000001A
-rw-------. 1 postgres postgres 16777216 Jan 25 15:27 00000001000000000000001B
-rw-------. 1 postgres postgres 16777216 Jan 25 15:27 00000001000000000000001C
-rw-------. 1 postgres postgres      339 Jan 25 15:27 00000001000000000000001C.00000028.backup
-rw-------  1 postgres postgres 16777216 Jan 25 15:44 00000001000000000000001D
-rw-------  1 postgres postgres 16777216 Jan 25 15:46 00000001000000000000001E
-rw-------  1 postgres postgres 16777216 Jan 25 15:47 00000001000000000000001F
-rw-------  1 postgres postgres 16777216 Jan 25 15:50 000000010000000000000020
-rw-------  1 postgres postgres 16777216 Jan 25 19:00 000000010000000000000021
-bash-4.2$