Linux shell 操作 postgresql，并设置crontab任务

Linux shell 操作 postgresql：删除间隔日期的数据
-删除指定日期的数据
-vacuumdb 清理数据库

-清理日志

-定期执行脚本

*修改pg_hba.conf 设置本地连接无密码，trust

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

crontab 定期执行
*添加执行任务

crontab -e

 

*/1 * * * * /home/sample/delDataAndVacuumdb.sh >> /var/log/pg_maintenance.log

*/5 * * * * /home/sample/cleanLog.sh

*查看执行任务

crontab -l

*查看执行情况

vi /var/spool/mail/root
cat /var/log/cron

delDataAndVacuumdb.sh

#!/bin/bash
# this script is aimed to delete the expired data;
# and use the vacummdb command to clean up databases.
# Copyright(c) 2016--2016 yuxiangli All Copyright reserved.
echo "-----------------------------------------------------"
echo `date +%Y%m%d%H%M%S`
echo "delete the expired data..."
psql -h 127.0.0.1 -p 5432 -U postgres << EOF
delete from company where join_date < (select now()::timestamp - interval'1 month');
\\q
EOF

echo "-----------------------------------------------------"
echo `date +%Y%m%d%H%M%S`
echo "clean the databases..."
vacuumdb --host=127.0.0.1 --port=5432 --all --username=postgres --analyze --verbose
echo `date +%Y%m%d%H%M%S`
echo "-----------------------------------------------------"

cleanLog.sh

#!/bin/bash
rm -rf /var/log/pg_maintenance.log
exit 0