📚 Using pg_basebackup to backup & recover the DB¶

🔍 Prepare：¶

## Prepare the DB Environment：

Environment name Version

OS version Redhat 7.6

PostgreSQL version 13.4

DB server 10.38.36.110

Database:HQRPAD1 PostgreSQL 16.X

Port 5435

Table space path /pgdata/HQRPAD1_tblspc

Archive Wal path /pgdata/HQRPAD1_archive

## Prepare parameter on the postgresql.conf file：

parameter name Version

archive_command 'cp %p /pgdata/HQRPAD1_archive/%f'

restore_command 'cp /pgdata/HQRPAD1_archive/%f %p'

pgadmin：

OS:

1. Backup PostgreSQL DB（postgres）¶

pg_basebackup -Ft -Pv -Xs -z -Z5 -p 5435 -D /tmp/DEV
ll

16384.tar.gz → archive for tablespace base.tar.gz → archive for database pg_wal.tar.gz → archive for wal file

2. Set the cron job to backup DB every day（postgres）¶

2.1 Make sure pg_hba.conf got the correct information

echo "host    replication postgres    10.38.36.111/32 trust" >> /pgdata/HQRPAD1/pg_hba.conf
more pg_hba.conf |grep postgres

Add server IP in pg_hba.conf

pg_ctl reload -D /pgdata/HQRPAD1

2.2 Modify daily backup script（WZS）

### PG Dump backup folder

mount -t nfs 192.168.10.86:/storage/SMARTPG/ /pgbackup/

vim /pgdata/wistron/script/ZSARMP/pgbasebackup.sh
#!bash

###设置基础环境变量###
PATH=$PATH:/usr/pgsql-16/bin:
###设置需要备份DB的标识###
SID=ZSARMP
###设置PGDATA环境变量###
pgdata=/pgdata/ZSARMP
###设置pgbasebackup的用户###
PG_USER=postgres
###设置端口号###
PG_PORT=`cat $pgdata/postmaster.pid|sed -n 4p`
###设置日期便利用于判断备份路径###
DA=$(date +%-j)
###设置脚本日志存放路径###
scriptfolder=/pgdata/wistron/script/$SID
###设置DB归档的位置###
ARCHIVE=/pg_arch/"$SID"_arch

if (($DA%2==0));
then Singleordouble=double
else Singleordouble=single
fi
Location=/pgbackup/"$SID"_"$Singleordouble"

###备份前清空备份路径###
rm -rf $Location

###确认pgbasebackup开始备份时间###
echo "###### $SID PGbasebackup begin at `date` ######" > $scriptfolder/pgbasebackup_"$SID"_"$Singleordouble".log
###执行pgbasebackup备份(-FT:指定备份格式为tar包 -Pv:输出详细的备份过程 -z:指定压缩格式 -Z5:指定压缩等级)###
pg_basebackup -Ft -Pv -Xs -z -Z5 -U $PG_USER -p $PG_PORT -D $Location -n -c fast &>> $scriptfolder/ pgbasebackup_"$SID"_"$Singleordouble".log
###备份归档的位置###
mkdir -p "$Location/archive"
###确认pgbasebackup结束备份时间###
echo "###### $SID PGbasebackup end at `date` ######" >> $scriptfolder/pgbasebackup_"$SID"_"$Singleordouble".log

###判断备份是否成功###
logstatus=`grep "pg_basebackup: base backup completed" $scriptfolder/pgbasebackup_"$SID"_"$Singleordouble".log`
percentcount=`grep "100%" $scriptfolder/pgbasebackup_"$SID"_"$Singleordouble".log |wc -l`
logerror=`grep "not removed at user's request" $scriptfolder/pgbasebackup_"$SID"_"$Singleordouble".log |wc -l`

