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
======================================================
Show quoted text
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