PostgreSQL backup and recovery - cold backup & recovery

PostgreSQL 冷备份, 指在数据库关机状态下对数据库的数据文件进行的备份. 

这种备份在生产中并不实用, 一般生产环境都需要保证数据库7*24小时不间断运行.

冷备份需要备份的是数据库集群主目录($PGDATA), 表空间目录, 事务日志(pg_xlog)目录. 如果在参数文件中指定了其他目录或文件, 某些也需要备份下来.

一、在备份前我们先要搞清楚需要备份哪些东西 : 

1. 数据库目录 : 

pg93@db-172-16-3-33-> cd $PGDATA

主目录所在位置. 

pg93@db-172-16-3-33-> pwd
/pgdata1999

主目录中的文件和文件夹 

pg93@db-172-16-3-33-> ll
total 140K
drwx------ 9 pg93 pg93 4.0K May 25 16:33 base
drwx------ 2 pg93 pg93 4.0K May 26 11:07 global
drwxr-xr-x 2 pg93 pg93 4.0K May 26 11:16 pgbak
drwx------ 2 pg93 pg93 4.0K May 24 14:52 pg_clog
-rw------- 1 pg93 pg93 4.6K May 25 17:59 pg_hba.conf
-rw------- 1 pg93 pg93 1.7K May 24 14:30 pg_ident.conf
drwx------ 2 pg93 pg93  12K May 26 11:06 pg_log
drwx------ 4 pg93 pg93 4.0K May  5 20:26 pg_multixact
drwx------ 2 pg93 pg93 4.0K May 26 11:06 pg_notify
drwx------ 2 pg93 pg93 4.0K May  5 20:26 pg_serial
drwx------ 2 pg93 pg93 4.0K May  5 20:26 pg_snapshots
drwx------ 2 pg93 pg93 4.0K May 26 11:06 pg_stat
drwx------ 2 pg93 pg93 4.0K May 26 11:16 pg_stat_tmp
drwx------ 2 pg93 pg93  16K May 16 15:30 pg_subtrans
drwx------ 2 pg93 pg93 4.0K May 26 11:15 pg_tblspc
drwx------ 2 pg93 pg93 4.0K May  5 20:26 pg_twophase
-rw------- 1 pg93 pg93    4 May  5 20:26 PG_VERSION
lrwxrwxrwx 1 pg93 pg93   44 May 26 11:15 pg_xlog -> /pgdata/digoal/1921/data03/pg93/1999/pg_xlog
-rw------- 1 pg93 pg93  20K May 24 09:36 postgresql.conf
-rw------- 1 pg93 pg93   27 May 26 11:06 postmaster.opts
-rw------- 1 pg93 pg93   64 May 26 11:06 postmaster.pid
-rw-r--r-- 1 root root 4.7K May  8 15:37 recovery.done
-rw-r--r-- 1 pg93 pg93 2.5K May 24 13:46 root.crt
-rw-r--r-- 1 pg93 pg93 1.3K May 24 13:37 server.crt
-r-------- 1 pg93 pg93 1.7K May 24 13:32 server.key

2. 事务日志目录

本例为$PGDATA/pg_xlog

3. 表空间目录

进入表空间目录查看表空间 : 

pg93@db-172-16-3-33-> cd pg_tblspc/
pg93@db-172-16-3-33-> ll
total 0
lrwxrwxrwx 1 pg93 pg93 47 May 26 11:15 26417 -> /pgdata/digoal/1921/data03/pg93/1999/tbs_digoal

4. 配置文件中指定的在主目录以外的文件或目录

回到$PGDATA, 查看postgresql.conf中是否配置了$PGDATA以外的其他文件 

pg93@db-172-16-3-33-> grep -E -i "dir|file" postgresql.conf
#data_directory = 'ConfigDir'           # use data in another directory
#hba_file = 'ConfigDir/pg_hba.conf'     # host-based authentication file
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
ssl_cert_file = 'server.crt'            # (change requires restart)
ssl_key_file = 'server.key'             # (change requires restart)
ssl_ca_file = 'root.crt'                        # (change requires restart)
#ssl_crl_file = ''                      # (change requires restart)
#krb_server_keyfile = ''
#include_dir = 'conf.d'                 # include files ending in '.conf' from
                                        # directory 'conf.d'
