Re: Automated backup problems - pg_dump

At 03:46 PM 8/24/2003 , Jesse Burkhardt wrote:
One (non-trivial) solution is to use a cron (like fcron) that allows you to
set the environment of the executed process.

I like fcron very much, but you'd have to be the system administer (or his
cooperation) to install it.

>I am sure many of you have encountered the problems I am experiencing with
>automating the pg_dump command from a cron. The problem arises from a
>failure of this command to use the environmental variable, PG_PASSWORD.
>(PGUSER, on the other hand, is picked up during command execution.) The
>cron seems to fail completely when crontabbed as the PGSQL superuser,
>postgres. I do, however, get partial execution when I su to the user
>postgres from a cron crontabbed as root. Let me layout some relevant info:
>
>root orginated cron:
>
>30 * * * * su - postgres --command="cd /var/lib/pgsql/skybuilders;
>/var/lib/pgsql/skybuilders/dbbackup.pl; echo `date` > timeRun.txt"
>
>skybuilders is a directory where I copy and dump PostgreSQL files and DBs
>to be picked up by an rsync cron on an archiving machine.
>
>dbbackup.pl is a perl script that generates a bash shell. I will excerpt
>the pertinant (failing) section of the generated script (dbbackupGenerated.sh):
>
>PGUSER=someuser
>export PGUSER
>PG_PASSWORD=somepassword
>export PG_PASSWORD
>pg_dump -o somedb > /var/lib/pgsql/skybuilders/dbbackup/somedb.dump
>
>When I run the script from the command line, after having su'ed into the
>postgres account, this script stops at the pg_dump line waiting for a password.
>
>Finally I will excerpt some of the final lines of my pg_hba.conf file:
>
># TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE
>AUTH_ARGUMENT
>
>#local all trust
>#host all 127.0.0.1 255.255.255.255 trust
>
># Using sockets credentials for improved security. Not available everywhere,
># but works on Linux, *BSD (and probably some others)
>
>#local all ident sameuser
>
>### --> Added by JSB <--
>
>local skyTemplate md5
>local sameuser md5
>local all md5 admins
># use following line to perform commands as postgres without password
>challenge
># local all trust
>
>host all 10.10.40.25 255.255.255.255 md5
>
>I understand were I to change the AUTH_TYPE setting from "md5" to "trust"
>the password challenge emanating from the pg_dump command will go away.
>However, I wish to maintain the use of md5 authentication.
>
>Ideas anybody?
>
>--
>
>Jesse Burkhardt
>jesse(at)skybuilders(dot)com (w) 617-876-5680
>goose(at)aerogoose(dot)com (h) 617-354-5523
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you.