os: centosdb: postgresql9.3 , os user: postgres 一、编写pg_dump备份脚本: # get the day of week 1-7 starting mon=1 dow=`
os: centos
db: postgresql9.3 , os user: postgres 
一、编写pg_dump备份脚本:
# get the day of week 1-7 starting mon=1dow=`date +%u`# define variablesdb_install_dir='/opt/postgresql/9.3'# db_type='postgresql'# db_server='127.0.0.1'# db_port='5432'db_instance='mydb'db_user='postgres'db_password='postgres'db_storage='/home/postgres/backup'# specify the postgres password in the pgpassword var# for pg_dump not to prompt for a passwordexport pgpassword=$db_password# change to the bin dircd $db_install_dir/bin# run pg_dump./pg_dump -f $db_storage/${dow}-mydb.sql -u $db_user -f p -a $db_instance将以上脚本保存到 /home/postgres/backup/backup.sh。
二、设置脚本执行计划（定时或周期性）：
每个用户都有各自不同的计划任务列表，用各自的帐户su username登录后，
执行crontab -l命令可查看到各任务的计划任务情况，
执行crontab -e进入vi模式，可以修改自己的计划任务，
每次添加完任务后，一定要service crond restart重新启动crond服务，，否则任务不会生效；如果当前用户没有权限重启任务，可以切换到root用户再进行重启服务。
之后系统会按照设置定时或者周期地执行执行脚本。
su postgrescrontab -l crontab -e进入vi模式，添加相应的脚本执行计划，如：
*/2 * * * * root run-parts /home/postgres/backup/backup.sh 表示每隔2分钟执行一次backup.sh ，即每隔2分钟备份一次；
或者 0 2 * * * root run-parts /home/postgres/backup/backup.sh 表示每天的2点执行一次backup.sh ，即每天2点备份一次。
su rootservice crond restart附注：
1.设置shell脚本定期执行
通过crontab -e 添加的内容，实际上是在/var/spool/cron目录下，生成了一个名为操作系统用户名（此处为postgres）的文件，其内容就是crontab -e添加的内容（此处为 */2 * * * * root run-parts /home/postgres/backup/backup.sh）。
参考资料：
2.crontab usage: crontab [-u user] file
crontab [-u user] [ -e | -l | -r ]
(default operation is replace, per 1003.2)
-e (edit user’s crontab)
-l (list user’s crontab)
-r (delete user’s crontab)
-i (prompt before deleting user’s crontab)
-s (selinux context)
3.pg_dump usage:
pg_dump [option]… [dbname]
general options:
-f, –file=filename output file or directory name
-f, –format=c|d|t|p output file format (custom, directory, tar,
plain text (default))
-j, –jobs=num use this many parallel jobs to dump
-v, –verbose verbose mode
-v, –version output version information, then exit
-z, –compress=0-9 compression level for compressed formats
–lock-wait-timeout=timeout fail after waiting timeout for a table lock
-?, –help show this help, then exit
options controlling the output content:
-a, –data-only dump only the data, not the schema
-b, –blobs include large objects in dump
-c, –clean clean (drop) database objects before recreating
-c, –create include commands to create database in dump
-e, –encoding=encoding dump the data in encoding encoding
-n, –schema=schema dump the named schema(s) only
-n, –exclude-schema=schema do not dump the named schema(s)
-o, –oids include oids in dump
-o, –no-owner skip restoration of object ownership in
plain-text format
-s, –schema-only dump only the schema, no data
-s, –superuser=name superuser user name to use in plain-text format
-t, –table=table dump the named table(s) only
-t, –exclude-table=table do not dump the named table(s)
-x, –no-privileges do not dump privileges (grant/revoke)
–binary-upgrade for use by upgrade utilities only
–column-inserts dump data as insert commands with column names
–disable-dollar-quoting disable dollar quoting, use sql standard quoting
–disable-triggers disable triggers during data-only restore
–exclude-table-data=table do not dump data for the named table(s)
–inserts dump data as insert commands, rather than copy
–no-security-labels do not dump security label assignments
–no-synchronized-snapshots do not use synchronized snapshots in parallel jobs
–no-tablespaces do not dump tablespace assignments
–no-unlogged-table-data do not dump unlogged table data
–quote-all-identifiers quote all identifiers, even if not key words
–section=section dump named section (pre-data, data, or post-data)
–serializable-deferrable wait until the dump can run without anomalies
–use-set-session-authorization
use set session authorization commands instead of
alter owner commands to set ownership
connection options:
-d, –dbname=dbname database to dump
-h, –host=hostname database server host or socket directory
-p, –port=port database server port number
-u, –username=name connect as specified database user
-w, –no-password never prompt for password
-w, –password force password prompt (should happen automatically)
–role=rolename do set role before dump
if no database name is supplied, then the pgdatabase environment
variable value is used.
推荐阅读：
postgresql删除表中重复数据行
postgresql数据库连接池pgbouncer的搭建
windows平台编译 postgresql
postgresql备份心得笔记
postgresql 的详细介绍：请点这里
postgresql 的下载地址：请点这里