PostgreSQL数据库管理第九章备份恢复

概述

任何系统都有崩溃的可能，数据库备份工作的重要性毋庸置疑。通过备份和恢复来保护数据，避免数据丢失，在发生灾难或人为误操作的情况下，能够进行恢复是DBA的日常最重要的工作。不仅要保证能够成功备份，还要保证备份数据能够恢复，如果能在更短的时间进行恢复更是锦上添花。利用现有资源，基于现实情况考虑，制定严谨、可靠的备份策略，应对可能出现的需要恢复的情况是每个DBA都应该掌握的基本技能。

1逻辑/物理

psql, pg_dump, pg_restore,copy,rsync

2第三方全量备份/增量

pg_rman, pg_probackup

3常用工具

pg_dump, pg_restore, pg_rman, pg_probackup

PostgreSQL数据库管理第九章备份恢复概述9.1逻辑备份 pg_dump9.2 逻辑备份pg_dumpall9.3 物理备份pg_basebackup9.4 pg_restore9.5 实例9.6 物理备份pg_basebackup与流复制9.7 archive_command、restore_command

9.1逻辑备份 pg_dump

pg_dump — 把PostgreSQL数据库抽取为一个脚本文件或其他归档文件。

pg_dump是用于备份一种PostgreSQL数据库的工具。即使数据库正在被并发使用，它也能创建一致的备份。pg_dump不阻塞其他用户访问数据库（读取或写入）。

pg_dump只转储单个数据库。要备份一个集簇中对于所有数据库公共的全局对象（例如角色和表空间），应使用 pg_dumpall。

9.1.1 实例

1 备份数据库

-Fc 是压缩的意思

[postgr[email protected] ~]$ pg_dump -d postgres -f /db-backups/p20191026.dump

[[email protected] ~]$ pg_dump -Fc -d postgres -f /db-backups/pfc20191026.dump

[[email protected] ~]$ ll -h /db-backups/

总用量 37M

-rw-rw-r-- 1 postgres postgres 37M 10月 26 17:49 p20191026.dump

-rw-rw-r-- 1 postgres postgres 118K 10月 26 17:49 pfc20191026.dump

2 备份表

[[email protected] ~]$ pg_dump -Fc -d newdb -t hctsyj -f /db-backups/newdb-t-hctsyj-20191026.dump

9.1.3 检查备份

[[email protected] ~]$ pg_restore -l /db-backups/pfc20191026.dump

;

; Archive created at 2019-10-26 17:49:33 CST

; dbname: postgres

; TOC Entries: 34

; Compression: -1

; Dump Version: 1.13-0

; Format: CUSTOM

; Integer: 4 bytes

; Offset: 8 bytes

; Dumped from database version: 11.4

; Dumped by pg_dump version: 11.4

;

; Selected TOC Entries:

;

10; 2615 18758 SCHEMA - hct syj

2; 3079 18688 EXTENSION - pg_repack

3186; 0 0 COMMENT - EXTENSION pg_repack

3; 3079 18634 EXTENSION - pgstattuple

3187; 0 0 COMMENT - EXTENSION pgstattuple

201; 1259 18644 TABLE public bloat_stats postgres

3188; 0 0 COMMENT public TABLE bloat_stats postgres

203; 1259 18664 TABLE public bloat_indexes postgres

9.2 逻辑备份pg_dumpall

pg__dumpall一将一个PostgreSQL数据库集簇抽取到一个脚本文件中

pg_dumpall工具可以一个集簇中所有的PostgreSQL数据库写出到（“转储”）一个脚本文件。该脚本文件包含可以用作psql的输入SQL命令来恢复数据库。它会对集簇中的每个数据库调用pg_dump来完成该工作。pg_dumpall还转储对所有数据库公用的全局对象（pg_dump不保存这些对象）。目前这包括适数据库用户和组、表空间以及适合所有数据库的访问权限等属性。

--verbose

指定细节模式。这将导致pg__dumpall向标准错误输出详细的对象注释以及转储文件的开始/停止时间，还有进度消息。它也会启用Pg__dump中的细节输出。

9.2.1 实例

1 备份整个数据库集群

[[email protected] ~]$ pg_dumpall -v >/db-backups/pg-cluster-20191026.out

在新服务上面恢复，但是配置文件时没有恢复过去的。就是把数据库COPY过去了。

