Thread: Automated backup problems - pg_dump

Automated backup problems - pg_dump

From
Jesse Burkhardt
Date:
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



Re: Automated backup problems - pg_dump

From
Stephan Szabo
Date:
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.


Re: Automated backup problems - pg_dump

From
"Chad R. Larson"
Date:
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. 

Re: Automated backup problems - pg_dump

From
Jesse Burkhardt
Date:
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:
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. 

-- 
Jesse Burkhardtjesse@skybuilders.com  (w) 617-876-5680goose@aerogoose.com    (h) 617-354-5523

Re: Automated backup problems - pg_dump

From
Chris Miles
Date:
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/