PostgreSQL备份恢复

PostgreSQL备份的内容包括：

  • 数据（配置文件）
  • 归档WAL日志
  • 表空间目录

备份方式：

  • 逻辑备份：适用于跨版本和跨平台的备份恢复，postgresql提供了pg_dump和pg_dumpall命令进行数据库的逻辑备份。
  • 物理备份：适用于小版本的恢复，但不支持跨平台和大版本

逻辑备份：

pg_dump: 可以将数据库备份成一个文本文件或归档文件，包含多个 create 和 insert 语句，使用这些语句可以重新创建表并插入数据

pg_dumpall: 可以存储一个数据库集群里的所有数据库到一个脚本文件，本质上是通过对集群的每个数据库执行pg_dump实现这一功能

pg_restore: 配合pg_dump生成的文件，可以恢复数据库

使用pg_dump备份和pg_restore恢复范例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
# 查看当前数据库
[postgres@Rocky8-2 ~]$ psql  -h 10.0.0.12 -p 5432 -U postgres
Password for user postgres: 
psql (15.3)
Type "help" for help.

postgres=# \l
                                            List of databases
   Name    |  Owner   | Encoding | Collate | Ctype | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+---------+-------+------------+-----------------+-----------------------
 hellodb   | postgres | UTF8     | C       | C     |            | libc            | 
 postgres  | postgres | UTF8     | C       | C     |            | libc            | 
 template0 | postgres | UTF8     | C       | C     |            | libc            | =c/postgres          +
           |          |          |         |       |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     |            | libc            | =c/postgres          +
           |          |          |         |       |            |                 | postgres=CTc/postgres
 testdb1   | postgres | UTF8     | C       | C     |            | libc            | 
 testdb2   | postgres | UTF8     | C       | C     |            | libc            | 
(6 rows)

## pg_dump备份
# 备份hellodb数据库(备份时可以-C选项在备份文件中生成创建数据库语句)
[root@Rocky8-2 ~]# mkdir /backup 
[root@Rocky8-2 ~]# pg_dump -Fc -h 10.0.0.12 -U postgres  -f /backup/hellodb_backup hellodb
Password: 

# 查看备份的文件内容
[root@Rocky8-2 ~]# pg_restore -l /backup/hellodb_backup 
;
; Archive created at 2024-01-15 13:19:49 CST
;     dbname: hellodb
;     TOC Entries: 25
;     Compression: -1
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 15.3
;     Dumped by pg_dump version: 15.3
;
;
; Selected TOC Entries:
;
214; 1259 16420 TABLE public classes postgres
215; 1259 16426 TABLE public coc postgres
216; 1259 16431 TABLE public courses postgres
217; 1259 16436 TABLE public scores postgres
218; 1259 16441 TABLE public students postgres
219; 1259 16446 TABLE public teachers postgres
220; 1259 16452 TABLE public toc postgres
2625; 0 16420 TABLE DATA public classes postgres
2626; 0 16426 TABLE DATA public coc postgres
2627; 0 16431 TABLE DATA public courses postgres
2628; 0 16436 TABLE DATA public scores postgres
2629; 0 16441 TABLE DATA public students postgres
2630; 0 16446 TABLE DATA public teachers postgres
2631; 0 16452 TABLE DATA public toc postgres
2470; 2606 16425 CONSTRAINT public classes classes_pkey postgres
2472; 2606 16430 CONSTRAINT public coc coc_pkey postgres
2474; 2606 16435 CONSTRAINT public courses courses_pkey postgres
2476; 2606 16440 CONSTRAINT public scores scores_pkey postgres
2478; 2606 16445 CONSTRAINT public students students_pkey postgres
2480; 2606 16451 CONSTRAINT public teachers teachers_pkey postgres
2482; 2606 16456 CONSTRAINT public toc toc_pkey postgres
[root@Rocky8-2 ~]# ls /backup
testdb1_backup


## pg_restore 还原
#先创建一个数据库才能开始还原
[root@Rocky8-2 ~]# psql -U postgres -h 10.0.0.12 -c "create database hellodb2"
Password for user postgres: 
CREATE DATABASE

#将备份还原到hellodb2中
[root@Rocky8-2 ~]# pg_restore -h 10.0.0.12 -U postgres -d hellodb2 /backup/hellodb_backup
Password: 

#查看恢复的数据库hellodb2
[root@Rocky8-2 ~]# psql  -h 10.0.0.12 -p 5432 -U postgres hellodb2
Password for user postgres: 
psql (15.3)
Type "help" for help.

