PostgreSQL冷备份与还原详解介绍

背景
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中找到，也可以使用pg_config得到，或者查看控制文件的内容得到 :

./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"