PostgreSQL备份与恢复方法

   一、 简介

    Postgresql的备份分为三种：

    l SQL转储

    l 文件系统级别备份（冷备份）

    l 在线热备份（归档）

    以下通过实例来讲解PostgreSQL的三种备份。

    二、 SQL转储

    （一）pg_dump

    1，创建数据库

    createdb pg

    2，连入数据库pg

    psql pg

    3，创建测试表，插入数据

    pg=# create table pg_test(a int);

    pg=# insert into pg_test(a) values(1);

    pg=# insert into pg_test(a) values(2);

    4，查看数据

    pg=# select * from tb;

    a

    ---

    1

    2

    (2 rows)

    5，备份

    pg_dump pg > /usr/local/pgsql/backup/pg.dmp

    6，删除数据库pg

    dropdb pg

    7，创建新数据库（恢复之前需创建数据库）

    createdb pg

    8，恢复数据

    psql pb

    9，查看数据是否回复

    pg=# select * from tb;

    a

    ---

    1

    2

    (2 rows)

    至此，数据已成功恢复！

    注：pg_dump可以对针对单表或者多表进行备份

    如：pg_dump databasename Ct tablename1 Ct tablename2 >filename

    （二）pg_dumpall

    pg_dump只能备份单个数据库，而且恢复的时候需要创建空数据库。pg_dumpall可以备份所有数据库，并且备份角色、表空间。

    1，创建数据库

    createdb pg1

    createdb pg2

    2， pg1中创建表并插入数据

    psql pg1

    pg=# create table tb1(a int)

    pg=# insert into tb1(a) values(1);

    3， pg2中创建表并插入数据

    psql pg2

    pg=# create table tb2(a int)

    pg=# insert into tb2(a) values(2);

    4，备份数据库

    pg_dumpall > /usr/local/pgsql/backup/pg_all.dmp

    5，删除数据库

    dropdb pg1

    dropdb pg2

    6，恢复数据库

    psql Cf /usr/local/pgsql/backup/pg_all.dmp postgres

    可以指定任何数据库名，如果恢复到一个空的集群中，通常使用postgres数据库

    7，查看数据库pg1是否恢复

    psql pg1

    pg=# select * from tb1;

    a

    ---

    1

    (1 rows)

    8，查看数据库pg2是否恢复

    psql pg2

    pg=# select * from tb2;

    a

    ---

    2

    (1 rows)

    至此，数据已成功恢复！

    （三）备份压缩与分割

    压缩与分割的原理都是利用Linux的管线（PIPE）命令，不再进行试验。

    1）压缩

    l 备份：

    pg_dump dbname | gzip > filename.gz

    l 恢复

    gunzip -c filename.gz | psql dbname

    2）分割

    l 备份：

    pg_dump dbname | split -b1m- filename

    l 恢复

    cat filename* | psql dbname

    三、 文件系统级别备份（冷备份）

    文件系统级别的备份是冷备份，需要停止数据库。

    1，停止数据库

    pg_ctl CD /usr/local/pgsql/data stop

    2，备份数据库

    tar Cjcv Cf /usr/local/pgsql/backup/filesystem.tar.bz2 /usr/local/pgsql/data/

    3，删除/usr/local/pgsql/data/目录

    rm Cr /usr/local/pgsql/data/

    4，解压备份文件到原目录

    tar Cjxv Cf /usr/local/pgsql/backup/filesystem.tar.bz2 CC /

    5，启动数据库

    pg_ctl CD /usr/local/pgsql/data start

    6，查看数据库pg1是否恢复

    psql pg1

    pg=# select * from tb1;

    a

    ---

    1

    (1 rows)

    7，查看数据库pg2是否恢复

    psql pg2

    pg=# select * from tb2;

    a

    ---

    2

    (1 rows)

    至此，数据已成功恢复！

    四、 在线热备份（归档）

    （一）备份

    1，配置归档模式

    配置归档需要编辑postgresql.conf文件，默认为与/usr/local/pgsql/data/目录下

    vim /usr/local/pgsql/data/postgesql.conf

    archive_mode = on

    archive_command = ‘cp %p /usr/local/pgsql/backup/archived_log/%f’

    注：%p要被归档的日志文件的路径，%f是要被归档的日志文件的文件名

    2，启动数据库

    pg_ctl CD /usr/local/pgsql/data start

    3，创建数据库arch

    createdb arch

    4，创建表并插入记录

    psql arch

    arch=# create table tb(a int);

    arch=# insert into tb(a) values(1);

    5，创建备份

    arch=# select pg_start_backup(‘baseline’);

    6，备份整个data目录

    tar Cjcv Cf /usr/local/pgsql/backup/baseline.tar.bz2 /usr/local/pgsql/data/

    7，停止备份

    psql arch

    arch=# select pg_stop_backup();

    8，插入新记录，然后切换日志，重复3次

    arch=# insert into tb(a) values(2);

    arch=# select pg_switch_xlog();

    arch=# insert into tb(a) values(3);

    arch=# select pg_switch_xlog();

    arch=# insert into tb(a) values(4);

    arch=# select pg_switch_xlog();

    9，把/data/pg_xlog/下的WAL日志文件复制到预设的归档目录下，保证产生的WAL日志都已归档。

    （二）恢复

    1，停止数据库

    pg_ctl CD /usr/local/pgsql/data/ stop

    2，删除/data/

    rm Cr /usr/local/pgsql/data/

    3，恢复备份

    tar Cjxv Cf /usr/local/pgsql/backup/baseline.tar.bz2 CC /

    4，清空/data/pg_xlog/目录下所有文件

    rm Cr /usr/local/pgsql/data/pg_xlog/

    5，创建/pg_xlog/及其下面的archive_status目录

    mkdir /usr/local/pgsql/data/pg_xlog/

    mkdir /usr/local/pgsql/data/pg_xlog/archive_status

    6，在/data/目录下创建recovery.conf

    vim /usr/local/pgsql/data/recovery.conf

    restore_command = ‘cp /usr/local/pgsql/backup/archived_log/%f “%p”’

    7，启动数据库