#include_if_exists = 'exists.conf'      # include file only if it exists
#include = 'special.conf'               # include file

以上配置文件中指定的目录或者文件同样需要备份下来.

二、检查哪些是不需要备份的

1. pg_xlog中的文件是不是需要全部备份呢?

在数据库关闭后, 通过控制文件的信息可以知道需要备份哪些文件.

注意是要在数据库关闭后查看哦, 否则是不准确的. 

pg93@db-172-16-3-33-> pg_controldata
pg_control version number:            937
Catalog version number:               201304271
Database system identifier:           5874470726249995168
Database cluster state:               shut down
pg_control last modified:             Sun 26 May 2013 11:28:29 AM CST
Latest checkpoint location:           E/E9000028
Prior checkpoint location:            E/E8000028
Latest checkpoint's REDO location:    E/E9000028
Latest checkpoint's REDO WAL file:    000000030000000E000000E9
Latest checkpoint's TimeLineID:       3
Latest checkpoint's PrevTimeLineID:   3
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/221848459
Latest checkpoint's NextOID:          26418
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        150439034
Latest checkpoint's oldestXID's DB:   12815
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint:            Sun 26 May 2013 11:28:29 AM CST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            hot_standby
Current max_connections setting:      100
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       16384
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0

我们需要的信息是 

Latest checkpoint location:           E/E9000028
Latest checkpoint's REDO location:    E/E9000028
Latest checkpoint's TimeLineID:       3

从这3个信息都可以得到1个pg_xlog文件名, 表示这个数据库最后一次成功的checkpoint完成的xlog位置. 

000000030000000E000000E9

对于PostgreSQL 9.3直接看这个就要可以了. 

Latest checkpoint's REDO WAL file:    000000030000000E000000E9

因此需要备份的pg_xlog内容是从这个文件开始在内以及后面产生的一系列xlog文件.

本例中就只有这个文件, 如下. 

pg93@db-172-16-3-33-> cd pg_xlog
pg93@db-172-16-3-33-> ll -rt
-rw------- 1 pg93 pg93 16M May 26 11:28 000000030000000E000000E9
drwx------ 2 pg93 pg93 32K May 26 11:28 archive_status

2. 不属于数据库系统的文件时不需要的.

例如在$PGDATA中, 

drwxr-xr-x 2 pg93 pg93 4.0K May 26 11:16 pgbak

这个目录不是数据库集群的目录, 所以不需要备份.

另外要提一下, 尽量不要在$PGDATA或者其他数据库目录中放其他文件, 这样会使备份过程变得复杂, 因为还要去过滤这些文件. 

3. 数据库日志也是不需要备份的, 例如本例中的$PGDATA/pg_log目录.

三、备份

1. 准备好备份需要的空间. 可以是本地的也可以是异地的存储.

查看数据库的大小 : 

digoal=# select round(sum(pg_database_size(oid))/1024/1024.0,2)||'MB' from pg_database;
 ?column?
----------
 73.59MB
(1 row)

准备好可以放下整个备份的目录 

[root@db-172-16-3-33 1999]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/cciss/c0d0p1      29G   16G   12G  56% /
tmpfs                 6.9G     0  6.9G   0% /dev/shm
/dev/mapper/vgdata01-lv03
                      135G   25G  104G  20% /pgdata/digoal/1921/data03
/dev/mapper/vgdata01-lv04
                      135G   69G   59G  54% /pgdata/digoal/1921/data04
/dev/mapper/vgdata01-lv05
                      135G   85G   44G  66% /pgdata/digoal/1921/data05
/dev/mapper/vgdata01-lv06
                       98G  189M   93G   1% /mnt
/mnt/enc_dir           98G  189M   93G   1% /mnt/enc_dir
[root@db-172-16-3-33 1999]# mkdir -p /pgdata/digoal/1921/data04/pg93backup
[root@db-172-16-3-33 1999]# chown pg93:pg93 /pgdata/digoal/1921/data04/pg93backup

2. 停库 

pg93@db-172-16-3-33-> pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped

3. 备份$PGDATA, 排除pg_xlog, pg_log以及不需要备份的目录pgbak. 

pg93@db-172-16-3-33-> rsync -acvz -L --exclude "pg_xlog" --exclude "pgbak" --exclude "pg_log" $PGDATA /pgdata/digoal/1921/data04/pg93backup/

