DBA Hub

📋Steps in this guide1/2

How to enable and disable archive mode in postgres - DBACLASS DBACLASS

Find detailed steps for enabling disabling archive mode in postgres. check the archive_mode parameter in postgres.conf file and then modify to

postgresql configurationintermediate
by PostgreSQL
16 views
1

STEPS TO ENABLE ARCHIVE MODE IN POSTGRES:

1. Check archive setting in the postgres config file: If wal_le 2. Now alter below parameters: Alternatively you can change below parameter directly in the postgres.conf file: 3. Now restart the postgres instance: 4. Now check archive mode: 5. Do manual log switch and check whether archive is generating or not:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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)

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

[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

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)

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
2

STEPS TO DISABLE ARCHIVE MODE:

1. Set archive_mode to off: 2. Now restart the postgres instance: 3. Check archive mode:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
postgres=# <strong>alter system set archive_mode=off;</strong>
ALTER SYSTEM

[root@]# <strong>systemctl stop edb-as-12</strong>
[root@]# <strong>systemctl start edb-as-12</strong>

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)

Comments (0)

Please to add comments

No comments yet. Be the first to comment!