How to Enable / Disable Archive Mode in Postgres

In this PostgreSQL tutorial, we will discuss about enabling WAL Archiving in PostgreSQL. WAL is short for Write Ahead Log. You must enable archive mode in PostgreSQL for PITR.

In short, archiving is the process of creating a backup of all transactions that have occurred in the database so that you can recover database to any point in time.

Listing Databases in PostgreSQL Using psql

What is PostgreSQL WAL Archiving?

Any transaction performed on the database is first written into a WAL file like redo logs in Oracle, then applied to the actual datafiles. As you keep adding and modifying the data in the databases, WAL files keep getting generated. In PostgreSQL terms, copying out generated WAL files is called archiving, which is used for backup and recovery, point-in-time-recovery and replication of database.

• WAL files are stored in $PGDATA/pg_wal in postgresql version 10 and higher whereas $PGDATA/pg_xlog in lower version, where $PGDATA is the data directory or postgres home directory.
• It is the log file, where all the logs are stored of committed and uncommitted transaction.
• It contains max 6 logs, and last one overwrites.
• The write-ahead log is composed of each 16 MB large, which are called segments.
• The filenames will have numerical(0-9) and character(a-z) named in ascending order by PostgreSQL Instance.

Steps to Enable WAL Archiving in Postgres

STEP 1: Verify archive mode is enabled or not using below command.

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

postgres=# show archive_command;
 archive_command
-----------------
 (disabled)
(1 row)

STEP 2: Create archive directory as root user.

bash-4.1$ mkdir -p /scratch/postgres/backup/archive
bash-4.1$ chown postgres:postgres -R /scratch/postgres/backup/archive

STEP 3: Enable PostgreSQL archive_mode on using below command.

postgres=# ALTER SYSTEM SET archive_mode to 'ON';
ALTER SYSTEM

STEP 4: Set the PostgreSQL archive_command which copies WALs from pg_wal to archive location.

postgres=# ALTER SYSTEM SET archive_command TO 'cp %p /scratch/postgres/backup/archive/archive%f';
ALTER SYSTEM

Compress WAL Before Archiving

If archive storage size is a concern, you can use gzip to compress postgres archive logs:

archive_command = ‘gzip < %p > /archive_dir/%f’

Note: You will then need to use gunzip during recovery, like below:

restore_command = ‘gunzip < /mnt/server/archivedir/%f > %p’

STEP 5: Set wal_level, archive_timeout and max_wal_senders parameters.

postgres=# alter system set wal_level=replica;
ALTER SYSTEM
postgres=# alter system set archive_timeout to '900s';
ALTER SYSTEM
postgres=# alter system set max_wal_senders to 10;
ALTER SYSTEM

• wal_level = replica (Version PostgreSQL 10.x or later) and hot_standby (Version PostgreSQL 9.x or older).
• archive_timeout: 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: 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

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

bash-4.1$ export PGDATA=/scratch/postgres_db/db_home
bash-4.1$ /usr/pgsql-12/bin/pg_ctl restart
server started

STEP 7: Force log switch using pg_switch_wal and check whether archive is generating or not.

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

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

STEP 8: Now, verify archive mode settings.

bash-4.1$ ls -lrth /scratch/postgres/backup/archive
total 48M
-rwx------ 1 postgres postgres 16M Nov  7 09:05 archive000000010000000000000006
-rw------- 1 postgres postgres 16M Nov  7 09:06 archive000000010000000000000007
-rw------- 1 postgres postgres 16M Nov  7 09:07 archive000000010000000000000008

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

postgres=#  select name,setting from pg_settings where name like 'archive%';
          name           |                     setting
-------------------------+--------------------------------------------------
 archive_cleanup_command |
 archive_command         | cp %p /scratch/postgres/backup/archive/archive%f
 archive_mode            | on
 archive_timeout         | 900
(4 rows)

postgres=# SELECT * FROM pg_stat_archiver;

STEPS to Disable Archive Mode in PostgreSQL

1: Set the archive_mode=off;

2: Restart the postgres server

postgres=# alter system set archive_mode=off;
ALTER SYSTEM

Hope, this will help you to enable archiving in postgres. After changing any configuration you can use pg_reload_conf() to reload the new changes as mention below.

SELECT pg_reload_conf();