Thread: 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