linux下的pg_dump周期性备份

2019独角兽企业重金招聘Python工程师标准>>>

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://flandycheng.blog.51cto.com/855176/682793

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.

转载于:https://my.oschina.net/liuyuanyuangogo/blog/170284