pg_basebackup备份、恢复

pg_basebackup备份、恢复

详细方案

1、pg_basebackup备份命令介绍

pg_basebackup takes a base backup of a running PostgreSQL server.

Usage:
  pg_basebackup [OPTION]...

Options controlling the output:
  -D, --pgdata=DIRECTORY receive base backup into directory
  -F, --format=p|t       output format (plain (default), tar)
  -r, --max-rate=RATE    maximum transfer rate to transfer data directory
                         (in kB/s, or use suffix "k" or "M")
  -R, --write-recovery-conf
                         write configuration for replication
  -T, --tablespace-mapping=OLDDIR=NEWDIR
                         relocate tablespace in OLDDIR to NEWDIR
      --waldir=WALDIR    location for the write-ahead log directory
  -X, --wal-method=none|fetch|stream
                         include required WAL files with specified method
  -z, --gzip             compress tar output
  -Z, --compress=0-9     compress tar output with given compression level

General options:
  -c, --checkpoint=fast|spread
                         set fast or spread checkpointing
  -C, --create-slot      create replication slot
  -l, --label=LABEL      set backup label
  -n, --no-clean         do not clean up after errors
  -N, --no-sync          do not wait for changes to be written safely to disk
  -P, --progress         show progress information
  -S, --slot=SLOTNAME    replication slot to use
  -v, --verbose          output verbose messages
  -V, --version          output version information, then exit
      --no-slot          prevent creation of temporary replication slot
      --no-verify-checksums
                         do not verify checksums
  -?, --help             show this help, then exit

Connection options:
  -d, --dbname=CONNSTR   connection string
  -h, --host=HOSTNAME    database server host or socket directory
  -p, --port=PORT        database server port number
  -s, --status-interval=INTERVAL
                         time between status packets sent to server (in seconds)
  -U, --username=NAME    connect as specified database user
  -w, --no-password      never prompt for password
  -W, --password         force password prompt (should happen automatically)

指定输出格式：
-F, --format=p|t output format (plain (default), tar) p 原样输出，即把主数据库中的各个数据文件，配置文件、目录结构都完全一样的写到备份目录；t 把输出的备份文件打包到一个tar文件中。
-z, --gzip 使用gzip压缩，仅能能与tar输出模式配合使用。
-Z, --compress=0-9 指定压缩级别
-x, --xlog (fetch mode) 备份时会把备份中产生的xlog文件也自动备份出来，这样才能在恢复数据库时，应用这些xlog文件把数据库推到一个一致点，然后真正打开这个备份的数据库，这个选项与-X fetch是完全一样的。使用这个选项，需要设置“wal_keep_segments"参数，以保证在备份过程中，需要的WAL日志文件不会被覆盖。
-P, --progress 在备份过程中实时打印备份进度
-v, --verbose 详细模式，使用了-P后，还会打印出正在备份的具体文件的信息。
-D 路径参数,指的依旧是容器内的路径,可以使用docker挂载

2、数据库处于归档模式

[postgres@ora11g-node02 ~]$ psql 
psql (12.5)
Type "help" for help.

postgres=# show archive_mode;
 archive_mode 
--------------
 on
(1 row)

postgres=# show archive_command;
                    archive_command                     
--------------------------------------------------------
 test ! -f /backup/pgarch/%f && cp %p /backup/pgarch/%f
(1 row)

pms3=#  select count(*) from t1; 
count 524288

日志切换
pms3=# select pg_switch_wal();
 pg_switch_wal 
---------------
 1/52001310
(1 row)

3、备份：

a、产生压缩的tar包，-Ft参数指定：

pg_basebackup -D bk1 -Ft -z -P此备份花的时间比较长，会生成压缩文件
[postgres@ora11g-node02 ~]$ pg_basebackup -h 192.168.56.51 -p 5432 -U replication -Ft -Xs -Pv -D /backup/data/
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 1/C000028 on timeline 6
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_28682"
2175610/2175610 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 1/C000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed
[postgres@ora11g-node02 ~]$ ls -l /backup/data/
total 2192008
-rw------- 1 postgres postgres 2227827200 Jun 26 11:13 base.tar
-rw------- 1 postgres postgres   16780288 Jun 26 11:13 pg_wal.tar

b、产生跟源文件一样的格式，即原样格式，-Fp参数指定：

pg_basebackup -D bk2 -Fp -P此备份方式很快，但是不节省空间

[postgres@ora11g-node02 ~]$ pg_basebackup -h 192.168.56.51 -p 5432 -U replication -Fp -Xs -Pv -D /backup/data/
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 1/E000028 on timeline 6
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_28720"
2175610/2175610 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 1/E000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

backup_label.old会记录备份信息：

[postgres@ora11g-node02 pgsql]$ more /data/pgsql/backup_label.old 
START WAL LOCATION: 1/4B000028 (file 00000007000000010000004B)
CHECKPOINT LOCATION: 1/4B000060
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2022-07-14 20:52:39 CST
LABEL: pg_basebackup base backup
START TIMELINE: 7

4、恢复验证：

a、关闭数据库或者kill服务器主进程模拟主机断电

[postgres@ora11g-node02 data]$ pg_ctl stop -m immediate

b、删除data目录下所有的文件，（如果是删除这个data目录，则下一次创建该目录时要求该目录的权限是750，否则启动数据库时会报错）：

[root@ora11g-node02 data]# rm –rf $PGDATA/*

c、数据库恢复

可以使用tar包进行恢复：

