Enable Archiving Of WALs In PostgreSQL For Online Backup And PITR

PostgreSQL maintains Write Ahead Logging to keep records of transactions of database server. And keeps WALs in pg_xlog (Version 9.x and earlier) / pg_wal (Version 10.x and later) directory. However these WALs remains temporarily in this directory and overwrites WALs in Round Robin fashion depending upon configured wal_keep_segments parameter in postgresql.conf file. But these WALs can be archived to different permanent location where it will never be overwritten. Archived WALs plays major role in crash recovery or Point In Time Recovery, Online Backup and also setting up HA and Replication.

In this post we’ll see how to enable archiving of WALs in PostgreSQL and what all parameters to change in postgresql.conf file.

Let’s first verify the archive status of your database server.

[postgres@dcdb ~]$ psql
psql (10.11)
Type "help" for help.

demunix=# show archive_mode;
 archive_mode 
--------------
 off
(1 row)

As we can see, currently the database is not in archive mode.

Since the database is not in archive mode, below are the list of postgresql parameters to be configured.

archive_mode = on (Turns archive mode on)
archive_command = 'cp %p /archive/pgsql/10.11/archived_wals/%f' (Copies WALs from pg_wal to given archive location. You can also specify remote target to store archives using scp or rsync command which may degrade the performance in some cases).
wal_level = replica (Version PostgreSQL 10.x or later).
            hot_standby (Version PostgreSQL 9.x or older).
archive_timeout=900s (Forces a WAL switch after specified time interval and archives it even when WAL file is not fully filled due to less transaction in non peak time. This is important because when there are less transactions and WAL file is not filled for long period of time, but still it has information of some transactions which will be lost in case of crash, if not archived)
max_wal_senders = 10 (This parameter is not mandatory for archiving, but for online backup. We can define the maximum number of wal sender process the PostgreSQL server can spawn for Online Backup and Streaming Replication).

Note : It is good practice to archive your WALs to a dedicated mountpoint and outside of your database cluster.

You can either edit the postgresql.conf file located in data directory, and then change above listed parameters. Or use alter system command to make the changes.

I’m going to do it with alter system command.

[postgres@dcdb ~]$ psql
psql (10.11)
Type "help" for help.

demunix=# alter system set archive_mode to on;
ALTER SYSTEM
demunix=# alter system set archive_command to 'cp %p /archive/pgsql/10.11/archived_wals/%f';
ALTER SYSTEM
demunix=# alter system set wal_level to replica;
ALTER SYSTEM
demunix=# alter system set archive_timeout to '900s';
ALTER SYSTEM
demunix=# alter system set max_wal_senders to 10;
ALTER SYSTEM

Note : When you set parameter using alter system command, values of changed parameters will be saved in postgresql.auto.conf file.

Bounce the database server in order to changes come into effect.

[postgres@dcdb ~]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-03-29 23:45:13.054 IST [7778] LOG:  listening on IPv6 address "::1", port 5432
2020-03-29 23:45:13.054 IST [7778] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2020-03-29 23:45:13.064 IST [7778] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-03-29 23:45:13.075 IST [7778] LOG:  redirecting log output to logging collector process
2020-03-29 23:45:13.075 IST [7778] HINT:  Future log output will appear in directory "log".
 done
server started

Verify the changes.

demunix=# show archive_mode;
 archive_mode 
--------------
 on
(1 row)

demunix=# show archive_command;
               archive_command               
---------------------------------------------
 cp %p /archive/pgsql/10.11/archived_wals/%f
(1 row)

demunix=# show wal_level;
 wal_level 
-----------
 replica
(1 row)

demunix=# show archive_timeout ;
 archive_timeout 
-----------------
 15min
(1 row)

demunix=# show max_wal_senders ;
 max_wal_senders 
-----------------
 10
(1 row)

Verify WAL archiving on the given location.

[postgres@dcdb data]$ ll /archive/pgsql/10.11/archived_wals/
total 49212
-rw-------. 1 postgres postgres 16777216 Mar 29 23:47 000000010000000000000049
-rw-------. 1 postgres postgres 16777216 Mar 29 23:48 00000001000000000000004A
-rw-------. 1 postgres postgres 16777216 Mar 29 23:48 00000001000000000000004B

As we can see, WALs are now archiving on a given location.

There you go. Peace 🙂