How to Enable / Disable Archive Mode in PostgreSQL - Query

 

How to Enable / Disable Archive Mode in PostgreSQL

 

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.

WAL is automatically enabled; no action is required from the administrator except ensuring that the disk-space requirements for the WAL files are met, and that any necessary tuning is done (see Section 30.5).

WAL records are appended to the WAL files as each new record is written. The insert position is described by a Log Sequence Number (LSN) that is a byte offset into the WAL, increasing monotonically with each new record. LSN values are returned as the datatype pg_lsn. Values can be compared to calculate the volume of WAL data that separates them, so they are used to measure the progress of replication and recovery.

WAL files are stored in the directory pg_wal under the data directory, as a set of segment files, normally each 16 MB in size (but the size can be changed by altering the --wal-segsize initdb option). Each segment is divided into pages, normally 8 kB each (this size can be changed via the --with-wal-blocksize configure option). The WAL record headers are described in access/xlogrecord.h; the record content is dependent on the type of event that is being logged. Segment files are given ever-increasing numbers as names, starting at 000000010000000000000001. The numbers do not wrap, but it will take a very, very long time to exhaust the available stock of numbers.

It is advantageous if the WAL is located on a different disk from the main database files. This can be achieved by moving the pg_wal directory to another location (while the server is shut down, of course) and creating a symbolic link from the original location in the main data directory to the new location.

Below are the steps for enabling archive mode or wal archiving in postgres.

STEPS TO ENABLE ARCHIVE MODE IN POSTGRES:

1. Check archive setting in the postgres config file:

postgres=# select name,setting from pg_settings where name like 'archive%' ;

          name           |  setting   

-------------------------+------------

 archive_cleanup_command | 

 archive_command         | (disabled)

 archive_library         | 

 archive_mode            | off --->>>> 

 archive_timeout         | 0

(5 rows)

postgres=# show archive_command;

 archive_command 

-----------------

 (disabled)

(1 row)

postgres=# show wal_level;

 wal_level 

-----------

 replica

(1 row)

STEP 2: Create archive directory 

-bash-4.2$ mkdir 15_cluster_db1_archive

-bash-4.2$ 

-bash-4.2$ cd 15_cluster_db1_archive

-bash-4.2$ pwd

/u02/PostgreSQL/15_cluster_db1_archive

-bash-4.2$ ls -lrt

total 0

-bash-4.2$ mkdir archive

-bash-4.2$ 

-bash-4.2$ cd archive/

-bash-4.2$ pwd

/u02/PostgreSQL/15_cluster_db1_archive/archive

-bash-4.2$ 

-bash-4.2$ 

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 /u02/PostgreSQL/15_cluster_db1_archive/archive/archive%f';

ALTER SYSTEM

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.2$ /usr/pgsql-15/bin/pg_ctl -D /u02/PostgreSQL/15_cluster_db1 restart

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/2000210

(1 row)

postgres=# select pg_switch_wal();

 pg_switch_wal 

---------------

 0/3000078

(1 row)

STEP 8: Now, verify archive mode settings.

-bash-4.2$ pwd

/u02/PostgreSQL/15_cluster_db1_archive/archive

-bash-4.2$ ls -lrt

total 32768

-rw------- 1 postgres postgres 16777216 Jan 22 00:06 archive000000010000000000000002

-rw------- 1 postgres postgres 16777216 Jan 22 00:07 archive000000010000000000000003

postgres=# show archive_mode;

 archive_mode

--------------

 on

(1 row)

postgres=#  select name,setting from pg_settings where name like 'archive%';

postgres=# select name,setting from pg_settings where name like 'archive%';

          name           |                            setting                             

-------------------------+----------------------------------------------------------------

 archive_cleanup_command | 

 archive_command         | cp %p /u02/PostgreSQL/15_cluster_db1_archive/archive/archive%f

 archive_library         | 

 archive_mode            | on

 archive_timeout         | 900

(5 rows)

postgres=# SELECT * FROM pg_stat_archiver;

 archived_count |    last_archived_wal     |        last_archived_time        | failed_count | last_failed_wal | last_failed_time |         

  stats_reset            

----------------+--------------------------+----------------------------------+--------------+-----------------+------------------+---------

-------------------------

              2 | 000000010000000000000003 | 2024-01-22 00:07:12.504254+05:30 |            0 |                 |                  | 2024-01-

21 14:48:53.500076+05:30

(1 row)

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();