pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedCron job doing VACUUM and BACUP from root
3+ messages / 3 participants
[nested] [flat]
* Cron job doing VACUUM and BACUP from root
@ 2000-11-04 19:29 Aage J. Skjolingstad <aages@aajs.com>
2000-11-05 00:06 ` Re: Cron job doing VACUUM and BACUP from root Oliver Elphick <olly@lfix.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Aage J. Skjolingstad @ 2000-11-04 19:29 UTC (permalink / raw)
To: pgsql-interfaces
Have been searching around the last two days to find some scripts or
cron jobs to automate backup and vacuum.
I'm running a small db on PSQL 7.02 / RH 7 and keeping my login as
root.
I know how to set up cron from root and I believe it should be
possible to call the job from root, or am I totally wrong.
For what I have been doing I only get FATAL 1 errors, believe that's
from not being in su postgres ?
Any help and assistance (step by step) to a novice would be helpful.
Regards,
Aage J. Skjolingstad
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Cron job doing VACUUM and BACUP from root
2000-11-04 19:29 Cron job doing VACUUM and BACUP from root Aage J. Skjolingstad <aages@aajs.com>
@ 2000-11-05 00:06 ` Oliver Elphick <olly@lfix.co.uk>
0 siblings, 0 replies; 3+ messages in thread
From: Oliver Elphick @ 2000-11-05 00:06 UTC (permalink / raw)
To: Aage J. Skjolingstad <aages@aajs.com>; +Cc: pgsql-interfaces
"Aage J. Skjolingstad" wrote:
>Have been searching around the last two days to find some scripts or
>cron jobs to automate backup and vacuum.
>
>I'm running a small db on PSQL 7.02 / RH 7 and keeping my login as
>root.
>
>I know how to set up cron from root and I believe it should be
>possible to call the job from root, or am I totally wrong.
>
>For what I have been doing I only get FATAL 1 errors, believe that's
>from not being in su postgres ?
>
>Any help and assistance (step by step) to a novice would be helpful.
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Train up a child in the way he should go; and when he
is old, he will not depart from it." Proverbs 22:6
Attachments:
do.maintenance (text/plain, 1.2K) download
#! /bin/sh
#
# Script for automatic maintenance of Debian PostgreSQL, to be run by cron,
# with the owner being the postgres superuser.
# If the username and password have to be used, make sure the cron script is not
# world-readable!
syntax() {
echo Syntax: $0 [-v] [-a] [-u user -p password]
exit 1
}
verbose=
analyse=
while getopts avu:p: arg
do
case $arg
in
a)
analyse=analyze
;;
v)
verbose=verbose
;;
u)
user=$OPTARG
;;
p)
password=$OPTARG
;;
*)
syntax;;
esac
done
if [ -n "$user" -a -z "$password" ]
then
syntax
fi
if [ -z "$user" -a -n "$password" ]
then
syntax
fi
set -a
. /etc/postgresql/postgresql.env # set standard environment
if [ -z "$user" ]
then
dblist=$(psql -t -c "select datname from pg_database order by datname" -d template1)
else
dblist=$(psql -U $user -W -t -c "select datname from pg_database order by datname" -d template1 <<EOI | tail +1
$password
EOI
)
fi
for database in $dblist
do
if [ -n "$verbose" ]
then
echo Vacuuming $database
fi
if [ -z "$user" ]
then
/usr/bin/psql -c "vacuum ${verbose} ${analyse}" -q -S -d $database
else
/usr/bin/psql -U $user -W -c "vacuum ${verbose} ${analyse}" -q -S -d $database <<EOI
$password
EOI
fi
done
* RE: Cron job doing VACUUM and BACUP from root
@ 2000-11-04 20:37 Dave Page <dpage@vale-housing.co.uk>
0 siblings, 0 replies; 3+ messages in thread
From: Dave Page @ 2000-11-04 20:37 UTC (permalink / raw)
To: 'Aage J. Skjolingstad' <aages@aajs.com>; pgsql-interfaces
> -----Original Message-----
> From: Aage J. Skjolingstad [mailto:aages@aajs.com]
> Sent: 04 November 2000 19:30
> To: pgsql-interfaces@postgresql.org
> Subject: [INTERFACES] Cron job doing VACUUM and BACUP from root
>
>
> Have been searching around the last two days to find some scripts or
> cron jobs to automate backup and vacuum.
>
> I'm running a small db on PSQL 7.02 / RH 7 and keeping my login as
> root.
>
I run the following (extremely basic) script on RH6 with PostgreSQL 7.0.1:
HTH, Regards
Dave.
---------->
#! /bin/sh
cd /
rm /var/log/backup.old
mv /var/log/backup.log /var/log/backup.old
echo Subject: backup results >> /var/log/backup.log
echo >> /var/log/backup.log
echo Mephesto Auto-Backup started: `date` >> /var/log/backup.log
echo >> /var/log/backup.log
echo Vacuuming Database... >> /var/log/backup.log
su - postgres -c "/usr/local/pgsql/bin/psql alarm -c vacuum;"
su - postgres -c "/usr/local/pgsql/bin/psql helpdesk -c vacuum;"
su - postgres -c "/usr/local/pgsql/bin/psql insurance -c vacuum;"
su - postgres -c "/usr/local/pgsql/bin/psql intranet -c vacuum;"
su - postgres -c "/usr/local/pgsql/bin/psql mailtraq -c vacuum;"
su - postgres -c "/usr/local/pgsql/bin/psql pbx -c vacuum;"
su - postgres -c "/usr/local/pgsql/bin/psql sitedev -c vacuum;"
echo Dumping Database... >> /var/log/backup.log
rm /disk1/archive.old
mv /disk1/archive.sql /disk1/archive.old
su - postgres -c "/usr/local/pgsql/bin/pg_dumpall > /disk1/archive.sql"
chown root.root /disk1/archive.sql
chmod 600 /disk1/archive.sql
echo Done. >> /var/log/backup.log
echo Starting TAR dump to DAT... >> /var/log/backup.log
echo >> /var/log/backup.log
tar cf /dev/st0 -W -P /bin /boot /disk1 /etc /home /lib /mnt /root /sbin
/usr /var >> /var/log/backup.log
echo >> /var/log/backup.log
echo Backup completed: `date` >> /var/log/backup.log
/usr/sbin/sendmail root@vale-housing.co.uk < /var/log/backup.log
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2000-11-05 00:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-04 19:29 Cron job doing VACUUM and BACUP from root Aage J. Skjolingstad <aages@aajs.com>
2000-11-05 00:06 ` Oliver Elphick <olly@lfix.co.uk>
2000-11-04 20:37 RE: Cron job doing VACUUM and BACUP from root Dave Page <dpage@vale-housing.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox