使用pg_basebackup备份和恢复一个pg数据库

创建用于备份用的用户：

创建数据库备份用户：
psql -U postgres -c "CREATE USER dbbackup WITH REPLICATION PASSWORD '123456'"
-U 指定备份用户
-c 指定创建用户命令
备份用户只需要授权：replication即可

创建用于备份的目录：

创建备份目录：
mkdir -p /pgdata/backup/

如果不是本地备份，还需要开通相关权限：修改 pg_hba.conf 放行备份服务器IP

备份数据库：

pg_basebackup -h 192.168.56.100 -U dbbackup -p 5432 -D /pgdata/backup/`date +"%Y%m%d"` -c fast -Ft -z -P -Xs -l backup_label

-h 指定数据库地址(本地也可以不指定)

-U 指定数据库用户

-P 指定数据库端口

-D 指定备份目录

-c 指定备份模式

-Ft 指定备份格式

-z 指定压缩方式

-P 指定备份进度

-Xs 指定增量备份模式

-l 指定备份名称

日志输出：

[postgres@elan ~]$ pg_basebackup -h 192.168.56.100 -U dbbackup -p 5432 -D /pgdata/backup/`date +"%Y%m%d"` -c fast -Ft -z -P -Xs -l backup_label
2024-08-02 14:43:29.986 CST [10956] LOG: checkpoint starting: immediate force wait
2024-08-02 14:43:29.994 CST [10956] LOG: checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.003 s, total=0.009 s; sync files=4, longest=0.002 s, average=0.001 s; distance=16377 kB, estimate=16377 kB; lsn=0/26000060, redo lsn=0/26000028
44987/44987 kB (100%), 3/3 tablespaces
[postgres@elan ~]$

备份完成

[postgres@elan backup]$ cd /pgdata/backup/
[postgres@elan backup]$ ls -rtl
total 0
drwx------ 2 postgres postgres 109 Aug  2 14:43 20240802
[postgres@elan backup]$ cd 20240802/
[postgres@elan 20240802]$ ls -rtl
total 17376
-rw------- 1 postgres postgres     231 Aug  2 14:43 16453.tar.gz
-rw------- 1 postgres postgres 1062979 Aug  2 14:43 16444.tar.gz
-rw------- 1 postgres postgres 9068004 Aug  2 14:43 base.tar.gz
-rw------- 1 postgres postgres  251485 Aug  2 14:43 backup_manifest
-rw------- 1 postgres postgres   17075 Aug  2 14:43 pg_wal.tar.gz

开始恢复数据库：

创建用于恢复的目录：
[postgres@elan ~]$ mkdir /pgrecover

恢复base目录

[postgres@elan ~]$ cd /pgdata/backup/
[postgres@elan backup]$ ls
20240802
[postgres@elan backup]$ cd 20240802/
[postgres@elan 20240802]$ ls
16444.tar.gz  16453.tar.gz  backup_manifest  base.tar.gz  pg_wal.tar.gz
[postgres@elan 20240802]$ pwd
/pgdata/backup/20240802
[postgres@elan 20240802]$ clear
[postgres@elan 20240802]$ pwd
/pgdata/backup/20240802
[postgres@elan 20240802]$ ls -rtl
total 17376
-rw------- 1 postgres postgres     231 Aug  2 14:43 16453.tar.gz
-rw------- 1 postgres postgres 1062979 Aug  2 14:43 16444.tar.gz
-rw------- 1 postgres postgres 9068004 Aug  2 14:43 base.tar.gz
-rw------- 1 postgres postgres  251485 Aug  2 14:43 backup_manifest
-rw------- 1 postgres postgres   17075 Aug  2 14:43 pg_wal.tar.gz
[postgres@elan 20240802]$ tar -zxvf base.tar.gz -C /pgrecover/

恢复表空间信息，需还原到对应目录下

base还原后，在目录下会存在一个文件：tablespace_map
-rw------- 1 postgres postgres     52 Aug  2 14:43 tablespace_map
drwx------ 2 postgres postgres      6 Aug  2 14:43 pg_replslot
-rw------- 1 postgres postgres    214 Aug  2 14:43 backup_label
drwx------ 2 postgres postgres   4096 Aug  2 14:47 global

查看tablespace_map文件，即是表空间oid与目录的对应关系
[postgres@elan pgrecover]$ cat tablespace_map
16444 /pgdata/ts_primary
16453 /pgdata/test2_tblspc
[postgres@elan pgrecover]$

将表空间备份还原到对应目录下：

[postgres@elan pgrecover]$ tar -zxf /pgdata/backup/20240802/16444.tar.gz -C /pgrecover/ts_primary/
[postgres@elan pgrecover]$ tar -zxf /pgdata/backup/20240802/16453.tar.gz -C /pgrecover/ts_primary/

还原wal日志：

[postgres@elan pgrecover]$ tar -zxf /pgdata/backup/20240802/pg_wal.tar.gz -C /pgrecover/pg_wal/

使用恢复后目录，启动数据库

[postgres@elan pgrecover]$ pg_ctl -D /pgrecover/ start
waiting for server to start....2024-08-02 14:52:36.458 CST [12674] LOG:  starting PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
2024-08-02 14:52:36.458 CST [12674] LOG:  listening on IPv4 address "192.168.56.100", port 5432
2024-08-02 14:52:36.462 CST [12674] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-08-02 14:52:36.467 CST [12678] LOG:  database system was interrupted; last known up at 2024-08-02 14:43:29 CST
2024-08-02 14:52:37.255 CST [12678] LOG:  starting backup recovery with redo LSN 0/26000028, checkpoint LSN 0/26000060, on timeline ID 1
2024-08-02 14:52:37.259 CST [12678] LOG:  redo starts at 0/26000028
2024-08-02 14:52:37.260 CST [12678] LOG:  completed backup recovery with redo LSN 0/26000028 and end LSN 0/26000100
2024-08-02 14:52:37.260 CST [12678] LOG:  consistent recovery state reached at 0/26000100
2024-08-02 14:52:37.260 CST [12678] LOG:  redo done at 0/26000100 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2024-08-02 14:52:37.316 CST [12676] LOG:  checkpoint starting: end-of-recovery immediate wait
2024-08-02 14:52:37.335 CST [12676] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 3 recycled; write=0.001 s, sync=0.005 s, total=0.021 s; sync files=2, longest=0.003 s, average=0.003 s; distance=16384 kB, estimate=16384 kB; lsn=0/27000028, redo lsn=0/27000028
2024-08-02 14:52:37.339 CST [12674] LOG:  database system is ready to accept connections
 done
server started