Обсуждение: Automated Database Backups

Does anyone have a way to run pg_dump from cron in a way that doesn't
require someone to enter the password on every run?  I get the following
error even when the backup user has read permissions on all application
databases.

The database that fails is part of the PostgreSQL kernel, not my
application data so I'm leery of tampering with permissions here.

The command line I am using is:

/usr/postgres-8.4.2/bin/pg_dump -f /notraid/dbbkup.`date +%Y%m%d` -F
custom -v -U xxxx dbxxxx

Thanks in advance.

John

On 15/10/2010 22:12, John Iliffe wrote:
> Does anyone have a way to run pg_dump from cron in a way that doesn't
> require someone to enter the password on every run?  I get the following
> error even when the backup user has read permissions on all application
> databases.

You need to use a .pgpasss file:

    http://www.postgresql.org/docs/9.0/static/libpq-pgpass.html

You can instead specify the password in an environment variable, but
that potentially makes it visible to all users on the system, so the
.pgpass is better.

HTH,

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

John,

> Does anyone have a way to run pg_dump from cron in a way that doesn't
> require someone to enter the password on every run?  I get the following
> error even when the backup user has read permissions on all application
> databases.

You can use the .pgpass file in the backup owner's home directory:

*:5432:*:postgres:MyPassword
*:5432:*:xxxx:XsPassword

-- Gary Chambers

Thanks to those who replied to my post.

I tried that and works a treat!

Regards.

John
======================================================
On Sat, 2010-16-10 at 17:18 +0100, Raymond O'Donnell wrote:
> On 15/10/2010 22:12, John Iliffe wrote:
> > Does anyone have a way to run pg_dump from cron in a way that doesn't
> > require someone to enter the password on every run?  I get the following
> > error even when the backup user has read permissions on all application
> > databases.
>
> You need to use a .pgpasss file:
>
>     http://www.postgresql.org/docs/9.0/static/libpq-pgpass.html
>
> You can instead specify the password in an environment variable, but
> that potentially makes it visible to all users on the system, so the
> .pgpass is better.
>
> HTH,
>
> Ray.
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod@iol.ie
>

On Fri, 15 Oct 2010 17:12:50 -0400, John Iliffe <john.iliffe@iliffe.ca>
wrote:
> Does anyone have a way to run pg_dump from cron in a way that doesn't
> require someone to enter the password on every run?  I get the following
> error even when the backup user has read permissions on all application
> databases.

You can configure PSQL to use ident authentication, it should be
configured as default, so user "postgres" have full access to all
databases. Then assign home directory for this user, and create user
specified cron file. Then you will don't need to store password.

--
----------
Radosław Smogura
http://www.softperience.eu