pg_basebackup 13备份与恢复(无自定义表空间)

环境:
OS:Centos 7
DB:13.8

1.直接备份
需要提前创建好备份目录
su - postgres
[postgres@host134 pg_backup]$ mkdir -p /tmp/pg_backup/
[postgres@host134 pg_backup]$ pg_basebackup -D /tmp/pg_backup/ -Ft -Pv -U postgres -h 192.168.1.134 -p15432 -R
pg_basebackup: error: FATAL: no pg_hba.conf entry for replication connection from host "192.168.1.134", user "postgres", SSL off

解决办法:
修改 pg_hba.conf 文件,在最后面加入如下条目
host replication all 0.0.0.0/0 md5

 

然后重新reload
[postgres@host134 pg_backup]$pg_ctl -D /opt/pg13/data reload

 

[postgres@host134 data]$ pg_basebackup -D /tmp/pg_backup/ -Ft -Pv -U postgres -h 192.168.1.134 -p15432 -R
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/701BC98 on timeline 2
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_30091"
32246/32246 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/701BD48
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

 

或是使用带日期目录的备份
pg_basebackup -D /tmp/pg_backup/`date +%Y%m%d` -Ft -Pv -U postgres -h 192.168.1.134 -p15432

 

 

 

 

2.查看备份的文件
[postgres@host134 pg_backup]$ pwd
/tmp/pg_backup
[postgres@host134 pg_backup]$ ls -al
total 48816
drwxrwxr-x 2 postgres postgres 63 Oct 24 10:55 .
drwxrwxrwt. 11 root root 4096 Oct 24 10:51 ..
-rw------- 1 postgres postgres 178302 Oct 24 10:55 backup_manifest
-rw------- 1 postgres postgres 33021440 Oct 24 10:55 base.tar
-rw------- 1 postgres postgres 16780288 Oct 24 10:55 pg_wal.tar

base.tar ##备份是PG_DATA目录下的文件
pg_wal.tar ##备份是的是pg_wal下面的文件

 

#########################恢复###############################

 

1.停掉当前数据库
su - postgres
[postgres@host134 pg_backup]$ pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop -m fast
waiting for server to shut down.... done
server stopped

 

2.模拟删除data目录和归档目录
[postgres@host134 pg13]$ cd /opt/pg13
[postgres@host134 pg13]$ mv data bakdata
[postgres@host134 pg13]$ mv archivelog bakarchivelog
[postgres@host134 pg13]$ mkdir data
[postgres@host134 pg13]$ mkdir archivelog

 

3.恢复
解压备份包到相应的目录
[postgres@host134 pg_backup]$ cd /tmp/pg_backup
[postgres@host134 pg_backup]$tar -xvf base.tar -C /opt/pg13/data
[postgres@host134 pg_backup]$tar -xvf pg_wal.tar -C /opt/pg13/archivelog ##解压到归档目录

 

vi /opt/pg13/data/postgresql.conf 修改如下参数

restore_command = 'cp /opt/pg13/archivelog/%f %p'
##下面这两个参数也是正确的
##restore_command = 'cp /opt/pg13/archivelog/%f "%p"'
##restore_command = 'cp /opt/pg13/archivelog/%f /opt/pg13/data/pg_wal/'

##recovery_target = 'immediate' ##该选择只能恢复到备份的时间点,无法使用备份后生成的wal
recovery_target_timeline = 'latest'
##这里我们使用timeline恢复到最近的wal,因为备份的时候还会产生wal,若使用recovery_target = 'immediate'
##就无法使用到备份之后产生的wal日志,达不到恢复到最近wal的目的

生成recovery.signal标识文件
[postgres@host134 data]$ cd /opt/pg13/data
[postgres@host134 data]$ touch recovery.signal

 

4.修改权限
su - root
[root@host134 ~]# chmod 0700 /opt/pg13/data

 

5.将备份后产生的wal日志拷贝到归档目录

拷贝备份后的归档日志
cp /opt/pg13/bakarchivelog/20221025/00000004000000000000001B /opt/pg13/archivelog/
cp /opt/pg13/bakarchivelog/20221025/00000004000000000000001C /opt/pg13/archivelog/
cp /opt/pg13/bakarchivelog/20221025/00000004000000000000001D /opt/pg13/archivelog/
cp /opt/pg13/bakarchivelog/20221025/00000004000000000000001E /opt/pg13/archivelog/

拷贝备份后的wal日志
cp /opt/pg13/bakdata/pg_wal/00000004000000000000001F /opt/pg13/archivelog/
cp /opt/pg13/bakdata/pg_wal/000000040000000000000020 /opt/pg13/archivelog/
cp /opt/pg13/bakdata/pg_wal/000000040000000000000021 /opt/pg13/archivelog/
cp /opt/pg13/bakdata/pg_wal/000000040000000000000022 /opt/pg13/archivelog/
cp /opt/pg13/bakdata/pg_wal/000000040000000000000023 /opt/pg13/archivelog/
cp /opt/pg13/bakdata/pg_wal/000000040000000000000024 /opt/pg13/archivelog/
cp /opt/pg13/bakdata/pg_wal/000000040000000000000025 /opt/pg13/archivelog/

 

