Configure WAL Archiving in PostgreSQL

Jamsher Khan 10 mins6
()

Configure WAL Archiving in PostgreSQL


WAL is short for Write Ahead Log. In PostgreSQL terms, copying out generated WAL files is called archiving,
If you save a copy of each WAL file that was generated, you could replay back the entire set of changes to another server. Saving all generated WAL files to a safe offline location essentially becomes incremental backup and can be used to perform PITR (Point-In-Time-Recovery). WAL files reside in $PGDATA/pg_wal.


Required Parameters


wal_level: Determines how much information is written to the WAL. The default value is a replica, which writes enough data to support WAL archiving and replication, including running read-only queries on a standby server.


wal_keep_size: An integer parameter that define the size of the log files segments that should be kept in the pg_wal directory.  wal_keep_size = wal_keep_segments * wal_segment_size (typically 16MB).

archive_mode: Indicates whether to perform the archive operation and can be selected as off (off), on (start), and always (always on), the default value is off (off).


archive_command: The command set by the administrator for archiving WAL logs. In the command for archiving, the predefined variable “%p” is used to refer to the WAL full path file name that needs to be archived, “%f” indicates the file name without a path. When each WAL segment file is archived, the command specified by archive_command will be called. When the archive command returns 0, PostgreSQL considers the file to be successfully archived, and then deletes or recycles the WAL segment file. Otherwise, if a non-zero value is returned, PostgreSQL will assume that the file has not been successfully archived and will periodically retry until it succeeds.


archive_timeout: Indicates the archiving period. When the time set by this parameter is exceeded, the WAL segment is forcibly switched. The default value is 0 (indicating that the function is disabled).


Parameters Default Values 

[root@test-machine01 system]# su - postgres
Last login: Tue May 18 12:19:26 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ psql
psql (13.1)
Type "help" for help.
postgres=#
postgres=# select name,setting,unit from pg_settings where name in ('wal_level','wal_keep_size');
     name      | setting | unit
---------------+---------+------
 wal_keep_size | 0       | MB
 wal_level     | replica |
(2 rows)

postgres=# select name,setting,unit from pg_settings where name in ('archive_mode','archive_command','archive_timeout');
      name       |  setting   | unit
-----------------+------------+------
 archive_command | (disabled) |
 archive_mode    | off        |
 archive_timeout | 0          | s
(3 rows)

postgres=#


Follow below Steps to configure WAL Archiving


Step 1. Create directory Structure: We will be using /u01/wal_backup directory to store our WAL archives. Grant ownership to user postgres.

[root@test-machine02 ~]#
[root@test-machine02 ~]# cd /u01
[root@test-machine02 u01]# mkdir wal_backup
[root@test-machine02 wal_backup]# pwd
/u01/wal_backup
[root@test-machine02 13]# ls -ld /u01/wal_backup
drwxr-xr-x. 2 root root 6 May  4 12:31 /u01/wal_backup

[root@test-machine02 13]# chown -R postgres:postgres /u01/wal_backup
[root@test-machine02 13]#

[root@test-machine02 13]#  ls -ld /u01/wal_backup
drwxr-xr-x. 2 postgres postgres 6 May  4 12:31 /u01/wal_backup
[root@test-machine02 13]#
[root@test-machine02 wal_backup]#
[root@test-machine02 wal_backup]# ls -l /u01/wal_backup/
total 0
[root@test-machine02 wal_backup]#


Step 2. Configure Archiving Parameters: Update the below archive parameters and restart the postgresql service.

postgres=# alter system set archive_mode = on;
ALTER SYSTEM
postgres=# alter system set archive_command = 'test ! -f /u01/wal_backup/%f && cp %p /u01/wal_backup/%f';
ALTER SYSTEM
postgres=# alter system set archive_timeout= '6 h';
ALTER SYSTEM
postgres=#
postgres=# alter system set wal_keep_size='160 MB';
ALTER SYSTEM
postgres=#
postgres=#
postgres=# \quit
-bash-4.2$ exit
logout
[root@test-machine02 wal_backup]#
[root@test-machine02 wal_backup]# systemctl list-unit-files |grep  post
postfix.service                               enabled
postgresql-13.service                         enabled
[root@test-machine02 wal_backup]#
[root@test-machine02 wal_backup]# systemctl restart postgresql-13.service
[root@test-machine02 wal_backup]#


