【PostgreSQL】PostgreSQL常用备份方法

针对于PostgreSQL备份方式有三种：

1.SQL转储

2.热备

3.文件系统冷备份

之后 对于这三种备份方式一一演示：

 

1.SQL转储

这里我们用到的工具是pg_dump和pg_dumpall，它和Oracle中的expdp，Mysql的mysqldump类似，它可以在数据库正在使用的时候进行完整一致的备份，并不阻塞其它用户对数据库的访问。

同时在使用此种备份方法时可以使用数据库层面的命令对数据进行压缩或分割，如：

1）压缩

备份：

pg_dump dbname | gzip > filename.gz

恢复

gunzip -c filename.gz | psql dbname

 

2）分割

备份：

pg_dump dbname | split -b 1m - filename

恢复

cat filename* | psql dbname

 

具体实验步骤如下：

pg_dump

首先创建数据库并创建测试表

[pg@edb1 ~]$ createdb pg

[pg@edb1 ~]$ psql pg

psql (9.3.9)

Type "help" for help.

 

pg=# create table backup_test(x int);

CREATE TABLE

pg=# insert into backup_test values(1);

INSERT 0 1

pg=# insert into backup_test values(2);

INSERT 0 1

pg=# select * from backup_test;

 x 

---

 1

 2

(2 rows)

pg=# \q

使用pg_dump工具进行SQL转储：

[pg@edb1 ~]$ pg_dump pg > /home/pg/pg.dmp

删掉原有数据库，使用dmp文件重建：

[pg@edb1 ~]$ dropdb pg

[pg@edb1 ~]$ createdb pg

[pg@edb1 ~]$ psql pg < /home/pg/pg.dmp

....

验证数据是否恢复：

[pg@edb1 ~]$ psql pg

psql (9.3.9)

Type "help" for help.

 

pg=# select * from backup_test;

 x 

---

 1

 2

(2 rows)

 

pg_dumpall

刚才使用pg_dump只能备份单库，使用pg_dumpall可以备份本地所有库

实验过程如下：

创建测试数据库及测试表

[pg@edb1 ~]$ createdb pg1

[pg@edb1 ~]$ createdb pg2

[pg@edb1 ~]$ psql pg1

psql (9.3.9)

Type "help" for help.

 

pg1=# create table btest1(x int);

CREATE TABLE

pg1=# insert into btest1 values(1);

INSERT 0 1

pg1=# \q

[pg@edb1 ~]$ psql pg2

psql (9.3.9)

Type "help" for help.

 

pg2=# create table btest2(x int);

CREATE TABLE

pg2=# insert into btest2 values(1);

INSERT 0 1

pg2=# \q

备份数据库：

[pg@edb1 ~]$ pg_dumpall > /home/pg/pg_all.dmp

删除数据库：

[pg@edb1 ~]$ dropdb pg1

[pg@edb1 ~]$ dropdb pg2

恢复数据库：

[pg@edb1 ~]$ psql -f /home/pg/pg_all.dmp postgres

......

验证数据：

[pg@edb1 ~]$ psql pg1

psql (9.3.9)

Type "help" for help.

 

pg1=# select * from btest1;

 x 

---

 1

(1 row)

 

pg1=# \q

[pg@edb1 ~]$ psql pg2

psql (9.3.9)

Type "help" for help.

 

pg2=# select * from btest2;

 x 

---

 1

(1 row)

 

pg2=# \q

 

2.在线热备

使用select pg_start_backup和select pg_stop_backup()对数据库进行热备

实验过程如下：

（1）开启归档

[pg@edb1 ~]$ vi /home/pg/pgsql/data/postgresql.conf 

配置以下三个参数：

wal_level = archive

archive_mode = on

archive_command = 'cp %p /home/pg/pgsql/backup/archived_log/%f'

（2）创建归档目录

[pg@edb1 ~]$ mkdir -p /home/pg/pgsql/backup/archived_log

（3）重启数据库，使参数生效

[pg@edb1 ~]$ pg_ctl -D /home/pg/pgsql/data stop

[pg@edb1 ~]$ pg_ctl -D /home/pg/pgsql/data start

（4）创建测试库

[pg@edb1 ~]$ createdb arch

[pg@edb1 ~]$ psql arch

psql (9.3.9)

Type "help" for help.

 

arch=# create table btest(x int);

CREATE TABLE

arch=# insert into btest values(1);

INSERT 0 1

（5）创建备份

arch=# select pg_start_backup('baseline');

 pg_start_backup 

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

 0/5000028

(1 row)

 

arch=# \q

（6）备份数据库目录

[pg@edb1 ~]$ tar -zcvf /home/pg/pg_backup.tar.gz /home/pg/pgsql/data/

......

（7）停止备份

[pg@edb1 ~]$ psql arch

