进阶数据库系列(二十):PostgreSQL 数据库备份与恢复

本文详细介绍了PostgreSQL数据库的备份与恢复方法,包括物理备份(冷备冷恢复、热备冷恢复)和逻辑备份(热备热恢复)。重点讨论了pg_basebackup工具的使用,包括备份和恢复的步骤,以及归档文件的处理。此外,还提到了pg_dump的逻辑备份,以及Barman安装和备份PostgreSQL的相关内容。通过本文,读者可以全面了解PostgreSQL的备份恢复策略。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

前面介绍了 PostgreSQL 事务与并发控制、主从复制高可用方案、基于 Patroni 高可用架构部署及故障切换、 基于 repmgr 高可用架构实践、基于 pgpool 实现读写分离实践等相关的知识点,今天我将详细的为大家介绍 PostgreSQL 数据库备份与恢复相关知识,希望大家能够从中收获多多!如有帮助,请点在看、转发支持一波!!!

今天将就比较常用的postgresql数据库备份做一个比较详尽的总结。

PostgreSQL 备份工具

Image

物理备份

冷备冷恢复(第一种物理备份方式)

此种方式是直接备份数据库物理文件,在pg数据库里,这通常指的是PGDATA变量定义的文件夹,例如:

[root@EULEER ~]# echo $PGDATA/usr/local/pgsql/data

我的这个示例数据库是安装在了/usr/local/pgsql目录下,那么,备份如下,文件备份到了/media目录下了:

cp -r /usr/local/pgsql/data /media/

但需要注意的是,此种方式由于比较直接,不管是否数据库有无IO情况,因此,备份的时候需要先停止数据库,恢复的时候要删除原数据库文件,重新覆盖回去后,才可以在启动数据库,如果在数据库启动的时候备份,那么,可能会造成数据备份不全,也可以理解为冷备方式。

物理备份的恢复
[root@EULEER ~]# rm -rf /usr/local/pgsql/data/*
[root@EULEER ~]# cp -r /media/data/* /usr/local/pgsql/data/
[root@EULEER ~]# bash start-pgsql.sh 
waiting for server to start....2023-03-09 08:18:58.143 CST [23561] LOG:  starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (EulerOS 4.8.5-28), 64-bit
2023-03-09 08:18:58.144 CST [23561] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-03-09 08:18:58.144 CST [23561] LOG:  listening on IPv6 address "::", port 5432
2023-03-09 08:18:58.148 CST [23561] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-03-09 08:18:58.224 CST [23562] LOG:  database system was shut down at 2023-03-09 08:04:04 CST
2023-03-09 08:18:58.232 CST [23561] LOG:  database system is ready to accept connections
 done
server started

那么,此种方式比较霸道,也可以说是野蛮,因此,优点是简单,直接,高效,缺点是需要停机维护,不能实时备份,算是冷备范畴。另一个缺点是,一般生产环境的数据库由于经年累月的使用,数据库一般会是非常大的,几百G 上T也是经常的事情,而cp命令的效率是和文件大小是有关联的,受限于此,这个时候的备份和恢复是比较困难的。更多关于 PostgreSQL 系列的学习文章,请参阅:PostgreSQL 数据库,本系列持续更新中。

热备冷恢复(第二种物理备份方式)
pg_basebackup 基本原理和步骤

pg_basebackup 也算是物理方式,是可以使用流协议。并且是热备范畴,备份的时候不需要停数据库,但,恢复的时候需要停数据库(多说一句,某些项目数据库是24小时不能停的,如果停了,可能接受不了,因此,备份前最好搞清楚自己是否能够接受冷备)。

备份:使用pg_basebackup命令来进行备份,这个命令可以将postgresql的数据文件备份为两个压缩文件:base.tar和 pg_wal.tar。本别是数据文件和归档文件,恢复的时候,需要设置按照归档文件来恢复。那么,此种方式的备份可以备份自定义表空间。

恢复:需要先把备份的压缩文件替换当前的数据文件,然后修改 postgresql.conf,因为这个配置文件在data文件夹中,所以只能是在把base.tar解压到数据库当前数据位置,也就是我们默认初始化指定的数据保存位置data文件夹中,才能修改配置,在配置好归档设置以后,可以启动pgsql服务,进行启动恢复。

