Skip to content

PostgreSQL Standby DB Creation

1. Prepare the master

  • 1.1 Create master DB (refer to PostgreSQL-DB-Creation)

  • 1.2 Make sure you had set wal_level and max_wal_senders on master DB

cd /pgdata/SEQAS
cat postgresql.conf |grep wal_level
cat postgresql.conf |grep wal_senders
cat postgresql.conf |grep hot_standby
pg_ctl restart -D /pgdata/SEQAS

Notice： wal_level = replica # Suggest >= replica wal_senders = 30 # Suggest >= 100 hot_standby = on # Suggest >= on

  • 1.3 Add PRD/STB IP in pg_hba.conf
echo "host    replication     postgres        10.41.xx.xx/24        trust" >> /pgdata/SEQAS/pg_hba.conf
echo "host    replication     postgres        10.41.xx.xx/24        trust" >> /pgdata/SEQAS/pg_hba.conf
pg_ctl reload -D /pgdata/SEQAS

Notice： Add PRD/STB IP For future failover convenience

image-20260202131116770

2. Build STB DB

  • 2.1 Create slave DB (refer to PostgreSQL-DB-Creation)

  • 2.2 Run base backup on Standby server.(before backup, please also check the pg_hba.conf if need added STB IP)

pg_basebackup -h [Primary DB IP] -U postgres -p 5432 -F p -P -X stream -R -D /pgdata/SEQAS -l ZSQAS.log

image-20260202142421422

  • 2.3 Start STB DB
pg_ctl start -D /pgdata/ZSARMP -l logfile

3. Check sync status

  • 3.1 Check standby process (standby)
ps -ef |grep postgres

image-20260202142554970

  • 3.2 Check replication status in Master Server (Master server)
su - postgres
psql -p 5432

select client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn from pg_stat_replication;

image-20260202142724019

  • 3.3 Check master DB log status
SELECT pg_current_wal_lsn();

image-20260202142814986

  • 3.4 Check slave DB log status
SELECT pg_last_wal_receive_lsn();

image-20260202142836982

  • 3.5 Check cluster status @Master Server
pg_controldata /pgdata/SEQAS | grep cluster

image-20260202142954644

  • 3.6 Check cluster status @Slave Server
pg_controldata /pgdata/SEQAS | grep cluster

