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_mode | off --- >>>
archive_timeout | 0
(4 rows)
postgres=# show wal_level
wal_level
-----------
minimal
(1 row)
If wal_le
2. Now alter below parameters:
Alternatively you can change below parameter directly in the postgres.conf file:
postgres=# alter system set archive_mode=on;
ALTER SYSTEM
postgres=# alter system set archive_command='test ! -f /var/lib/edb/as12/archive/%f && cp %p /var/lib/edb/as12/archive/%f';
ALTER SYSTEM
postgres=# alter system set wal_level=replica;
ALTER SYSTEM
3. Now restart the postgres instance:
[root@]# systemctl stop edb-as-12
[root@]# systemctl start edb-as-12
-- ALTERNATIVELY YOU CAN USE PG_CTL COMMAND:
export PGDATA=/var/lib/edb/as12/data
pg_ctl stop
pg_ctl start
4. Now check archive mode:
postgres=# select name,setting from pg_settings where name like 'archive%';
name | setting
-------------------------+------------------------------------------------------------------------------
archive_cleanup_command |
archive_command | test ! -f /var/lib/edb/as12/archive/%f && cp %p /var/lib/edb/as12/archive/%f
archive_mode | on
archive_timeout | 0
(4 rows)
postgres=# show wal_level;
wal_level
-----------
replica
(1 row)
5. Do manual log switch 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)
[enterprisedb@localhost archive]$ pwd
/var/lib/edb/as12/archive
-rw-------. 1 enterprisedb enterprisedb 16777216 Oct 20 19:44 00000001000000000000001C
-rw-------. 1 enterprisedb enterprisedb 16777216 Oct 20 19:56 00000001000000000000001D
STEPS TO DISABLE ARCHIVE MODE:
1. Set archive_mode to off:
postgres=# alter system set archive_mode=off;
ALTER SYSTEM
2. Now restart the postgres instance:
[root@]# systemctl stop edb-as-12
[root@]# systemctl start edb-as-12
3. Check archive mode:
postgres=# select name,setting from pg_settings where name like 'archive%';
name | setting
-------------------------+------------------------------------------------------------------------------
archive_cleanup_command |
archive_command | test ! -f /var/lib/edb/as12/archive/%f && cp %p /var/lib/edb/as12/archive/%f
archive_mode | off
archive_timeout | 0
(4 rows)
i need to compress the wal files and archive files could you pls help me
You can refer below link for this compression solution.
https://www.percona.com/blog/2020/02/13/compression-of-postgresql-wal-archives-becoming-more-important/#:~:text=Compressing%20PostgreSQL%20WALs&text=For%20example%2C%20in%20pg_backrest%2C%20we,the%20gzip%20behind%20the%20scene.&text=1-,ALTER%20SYSTEM%20SET%20archive_command%20%3D%20'WALG_FILE_PREFIX%3D%2Fpath%2Fto%2F,lz4%20compression%20of%20WAL%20files.