Index: [Article Count Order] [Thread]

Date:  Fri, 12 Aug 2005 15:22:36 +0800
From:  patricko <patricko (at mark) staff.singnet.com.sg>
Subject:  [coba-e:02689] XTR - postgres vaccum [recommended by me]
To:  coba-e (at mark) bluequartz.org
Message-Id:  <20050812152236.2b211811@localhost>
X-Mail-Count: 02689

Hi,

If ur stats not showing on XTR
do these:
will automate the postgres vacuum
on cron.

Been running for months and its great


[1]
cp -p /var/lib/pgsql/data/pg_hba.conf
/var/lib/pgsql/data/pg_hba.conf.open

cp -p /var/lib/pgsql/data/pg_hba.conf
/var/lib/pgsql/data/pg_hba.conf.close

change: 
/var/lib/pgsql/data/pg_hba.conf.open
#LAST LINE
#local cobalt crypt
local all trust

/var/lib/pgsql/data/pg_hba.conf.close
#LAST LINE
local cobalt crypt
#local all trust

[2]
create dir
/home/postgres_bkup

[3]
ADD CRON JOB

####### Backup & Vacuum Postgres Cobalt DB ###### # patricko #
44 9 * * *      (cat /var/lib/pgsql/data/pg_hba.conf.open > /var/lib/pgsql/data/pg_hba.conf) > /dev/null 2>&1
45 9 * * 1      (su - postgres -c "pg_dump cobalt > /home/postgres_bkup/cobalt.dump.1") > /dev/null 2>&1
45 9 * * 2      (su - postgres -c "pg_dump cobalt > /home/postgres_bkup/cobalt.dump.2") > /dev/null 2>&1
45 9 * * 3      (su - postgres -c "pg_dump cobalt > /home/postgres_bkup/cobalt.dump.3") > /dev/null 2>&1
45 9 * * 4      (su - postgres -c "pg_dump cobalt > /home/postgres_bkup/cobalt.dump.4") > /dev/null 2>&1
45 9 * * 5      (su - postgres -c "pg_dump cobalt > /home/postgres_bkup/cobalt.dump.5") > /dev/null 2>&1
45 9 * * 6      (su - postgres -c "pg_dump cobalt > /home/postgres_bkup/cobalt.dump.6") > /dev/null 2>&1
45 9 * * 7      (su - postgres -c "pg_dump cobalt > /home/postgres_bkup/cobalt.dump.7") > /dev/null 2>&1
47 9 * * *      (chmod 600 /home/postgres_bkup/cobalt.dump*) > /dev/null 2>&1
47 9 * * *      (cat /var/lib/pgsql/data/pg_hba.conf.close > /var/lib/pgsql/data/pg_hba.conf) > /dev/null 2>&1
0 10 * * 2,4,6       /home/postgres_bkup/vacuumDBmax.sh #> /dev/null 2>&1

[4]
Add /home/vacuumDBmax.sh
#!/bin/bash
#patricko - 2005 June

#Declare control variables
x="/home/postgres_bkup/cobalt.dump.vacuum"

# Stop/Start hung postgres daemon with its associate and open session
/etc/rc.d/init.d/admserv stop
/etc/rc.d/init.d/postgresql stop
sleep 3;
/etc/rc.d/init.d/postgresql start
sleep 3;

# Defrag DB
cat /var/lib/pgsql/data/pg_hba.conf.open > /var/lib/pgsql/data/pg_hba.conf;
(ps ax | grep admserv | awk '{system("kill -9 "$1)}') > /dev/null 2>&1;
su - postgres -c "pg_dump cobalt > /home/postgres_bkup/cobalt.dump.vacuum";

#Error control check
if [ -s $x ]
then
#Greater than zero go ahead
echo "Greater than zero file"
(ps ax | grep admserv | awk '{system("kill -9 "$1)}') > /dev/null 2>&1;
su - postgres -c "/home/chiliasp/odbc/internaldb/bin/destroydb cobalt";
sleep 1;
su - postgres -c "/home/chiliasp/odbc/internaldb/bin/createdb cobalt";
sleep 1;
su - postgres -c "/home/chiliasp/odbc/internaldb/bin/psql cobalt < /home/postgres_bkup/cobalt.dump.vacuum";
sleep 1;
sync;

else

echo "Dont do anything - report admins"
`echo "check /home/postgres_bkup/cobalt.dump.vacuum" | mail -s "[HOSTNAME] postgres failed" admin (at mark) yourdomain.com.sg`

fi

# Start admserv and close session
cat /var/lib/pgsql/data/pg_hba.conf.close > /var/lib/pgsql/data/pg_hba.conf;
/etc/rc.d/init.d/admserv start




-- 
cheers
patrick