postgresql pg_basebackup备份恢复实验

数据库版本：postgresql 9.4.1

操作系统：centos 7.6

创建模拟数据

备份数据

$ cat fullbackup.sh
#!/bin/bash

# 完全备份参数
full_backup_dir="/zabbix_src/pgbackup/fullbackup"       # 完全备份目录
full_backup_label="full_backup"             # 完全备份标签

# PostgreSQL 数据库参数
pg_username="postgres"                    # PostgreSQL 用户名
pg_host="127.0.0.1"                       # PostgreSQL 主机地址
pg_port="5436"                            # PostgreSQL 端口

# 当前日期和时间
echo ---------------备份开始时间---------------------
datetime=$(date +%Y%m%d%H%M%S)
echo $datetime

# 创建完全备份
create_full_backup() {
    # 删除之前的完全备份
#    rm -rf $full_backup_dir

    # 创建新的完全备份目录
#    mkdir -p $full_backup_dir

    # 使用pg_basebackup命令创建完全备份
    pg_basebackup -U $pg_username -h $pg_host -p $pg_port -D $full_backup_dir/$full_backup_label-$datetime -Ft -Pv -z --label=$full_backup_label

    echo "创建完全备份成功"
}

# 执行完全备份脚本
create_full_backup

# 备份结束时间
echo ----------------备份结束时间----------------------
endtime=$(date +%Y%m%d%H%M%S)
echo $endtime

模拟故障

mv pdata pdata.org
[postgres@dmdb1 pgdb941]$ ll
total 8
drwxrwxr-x  2 postgres postgres    6 Oct  9 10:36 archive
drwxrwxr-x  2 postgres postgres   21 Oct  9 10:58 logs
drwx------ 19 postgres postgres 4096 Oct 11 15:29 pdata
drwx------ 19 postgres postgres 4096 Oct 11 14:43 pdata.org
drwxrwxr-x  6 postgres postgres   56 Oct  9 10:52 psql

## 停止数据库
此时已经不能正常停止数据库，只能通过Kill进程结束

恢复操作：

1.创建数据目录
mkdir pdata
chmod 0700 pdata  ## 如果不修改权限，在启动数据库的时候会出现如下告警

LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  database system is shut down
LOG:  database system was shut down at 2023-10-11 14:43:25 CST
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
FATAL:  data directory "/zabbix_src/pgdb941/pdata" has group or world access
DETAIL:  Permissions should be u=rwx (0700).

2.拷贝备份文件到数据目录下
cp /backup/base.tar.gz pdata 
tar -zxvf base.tar.gz

3.拷贝recovery.conf.sample到数据目录下
cp /zabbix_src/pgdb941/psql/share/postgresql/recovery.conf.sample ./recovery.conf

4.修改recovery.conf
restore_command = 'cp /zabbix_src/pgdb941/pdata/arch/%f %p' 
/zabbix_src/pgdb941/pdata/arch目录是归档的路径


5.查看备份信息
$ cat backup_label
START WAL LOCATION: 0/27000028 (file 000000010000000000000027)
CHECKPOINT LOCATION: 0/27000028
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2023-10-11 12:29:47 CST
LABEL: full_backup

从备份信息中看到备份文件最新的归档文件是000000010000000000000027，确认归档目录下文件是否存在，
如果不存在则需要从备份文件中拷贝到归档目录下，否则恢复的时候会失败。


6.启动数据库
pg_ctl -D $PGDATA start

启动日志：
LOG:  database system was interrupted; last known up at 2023-10-11 12:29:47 CST
LOG:  starting archive recovery
LOG:  restored log file "000000010000000000000027" from archive
LOG:  redo starts at 0/27000090
LOG:  consistent recovery state reached at 0/270000B8
cp: cannot stat ‘/zabbix_src/pgdb941/pdata/arch/000000010000000000000028’: No such file or directory
LOG:  redo done at 0/270000B8
LOG:  restored log file "000000010000000000000027" from archive
cp: cannot stat ‘/zabbix_src/pgdb941/pdata/arch/00000002.history’: No such file or directory
LOG:  selected new timeline ID: 2
cp: cannot stat ‘/zabbix_src/pgdb941/pdata/arch/00000001.history’: No such file or directory
LOG:  archive recovery complete
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections


7.验证数据
"public"."testdata3"	38 MB	11 MB	49 MB
"public"."test"	35 MB	0 bytes	35 MB
"public"."testdata2"	15 MB	4408 kB	20 MB
"public"."testdata"	7896 kB	0 bytes	7896 kB
"public"."t_centerprises"	8192 bytes	16 kB	24 kB