###将昨天的备份日志及备份完成后当天的日志文件进行备份###
/bin/echo "###### $SID ARCH backup begin at `date` ######" >> $scriptfolder/pgbasebackup_"$SID"_"$Singleordouble".log
/bin/find $ARCHIVE/* -name "*" -daystart -mtime 1 -exec cp -a {} $Location/archive \;
/bin/find $ARCHIVE/* -name "*" -daystart -mtime 1 >> $scriptfolder/pgbasebackup_"$SID"_"$Singleordouble".log
/bin/find $ARCHIVE/* -name "*" -daystart -mtime 0 -exec cp -a {} $Location/archive \;
/bin/find $ARCHIVE/* -name "*" -daystart -mtime 0 >> $scriptfolder/pgbasebackup_"$SID"_"$Singleordouble".log
/bin/echo "###### $SID ARCH backup end at `date` ######" >> $scriptfolder/pgbasebackup_"$SID"_"$Singleordouble".log

if [ "$logstatus" = "pg_basebackup: base backup completed" ]
then
backupstatus="Successful"
else
        if [ "$logerror" -gt 0 ] && [ "$percentcount" -gt 0 ]
        then
                backupstatus="Successful with error"
        else
                backupstatus="error"
        fi
fi
echo "$SID PGbasebackup $backupstatus at `date`" >> $scriptfolder/pgbasebackup.log

cp $scriptfolder/pgbasebackup_"$SID"_"$Singleordouble".log /pgbackup/LOG/pgbasebackup_"$SID"_"$Singleordouble".log

###完成备份后发邮件通知###
mail -s "$SID PG PGbasebackup $backupstatus" -r "SE_PG_Basebackup_Check" "mzl320.wzs.wistron@wistron.com" < $scriptfolder/  pgbasebackup_"$SID"_"$Singleordouble".log

crontab -l
############Postgres DB basebackup###############
00 1 * * * sh /pgdata/wistron/script/ZSARMP/pgbasebackup.sh

if you want to recover the Database, you can refer to recovery DB SOP

2.3 Modify daily backup script（WIH）

vim /home/postgres/backup.sh
#!/bin/bash
# 2022.01.26

DAY="$(date +'%Y%m%d')"
TIME="$(date +'%Y%m%d%H%M%S')"
BACKPATH=/dbdump/POSTGRESQL/HQRPAD1/$DAY/$TIME
COPYPATH=/dbdump/POSTGRESQL/HQRPAD1
TARGET=/dbdump/POSTGRESQL/HQRPAD1
PATH=$PATH:/usr/pgsql-16/bin:
export PATH

#PG Information
PG_HOST=127.0.0.1
PG_USER=postgres
PG_PORT=5435
pg_basebackup -Ft -Pv -Xs -z -Z5 -U $PG_USER -h $PG_HOST -p $PG_PORT -D $BACKPATH

crontab -l
### Postgres DB backup, Dennis, 20220106###
0 22 * * * sh /pgdata/HQRPAD1/backup.sh  > /tmp/log 2>&1

if you want to recover the Database, you can refer to recovery DB SOP

3. Backup PostgreSQL DB（postgres）¶

3.1 Before doing the recovery, we need a full backup via pg_basebackup

pg_basebackup -Ft -Pv -Xs -z -Z5 -p 5435 -D /tmp/DEV
ll /tmp/backup1_all.sql

16384.tar.gz → archive for table space base.tar.gz → archive for database pg_wal.tar.gz → archive for wal file

3.2 Insert 300 data in test1 table. Every few minutes to insert data 3 times
- 3.2.1 First time
- 3.2.2 Second time
- 3.2.3 Third time
3.3 Now, we want to recovery the data after 2^nd insert before 3^rd insert.
- 3.3.1 shutdown DB first.
```
cd /pgdata
pg_ctl stop -D /pgdata/HQRPAD1/
```
- 3.3.2 delete the database folder and empty HQRPAD1_tbs folder
```
rm -rf HQRPAD1 
cd HQRPAD1_tbs
ll
rm -rf PG_13_202007201
```
- 3.3.3 go the backup tar folder, in this case is /tmp/DEV
```
cd /tmp/DEV
ll
```
- 3.3.4 create the database folder and un-tar base.tar.gz to the right path
- 3.3.4.1 unzip/tar the base.tar.gz
```
mkdir /pgdata/HQRPAD1
chmod 700 /pgdata/HQRPAD1
tar zxf base.tar.gz -C /pgdata/HQRPAD1
ll /pgdata/HQRPAD1
```
  16384.tar.gz → archive for table space base.tar.gz → archive for database pg_wal.tar.gz → archive for wal file
- 3.3.4.2 unzip/tar pg_wal.tar.gz
```
tar zxf pg_wal.tar.gz -C /pgdata/HQRPAD1/pg_wal
```
- 3.3.4.3 unzip/tar 16384.tar.gz （tablespace）
```
tar zxf 16384.tar.gz -C /pgdata/HQRPAD1_tbs
ll /pgdata/HQRPAD1_tbs
```
- 3.3.5 we need to create symbolic in pg_tblspc under database folder
- 3.3.5.1 know which file name to be symboliced
```
cat /pgdata/HQRPAD1/tablespace_map
```
- 3.3.5.2 know which file name to be symboliced
```
cd /tmp/DEV
ll
```
  16384 will be the symbolic file name
```
ln -s /pgdata/HQRPAD1_tbs /pgdata/HQRPAD1/pg_tblspc/16384
ll /pgdata/HQRPAD1/pg_tblspc/
```
- 3.3.6 revise recovery portion in postgresql.conf, and identify the time you want to recover. (this case, we want to recovery the data after 2^nd insert before 3^rd insert)
(The completed inserting time for 2^nd inserting)
```
echo "recovery_target_time = '2020-02-21 15:25:00'" >> /pgdata/HQRPAD1/postgresql.conf
```
- 3.3.7 we need to add one file named “recovery.signal” under database path. That means we tell postgres it will do the recovery from starting up DB
```
touch /pgdata/HQRPAD1/recovery.signal
```
recovery.signal: tells PostgreSQL to enter normal archive recovery standby.signal: tells PostgreSQL to enter standby mode
- 3.3.8 now, we start the DB to see if the data recover to the time of 2^nd inserting time
```
pg_ctl start -D /pgdata/HQRPAD1
```
Check the DB record. It’s 600 records after 2^nd inserting. At this moment, DB is read-only. Can’t insert any data

Try to insert but DB says it’s read-only mode

And database path 2 files will be renamed with .old. otherwise, recovery.signal still exists(database is recovery done, but read-only mode)
```
ll /pgdata/HQRPAD1
```
backup_label → backup_label.old

tablespace_map → tablespace_map.old
- 3.3.9 Finally, make sure the data is what we need to recovery. Then, we should to tell DB to continue(Read-Write mode) the transaction. Login as ‘postgres’, and execute below sql
```
psql -p 5435
```
```
\c HQRPAD1
```
```
select pg_wal_replay_resume();
\q
```
At the same time, the recovery.signal file is gone under database path. That means the recovery is finished. DB backs to normal (read-write mode)

Environment name	Version
OS version	Redhat 7.6
PostgreSQL version	13.4
DB server	10.38.36.110
Database:HQRPAD1	PostgreSQL 16.X
Port	5435
Table space path	/pgdata/HQRPAD1_tblspc
Archive Wal path	/pgdata/HQRPAD1_archive

parameter name	Version
archive_command	'cp %p /pgdata/HQRPAD1_archive/%f'
restore_command	'cp /pgdata/HQRPAD1_archive/%f %p'