备份：
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
