hi tom,
Based on your suggestion, I deleted the fragment "/bin/su - postgres -c"
from the bash script and it worked! Thanks for the pointer.
But for the way i go about fulfilling the user's reqs, I apologised if it
didn't come out clearly in the first post :) Let me try again.
A broker firm, the user needs to update the prices etc daily. If any mistake
happen in the updates, they would want to revert to the latest backup and
redo.
Hence, i am to create something that allows the user (from a webpage) to
manually backup and/or restore database of any backup date. So, the page
shows a list of backups for the last 7 days to be selected for restorating
and also a button for there-and-then manual backup of database.
For daily auto backup.i've already scheduled cronjob and it went well.
This is the new script that i've implemented which i have 'chown' to
postgres:
#!/bin/bash
#File is named according to abbreviation of week name (Sun...Sat)
#e.g. nsvm_Mon_2004.db
backupdate=`date +%a_%Y`
/usr/local/pgsql/bin/pg_dump -f /home/postgres/nsvm_$backupdate.db
excensio ## <--- the change
echo "Backup at `date` :
nsvm_$backupdate.db">>/home/postgres/nsvm_backup_log
Again, i appreciate any suggestions for more efficient ways. Thanks again !
best regards,
siew hui
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
Sent: Monday, April 12, 2004 1:27 PM
Subject: Re: [GENERAL] Error in function to pg_dump: Standard in must be a
tty
> "Siew Hui, Wong" <shwong@sebasasia.com> writes:
> > Is it possible that there is a permission problem etc that don't allow
> > plperlu function that calls a pg_dump command? I am still stumped,
trying to
> > solve the problem of "Standard in must be a tty" whenever i triggered
the
> > plperlu function :(
>
> I'd guess that that complaint is coming from /bin/su because it's not
> finding anyplace to ask for the password. But why are you trying to su
> to postgres at all? Anything the backend launches will be running as
> the postgres user to start with.
>
> The entire project seems fatally flawed anyway ... you can't seriously
> think it's a good idea to launch a complete-database pg_dump after every
> row insertion. Quite aside from the performance implications, you
> won't even manage to achieve what you presumably want, because the
> pg_dump run is executed before the row-inserting transaction commits,
> and so it won't include that new row.
>
> I'd counsel thinking about launching periodic pg_dumps via a cron job,
> or some such, instead. Also see the various replication tools that are
> available.
>
> regards, tom lane
>