DBA Hub

📋Steps in this guide1/5

How to do switchover in postgres - DBACLASS DBACLASS

In this article we will explain how to do switchover manually . Switchover means, change of role between master and slave. postgres doesn’t provide any inbuilt command to do switchover . We will use the pg_ctl promote command with some additional steps to do the switchover. Note – There are few third party tools like […]

postgresql configurationintermediate
by PostgreSQL
13 views
1

Overview

In this article we will explain how to do switchover manually . Switchover means, change of role between master and slave. postgres doesn’t provide any inbuilt command to do switchover . We will use the pg_ctl promote command with some additional steps to do the switchover. Note – There are few third party tools like EDB FAILOVER MANAGER(EFM) and REPMGR , which can be used to manage the replication(like switchover, failover). MASTER                           – > 10.20.30.6 SLAVE                               – > 10.20.30.7 POSTGRES VERSION – > 14
2

Section 2

1. Check whether replication process and the sync status: Now we will proceed with switchover 2. Shutdown the MASTER: [ ON MASTER SERVER 10.20.30.6 ]

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
-- Run on master server(10.20.30.6 )

ostgres=# <span style="color: #993300;"><strong>select * from pg_stat_replication;</strong></span>
-[ RECORD 1 ]----+------------------------------
pid              | 13132
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      | 10.20.30.6
client_hostname  |
client_port      | 18178
backend_start    | 2022-07-11 16:44:53.917819+03
backend_xmin     |
state            | streaming
sent_lsn         | 0/4652FA0
write_lsn        | 0/4652FA0
flush_lsn        | 0/4652FA0
replay_lsn       | 0/4652FA0
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2022-07-12 17:00:50.440717+03



postgres=# <span style="color: #993300;"><strong>SELECT pg_wal_lsn_diff(sent_lsn, replay_lsn) from pg_stat_replication;</strong></span>
-[ RECORD 1 ]---+--
pg_wal_lsn_diff | 0



-- Run on SLAVE server(10.20.30.7) 


postgres=# <span style="color: #993300;"><strong>select * from pg_stat_wal_receiver;</strong></span>
-[ RECORD 1 ]---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 10229
status                | streaming
receive_start_lsn     | 0/4000000
receive_start_tli     | 2
written_lsn           | 0/4652FA0
flushed_lsn           | 0/4652FA0
received_tli          | 2
last_msg_send_time    | 2022-07-12 17:00:32.22551+03
last_msg_receipt_time | 2022-07-12 17:01:30.559348+03
latest_end_lsn        | 0/4652FA0
latest_end_time       | 2022-07-11 16:50:36.695865+03
slot_name             |
sender_host           | 10.20.30.7
sender_port           | 5444
conninfo              | user=postgres password=******** channel_binding=prefer dbname=replication host=10.20.30.7 port=5444 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

postgres=# <span style="color: #993300;"><strong>select pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();</strong></span>
-[ RECORD 1 ]-----------------+-----------------------------
pg_last_wal_receive_lsn       | 0/4652FA0
pg_last_wal_replay_lsn        | 0/4652FA0
pg_last_xact_replay_timestamp | 2022-07-11 16:45:29.86949+03
3

Section 3

3. Promote slave as new master:[ RUN ON SLAVE SERVER 10.20.30.7 ] We can see it has been opened with read , write with a new timeline id. But replication is broken. i.e currently we have only master server, no slave server. So for the switchover to successful, we need to make old master as slave

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
-bash-4.2$ <span style="color: #993300;"><strong>/usr/pgsql-14/bin/pg_ctl stop  -D /oracle/pg_data</strong></span>
waiting for server to shut down.... done
server stopped

[postgres]$<span style="color: #993300;"><strong> /usr/pgsql-14/bin/pg_ctl  promote -D /oracle/pg_data</strong></span>
waiting for server to promote.... done
server promoted


-- CHECK THE LOG FILE:

                Is the server running on that host and accepting TCP/IP connections?
2022-07-12 17:03:43.890 +03 [10225] LOG:  received promote request
2022-07-12 17:03:43.890 +03 [10225] LOG:  redo done at 0/5000028 system usage: CPU: user: 0.39 s, system: 0.66 s, elapsed: 87473.32 s
2022-07-12 17:03:43.890 +03 [10225] LOG:  last completed transaction was at log time 2022-07-11 16:45:29.86949+03
2022-07-12 17:03:43.906 +03 [10225] LOG:  selected new timeline ID: 3
2022-07-12 17:03:43.997 +03 [10225] LOG:  archive recovery complete
2022-07-12 17:03:44.020 +03 [10223] LOG:  database system is ready to accept connections
4

Section 4

4. On OLD MASTER( i.e NEW SLAVE 10.20.30.6) 5 . START NEW SLAVE ( 10.20.30.6):

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Create a standby.signal file(empty)

<span style="color: #993300;"><strong>touch /oracle/pg_data/standby.signal
</strong></span>
-- Update the postgres.auto.conf file with NEW MASTER SERVER DETAILS

-bash-4.2$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=postgres password=postgres channel_binding=prefer host=10.20.30.6 port=5444 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
wal_log_hints = 'on'

/usr/pgsql-14/bin/pg_ctl start -D /oracle/pg_data
5

Section 5

6. Check whether master-slave replication is working fine or not: You can do some data changes on MASTER, and check whether they are getting replicated to SLAVE or not. We have switched over the roles of MASTER and SLAVE with few simple steps.

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
-- ON MASTER:
postgres=# \x
Expanded display is on.
postgres=# <span style="color: #993300;"><strong>select * from pg_stat_replication;</strong></span>
-[ RECORD 1 ]----+------------------------------
pid              | 6965
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      | 10.20.30.7
client_hostname  |
client_port      | 51244
backend_start    | 2022-07-13 10:29:03.01259+03
backend_xmin     |
state            | streaming
sent_lsn         | 0/9600C20
write_lsn        | 0/9600C20
flush_lsn        | 0/9600C20
replay_lsn       | 0/9600C20
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2022-07-13 10:31:10.636569+03


-- ON SLAVE:

postgres=# <span style="color: #993300;"><strong>select * from pg_stat_wal_receiver;</strong></span>
-[ RECORD 1 ]---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 30164
status                | streaming
receive_start_lsn     | 0/5000000
receive_start_tli     | 3
written_lsn           | 0/8000060
flushed_lsn           | 0/8000060
received_tli          | 3
last_msg_send_time    | 2022-07-13 10:30:32.937076+03
last_msg_receipt_time | 2022-07-13 10:29:33.399633+03
latest_end_lsn        | 0/8000060
latest_end_time       | 2022-07-13 10:30:02.798967+03
slot_name             |
sender_host           | 10.20.30.6
sender_port           | 5444
conninfo              | user=postgres password=postgres channel_binding=prefer host=10.20.30.6 port=5444 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

-- ON MASTER:
postgres=# create table test101 as select * from test100;
SELECT 88065


-- ON SLAVE:
postgres=# select  count(*) from test101;
-[ RECORD 1 ]
count | 88065

Comments (0)

Please to add comments

No comments yet. Be the first to comment!