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

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"

PostgreSQL backup and recovery - cold backup & recovery

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件