Thread: pg_dump.. auto backup

Ganesan Kanavathy wrote:

> I am doing a cronjob to dump pg database everyday.
>
> Below is my script for cronjob.
>
> #!/bin/sh
>
> */usr/local/pgsql/bin/pg_dump -U ganesh --no-owner -d mq >
> /backupdb/mq.dump*
>
> cd /
>
> cd /backupdb/
>
> tar -czf /backupdb/mq-`date '+%d-%m-%Y'`.tar.gz -R *
>
> This cronjob is schedule to run everyday at 3.30am.
>
> The problem I am facing is the when running the cronjob it is asking
> for password. If I remove *–U ganesh*, it is still prompting for
> password but this time root password.
>
> */How do I solve this, when running the pg_dump I don’t want it to
> prompt for password./*
>
> Regards,
>
> ganesh
>
Hi,

Just add:
export PGPASSWORD=mypassword
to your script.

Be careful - this is like leaving the key under your house key under the
mat.

Regards
Rudi.

Thanks Rudi,

If possible I always prefer not to hard code any password in my script.

Regards,
ganesh

-----Original Message-----
From: Rudi Starcevic [mailto:rudi@oasis.net.au]
Sent: Thursday, October 16, 2003 10:05 AM
To: Ganesan Kanavathy
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_dump.. auto backup

Ganesan Kanavathy wrote:

> I am doing a cronjob to dump pg database everyday.
>
> Below is my script for cronjob.
>
> #!/bin/sh
>
> */usr/local/pgsql/bin/pg_dump -U ganesh --no-owner -d mq >
> /backupdb/mq.dump*
>
> cd /
>
> cd /backupdb/
>
> tar -czf /backupdb/mq-`date '+%d-%m-%Y'`.tar.gz -R *
>
> This cronjob is schedule to run everyday at 3.30am.
>
> The problem I am facing is the when running the cronjob it is asking
> for password. If I remove *-U ganesh*, it is still prompting for
> password but this time root password.
>
> */How do I solve this, when running the pg_dump I don't want it to
> prompt for password./*
>
> Regards,
>
> ganesh
>
Hi,

Just add:
export PGPASSWORD=mypassword
to your script.

Be careful - this is like leaving the key under your house key under the

mat.

Regards
Rudi.

Rudi Starcevic <rudi@oasis.net.au> writes:
> Ganesan Kanavathy wrote:
>> The problem I am facing is the when running the cronjob it is asking
>> for password.

> Just add:
> export PGPASSWORD=mypassword
> to your script.

A much better solution (assuming you are using a reasonably recent PG
release) is to put the needed password in ~/.pgpass, which can be set to
0600 permissions.  PGPASSWORD is insecure on many platforms because
other people can see your environment variables.

            regards, tom lane

Thanks for the reply,

I don't really understand this. How to put password in ~/.pgpass. What
changes do I need to do on the script? How do I access to the password
to authenticate?

I'm still new Linux. Hope to get more explanation and guidance.

Thanks.

Regards,
ganesh

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Thursday, October 16, 2003 11:37 AM
To: Rudi Starcevic
Cc: Ganesan Kanavathy; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_dump.. auto backup

Rudi Starcevic <rudi@oasis.net.au> writes:
> Ganesan Kanavathy wrote:
>> The problem I am facing is the when running the cronjob it is asking
>> for password.

> Just add:
> export PGPASSWORD=mypassword
> to your script.

A much better solution (assuming you are using a reasonably recent PG
release) is to put the needed password in ~/.pgpass, which can be set to
0600 permissions.  PGPASSWORD is insecure on many platforms because
other people can see your environment variables.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

> I don't really understand this. How to put password in ~/.pgpass. What
> changes do I need to do on the script? How do I access to the password
> to authenticate?

Follow the below steps to automate your backup process.

bash$ echo your_host:5432:your_user:your_db:your_pass > ~/.pgpass
bash$ chmod 600 ~/.pgpass

Now, run your script and it must have been automated.

regards,
bhuvaneswaran