4. 备份pg_xlog 

pg93@db-172-16-3-33-> pg_controldata |grep checkpoint
Latest checkpoint location:           E/EB000028
Prior checkpoint location:            E/EA000028
Latest checkpoint's REDO location:    E/EB000028
Latest checkpoint's REDO WAL file:    000000030000000E000000EB
Latest checkpoint's TimeLineID:       3
Latest checkpoint's PrevTimeLineID:   3
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/221848464
Latest checkpoint's NextOID:          26421
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        150439034
Latest checkpoint's oldestXID's DB:   12815
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint:            Sun 26 May 2013 12:24:37 PM CST

在备份目录中创建pg_xlog目录 

pg93@db-172-16-3-33-> mkdir -p /pgdata/digoal/1921/data04/pg93backup/pgdata1999/pg_xlog

修改目录权限 

pg93@db-172-16-3-33-> chmod 700 /pgdata/digoal/1921/data04/pg93backup/pgdata1999/pg_xlog

查找需要的pg_xlog文件 

pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> ll -rt $PGDATA/pg_xlog/000000030000000E000000E*
-rw------- 1 pg93 pg93 16M May 26 12:24 /pgdata1999/pg_xlog/000000030000000E000000EA
-rw------- 1 pg93 pg93 16M May 26 12:24 /pgdata1999/pg_xlog/000000030000000E000000EB

拷贝需要的pg_xlog文件 

pg93@db-172-16-3-33-> cp $PGDATA/pg_xlog/000000030000000E000000EB /pgdata/digoal/1921/data04/pg93backup/pgdata1999/pg_xlog/

5. 检查备份目录, 是否备份正常 

pg93@db-172-16-3-33-> ll
total 112K
drwx------ 9 pg93 pg93 4.0K May 25 16:33 base
drwx------ 2 pg93 pg93 4.0K May 26 11:49 global
drwx------ 2 pg93 pg93 4.0K May 24 14:52 pg_clog
-rw------- 1 pg93 pg93 4.6K May 25 17:59 pg_hba.conf
-rw------- 1 pg93 pg93 1.7K May 24 14:30 pg_ident.conf
drwx------ 4 pg93 pg93 4.0K May  5 20:26 pg_multixact
drwx------ 2 pg93 pg93 4.0K May 26 11:45 pg_notify
drwx------ 2 pg93 pg93 4.0K May  5 20:26 pg_serial
drwx------ 2 pg93 pg93 4.0K May  5 20:26 pg_snapshots
drwx------ 2 pg93 pg93 4.0K May 26 11:49 pg_stat
drwx------ 2 pg93 pg93 4.0K May 26 11:49 pg_stat_tmp
drwx------ 2 pg93 pg93 4.0K May 16 15:30 pg_subtrans
drwx------ 3 pg93 pg93 4.0K May 26 11:15 pg_tblspc
drwx------ 2 pg93 pg93 4.0K May  5 20:26 pg_twophase
-rw------- 1 pg93 pg93    4 May  5 20:26 PG_VERSION
drwx------ 2 pg93 pg93 4.0K May 26 12:13 pg_xlog
-rw------- 1 pg93 pg93  20K May 24 09:36 postgresql.conf
-rw------- 1 pg93 pg93   27 May 26 11:45 postmaster.opts
-rw-r--r-- 1 pg93 pg93 4.7K May  8 15:37 recovery.done
-rw-r--r-- 1 pg93 pg93 2.5K May 24 13:46 root.crt
-rw-r--r-- 1 pg93 pg93 1.3K May 24 13:37 server.crt
-r-------- 1 pg93 pg93 1.7K May 24 13:32 server.key
pg93@db-172-16-3-33-> cd pg_tblspc/
pg93@db-172-16-3-33-> ll
total 4.0K
drwx------ 3 pg93 pg93 4.0K May 26 11:15 26417

6. 其他

如果表空间目录非常大, 也可以分开进行备份. 在备份$PGDATA时排除pg_tblspc目录即可.

四, 还原

还原冷备份前需要注意几个问题 : 

还原环境中的数据库软件需与备份时的版本一致(例如9.0的备份不能还原到9.1上), 

