📚 PostgreSQL Promote STB To PRD¶

🔍 Prepare：¶

## Preparing the Master and Slave Server：

Environment name Version IP Folder

OS Rocky Linux 9.x

Master DB（PSDBDEV01） PostgreSQL 16.X 10.38.36.110 /pgdata/SID

Slave DB（PSDBDEV02） PostgreSQL 16.X 10.38.36.111 /pgdata/SID

1. Promote PostgreSQL¶

1.1 Modify postgresql.conf on Master DB server

vi /pgdata/SID/postgresql.conf
echo "primary_conninfo = 'host=10.38.36.111 port=5436 user=postgres'" >> /pgdata/SID/postgresql.conf
pg_ctl restart -D /pgdata/SID

1.2 Modify postgresql.conf on Standby DB server

vi /pgdata/SID/postgresql.conf
echo "primary_conninfo = 'host=10.38.36.111 port=5435 user=postgres'" >> /pgdata/SID/postgresql.conf
pg_ctl restart -D /pgdata/SID

Notice： ADD PRD/STB IP For future failover convenience

1.3 Stop the DB service for PSDBDEV01

pg_ctl stop -D /pgdata/HQRPAD1 -l logfile -m fast

1.4 Execute the SQL for PSDBDEV02
```
pg_controldata /pgdata/HQRPAD1 |grep state
psql -p 5435
```
```
SELECT pg_promote(true,60);
```
Note:pg_promote(wait boolean DEFAULT true, wait_seconds integer DEFAULT 60)
1.5 Make sure PSDBDEV02 in Master or not
```
pg_controldata /pgdata/HQRPAD1 |grep state
```
In production mean Master

1.6 Go to PSDBDEV01 to touch the standby.signal file

cd /pgdata/HQRPAD1
touch standby.signal
pg_controldata /pgdata/HQRPAD1 |grep state
ll

1.7 Start DB service for PSDBDEV01

pg_ctl start -D /pgdata/HQRPAD1 -l logfile

1.8 Make sure PSDBDEV01 in Slave or not
```
pg_controldata /pgdata/HQRPAD1 |grep state
```
In archive recovery mean Slave
1.9 Check pg_stat_replication in PSDBDEV02
```
psql -p 5435
```
```
select * from pg_stat_replication;
```
Means data has transmitted to PSDEV01 successfully

2. Promote Back(Switch over)¶

2.1 Stop the DB service for PSDBDEV02

pg_ctl stop -D /pgdata/HQRPAD1 -l logfile

2.2 Execute the SQL for PSDBDEV01

psql -p 5435

SELECT pg_promote(true,60);

2.3 Make sure PSDBDEV01 in Master or not
```
pg_controldata /pgdata/HQRPAD1 |grep state
```
In production mean Master
2.4 Go to PSDBDEV02 to touch the standby.signal file
```
cd /pgdata/HQRPAD1
touch standby.signal
ll
```

2.5 Go to PSDBDEV02 to touch the standby.signal file

pg_ctl start -D /pgdata/HQRPAD1 -l logfile
ll

2.6 Make sure PSDBDEV02 in Slave or not
```
pg_ctl start -D /pgdata/HQRPAD1 -l logfile
ll
```
In archive recovery mean Slave
2.7 Check pg_stat_replication in PSDBDEV01
```
psql -p 5435
```
```
select * from pg_stat_replication;
```
Means data has transmitted to PSDEV02 successfully

Environment name	Version	IP	Folder
OS	Rocky Linux 9.x
Master DB（PSDBDEV01）	PostgreSQL 16.X	10.38.36.110	/pgdata/SID
Slave DB（PSDBDEV02）	PostgreSQL 16.X	10.38.36.111	/pgdata/SID