Re: 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(at)skybuilders(dot)com (w) 617-876-5680
goose(at)aerogoose(dot)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.

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 Burkhardt
jesse(at)skybuilders(dot)com (w) 617-876-5680
goose(at)aerogoose(dot)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/