📚 PostgreSQL Create STB/Slave¶

🔍 Prepare：¶

## Preparing the Master and Slave Server：

Environment name Version IP Folder

OS Rocky Linux 9.x

Master DB PostgreSQL 16.X 10.38.36.110 /pgdata/SID

Slave DB PostgreSQL 16.X 10.38.36.111 /pgdata/SID

1. Preparing the Master（Master）¶

1.1 Create Master DB as installation document/Create DB document

1.2 Make sure you had set wal_level and max_wal_senders on Master DB

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

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

1.3 Add PRD/STB IP in pg_hba.conf

echo "host    replication     postgres        10.38.36.110/24        trust" >> /pgdata/SID/pg_hba.conf
echo "host    replication     postgres        10.38.36.111/24        trust" >> /pgdata/SID/pg_hba.conf
pg_ctl reload -D /pgdata/SID

Notice： ADD PRD/STB IP For future failover convenience

2. Build STB（STB）¶

2.1 Install PG software on Standby, refer to installation document
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 5435 -F p -P -X stream -R -D /pgdata/HQRPAD1 -l backup20210706

2.3 Start STB DB

pg_ctl start -D /pgdata/HQRPAD1 -l logfile

3. Check Process on Standby server¶

3.1 Check standby process (standby)

ps -ef |grep postgres

3.2 Check replication status in Master Server (Master server)

su - postgres
psql -p 5436

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

3.3 Check Master log status (Master DB)

SELECT pg_current_wal_lsn();

3.4 Check Standby log status (Standby DB)

SELECT pg_last_wal_receive_lsn();

3.5 check cluster status in Master server

pg_controldata /pgdata/SID/ | grep cluster

3.6 check cluster status in Standby server

pg_controldata /pgdata/HQRPAD1/ | grep cluster

3.7 You can login STB for query only (like oracle ADG)

4. Monitor Script¶

4.1 check.sh

vim /pgdata/wistron/script/ZSARMP/check.sh
###设置基础环境变量###
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.XX.XX"
###设置STB 实例IP地址###
host2="10.41.XX.XX"
###设置登录数据库postgres账号密码###
export PGPASSWORD='XXX'
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 check15min.sh

vim /pgdata/wistron/script/ZSARMP/check15min.sh
###设置基础环境变量###
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.XX.XX"
###设置STB 实例IP地址###
host2="10.41.XX.XX"
###设置登录数据库postgres账号密码###
export PGPASSWORD='W2XXX'
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 check-prd.sh

vim /pgdata/wistron/script/ZSARMP/check-prd.sh
#!/bin/bash

export PGPASSWORD='W2XXX'
host1="10.41.XX"
port="5433"
SID=ZSARMP
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_$SID ]; then
        echo "$client_check_info" > pg_stream_client_$SID
      else
        client_before=(`cat pg_stream_client_$SID`)
        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_$SID

              fi
            done
         else
           printf  "\tAttention! No Client Info found in pg_stream_client_$SID.\n"
         fi
      fi
    fi
 fi

exit 0

4.4 check-stb.sh

vim /pgdata/wistron/script/ZSARMP/check-stb.sh
#!/bin/bash

export PGPASSWORD='W2XXX'
host1="10.41.XX.XX"
port="5433"
SID=ZSARMP
pgdata="/pgdata/ZSARMP"
work_dir=$(cd `dirname $0`;pwd)

cd $work_dir


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

if [ "$is_slave" == " t" ];then


   slave=$(psql  -h $host1 -p $port -c 'select pid,sender_host,status,written_lsn,last_msg_receipt_time from pg_stat_wal_receiver;'|  sed -n '3,$'p |tac  | sed -n '3,$'p | sed s/" *"//g )
    if [ ! "$slave" ];then
      echo "Warning! Can't Connect to PRD Database. Please Check Database Status"
    else
      printf "\n\t%-15s: %-15s\n" "STREAM_ROLE" "Slave"
      echo  -e "\t-----------------------------------------------------"

      echo "$slave" | while read i
        do
          echo  -e "\tReplication Client Info:\n\t-----------------------------------------------------"
          a=(null PID PRD_HOST STATUS APPLY_DIFF LAST_MSG_RECEIPT_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


      host_check_info=(`psql -h $host1 -p $port -c 'select sender_host from pg_stat_wal_receiver;' | sed -n '3,$'p |tac  | sed -n '3, $'p | sed s/" *"//g`)
      if [ ! -f pg_stream_host_$SID ]; then
        echo "$host_check_info" > pg_stream_host_$SID
      else
        client_before=(`cat pg_stream_host_$SID`)
        if [ ${#client_before[*]} -gt 0 ]; then
          for i in `seq ${#client_before[*]}`
            do
              index=$(expr $i - 1)
              if [ "${host_check_info[$index]}"x !=  "${client_before[$index]}"x ];then
                printf  "\tWarning! Replication host Lost.\n"
              else
                echo "$host_check_info" > pg_stream_host_$SID

              fi
            done
         else
           printf  "\tAttention! No host Info found in pg_stream_host_$SID.\n"
         fi
      fi
    fi
  fi

exit 0

4.5 Grant privilege

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 Seting cron job and test

############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

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