[postgre[email protected] ~]$ psql -f /db-backup/pg-cluster-20191026.out postgres

9.3 物理备份pg_basebackup

pg_ basebackup被用于获得一个正在运行的PostgreSQL数据库集簇的基础备份。获得这些备份不会影响连接到该数据库的其他客户端，并且可以被用于时间点恢复，以及用作一个日志传送或流复制后备服务器的开始点、

pg_ basebackup建立数据库集簇文件的一份二进制副本，同时保证系统进入和退出备份模式。备份总是从整个数据库集簇获得，不可能备份单个数据库或数据库对象。关于个体数据库备份，必须使用一个像pg. _dump的工具。

pg_basebackup -h 192.168.91.132 -p 5432 -U repmgr -D /pg_backup/20200228 -Ft -R -z -v --checkpoint=fast

主要选项说明

9.3.1 建立基础备份

[[email protected] ~]$ pg_basebackup -h 192.168.91.132 -p 5432 -U repmgr -D /pg_backup/20200229 -Ft -R -z -v --checkpoint=fast

pg_basebackup: initiating base backup, waiting for checkpoint to complete

pg_basebackup: checkpoint completed

pg_basebackup: write-ahead log start point: 0/12000028 on timeline 7

pg_basebackup: starting background WAL receiver

pg_basebackup: created temporary replication slot "pg_basebackup_23867"

pg_basebackup: write-ahead log end point: 0/12002438

pg_basebackup: waiting for background process to finish streaming ...

pg_basebackup: base backup completed

9.3.2 模拟主备数据库全被删除恢复到最近时间点

1 创建测试数据

T_STATION=# insert into syj.t_test select generate_series(201,300),md5(random()::text),clock_timestamp();

INSERT 0 100

T_STATION=# insert into syj.t_test select generate_series(301,400),md5(random()::text),clock_timestamp();

INSERT 0 100

2 做破坏

/usr/local/pgsql/bin/pg_ctl stop -D /usr/local/pgsql/data -m f

由于有repmgr需要把辅助的机器也停了

在主机上面把数据迁移

