PostgreSQL自动备份(backup)与恢复(restore)数据库(图解)

以下内容为Windows下安装并测试通过。一般的工具(pgAdmin III等)都有对PostgreSQL数据库导入导出的功能。
这里就不讲了。下面介绍的是用命令pg_dump、pg_restore进行数据库的备份与恢复。

一、备份(backup)

导出命令：pg_dump [OPTION]… [DBNAME]
查看使用帮助：pg_dump –help
主要参数解释：

关键字	说明（默认值）
-h, –host=HOSTNAME	数据库服务器IP
-p, –port=PORT	数据库服务器端口号
-U, –username=NAME	用户名
-W, –password	密码
-F, –format=c|t|p	输出文件格式（custom，tar，plain text）
-v, –verbose	详细信息模式，会显示备份时的信息
-f, –file=FILENAME	输出文件名
-b, –blobs	在dump文件中包含大对象

更多参数查看官网：http://www.postgresql.org/docs/8.1/static/app-pgdump.html
CMD命令如下：
“C:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe” -i -h 192.168.1.200 -p 5432 -U system1 -b -c -C -d -D -Fc -v -f D:\Backup\201237.backup techpro_db

输入密码后就会在指定目录生成07.backup文件(如下图③)。
要是不用手动输入密码，然后写个批处理就可以做定期备份了是吧？那怎么做呢？下面介绍(要7.3以上的版本)
①新建系统变量：
变量名：PGPASSFILE
变量值：C:\Program Files\PostgreSQL\8.3\data\pgpass.conf

②按上面的变量值新建文件：pgpass.conf
文件内容格式为：hostname:port:database:username:password

③再输入上面的CMD命令是不是就不会提示密码了？下图是执行生成后的备份文件。

④最后写个bat批处理，然后保存。放在【任务计划】里定期执行就可以了。
@ECHO OFF
@ECHO ============================
@ECHO PostgreSQL Backup Command
@ECHO Created By: 許立尭
@ECHO Created Date:2012-03-07
@ECHO ============================
@set DirName=%date:~0,7%
@set FileName=%date:~0,10%.backup

@if %date:~8,2% == 01 (md D:\Backup\”%DirName%”)
@if not exist D:\Backup\”%DirName%” (md D:\Backup\”%DirName%”)
“C:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe” -i -h 192.168.1.200 -p 5432 -U system1 -b -c -C -d -D -Fc -v -f D:\Backup\%FileName% techpro_db

if exist D:\Backup\%FileName% (“C:\Program Files\WinRAR\WinRAR.exe” a -df D:\Backup\%FileName%.rar D:\Backup\%FileName%)
@ECHO ON

二、恢复(restore)

导出命令：pg_restore [OPTION]… [FILE]
查看使用帮助：pg_restore –help
主要参数解释：

关键字	说明（默认值）
-h, –host=HOSTNAME	数据库服务器IP
-p, –port=PORT	数据库服务器端口号
-U, –username=NAME	用户名
-W, –password	密码
-d, –dbname=NAME	连接的数据库名
-v, –verbose	详细信息模式，会显示恢复时的信息
-a, –data-only	只导入数据，不导入结构
-t, –table=NAME	恢复指定名称的表

更多参数查看官网：http://www.postgresql.org/docs/8.1/static/app-pgrestore.html
CMD命令如下：
“C:\Program Files\PostgreSQL\8.3\bin\pg_restore.exe” -d new_db -U system1 D:\Backup\201237.backup

①下图的红色部分(new_db)是已经建好了的DB。得先建好DB再执行命令。
②下图的橙色部分报了个错：pg_restore: [archiver (db)] connection to database “new_db” failed: FATAL: password authentication failed for user “system1”
这是因为没有权限，给用户加权限，或者用权限高的用户。
③下图的绿色部分是恢复成功的。用的是建立DB时的用户：postgres
附录：

备份
C:\Program Files\PostgreSQL\8.1\bin>
pg_dump.exe -h 192.168.1.200 -p 5432 -U system1 “techpro_db” -i -F t -E UTF8 -b -d -D -v -f ”D:\techpro_db.tar”
恢复
恢复整个DB(含有表定义和数据)
C:\Program Files\PostgreSQL\8.1\bin>
pg_restore.exe -h 192.168.1.200 -p 5432 -U system1 -i -d “techpro_db” -c -v “D:\techpro_db.tar”

恢复整个数据库的表定义
C:\Program Files\PostgreSQL\8.1\bin>
pg_restore.exe -h 192.168.1.200 -p 5432 -U system1 -i -d “techpro_db” -c -s -v “D:\techpro_db.tar”

恢复整个数据库表的数据
C:\Program Files\PostgreSQL\8.1\bin>
pg_restore.exe -h 192.168.1.200 -p 5432 -U system1 -i -d “techpro_db” -c -a -v “D:\techpro_db.tar”

恢复某个表的表定义
C:\Program Files\PostgreSQL\8.1\bin>
pg_restore.exe -h 192.168.1.200 -p 5432 -U system1 -i -d “techpro_db” -t t_emp -c -s -v “D:\techpro_db.tar”

恢复某个表的数据
C:\Program Files\PostgreSQL\8.1\bin>
pg_restore.exe -h 192.168.1.200 -p 5432 -U system1 -i -d “techpro_db” -t t_emp -c -a -v “D:\techpro_db.tar”