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