使用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
数据库已启动。