image-20260202143029599

  • 3.7 You can login slave DB for query only (like Oracle ADG

4. Monitor setup

  • 4.1 Create PRD/STB sync status check script (morning and afternoon)
vim /pgdata/wistron/script/ZSARMP/check.sh

add below content

###设置基础环境变量###
PATH=$PATH:/pgsql/pgsql-16/bin:
###设置需要备份DB的标识###
SID=ZSARMP
###设置PGDATA环境变量###
pgdata=/pgdata/ZSARMP
###设置脚本日志存放路径###
scriptfolder=/pgdata/wistron/script/$SID
###设置端口号###
PG_PORT=`cat $pgdata/postmaster.pid|sed -n 4p`
###设置PRD 实例IP地址###
host1="10.41.20.97"
###设置STB 实例IP地址###
host2="10.41.20.107"
###设置登录数据库postgres账号密码###
export PGPASSWORD='xxxxxx'
hostname=`hostname`

echo -e "###### PostgreSQL DB $SID PRD check at `date` ######\n" >$scriptfolder/pgstandbycheck.log
echo -e "Postgres $SID MASTER:" >>$scriptfolder/pgstandbycheck.log
$scriptfolder/check-prd.sh >>$scriptfolder/pgstandbycheck.log
echo -e "\nPostgres $SID SLAVE:" >>$scriptfolder/pgstandbycheck.log
$scriptfolder/check-stb.sh >>$scriptfolder/pgstandbycheck.log
echo -e "\n###### PostgreSQL DB $SID PRD check complete ######\n" >>$scriptfolder/pgstandbycheck.log

PRD=$(psql  -h $host1 -p $PG_PORT -c  'select  * from pg_stat_replication;'| sed -n '3,$'p |tac  | sed -n '3,$'p | sed s/" *"//g )
STB=$(psql  -h $host2 -p $PG_PORT -c  'select  * from pg_stat_wal_receiver;'| sed -n '3,$'p |tac  | sed -n '3,$'p | sed s/" *"//g )

    if [ ! "$PRD" ];then
          status=Warning
      echo "Warning!Please Check Database Status" >>$scriptfolder/pgstandbycheck.log
    elif [ ! "$STB" ];then
          status=Warning
      echo "Warning!Please Check Database Status" >>$scriptfolder/pgstandbycheck.log
    else
          status=Health
echo -e "\n###### PostgreSQL DB $SID IS HEALTH ######\n" >>$scriptfolder/pgstandbycheck.log
    fi

now=`date "+%Y-%m-%d %H:%M:%S"`

mail -s "$SID Sync Check $status in $now" -r "SE_PG_Standby_Check@$hostname.wistron.com" "mzl320.wzs.wistron@wistron.com" < $scriptfolder/pgstandbycheck.log

cat $scriptfolder/pgstandbycheck.log
  • 4.2 Create PRD/STB sync status check script (every 15min)
vim /pgdata/wistron/script/ZSARMP/check15min.sh

add below content

###设置基础环境变量###
PATH=$PATH:/usr/pgsql-16/bin:
###设置需要备份DB的标识###
SID=ZSARMP
###设置PGDATA环境变量###
pgdata=/pgdata/ZSARMP
###设置脚本日志存放路径###
scriptfolder=/pgdata/wistron/script/$SID
###设置端口号###
PG_PORT=`cat $pgdata/postmaster.pid|sed -n 4p`
###设置PRD 实例IP地址###
host1="10.41.20.97"
###设置STB 实例IP地址###
host2="10.41.20.107"
###设置登录数据库postgres账号密码###
export PGPASSWORD='xxxxxx'
hostname=`hostname`

echo -e "###### PostgreSQL DB $SID PRD check at `date` ######\n" >$scriptfolder/pgstandbycheck15.log
echo -e "Postgres $SID MASTER:" >>$scriptfolder/pgstandbycheck15.log
$scriptfolder/check-prd.sh >>$scriptfolder/pgstandbycheck15.log
echo -e "\nPostgres $SID SLAVE:" >>$scriptfolder/pgstandbycheck15.log
$scriptfolder/check-stb.sh >>$scriptfolder/pgstandbycheck15.log

echo -e "\n###### PostgreSQL DB $SID PRD check complete ######\n" >>$scriptfolder/pgstandbycheck15.log

PRD=$(psql  -h $host1 -p $PG_PORT -c  'select  * from pg_stat_replication;'| sed -n '3,$'p |tac  | sed -n '3,$'p | sed s/" *"//g )
STB=$(psql  -h $host2 -p $PG_PORT -c  'select  * from pg_stat_wal_receiver;'| sed -n '3,$'p |tac  | sed -n '3,$'p | sed s/" *"//g )

if [ ! "$PRD" ];then
  status=Warning
  echo "Warning!Please Check Database Status" >>$scriptfolder/pgstandbycheck15.log
elif [ ! "$STB" ];then
  status=Warning
  echo "Warning!Please Check Database Status" >>$scriptfolder/pgstandbycheck15.log
else
  status=Health
  echo -e "\n###### PostgreSQL DB $SID IS HEALTH ######\n" >>$scriptfolder/pgstandbycheck15.log
fi

now=`date "+%Y-%m-%d %H:%M:%S"`
if [ "$status" = "Warning" ];then
  echo "$SID Sync Check $status in $now" >> $scriptfolder/pgstandbycheck15min_$SID.log
  mail -s "$SID Sync Check $status in $now" -r "SE_PG_Standby_Check@$hostname.wistron.com" "mzl320.wzs.wistron@wistron.com" <   $scriptfolder/pgstandbycheck15.log
  else
  echo "$SID Sync Check $status in $now" >> $scriptfolder/pgstandbycheck15min_$SID.log
fi

cat $scriptfolder/pgstandbycheck15.log
  • 4.3 PRD DB Status check script
vim /pgdata/wistron/script/ZSARMP/check-prd.sh

add below content

#!/bin/bash

export PGPASSWORD='xxxxxx'
host1="10.41.20.97"
port="5433"
pgdata="/pgdata/ZSARMP"
work_dir=$(cd `dirname $0`;pwd)

cd $work_dir

is_master=$(psql -h $host1  -p $port -c 'select  pg_is_in_recovery();'| sed -n 3p)

if [ "$is_master" == " f" ];then
    rep=$(psql  -h $host1 -p $port -c 'select  pid, client_addr, sync_state,replay_lsn,reply_time from pg_stat_replication;'| sed -n '3,$'p |tac  | sed -n '3,$'p | sed s/" *"//g )
    if [ ! "$rep" ];then
      echo "Warning! Can't Connect to STB Database. Please Check Database Status"
    else
      printf "\n\t%-15s: %-15s\n" "STREAM_ROLE" "Master"
      echo  -e "\t-----------------------------------------------------"

      echo "$rep" | while read i
        do
          echo  -e "\tReplication Client Info:\n\t-----------------------------------------------------"
          a=(null PID CLIENT_ADDR SYNC_STATE REPLAY_LSN REPLAY_TIME)
          for j in `seq 5`
            do
              k=${a[j]}
              echo -e "$i" | awk -v k=$k -v j=$j -F"|" '{printf "\t%-23s: %-23s\n",k,$j}'
            done
          echo ""
        done

      client_check_info=(`psql -h $host1  -p $port -c 'select client_addr from pg_stat_replication;' | sed -n '3,$'p |tac  | sed -n '3,$'p | sed s/" *"//g`)
      if [ ! -f pg_stream_client_ZSARMP ]; then
        echo "$client_check_info" > pg_stream_client_ZSARMP
      else
        client_before=(`cat pg_stream_client_ZSARMP`)
        if [ ${#client_before[*]} -gt 0 ]; then
          for i in `seq ${#client_before[*]}`
            do
              index=$(expr $i - 1)
              if [ "${client_check_info[$index]}"x !=  "${client_before[$index]}"x ];then
                printf  "\tWarning! Replication Client Lost.\n"
              else
                echo "$client_check_info" > pg_stream_client_ZSARMP
              fi
            done
         else
           printf  "\tAttention! No Client Info found in pg_stream_client_ZSARMP.\n"
         fi
      fi
    fi
 fi

exit 0
  • 4.5 Grant privilege to check script
chmod u+x /pgdata/wistron/script/ZSARMP/check.sh /pgdata/wistron/script/ZSARMP/check15min.sh /pgdata/wistron/script/ZSARMP/check-prd.sh /pgdata/wistron/script/ZSARMP/check-stb.sh

  • 4.6 Setup cron job and test

  • setup cron job

    ############PostgreSQL DB STB check###############
00 8,16 * * * sh /pgdata/wistron/script/ZSARMP/check.sh

############PostgreSQL DB STB check every 15min###############
*/15 * * * * sh /pgdata/wistron/script/ZSARMP/check15min.sh

  • test check script

    sh /pgdata/wistron/script/ZSARMP/check.sh

    image-20260131201048497