psql (9.3.9)

Type "help" for help.

 

arch=# select pg_stop_backup();

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

 pg_stop_backup 

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

 0/50000B8

(1 row)

（8）再插入一些其他数据并切换WAL日志

arch=# insert into btest values(2);

INSERT 0 1

arch=# insert into btest values(3);

INSERT 0 1

arch=# insert into btest values(4);

INSERT 0 1

arch=# select pg_switch_xlog();

 pg_switch_xlog 

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

 0/6000208

(1 row)

（9）停止数据库

[pg@edb1 ~]$ pg_ctl -D /home/pg/pgsql/data stop

LOG:  received smart shutdown request

LOG:  autovacuum launcher shutting down

LOG:  shutting down

waiting for server to shut down....LOG:  database system is shut down

 done

server stopped

（10）删除目录

[pg@edb1 ~]$ rm -rf /home/pg/pgsql/data/

（11）使用tar包备份恢复目录

[pg@edb1 ~]$ tar -zxvf pg_backup.tar.gz -C /

......

（12）重置pg_xlog目录

[pg@edb1 ~]$ rm -r /home/pg/pgsql/data/pg_xlog/

[pg@edb1 ~]$ mkdir /home/pg/pgsql/data/pg_xlog/

（13）创建目录存放之前的归档

[pg@edb1 ~]$ mkdir arch

[pg@edb1 ~]$ cp /home/pg/pgsql/backup/archived_log/* arch/

（14）编辑recovery.conf文件

[pg@edb1 ~]$ vi pgsql/data/recovery.conf

restore_command = ‘cp /home/pg/arch/%f  “%p”’

(15)启动数据库

[pg@edb1 ~]$ pg_ctl -D /home/pg/pgsql/data/ start

server starting

[pg@edb1 ~]$ LOG:  database system was interrupted; last known up at 2015-09-21 18:27:41 CST

LOG:  starting archive recovery

LOG:  restored log file "000000010000000000000005" from archive

LOG:  redo starts at 0/5000090

LOG:  consistent recovery state reached at 0/50000B8

LOG:  restored log file "000000010000000000000006" from archive

cp: cannot stat `/home/pg/arch/000000010000000000000007': No such file or directory

LOG:  redo done at 0/60001E8

LOG:  last completed transaction was at log time 2015-09-21 18:30:16.033281+08

LOG:  restored log file "000000010000000000000006" from archive

cp: cannot stat `/home/pg/arch/00000002.history': No such file or directory

LOG:  selected new timeline ID: 2

cp: cannot stat `/home/pg/arch/00000001.history': No such file or directory

LOG:  archive recovery complete

LOG:  MultiXact member wraparound protections are now enabled

LOG:  database system is ready to accept connections

LOG:  autovacuum launcher started

可以看到数据库恢复到了最后一个归档所在的时间点

（16）验证数据

[pg@edb1 ~]$ psql arch

psql (9.3.9)

Type "help" for help.

 

arch=# select * from btest;

 x 

---

 1

 2

 3

 4

(4 rows)

 

 

3.文件系统冷备份

方法就是对数据库相关文件直接进行备份，由于需要停库，对业务影响较大，所以一般不使用这种方法。

实验过程如下：

创建测试库

[pg@edb1 ~]$ createdb test1

[pg@edb1 ~]$ psql test1

psql (9.3.9)

Type "help" for help.

 

test1=# create table test1(x int);

CREATE TABLE

test1=# insert into test1 values(1);

INSERT 0 1

test1=# \q

关库

[pg@edb1 ~]$ pg_ctl -D /home/pg/pg

pg_all.dmp  pg.dmp      pg.log      pgsql/      

[pg@edb1 ~]$ pg_ctl -D /home/pg/pgsql/data/ stop

waiting for server to shut down.... done

server stopped

进行文件系统打包冷备

[pg@edb1 ~]$ tar -zcvf pgdata.tar.gz  /home/pg/pgsql/data 

......

删除原来的文件

[pg@edb1 ~]$ rm -rf /home/pg/pgsql/data/

使用tar包中内容进行替换

[pg@edb1 ~]$ tar -zxvf pgdata.tar.gz -C /

启动数据库查看

[pg@edb1 ~]$ pg_ctl -D /home/pg/pgsql/data/ start

server starting

[pg@edb1 ~]$ LOG:  database system was shut down at 2015-09-21 18:02:27 CST

LOG:  MultiXact member wraparound protections are now enabled

LOG:  database system is ready to accept connections

LOG:  autovacuum launcher started

 

[pg@edb1 ~]$ 

[pg@edb1 ~]$ psql test1

psql (9.3.9)

Type "help" for help.

 

test1=# select * from test1;

 x 

---

 1

(1 row)

 

test1=# \q