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();
Laisser un commentaire