手机版
你好,游客 登录 注册
背景:
阅读新闻

Linux下的pg_dump周期性备份

[日期:2013-10-22] 来源:Linux社区  作者:Linux [字体: ]
OS: CentOS

DB: PostgreSQL9.3 , OS user: postgres 

一、编写pg_dump备份脚本:

# get the day of week 1-7 starting mon=1
DOW=`date +"%u"`

# define variables
DB_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 password
export PGPASSWORD=$DB_PASSWORD

# change to the bin dir
cd $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 postgres
crontab -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 root
service crond restart

附注:

1.设置shell脚本定期执行

通过crontab -e 添加的内容,实际上是在/var/spool/cron目录下,生成了一个名为操作系统用户名(此处为postgres)的文件,其内容就是crontab -e添加的内容(此处为 */2 * * * * root run-parts /home/postgres/backup/backup.sh)。

参考资料:http://www.linuxidc.com/Linux/2013-10/91737.htm

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删除表中重复数据行 http://www.linuxidc.com/Linux/2013-07/87780.htm

PostgreSQL数据库连接池PgBouncer的搭建 http://www.linuxidc.com/Linux/2013-06/85928.htm

Windows平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htm

PostgreSQL备份心得笔记 http://www.linuxidc.com/Linux/2013-04/82812.htm

PostgreSQL 的详细介绍请点这里
PostgreSQL 的下载地址请点这里

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款