linux下的pg_dump周期性备份

发布时间：2014-09-05 13:40:07作者：知识屋

linux下的pg_dump周期性备份

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）。

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.

（免责声明：文章内容如涉及作品内容、版权和其它问题，请及时与我们联系，我们将在第一时间删除内容，文章内容仅供参考）

上一篇:Centos Linux开启端口 - 解决Centos发布服务外网访问不了的问题

下一篇:在当前目录查找指定字符串search_text sh

软件推荐

更多 >

游戏推荐

更多 >

精选栏目

手机游戏

手机软件

电脑软件

猜你喜欢

通讯铃声直播街机传奇旅游关卡模拟即时彩票剧情角色分期经营挂机摄影视频系统冒险战国摄像 MOBA 个人借贷换装系统软件影音恋爱网易导航漫画 360 社交 3D 资讯解谜网贷借钱抢票格斗沙盒休闲放置母婴角色扮演仙侠小说美食闯关射击三国生活

linux下的pg_dump周期性备份

相关知识

软件推荐

游戏推荐

精选栏目

win10

YLMF—OS

Win—XP

windows8

linux

windows7

热门推荐

手机游戏

手机软件

电脑软件

猜你喜欢