Re: Perl and pg_dump

On Mon, Jul 10, 2000 at 04:54:43PM -0500, James Hall wrote:
> I have a small perl script that is supposed to run pg_dump daily to backup
> databases.
> The script is called via the cron on a Redhat 6.1 system with PostgreSQL
> 6.5.
>
> The error that I receive when cron attempts to run the script is:
>
> Connection to database 'template1' failed.
> FATAL 1: SetUserId: user 'root' is not in 'pg_shadow'
>
> I know root isn't in pg_shadow, and I don't want it there (I _do_ know
> better than to do things as root ;)).

Hmmm... that needn't stop you from putting "root" into the postgres list,
but that won't help if you're using password authentication. I ran into
this when I installed postgres here (debian system - has a stock cron job
that, fortunately, only needed to have some options added).

> PSQL=/usr/bin/psql
> DUMP=/usr/bin/pg_dump

> Databases=`$PSQL -tq -d template1 -c "select datname from pg_database"`

> $DUMP -D $db > $BACKUP_DIR/$PREFIX.$db

The problem is that (as far as I can see) there's not any conveninent way to
supply a valid user name and password to these utilities - they only have an
option that will cause them to prompt for them, which is useless for
scripting. I'm afraid I can't see any simpler solution than using the -u
option and arranging to have the name and password stuffed into the
commands' stdin. Because it has worked well for me in the past the first
thing I thought of for this was 'expect', but surely it is overkill for
this...