Step 3. Confirm Parameter Changes.

postgres=#
postgres=# select name,setting,unit from pg_settings where name in ('wal_level','wal_keep_size');
     name      | setting | unit
---------------+---------+------
 wal_keep_size | 160     | MB
 wal_level     | replica |
(2 rows)

postgres=#
postgres=# select name,setting,unit from pg_settings where name in ('archive_mode','archive_command','archive_timeout');
      name       |                         setting                          | unit
-----------------+----------------------------------------------------------+------
 archive_command | test ! -f /u01/wal_backup/%f && cp %p /u01/wal_backup/%f |
 archive_mode    | on                                                       |
 archive_timeout | 21600                                                    | s
(3 rows)

postgres=#


Step 4: Verify Archiving in Action: Generate some load in PostgreSql Server by importing sample DB, Then you will notice WAL files are getting archived to Location: /u01/wal_backup/, You can also list the archive_status folder in $PGDATA/pg_wal to get status of WAL Archive.

[root@test-machine02 archive_status]# ls -l /var/lib/pgsql/13/data/pg_wal/archive_status
total 0
-rw-------. 1 postgres postgres 0 May 20 16:44 000000010000000000000002.done
-rw-------. 1 postgres postgres 0 May 20 16:46 000000010000000000000003.done
-rw-------. 1 postgres postgres 0 May 20 16:46 000000010000000000000004.done
-rw-------. 1 postgres postgres 0 May 20 16:47 000000010000000000000005.done
[root@test-machine02 archive_status]#


[root@test-machine02 archive_status]# ls -l /u01/wal_backup/
total 65536
-rw-------. 1 postgres postgres 16777216 May 20 16:44 000000010000000000000002
-rw-------. 1 postgres postgres 16777216 May 20 16:46 000000010000000000000003
-rw-------. 1 postgres postgres 16777216 May 20 16:46 000000010000000000000004
-rw-------. 1 postgres postgres 16777216 May 20 16:47 000000010000000000000005
[root@test-machine02 archive_status]#


Step 5. Verify from Sql Query: Use below queries to monitor archiving.

postgres=#
postgres=# \x
Expanded display is on.
postgres=#
postgres=# SELECT *,
postgres-#     current_setting('archive_mode')::BOOLEAN
postgres-#         AND (last_failed_wal IS NULL
postgres(#             OR last_failed_wal <= last_archived_wal)
postgres-#         AS is_archiving,
postgres-#     CAST (archived_count AS NUMERIC)
postgres-#         / EXTRACT (EPOCH FROM age(now(), stats_reset))
postgres-#         AS current_archived_wals_per_second
postgres-# FROM pg_stat_archiver;
-[ RECORD 1 ]--------------------+------------------------------
archived_count                   | 7
last_archived_wal                | 000000010000000000000008
last_archived_time               | 2021-05-20 17:08:41.194777+03
failed_count                     | 0
last_failed_wal                  |
last_failed_time                 |
stats_reset                      | 2021-04-26 16:29:37.623539+03
is_archiving                     | t
current_archived_wals_per_second | 3.3678000776867078e-06

postgres=#


postgres=# select * from pg_stat_archiver;
-[ RECORD 1 ]------+------------------------------
archived_count     | 7
last_archived_wal  | 000000010000000000000008
last_archived_time | 2021-05-20 17:08:41.194777+03
failed_count       | 0
last_failed_wal    |
last_failed_time   |
stats_reset        | 2021-04-26 16:29:37.623539+03

postgres=#


This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

Recent articles

 

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

6 thoughts on “Configure WAL Archiving in PostgreSQL”

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Articles