bash 如何使用 Cron 备份我的 PostgreSQL 数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/854200/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How Do I Backup My PostgreSQL Database with Cron?
提问by Volomike
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.
当我 sudo 到 root 时，这些在 Redhat 上的命令行上运行，然后如您在上面的命令中看到的那样，我对 postgres 执行了 sudo -u。
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?
有谁知道是什么给的？
采纳答案by jhwist
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 命令放在那里。
回答by Volomike
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 '}`
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 [email protected]
回答by daronwolff
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.shfile 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!
对不起我的英语不好！
回答by Luc M
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.
在脚本中添加一个“env”。
回答by Floris
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 或配置不同的身份验证方法。
回答by Muammer Cavusoglu
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 "select datname from pg_database where datname not like 'template%';" | grep -v '^$'
The first one return '|' for template databases and an empty line.
第一个返回'|' 用于模板数据库和空行。
The second one is cleaner.
第二个比较干净。
回答by user2172798
databases=psql -h localhost -U postgres -q -x -t -c "\l" | grep 'Name' | sed 's/ //g' | sed 's/Name|//g'
数据库=psql -h localhost -U postgres -q -x -t -c "\l" | grep 'Name' | sed 's/ //g' | sed 's/Name|//g'
回答by Pascal Polleunus
Another version to get the list of databases:psql -lqt | grep -vE '^ +(template[0-9]+|postgres)? *\|' | cut -d'|' -f1| sed -e 's/ //g' -e '/^$/d'
获取数据库列表的另一个版本：psql -lqt | grep -vE '^ +(template[0-9]+|postgres)? *\|' | cut -d'|' -f1| sed -e 's/ //g' -e '/^$/d'
As my psql -lqtoutput 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 |