在恢复过程中,会拷贝归档文件,进行数据恢复。

恢复成功,也就是数据库服务启动成功。这个时候我们访问数据库,它是作为归档状态存在的,所以只能读,不能写操作。

为了恢复数据库写操作,我们需要在命令行下执行切换数据库状态的指令。切换成功之后,才可以进行读写操作。

实操示例

环境介绍:数据库主节点,IP:192.168.123.60,数据库端口:5432,数据库安装路径为/usr/local/pgsql ,管理用户为pg1。

创建一个自定义表空间,创建表空间存放路径。

[root@EULER1 ~]# mkdir /opt/custome-tablespace
[root@EULER1 ~]# chown -Rf pg1. /opt/custome-tablespace/

登陆命令行,创建表空间

[root@EULER1 ~]# su - pg1 -c "psql -Upostgres -p 5432 -h 192.168.123.60"
Password for user postgres: 
psql (12.5)
Type "help" for help.
 
postgres=# create tablespace mytbs location '/opt/custome-tablespace';
CREATE TABLESPACE
postgres=# \db+
                                          List of tablespaces
    Name    |  Owner   |        Location         | Access privileges | Options |  Size   | Description 
------------+----------+-------------------------+-------------------+---------+---------+-------------
 mytbs      | postgres | /opt/custome-tablespace |                   |         | 0 bytes | 
 pg_default | pg1      |                         |                   |         | 23 MB   | 
 pg_global  | pg1      |                         |                   |         | 623 kB  | 
(3 rows)

此时的$PGDATA目录下的pg_tblspc目录下有一个软链接:

[root@EULER1 ~]# ls -al /usr/local/pgsql/data/pg_tblspc/
total 8
drwx------.  2 pg1 pg1 4096 Mar 19 20:06 .
drwx------. 20 pg1 pg1 4096 Mar 19 18:39 ..
lrwxrwxrwx   1 pg1 pg1   23 Mar 19 20:06 32771 -> /opt/custome-tablespace

创建一个新库和新表在此表空间内:

postgres=# create database test;
CREATE DATABASE
postgres=# alter database test set tablespace mytbs;
ALTER DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table tb_mytps(i int,name varchar(32)) tablespace mytbs;
CREATE TABLE

插入实验数据

insert into tb_mytps(i,name) values(2,'name2');
insert into tb_mytps(i,name) values(3,'name3');
insert into tb_mytps(i,name) values(4,'name4');
insert into tb_mytps(i,name) values(5,'name5');
insert into tb_mytps(i,name) values(6,'name6');
insert into tb_mytps(i,name) values(7,'name7');
insert into tb_mytps(i,name) values(8,'name8');
insert into tb_mytps(i,name) values(9,'name9');
insert into tb_mytps(i,name) values(10,'name10');

查看是否正确插入数据:

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from tb_mytps ;
 i  |  name  
----+--------
  1 | name1
  2 | name2
  3 | name3
  4 | name4
  5 | name5
  6 | name6
  7 | name7
  8 | name8
  9 | name9
 10 | name10
(10 rows)
开始备份

建立备份文件存放路径

[root@EULER1 ~]# mkdir /opt/backup
[root@EULER1 ~]# chown -Rf pg1. /opt/backup

可以看到有两个tablespace被备份了,-z -Z 5是压缩等级,范围是0-9, -Ft是tar包格式备份。

[root@EULER1 ~]# su - pg1 -c "pg_basebackup  -D /opt/backup -Ft -z -Z 5 -Pv  -Upostgres   -h 192.168.123.60  -p5432"
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/8000028 on timeline 2
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_119924"
32567/32567 kB (100%), 2/2 tablespaces                                         
pg_basebackup: write-ahead log end point: 0/8000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

查看备份的文件:

[root@EULER1 backup]# ls -al /opt/backup/
total 48968
drwx------  2 pg1  pg1      4096 Mar 19 20:27 .
drwxr-xr-x. 5 root root     4096 Mar 19 20:24 ..
-rw-------  1 pg1  pg1   8162304 Mar 19 20:27 32771.tar
-rw-------  1 pg1  pg1  25188352 Mar 19 20:27 base.tar
-rw-------  1 pg1  pg1  16780288 Mar 19 20:27 pg_wal.tar