hellodb2=# \l
                                            List of databases
   Name    |  Owner   | Encoding | Collate | Ctype | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+---------+-------+------------+-----------------+-----------------------
 hellodb   | postgres | UTF8     | C       | C     |            | libc            | 
 hellodb2  | postgres | UTF8     | C       | C     |            | libc            | 
 postgres  | postgres | UTF8     | C       | C     |            | libc            | 
 template0 | postgres | UTF8     | C       | C     |            | libc            | =c/postgres          +
           |          |          |         |       |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     |            | libc            | =c/postgres          +
           |          |          |         |       |            |                 | postgres=CTc/postgres
 testdb1   | postgres | UTF8     | C       | C     |            | libc            | 
 testdb2   | postgres | UTF8     | C       | C     |            | libc            | 
(7 rows)

hellodb2-# \d
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | classes  | table | postgres
 public | coc      | table | postgres
 public | courses  | table | postgres
 public | scores   | table | postgres
 public | students | table | postgres
 public | teachers | table | postgres
 public | toc      | table | postgres
(7 rows)

物理备份

冷备份

数据库实例有关的配置文件和数据文件都存放在 $PGDATA 目录下，只需停止数据库，然后将pgdata目录拷贝备份即可。

范例：利用逻辑卷快照功能实现数据库冷备份（快照生成快，停服时间相对较短）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
# 检查数据是否在逻辑巻上，查询得知 / 在rl-root 逻辑卷上，确保空间足够
[root@Rocky8-2 ~]# lvs
  LV              VG Attr       LSize   Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  pgdata_snapshot rl swi-a-s---   1.00g      root   0.09                                   
  root            rl owi-aos--- <32.00g                                                    
  swap            rl -wi-ao----   2.00g                                                    

[root@Rocky8-2 ~]# df 
Filesystem          1K-blocks    Used Available Use% Mounted on
devtmpfs               972852       0    972852   0% /dev
tmpfs                  992912    1052    991860   1% /dev/shm
tmpfs                  992912    8956    983956   1% /run
tmpfs                  992912       0    992912   0% /sys/fs/cgroup
/dev/mapper/rl-root  17811456 2634612  15176844  15% /
/dev/nvme0n1p1        1038336  197780    840556  20% /boot
tmpfs                  198580       0    198580   0% /run/user/0


# 创建逻辑卷快照（保证空间足够）
[root@Rocky8-2 ~]# systemctl stop postgresql.service ; lvcreate -n pgdata_snapshot -s -L 1G /dev/mapper/rl-root ; systemctl start postgresql.service 
  Logical volume "pgdata_snapshot" created.
 
# 挂载逻辑卷快照备份文件
[root@Rocky8-2 ~]# mount /dev/rl/pgdata_snapshot /mnt
mount: /mnt: wrong fs type, bad option, bad superblock on /dev/mapper/rl-pgdata_snapshot, missing codepage or helper program, or other error.
###  这里有报错提示，是因为XFS不允许相同的UUID
[root@Rocky8-2 ~]# dmesg | tail
[   14.618902] vmxnet3 0000:03:00.0 eth0: NIC Link is Up 10000 Mbps
[   30.227772] hub 2-2:1.0: hub_ext_port_status failed (err = -110)
[  269.246498]  nvme0n2: p1
[ 1282.253483] XFS (dm-4): Filesystem has duplicate UUID 8642e5bc-1edc-40d9-ac92-fef3ba887b4f - can't mount

# 重新挂载，指定 nouuid 参数
[root@Rocky8-2 ~]# mount -o nouuid /dev/rl/pgdata_snapshot /mnt
[root@Rocky8-2 ~]# ls /mnt
apps  bin  boot  dev  etc  home  lib  lib64  media  mnt  opt  pgsql  proc  root  run  sbin  srv  sys  tmp  usr  var

# 备份数据库相关文件
[root@Rocky8-2 ~]# cp -a  /mnt/pgsql/data/* /back/

# 删除LVM快照 
[root@Rocky8-2 ~]# umount /mnt
[root@Rocky8-2 ~]# lvremove /dev/rl/pgdata_snapshot 
Do you really want to remove active logical volume rl/pgdata_snapshot? [y/n]: y
  Logical volume "pgdata_snapshot" successfully removed.

PITR备份

point in time recovery 基于时间点的备份

pg_basebackup 基于流复制协议可以实现完全备份，并支持热备份。这个工具是备份整个数据库实例，便用 replication 协议连接数据库，必须在 pg_hba.conf 中对其授权。

1
2
3
# 在 pg_hba 配置文件中授权，增加一行，对所有地址采用MD5验证授权
[root@Rocky8-2 ~]# vim /pgsql/data/pg_hba.conf 
host    replication     all             0.0.0.0/24              md5

范例：用 pg_basebackup 和pg_ba工具进行热备份，模拟故障并还原

20240116102707

1
2
# 在pg服务器上开启归档

PostgreSQL
#Backup #PostgreSQL #pg_dump
PostgreSQL备份恢复
https://www.xcjyc.top/2023/07/31/PostgreSQL备份恢复/
作者
XCJYC
发布于
2023年7月31日
许可协议