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=# <strong>select name,setting from pg_settings where name like 'archive%' ;</strong>
name | setting
-------------------------+------------
archive_cleanup_command |
archive_command | (disabled)
<span style="color: #ff0000;"><strong> archive_mode | off --- >>>
</strong></span> archive_timeout | 0
(4 rows)
postgres=# <strong>show wal_level</strong>
wal_level
-----------
<span style="color: #ff0000;"><strong>minimal</strong></span>
(1 row)
If wal_le
2. Now alter below parameters:
Alternatively you can change below parameter directly in the postgres.conf file:
postgres=# <strong>alter system set archive_mode=on;</strong>
ALTER SYSTEM
postgres=# <strong>alter system set archive_command='test ! -f /var/lib/edb/as12/archive/%f && cp %p /var/lib/edb/as12/archive/%f';</strong>
ALTER SYSTEM
postgres=# <strong>alter system set wal_level=replica;</strong>
ALTER SYSTEM
3. Now restart the postgres instance:
[root@]# <strong>systemctl stop edb-as-12</strong>
[root@]# <strong>systemctl start edb-as-12</strong>
-- 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=# <strong>select name,setting from pg_settings where name like 'archive%';</strong>
name | setting
-------------------------+------------------------------------------------------------------------------
archive_cleanup_command |
archive_command | test ! -f /var/lib/edb/as12/archive/%f && cp %p /var/lib/edb/as12/archive/%f
<span style="color: #ff0000;"><strong> archive_mode | on
</strong></span> archive_timeout | 0
(4 rows)
postgres=# <strong>show wal_level;</strong>
wal_level
-----------
<span style="color: #ff0000;"><strong>replica</strong></span>
(1 row)
5. Do manual log switch and check whether archive is generating or not:
postgres=# <strong>select pg_switch_wal();</strong>
pg_switch_wal
---------------
0/1D392648
(1 row)
postgres=# <strong>select pg_switch_wal();</strong>
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=# <strong>alter system set archive_mode=off;</strong>
ALTER SYSTEM
2. Now restart the postgres instance:
[root@]# <strong>systemctl stop edb-as-12</strong>
[root@]# <strong>systemctl start edb-as-12</strong>
3. Check archive mode:
postgres=# <strong>select name,setting from pg_settings where name like 'archive%';</strong>
name | setting
-------------------------+------------------------------------------------------------------------------
archive_cleanup_command |
archive_command | test ! -f /var/lib/edb/as12/archive/%f && cp %p /var/lib/edb/as12/archive/%f
<span style="color: #ff0000;"><strong> archive_mode | off
</strong></span> 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.