备份完成后,删除test数据库,看看一会能否恢复。更多关于 PostgreSQL 系列的学习文章,请参阅:PostgreSQL 数据库,本系列持续更新中。

恢复

停止数据库:

[root@EULER1 ~]# bash ~/stop-pgsql.sh 
waiting for server to shut down.... done
server stopped

删除原库的数据文件:

[root@EULER1 ~]# rm -rf /usr/local/pgsql/data/*
[root@EULER1 ~]# rm -rf /opt/custome-tablespace/*

解压备份文件到对应路径:要注意是解压到哪里

[root@EULER1 ~]# tar xf /opt/backup/32771.tar -C /opt/custome-tablespace/
[root@EULER1 ~]# tar xf /opt/backup/base.tar -C /usr/local/pgsql/data/

创建wal文件存放路径:赋予数据库管理用户权限,最后一个命令不能忘

[root@EULER1 ~]# mkdir /usr/local/pgsql/wal-back
[root@EULER1 ~]# tar xf /opt/backup/pg_wal.tar -C /usr/local/pgsql/wal-back/
[root@EULER1 data]# chown -Rf pg1. /usr/local/pgsql/

修改 postgresql.conf 文件

任选一种恢复方式
  • 立刻恢复

restore_command = 'cp /usr/local/pgsql/wal-back/%f %p'
recovery_target = 'immediate' 
  • 可以按时间线恢复到最新

恢复到最新: 
restore_command = 'cp /usr/local/pgsql/wal-back/%f %p'
recovery_target_timeline = 'latest'
  • 按时间点恢复

restore_command = 'cp /usr/local/pgsql/wal-back/%f %p' 
recovery_target_time = '2023-03-19 16:16:16.007657+08'

如果不想进入备份模式,直接数据库启动就可以用,那么就使用promote。

recovery_target_action  #指定在达到恢复目标时服务器采取的动作。 
pause  #默认值,表示恢复将被暂停
promote  #表示恢复结束且服务器将开始接受连接
shutdown #表示在达到恢复目标之后停止服务器。
启动数据库
[pg1@EULER1 ~]$ pg_ctl -D /usr/local/pgsql/data/ start
waiting for server to start....2023-03-19 21:17:49.399 CST [28172] LOG:  starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (EulerOS 4.8.5-28), 64-bit
2023-03-19 21:17:49.400 CST [28172] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-03-19 21:17:49.400 CST [28172] LOG:  listening on IPv6 address "::", port 5432
2023-03-19 21:17:49.405 CST [28172] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-03-19 21:17:49.444 CST [28172] LOG:  redirecting log output to logging collector process
2023-03-19 21:17:49.444 CST [28172] HINT:  Future log output will appear in directory "log".
 done
server started

查看数据库状态:此时的数据库是备份状态,因此 pg_ctl promote即可。

[root@EULER1 data]# pg_controldata 
pg_control version number:            1201
Catalog version number:               201909212
Database system identifier:           7211655189372047015
Database cluster state:               in archive recovery
pg_control last modified:             Sun 19 Mar 2023 09:17:49 PM CST
Latest checkpoint location:           0/A000060
Latest checkpoint's REDO location:    0/A000028

或者是进入命令行,执行以下函数即可:

select pg_wal_replay_resume();

最终确认是否恢复正常:

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select *from tb_mytps;
 i  |  name  
----+--------
  1 | name1
  2 | name2
  3 | name3
  4 | name4
  5 | name5
  8 | name8
  9 | name9
 10 | name10
  6 | name61
  7 | name71
(10 rows)
test=# update  tb_mytps set name = 'name999' where i = 7;
UPDATE 1
test=# select * from tb_mytps;
 i  |  name   
----+---------
  1 | name1
  2 | name2
  3 | name3
  4 | name4
  5 | name5
  8 | name8
  9 | name9
 10 | name10
  6 | name61
  7 | name999
(10 rows)

OK,利用 pg_basebackup 命令归档备份完全成功了。更多关于 PostgreSQL 系列的学习文章,请参阅:PostgreSQL 数据库,本系列持续更新中。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值