Re: Automated backup problems - pg_dump - Mailing list pgsql-admin

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@skybuilders.com  (w) 617-876-5680
>goose@aerogoose.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
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, 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
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.