Обсуждение: Automated backup problems - pg_dump
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
On Sun, 24 Aug 2003, Jesse Burkhardt wrote: > 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 I think the environment variable is PGPASSWORD (no underscore) in at least 7.3 and above. Also, it's somewhat depricated in favor of using the .pgpass file.
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.
Yo Stephan,
Thanks. That was the problem: that there is no longer and underscore in the pg password env var, PGPASSWORD.
Are you familiar with the Hungarian jazz guitarist Gabo Szabo?
Stephan Szabo wrote:
Thanks. That was the problem: that there is no longer and underscore in the pg password env var, PGPASSWORD.
Are you familiar with the Hungarian jazz guitarist Gabo Szabo?
Stephan Szabo wrote:
On Sun, 24 Aug 2003, Jesse Burkhardt wrote: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 commandI think the environment variable is PGPASSWORD (no underscore) in at least 7.3 and above. Also, it's somewhat depricated in favor of using the .pgpass file.
-- Jesse Burkhardtjesse@skybuilders.com (w) 617-876-5680goose@aerogoose.com (h) 617-354-5523
Yes the variable is actually: PGPASSWORD I have a crond pg_dump script (as root) that sets this variable and dumps with "-U postgres" and uses the variable successfully. (7.2.4). Cheers CM Stephan Szabo wrote: > On Sun, 24 Aug 2003, Jesse Burkhardt wrote: >>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 > > I think the environment variable is PGPASSWORD (no underscore) in at least > 7.3 and above. Also, it's somewhat depricated in favor of using the > .pgpass file. -- Chris Miles http://chrismiles.info/