[postgres@ora11g-node02 data]$ tar -xvf /backup/data/base.tar -C /data/pgsql/
[postgres@ora11g-node02 data]$ tar -xvf /backup/data/pg_wal.tar -C /data/pgsql/pg_wal/

[postgres@ora11g-node02 data]$ ls -l /data/pgsql/
total 136
-rw------- 1 postgres postgres   226 Jun 26 11:25 backup_label.old
drwxr-x--- 6 postgres postgres  4096 Jun 26 03:58 base
-rw------- 1 postgres postgres    44 Jun 26 11:42 current_logfiles
drwxr-x--- 2 postgres postgres  4096 Jun 26 11:43 global
drwxr-x--- 2 postgres postgres  4096 Jun 26 11:42 log
drwxr-x--- 2 postgres postgres  4096 Jun 26 03:58 pg_commit_ts
drwxr-x--- 2 postgres postgres  4096 Jun 26 03:58 pg_dynshmem
-rwxr-x--- 1 postgres postgres  4531 Jun 26 03:58 pg_hba.conf
-rwxr-x--- 1 postgres postgres  1636 Jun 26 03:58 pg_ident.conf
drwxr-x--- 4 postgres postgres  4096 Jun 26 11:25 pg_logical
drwxr-x--- 4 postgres postgres  4096 Jun 26 03:58 pg_multixact
drwxr-x--- 2 postgres postgres  4096 Jun 26 11:42 pg_notify
drwxr-x--- 2 postgres postgres  4096 Jun 26 11:25 pg_replslot
drwxr-x--- 2 postgres postgres  4096 Jun 26 03:58 pg_serial
drwxr-x--- 2 postgres postgres  4096 Jun 26 03:58 pg_snapshots
drwxr-x--- 2 postgres postgres  4096 Jun 26 03:58 pg_stat
drwxr-x--- 2 postgres postgres  4096 Jun 26 11:42 pg_stat_tmp
drwxr-x--- 2 postgres postgres  4096 Jun 26 04:22 pg_subtrans
drwxr-x--- 2 postgres postgres  4096 Jun 26 03:58 pg_tblspc
drwxr-x--- 2 postgres postgres  4096 Jun 26 03:58 pg_twophase
-rwxr-x--- 1 postgres postgres     3 Jun 26 03:58 PG_VERSION
drwxr-x--- 3 postgres postgres  4096 Jun 26 11:42 pg_wal
drwxr-x--- 2 postgres postgres  4096 Jun 26 03:58 pg_xact
-rw------- 1 postgres postgres   738 Jun 26 11:41 postgresql.auto.conf
-rwxr-x--- 1 postgres postgres 26651 Jun 26 03:58 postgresql.conf
-rw------- 1 postgres postgres    40 Jun 26 11:42 postmaster.opts
-rw------- 1 postgres postgres    70 Jun 26 11:42 postmaster.pid
-rw------- 1 postgres postgres     0 Jun 26 11:25 tablespace_map.old

也可以使用原样文件备份进行恢复：
[postgres@ora11g-node02 data]$ cp –rf /backup/data/* $PGDATA

d、在postgresql.auto.conf文件中添加如下2行：

restore_command = ‘cp /backup/pgarch/%f %p’
recovery_target_timeline = ‘latest’

e、$PGDATA目录下touch一个空文件recovery.signal，告诉pg需要做recovery，恢复完成后会自动删除

[postgres@ora11g-node02 data]$ touch /data/pgsql/recovery.signal

f、启动数据库：pg_ctl start

[postgres@ora11g-node02 log]$ more postgresql-2022-06-26_114203.log
2022-06-26 11:42:03.220 CST [28835] LOG:  database system was interrupted; last known up at 2022-06-26 11:25:12 CST
cp: cannot stat `/backup/pgarch/00000007.history': No such file or directory
2022-06-26 11:42:05.767 CST [28835] LOG:  entering standby mode
2022-06-26 11:42:05.775 CST [28835] LOG:  restored log file "00000006.history" from archive
2022-06-26 11:42:05.829 CST [28835] LOG:  restored log file "000000060000000100000010" from archive
2022-06-26 11:42:05.866 CST [28835] LOG:  redo starts at 1/10000028
2022-06-26 11:42:05.867 CST [28835] LOG:  consistent recovery state reached at 1/10000138
2022-06-26 11:42:05.867 CST [28833] LOG:  database system is ready to accept read only connections
2022-06-26 11:42:05.922 CST [28835] LOG:  restored log file "000000060000000100000011" from archive

g、登录数据库，执行函数(否则pg数据库处于只读状态)：

[postgres@ora11g-node02 pgsql]$ psql 
psql (12.5)
Type "help" for help.

postgres=# select pg_wal_replay_resume();

验证数据的完整性：
testdb=# select count(*) from t1; 
count 524288

5、备份、恢复注意要点：

1、数据库文件恢复，启动数据库前postgresql.conf一定要添加恢复参数，否则只恢复到备份的时间点。
2、第一次恢复后，数据库能够恢复到最新状态，t1表的数据能够恢复到归档的最后位置。对数据库进行操作，切换几个日志后，再用之前的备份对数据库进行恢复，结果能够恢复到最新的状态，能够自动应用归档。
3、如果当前的日志丢失，pg实际上做的是不完全恢复，但是恢复的时候没有提示，而且不需要显式进行recovery，在启动数据库的时候自动运行，类似oracle的实例恢复。（日志虽然未提示是不完全恢复，实际丢失的也就是未归档的wal日志对应的数据是无法恢复的）