[Postgres] How to Enable / Disable Archive Mode

 

https://orahow.com/enable-disable-archive-mode-in-postgres/

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

Commentaires

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Translate »