如何使用Cron备份我的PostgreSQL数据库？

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

我可以在脚本中运行像vacuumdb,pg_dump和psql这样的命令,如果我这样说的话:

/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

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.

这些在Redhat的命令行运行,当我sudo root时,然后你在上面的命令中看到我做了一个sudo -u到postgres。

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.

但是当我尝试从cron中取消它时,我在所有文件中得到零字节 - 这意味着它没有正常运行。而且我不知道我能看到的日志中的线索。

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

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

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

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

是的,/ etc / db_backup.cron是chmod ug + x,由root拥有,文件顶部显示“#!/ bin / bash”(减去双引号)。

Anyone know what gives?

谁知道是什么给出的?

8 个解决方案

#1

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

既然你似乎拥有超级用户权限,你可以将这些命令放入postgres用户的crontab中,如下所示:

sudo su postgres
crontab -e

and then put the pg_dump/vacuumdb commands there.

然后将pg_dump / vacuumdb命令放在那里。

#2

I have a dynamic bash script that backs up all the databases on the server. It gets a list of all the databases and then vacuums each DB before performing a backup. All logs are written to a file and then that log is emailed to me. This is something you could use if you want.

我有一个动态bash脚本备份服务器上的所有数据库。它获取所有数据库的列表,然后在执行备份之前清空每个数据库。所有日志都写入文件,然后该日志通过电子邮件发送给我。如果你愿意,可以使用这个。

Copy the code below into a file and add the file to your crontab. I have setup my pg_hba.conf to trust local connections.

将下面的代码复制到一个文件中,并将该文件添加到您的crontab中。我已经设置了我的pg_hba.conf以信任本地连接。

#!/bin/bash
logfile="/backup/pgsql.log"
backup_dir="/backup"
touch $logfile
databases=`psql -h localhost -U postgres -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | grep -v template0 | grep -v template1 | awk {'print $1'}`

echo "Starting backup of databases " >> $logfile
for i in $databases; do
        dateinfo=`date '+%Y-%m-%d %H:%M:%S'`
        timeslot=`date '+%Y%m%d%H%M'`
        /usr/bin/vacuumdb -z -h localhost -U postgres $i >/dev/null 2>&1
        /usr/bin/pg_dump -U postgres -i -F c -b $i -h 127.0.0.1 -f $backup_dir/$i-database-$timeslot.backup
        echo "Backup and Vacuum complete on $dateinfo for database: $i " >> $logfile
done
echo "Done backup of databases " >> $logfile

tail -15 /backup/pgsql.log | mailx youremail@domain.com

#3

Your environment variable are maybe not set in cron.

您的环境变量可能未在cron中设置。

In your normal session, you probably have defined these variables:

在正常会话中,您可能已经定义了以下变量:

PG_PORT
PG_HOST
PG_DATABASE
PG_USERNAME
PG_PASSWORD

Add an "env" into yout script.

在yout脚本中添加“env”。

#4

I have set my cron like this. Every 59 minutes since monday to friday

我已经设置了这样的cron。自星期一至星期五每59分钟一班

*/59 * * * 1-5 sh /home/my_user/scripts/back_my_bd.sh

The script to run the backup is inside back_my_bd.sh file and the content is:

运行备份的脚本位于back_my_bd.sh文件中,内容为:

pg_dump -U USERDATABASE DATABASENAME > /home/my_user/sql/mybackup.sql

And i created the .pgpass file inside home directory to allow the backup whithout specify the user and password

我在主目录中创建了.pgpass文件,以允许备份不指定用户和密码

localhost:5432:DATABASENAME:USER:PASSWORD

Sorry my english is not good!

对不起,我的英语不好!

#5

you probably have "ident" authentication in your pg_hba.conf for your postgres user. The option "-u postgres" fails when that is the case. either change user to postgres in your backup script or configure a different authentication method.

您可能在pg_hba.conf中为postgres用户进行了“ident”身份验证。在这种情况下,选项“-u postgres”将失败。要么在备份脚本中将用户更改为postgres,要么配置其他身份验证方法。

#6

Instead of the following command: databases=psql -h localhost -U postgres -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | grep -v template0 | grep -v template1 | awk {'print $1'}

而不是以下命令:databases = psql -h localhost -U postgres -q -c“\ l”| sed -n 4,/ \ eof / p | grep -v rows \)| grep -v template0 | grep -v template1 | awk {'print $ 1'}

You can use below: databases=psql -t -c "select datname from pg_database where datname not like 'template%';" | grep -v '^$'

您可以在下面使用:databases = psql -t -c“从pg_database中选择datname,其中datname不像'template%';” | grep -v'^ $'

The first one return '|' for template databases and an empty line.

第一个返回'|'用于模板数据库和空行。

The second one is cleaner.

第二个是更清洁。

#7

databases=psql -h localhost -U postgres -q -x -t -c "\l" | grep 'Name' | sed 's/ //g' | sed 's/Name|//g'

databases = psql -h localhost -U postgres -q -x -t -c“\ l”| grep'名字'| sed's / // g'| sed's / Name | // g'

#8

另一个获取数据库列表的版本:psql -lqt | grep -vE'^ +(模板[0-9] + | postgres)? * \ |” |切-d'|' -f1 | sed -e's / // g'-e'/ ^ $ / d'

As my psql -lqt output is:

因为我的psql -lqt输出是:

 abcdefghij         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres           | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0          | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                    |          |          |             |             | postgres=CTc/postgres
 template1          | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                    |          |          |             |             | postgres=CTc/postgres
 abc                | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

#1