Shell Script

[frasernm]

Hi,

I'm trying to write a shell script which logs in to postgres, executes a file filled with SQL commands (and in an ideal world would write output/errors to a file) and then exits postgres. I can't get the script to do anything else than log into the database.

I'm new to unix, so apols if this is a really easy problem,

Thanks,

Fraser

 

[DazzaMan]

I have written this script that remotely backs upa datafile and then recreates the database on a different machine. I also ftp los as well. if you wish to adapt this script to run sql commands froma file add the line:
su - postgres -c "psql -f pathandfilename

#!/bin/bash -vx
filename=`date +%Y_%m_%d`
filename="${filename}_datbase.sql"
logdir=`date +%Y_%m_%d`
logdir="/backup_1/${logdir}_dbbackup"
mkdir -p $logdir
rsh serverIPorHostName "pg_dump databasename > /backup_1/$filename"
if [ $? -eq 0 ]; then
echo "pg_dump Completed Successfully."
if [ -f "/backup_3/$filename" ]; then
su - postgres -c "dropdb databasename"
su - postgres -c "createdb -U databasename"
su - postgres -c &quot;psql databasename < /backup_3/$filename&quot;
mv /backup_3/$filename $logdir
fi
fi
echo &quot;Clearing down the replication log.&quot;
/usr/local/pgreplicate/bin/replicate_ctl stop
mv /usr/local/pgreplicate/logs/* $logdir
mv /usr/local/pgreplicate/etc/store.db $logdir
mv /usr/local/pgreplicate/etc/spooler.hdrs $logdir
/usr/local/pgreplicate/bin/replicate_ctl start
tar zcvf $logdir.tgz $logdir
/bin/rm -r $logdir
ncftpput -f login.cfg /home/postgres/ $logdir.tgz
mv $logdir.tgz /backup_1/PGReplicateBackup/dbbackup.tgz