Welcome Guest | Login

PostgreSQL Automated Secure Backup

I have a PostgreSQL database I need to back up regularly. The backup must happen in an automated way, e.g. from a cron job. What is the recommended way to do this on HostingRails?

I can successfully run pgdump by hand with a command like this:
  pg_dump my_database_name -o -c -f dbdump -u

To run this same command unattended / automated I need a way to specify the user name and password automatically. I understand PostgreSQL lets you do that with .pgpass, but I cannot seem to get .pgpass working for me. I don't want to use environment variables since that approach is not secure.

Is there a better way?  I've seen this post http://www.hostingrails.com/forums/wiki_thread/22 but it applies only to MySQL.

Osh

P.S. The PostgreSQL admin tool seems to generate nothing at all for backups.

2008-01-03 11:58 AM

The bash script below should work for you.
#!/bin/sh
/usr/bin/pg_dump <database> -U username   > /home/username/`date +%Y%m%d`.sql
/usr/bin/gzip /home/username/`date +%Y%m%d`.sql


You should have the entry below in your .pgpass file

*:*:*:username:password

PS: Please replace user name with full username assigned to the database and it's password with "password"

2008-01-03 01:25 PM

Thanks Varun, this works properly for a test database I just created afresh using the CPanel Postgres Account Maintenance tool, but it fails with a permission denied error for my production database.

I think this is a permissions problem specific to my database that I've run into before. You still have a support ticket open for me on this: #20071217100322434: PgAdmin tool can no longer browse PostgreSQL databases after I run a rails migration on the database. Please send me email and I'll let you know the specific database name that needs permissions fixed.

P.S. For others reading this, here is another useful Postgresql backup script:
http://archives.postgresql.org/pgsql-admin/2001-03/msg00143.php

2008-01-03 01:44 PM

Hi Osh  -

The ticket which you're referring to is actually closed with one of our tech's query. I've replied back to it again now. Please update the ticket for conferring this further.  

2008-01-03 02:11 PM


Hello Guest! In order to post you must be an active client with us, please log in or sign up today!