DBA Hub

📋Steps in this guide1/3

How to do failover in postgres - DBACLASS DBACLASS

When you master server is crashed or inaccessible, then we can do failover and promote slave server as master. ENVIRONMENT DETAILS: PRIMARY SERVER(MASTER)= 10.20.30.40 STANDBY SERVER(SLAVE) =10.20.30.41 REPLICATION MODE- ASYNC POSTGRES VERSION- 14 Let’s say master becomes inaccessible, then we need to promote the slave using below command. on slave server: -bash-4.2$ which pg_ctl /usr/pgsql-14/bin

postgresql configurationintermediate
by PostgreSQL
13 views
1

Overview

When you master server is crashed or inaccessible, then we can do failover and promote slave server as master. ENVIRONMENT DETAILS: PRIMARY SERVER(MASTER)= 10.20.30.40 STANDBY SERVER(SLAVE) =10.20.30.41 REPLICATION MODE- ASYNC POSTGRES VERSION- 14 Let’s say master becomes inaccessible, then we need to promote the slave using below command. on slave server:
2

Section 2

Then check the logfile: If you see the log, a new timeline ID as been created and database opened. i.e it becomes Primary and ready to accepts transactions(both read and write). But there is not standby now.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
-bash-4.2$ which pg_ctl
/usr/pgsql-14/bin/pg_ctl
-bash-4.2$ <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

Is the server running on that host and accepting TCP/IP connections?
2022-07-11 10:46:34.095 +03 [7788] LOG:  received promote request
2022-07-11 10:46:34.095 +03 [7788] LOG:  redo done at 0/3000AE8 system usage: CPU: user: 0.26 s, system: 0.42 s, elapsed: 70790.02 s
2022-07-11 10:46:34.095 +03 [7788] LOG:  last completed transaction was at log time 2022-07-10 15:54:55.077262+03
2022-07-11 10:46:34.102 +03 [7788] LOG:  selected new timeline ID: 2
2022-07-11 10:46:34.154 +03 [7788] LOG:  archive recovery complete
2022-07-11 10:46:34.167 +03 [7786] LOG:  database system is ready to accept connections
3

Section 3

So when old primary becomes accessible, then you need to either rebuild it as standby using fresh pg_basebackup or using pg_rewind.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!