还原环境中的数据库软件的小版本尽量与原始环境一致(例如备份的版本为9.0.4, 还原的版本也尽量使用9.0.4, 版本升级请参考release说明.)

还原环境中的数据库用到的lib库应该与原始环境一致, 例如原始环境中用到了postgis, 那么还原环境也必须编译同版本的postgis.

还原环境的数据库软件编译项应该与原始环境保持一致, 可以在原始环境的config.log中找到 : 

./configure --prefix=/opt/pgsql9.3beta1 --with-pgport=2099 --with-segsize=8 --with-wal-segsize=64 --with-wal-blocksize=64 --with-perl --with-python --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety

或者使用pg_config也可以得到 

pg93@db-172-16-3-33-> pg_config --configure
'--prefix=/opt/pgsql9.3' '--with-pgport=1999' '--with-perl' '--with-tcl' '--with-python' '--with-openssl' '--with-pam' '--without-ldap' '--with-libxml' '--with-libxslt' '--enable-thread-safety' '--with-wal-blocksize=16' '--enable-debug'

操作系统平台一致(例如linux的备份不能还原到windows上), 

硬件架构一致(例如x86的备份不能还原到小型机上)

如果是在本地环境恢复, 那么不需要考虑以上问题, 如果是在其他服务器上恢复, 请按以上要求配置好还原环境后, 再开始恢复.

1. 把数据库目录, 表空间目录, pg_xlog全部删掉. 

pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> rm -rf *
pg93@db-172-16-3-33-> cd /pgdata/digoal/1921/data03/pg93/1999/pg_xlog
pg93@db-172-16-3-33-> rm -rf *
pg93@db-172-16-3-33-> cd /pgdata/digoal/1921/data03/pg93/1999/tbs_digoal
pg93@db-172-16-3-33-> rm -rf *

2. 还原备份 

