176
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 postgresql-9.5.service
[root@]#systemctl start postgresql-9.5.service
[root@]#systemctl restart postgresql-9.5.service
[root@]#systemctl status postgresql-9.5.service
[root@]#systemctl reload postgresql-9.5.service
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)