如何使用Cron备份PostgreSQL数据库? [英] How Do I Backup My PostgreSQL Database with Cron?

问题描述

如果我像这样开头，我可以在脚本中很好地运行vacuumdb，pg_dump和psql之类的命令:

I can run commands like vacuumdb, pg_dump, and psql just fine in a script if I preface them like so:

/usr/bin/sudo -u postgres /usr/bin/pg_dump -Fc mydatabase > /opt/postgresql/prevac.gz
/usr/bin/sudo -u postgres /usr/bin/vacuumdb --analyze mydatabase
/usr/bin/sudo -u postgres /usr/bin/pg_dump -Fc mydatabase > /opt/postgresql/postvac.gz
SCHEMA_BACKUP="/opt/postgresql/$(date +%w).db.schema"
sudo -u postgres /usr/bin/pg_dump -C -s mydatabase > $SCHEMA_BACKUP

当我对root进行sudo操作时，这些命令在Redhat上运行，然后如您在上面的命令中看到的那样，对PostgreSQL进行sudo -u操作.

These run at command line on Redhat when I am sudo to root and then as you see in the commands above I do a sudo -u to postgres.

但是当我尝试从cron开始时，我在所有文件中得到的字节数均为零，这意味着它无法正常运行.而且我在日志中也没有任何线索我可以看到.

But when I try to kick this off from cron, I get zero bytes in all the files -- meaning it didn't run properly. And I don't get a clue in the logs that I can see.

我的/etc/crontab文件的底部有此条目

My /etc/crontab file has this entry at the bottom

00 23 * * * root /etc/db_backup.cron

是的，/etc/db_backup.cron是chmod ug + x，由root拥有，并且文件顶部显示#！/bin/bash"(减双引号).

And yes, /etc/db_backup.cron is chmod ug+x, owned by root, and the top of the file says "#!/bin/bash" (minus doublequotes).

任何人都知道给什么吗?

Anyone know what gives?

推荐答案

由于您似乎仍然拥有超级用户权限，因此可以将这些命令放入postgres用户的crontab中，如下所示:

Since you seem to have superuser rights anyway, you could put those commands into the crontab of the postgres user like so:

sudo su postgres
crontab -e

，然后在其中放置pg_dump/vacuumdb命令.

and then put the pg_dump/vacuumdb commands there.

这篇关于如何使用Cron备份PostgreSQL数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！