pg93@db-172-16-3-33-> cp -r /pgdata/digoal/1921/data04/pg93backup/pgdata1999/* /pgdata1999/

3. 创建日志目录 

pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> mkdir pg_log
pg93@db-172-16-3-33-> chmod 700 pg_log

4. 如果pg_xlog, pg_tblspc使用了软链接.

也恢复一下 : 

pg93@db-172-16-3-33-> mv /pgdata1999/pg_tblspc/26425/* /pgdata/digoal/1921/data03/pg93/1999/tbs_digoal/
pg93@db-172-16-3-33-> rm -rf /pgdata1999/pg_tblspc/26425
pg93@db-172-16-3-33-> ln -s /pgdata/digoal/1921/data03/pg93/1999/tbs_digoal /pgdata1999/pg_tblspc/26425

5. 启动数据库 

pg93@db-172-16-3-33-> pg_ctl start
server starting
pg93@db-172-16-3-33-> LOG:  00000: loaded library "pg_stat_statements"
LOCATION:  load_libraries, miscinit.c:1296

6. 验证, 抽查几条数据看看. 

pg93@db-172-16-3-33-> psql
psql (9.3devel)
Type "help" for help.

digoal=# \db
                           List of tablespaces
    Name    |  Owner   |                    Location
------------+----------+-------------------------------------------------
 pg_default | postgres |
 pg_global  | postgres |
 tbs_digoal | postgres | /pgdata/digoal/1921/data03/pg93/1999/tbs_digoal
(3 rows)
digoal=# select count(*) from test;
 count
-------
 10000
(1 row)

digoal=# \d test
     Table "public.test"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
Tablespace: "tbs_digoal"
时间： 2025-01-31 01:36:08

PostgreSQL backup and recovery - cold backup & recovery的相关文章

PostgreSQL backup and recovery - online backup & Point-In-Time-Recovery

前面介绍的冷备份, 以及逻辑备份都是一个时间点的全量备份, 没有增量的概念.  如果数据库在运行过程中发生故障, 使用逻辑备份只能将数据库还原到备份时刻, 无法恢复到故障发生前的那个时刻. 又或者在使用过程中由于误操作修改或删除了重要数据, 需要还原到误操作前的那个时刻怎么办呢? 使用冷备份加上有效的归档文件可以实现任意时间点的恢复. 但是冷备份需要停库操作, 所以实用性不大. 本文要介绍的是在线的数据库文件备份, 弥补了冷备份的缺陷, 同时又支持基于时间点的恢复. postgresql.con

联想A820怎么Recovery?A820刷Recovery教程

最后才弄清楚,该刷机说明中的进入Recovery的操作方法是不对的,该方法进入的确实就是工程模式,而要进入Recovery模式应该这样来操作:  先按住电源键 2 秒左右,然后再同时按住音量加.减键(此时是同时按 3 键),这样就可以进入Recovery模式了,下面整理了一文章一起看看. 一:准备工作: 1:确保手机能用usb数据线连接电脑,这个是必须的 2:电脑上已经安装手机的驱动,如果没有安装的话, 百度搜索下载 3:下载recovery包,百度搜索到官方下载 ,把这个包下载下来放到电脑上

三星手机怎么进入recovery模式?进入recovery的方法

 [图]三星最流行的大屏安卓手机演示三个按键,标的很清晰  如果您经常玩三星手机,肯定知道这三个键的作用,并且很容易进入工程模式(recovery)在关机状态下同时按住[音量上].[HOME键].[电源键],等待屏幕亮起后即可放开,进入Recovery模式    以上是大多数三星手机进入Recovery模式的方法

三星手机怎么进入recovery模式 三星手机recovery模式进入方法总结

使用安卓辅助工具 目前我们知道有很多刷机与ROOT工具,比如豌豆荚.卓大师.刷机精灵.360手机助手等等,这些安卓手机辅助工具都可以很方便帮助我们将安卓手机进入Recovery模式,下面我们以卓大师为例,教你如何进入Recovery模式. 1.首先在电脑中安卓卓大师: 2.将USB数据线将手机与电脑连接好,之后打开卓大师,等待卓大师识别,第一次使用,则需要下载安装手机驱动,需要联网条件下,等待一会即可完成: 3.以上都完成之后,进入卓大师的使用工具,然后点击顶部的"实用工具",之后在&

PostgreSQL pending patch : fail-back without fresh backup (have bug?)

[补充说明] 本文测试fail-back未成功的bug是-m immediate的一个bug, 已经修复 :  http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=91c3613d3748d881706c3e60d8221ea92833ac1a http://blog.163.com/digoal@126/blog/static/16387704020137894542352/ [正文] PostgreSQL的流复制应用非常

ZFS snapshot used with PostgreSQL PITR or FAST degrade or PG-XC GreenPlum plproxy MPP DB's consistent backup

上一篇BLOG介绍了一下ZFS的使用, 以及zfs的log和l2arc机制带来的读写性能提升. 本文将介绍一下ZFS的另一大功能, snapshot和clone. 结合PostgreSQL的PITR来使用, snapshot可以替换基础备份. 从而提高PostgreSQL恢复到过往时间点的速度(就近选择snapshot). 同时还可以作为 PG-XC GreenPlum plproxy 等并行数据库解决方案的全局一致性备份, 对于pg-xc有冻结事务的功能, 所以可以不停库实现一致性备份, 对于

Database Recovery in GitLab – Implementing Database Disaster Tolerance & High Availability

How can we restore an accidentally deleted database in GitLab? How can we implement database backup, recovery, disaster tolerance, and high availability? If you are in the database industry, you might be more concerned about these issues recently. La

76.3. Bacula, the Open Source, Enterprise ready, Network Backup Tool for Linux, Unix, Mac and Windows.

http://www.bacula.org/ ubuntu 10.10 neo@backup:~$ apt-cache search bacula bacula - network backup, recovery and verification - meta-package bacula-client - network backup, recovery and verification - client meta-package bacula-common - network backup

ORACLE备份策略(ORACLE BACKUP STRATEGY)

概要 1.了解什么是备份 2.了解备份的重要性 3.理解数据库的两种运行方式 4.理解不同的备份方式及其区别 5.了解正确的备份策略及其好处   一.了解备份的重要性 可以说,从计算机系统出世的那天起,就有了备份这个概念,计算机以其强大的速度处理能力,取代了很多人为的工作,但是,往往很多时候,它又是那么弱不禁风,主板上的芯片.主板电路.内存.电源等任何一项不能正常工作,都会导致计算机系统不能正常工作.当然,这些损坏可以修复,不会导致应用和数据的损坏.但是,如果计算机的硬盘损坏,将会导致数据丢失,