mv /usr/local/pgsql/data/* /usr/local/pgsql/data2020/

3 解压使用pg_basebackup的基础备份

发现问题以后，解压数据，马上关闭备机。

tar -zxvf /pg_backup/20200229/base.tar.gz -C /usr/local/pgsql/data/

4 修改recovery.conf 添加一下内容，恢复到最近时间点

restore_command = 'cp /usr/local/pgsql/pg_archive/%f %p'

recovery_target_timeline = 'latest'

5 启动数据库，查看效果，是只读模式，需要修改

postgres=# \c T_STATION

You are now connected to database "T_STATION" as user "postgres".

T_STATION=# insert into syj.t_test select generate_series(2,100),md5(random()::text),clock_timestamp();

ERROR: cannot execute INSERT in a read-only transaction

6 修改参数recovery.conf，重启数据库恢复正常

mv recovery.conf recovery20200229.conf

/usr/local/pgsql/bin/pg_ctl restart -D /usr/local/pgsql/data

T_STATION=# insert into syj.t_test select generate_series(2,100),md5(random()::text),clock_timestamp();

INSERT 0 99

[[email protected] data]$ repmgr -f /etc/repmgr.conf cluster show

9.3.3 no 模拟不小心删除数据，恢复到指定时间点有大问题。

1 测试数据

T_STATION=# select count(*) from syj.t_test;

count

----------

10000297

(1 row)

T_STATION=# select current_timestamp;

current_timestamp

-------------------------------

2020-02-29 20:29:35.373115+08

(1 row)

操作同上唯一不同

restore_command = 'cp /usr/local/pgsql/pg_archive/%f %p'

recovery_target_time = '2020-02-29 20:29:35'

tar -zxvf /pg_backup/20200229/base.tar.gz -C /usr/local/pgsql/data/

/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data

[[email protected] data]$ pg_controldata

Database cluster state: in archive recovery

[[email protected] data]$ mv recovery.conf recovery2020.conf

/usr/local/pgsql/bin/pg_ctl restart -D /usr/local/pgsql/data

为什么重启以后从10000297变成了99行，不重启，一直是归档模式。

postgres=# \c T_STATION

You are now connected to database "T_STATION" as user "postgres".

T_STATION=# select count(*) from syj.t_test;

count

-------

(1 row)

9.3.4 no 对于流复制是否又有意义，还原到指定还原点

有时候我们会希望将数据恢复到某一个重要事件发生之前的状态，例如对表做了一些变更，希望恢复到变更之前。这种情况可以在重要事件发生时创建一个还原点，通过基础备份和归档恢复到事件发生之前的状态。

1 创建还原点

T_STATION=# select count(*) from syj.t_test;

count

-------

(1 row)

select pg_switch_wal(); ##手动归档日志

T_STATION=# select pg_create_restore_point ('time202003022332');

pg_create_restore_point

-------------------------

0/120000C8

(1 row)

2 备份数据后删除数据

pg_basebackup -h 192.168.91.132 -p 5432 -U repmgr -D /pg_backup/20200229 -Ft -R -z -v --checkpoint=fast

T_STATION=# truncate table syj.t_test;

TRUNCATE TABLE

tar -zxvf /pg_backup/20200229/base.tar.gz -C /usr/local/pgsql/data/

restore_command = 'cp /usr/local/pgsql/pg_archive/%f %p'

recovery_target_name= 'restore_point'

3 重启数据库-恢复数据查看效果

T_STATION=# select count(*) from syj.t_test;

count

-------

(1 row)

4 为什么在重启就变成0了

/usr/local/pgsql/bin/pg_ctl restart -D /usr/local/pgsql/data

T_STATION=# select count(*) from syj.t_test;

count

-------

(1 row)

9.3.5 还原到制定事务

PostgreSQL还提供了-种可以恢复到指定事务的方法，下面我们通过一个小实验演示如何将数据库恢复到指定的事务之前的状态。

9.4 pg_restore

pg_restore是一个由pg_dump创建的归档文件恢复一个PostgreSQL数据库。

pg_restore是一个用来从pg_dump创建的非文本格式归档恢复PostgreSQL数据库的工具。它将发出必要的命令把该数据库重建成它被保存时的状态。这些归档文件还允许pg_restore选择恢复哪些内容或者在恢复前对恢复项重排序。这些归档文件被设计为可以在不同的架构之间迁移。

pg_restore可以在两种模式下操作。如果指定了一个数据库名称，pg_restore会连接那个数据库并且把归档内容直接恢复到该数据库中。否则，会创建一个脚本，其中包含着重建该数据库所必要的 SQL 命令，它会被写入到一个文件或者标准输出。这个脚本输出等效于pg_dump的纯文本输出格式。因此，一些控制输出的选项与pg_dump的选项类似。

9.4.1 实例

1 恢复表

postgres=# drop table test1;

DROP TABLE

[[email protected] ~]$ pg_restore -j 10 -d postgres -t test1 /db-backups/pfc20191026.dump

postgres=# select * from test1 limit 5;

id | name | age

----+------+-----

1 | syj | 25

2 | hct | 21

3 | jjj | 27

3 | lhr | 25

1 | syj | 25

pg_restore -j 10 -d T_STATION -t nettoll.s34_exit_hour_check /usr/local/pgsql/data/ T_STATION202001.dump

2 恢复数据库

[[email protected] ~]$ pg_dump -Fc -d newdb -f /db-backups/newdb20191026.dump

postgres=# drop database newdb;

DROP DATABASE

[[email protected] ~]$ pg_restore -C -d newdb /db-backups/newdb20191026.dump

pg_restore: [archiver (db)] connection to database "newdb" failed: FATAL: database "newdb" does not exist

[[email protected] ~]$ pg_restore -C -d postgres /db-backups/newdb20191026.dump

-d开关中提到的数据库可以是任何已经存在于集簇中的数据库，pg_restore只会用它来为newdb发出CREATE DATABASE命令。通过-C，数据总是会被恢复到出现在归档文件的数据库名中.

3 根据备份建立新数据库

[[email protected] ~]$ createdb -T template0 T_STATION;

[[email protected] ~]$ pg_restore -j 10 -d newdb /db-backups/pfc20191026.dump

postgres=# \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

newdb=# select * from hctsyj limit 3;

id | name | age | money

----+------+-----+-------

1 | hct | 18 | 10000

2 | lhr | 22 | 2000

3 | jjj | 34 | 12000

(3 rows)

9.5 实例

9.5.1 Error pgstattuple.control": 没有那个文件或目录

pg_restore -j 10 -d T_STATION /usr/local/pgsql/data/T_STATION20200120.dump

报错

pg_restore: [archiver (db)] could not execute query: ERROR: could not open extension control file "/usr/local/pgsql/share/extension/pgstattuple.control": 没有那个文件或目录

Command was: CREATE EXTENSION IF NOT EXISTS pgstattuple WITH SCHEMA public;

yum install postgresql-contrib.x86_64 -y

9.6 物理备份pg_basebackup与流复制

流复制一般有多台机器，当主机进行pg_basebackup与恢复以后，势必主机与从机的scn不一致，需要处理一下。

9.6.1 主机启动处理

当主机恢复以后，修改争取的recovery.done文件带流复制功能的。

[[email protected] ago]$ cat /usr/local/pgsql/data/recovery.done

standby_mode = 'on'

primary_conninfo = 'user=repmgr connect_timeout=2 host=192.168.198.130 application_name=192.168.198.129'

recovery_target_timeline = 'latest'

primary_slot_name = repmgr_slot_1

pg_ctl start -D /usr/local/pgsql/data/

9.6.2 repmer处理

repmgr -f /etc/repmgr.conf primary register –force

9.6.3 辅助机器处理-辅助机器强制克隆主数据库变辅助加--force

repmgr -h 192.168.198.129 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone –force

9.6.4初始化数据库与强制注册备库

pg_ctl -D /usr/local/pgsql/data start

[postgr[email protected] ~]$ repmgr -f /etc/repmgr.conf standby register --force

INFO: connecting to local node "192.168.198.130" (ID: 2)

INFO: connecting to primary database

INFO: standby registration complete

NOTICE: standby node "192.168.198.130" (ID: 2) successfully registered

9.6.3 测试

[[email protected] ~]$ repmgr -f /etc/repmgr.conf cluster show

----+-----------------+---------+-----------+-----------------+----------+--

1 | 192.168.198.129 | primary | * running | | default | 1

2 | 192.168.198.130 | standby | running | 192.168.198.129 | default |

9.7 archive_command、restore_command

9.7.1 非压缩

archive_command = 'cp %p /usr/local/pgsql/pg_archive/%f '

restore_command = 'cp /usr/local/pgsql/pg_archive/%f %p'

9.7.2 gzip压缩

archive_command = '/bin/gzip -c %p > /home/postgres/archive_wal/%f.gz '

restore_command = ' /bin/gunzip -c /home/postgres/archive_wal/%f.gz > %p '

PostgreSQL数据库管理第九章备份恢复概述9.1逻辑备份 pg_dump9.2 逻辑备份pg_dumpall9.3 物理备份pg_basebackup9.4 pg_restore9.5 实例9.6 物理备份pg_basebackup与流复制9.7 archive_command、restore_command

概述

9.1逻辑备份 pg_dump

9.1.1 实例

1 备份数据库

2 备份表

9.1.3 检查备份

9.2 逻辑备份pg_dumpall

9.2.1 实例

9.3 物理备份pg_basebackup

9.3.1 建立基础备份

9.3.2 模拟主备数据库全被删除恢复到最近时间点

1 创建测试数据

2 做破坏

3 解压使用pg_basebackup的基础备份

4 修改recovery.conf 添加一下内容，恢复到最近时间点

5 启动数据库，查看效果，是只读模式，需要修改

6 修改参数recovery.conf，重启数据库恢复正常

9.3.3 no 模拟不小心删除数据，恢复到指定时间点有大问题。

9.3.4 no 对于流复制是否又有意义，还原到指定还原点

1 创建还原点

2 备份数据后删除数据

3 重启数据库-恢复数据查看效果

4 为什么在重启就变成0了

9.3.5 还原到制定事务

9.4 pg_restore

9.4.1 实例

1 恢复表

2 恢复数据库

3 根据备份建立新数据库

9.5 实例

9.5.1 Error pgstattuple.control": 没有那个文件或目录

9.6 物理备份pg_basebackup与流复制

9.6.1 主机启动处理

9.6.2 repmer处理

9.6.3 辅助机器处理-辅助机器强制克隆主数据库变辅助 加--force

9.6.4初始化数据库与强制注册备库

9.6.3 测试

9.7 archive_command、restore_command

9.7.1 非压缩

9.7.2 gzip压缩

继续阅读

9.6.3 辅助机器处理-辅助机器强制克隆主数据库变辅助加--force