或者是拷贝到wal目录

拷贝备份后的归档日志
cp /opt/pg13/bakarchivelog/20221025/00000004000000000000001B /opt/pg13/data/pg_wal/
cp /opt/pg13/bakarchivelog/20221025/00000004000000000000001C /opt/pg13/data/pg_wal/
cp /opt/pg13/bakarchivelog/20221025/00000004000000000000001D /opt/pg13/data/pg_wal/
cp /opt/pg13/bakarchivelog/20221025/00000004000000000000001E /opt/pg13/data/pg_wal/

拷贝备份后的wal日志
cp /opt/pg13/bakdata/pg_wal/00000004000000000000001F /opt/pg13/data/pg_wal/
cp /opt/pg13/bakdata/pg_wal/000000040000000000000020 /opt/pg13/data/pg_wal/
cp /opt/pg13/bakdata/pg_wal/000000040000000000000021 /opt/pg13/data/pg_wal/
cp /opt/pg13/bakdata/pg_wal/000000040000000000000022 /opt/pg13/data/pg_wal/
cp /opt/pg13/bakdata/pg_wal/000000040000000000000023 /opt/pg13/data/pg_wal/
cp /opt/pg13/bakdata/pg_wal/000000040000000000000024 /opt/pg13/data/pg_wal/
cp /opt/pg13/bakdata/pg_wal/000000040000000000000025 /opt/pg13/data/pg_wal/

 

 

6.启动数据库
[postgres@host134 data]$pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start

数据库启动后会自动应用归档目录下的wal文件

2022-10-25 14:18:57 CST [8857]: [1-1] user=,db=,app=,client=LOG:  database system was interrupted; last known up at 2022-10-25 09:25:35 CST
cp: cannot stat ‘/opt/pg13/archivelog/00000005.history’: No such file or directory
2022-10-25 14:18:57 CST [8857]: [2-1] user=,db=,app=,client=LOG:  starting archive recovery
2022-10-25 14:18:57 CST [8857]: [3-1] user=,db=,app=,client=LOG:  restored log file "00000004.history" from archive
2022-10-25 14:18:57 CST [8857]: [4-1] user=,db=,app=,client=LOG:  restored log file "00000004000000000000001A" from archive
2022-10-25 14:18:57 CST [8857]: [5-1] user=,db=,app=,client=LOG:  redo starts at 0/1A000028
2022-10-25 14:18:57 CST [8857]: [6-1] user=,db=,app=,client=LOG:  consistent recovery state reached at 0/1A000100
2022-10-25 14:18:57 CST [8854]: [7-1] user=,db=,app=,client=LOG:  database system is ready to accept read only connections
2022-10-25 14:18:57 CST [8857]: [7-1] user=,db=,app=,client=LOG:  restored log file "00000004000000000000001B" from archive
2022-10-25 14:18:57 CST [8857]: [8-1] user=,db=,app=,client=LOG:  restored log file "00000004000000000000001C" from archive
cp: cannot stat ‘/opt/pg13/archivelog/00000004000000000000001D’: No such file or directory
2022-10-25 14:18:58 CST [8857]: [9-1] user=,db=,app=,client=LOG:  redo done at 0/1C000060
2022-10-25 14:18:58 CST [8857]: [10-1] user=,db=,app=,client=LOG:  last completed transaction was at log time 2022-10-25 09:27:05.960281+08
2022-10-25 14:18:58 CST [8857]: [11-1] user=,db=,app=,client=LOG:  restored log file "00000004000000000000001C" from archive
cp: cannot stat ‘/opt/pg13/archivelog/00000005.history’: No such file or directory
2022-10-25 14:18:58 CST [8857]: [12-1] user=,db=,app=,client=LOG:  selected new timeline ID: 5
2022-10-25 14:18:58 CST [8857]: [13-1] user=,db=,app=,client=LOG:  archive recovery complete
2022-10-25 14:18:58 CST [8857]: [14-1] user=,db=,app=,client=LOG:  restored log file "00000004.history" from archive

若丢失了某个归档的话,只能恢复到丢失归档前的一个归档,比如上面的 00000004000000000000001D 不存在,只能恢复到00000004000000000000001C
即使00000004000000000000001D后的日志存在,也没有办法使用.若00000004000000000000001D找到了,可以使用完整的wal日志重新进行恢复,直接将丢失的
日志拷贝的相应目录好像是不管用的.

 

7.重放WAL日志
db_test=# select pg_wal_replay_resume();
ERROR: recovery is not in progress
HINT: Recovery control functions can only be executed during recovery.
该步骤看情况需要

 

8.登录数据库验证
[postgres@host134 data]$psql -h 192.168.1.134 -U postgres -p15432

9.删除掉recovery.signal文件
该步骤不需要,恢复完成后系统会自动删除该文件

10.去掉如下两个参数
vi postgresql.conf修改如下参数
##restore_command = 'cp /opt/pg13/archivelog/%f %p'
##recovery_target_timeline = 'latest'

 

11.重新启动
pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop -m fast
pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start