Re: Creating an hourly backup - Mailing list pgsql-general

I have given the requested info below, but read this first:
1)  Check the permissions on the backup file, ensure that the user permissions are such that the
system user you are trying to restore with has permission to read the file
2)  Look at the actual backup file, ensure that there is a backup in it (it should contain SQL
statements to restore your schema and then COPY commands to restore your data)
3)  Your script is complex because it uses expect, I use "su" to switch to the user who is the owner
of the database (in this case postgres) at which point the pg_dump command does not prompt for
username/password (assuming your pg_hba.conf is the default that accepts local connections as
trusted for the username).


Anyway, I trust my hardware for more then an hour, but my cron backup script runs from the root
cron, and looks like this:

su - postgres -c 'pg_dumpall > /backup/postgresdump'

Now you want to do just a specific database perhaps, in which case you likely want:

su - postgres -c 'pg_dump $database > /backup/postgresdump'


Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jason Tesser
> Sent: Wednesday, August 04, 2004 9:29 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Creating an hourly backup
>
>
> I am trying to get an hourly backup of postgres.  I tried using the
> script below
> but when I try to restore off the dumped file it throws an error that
> says it
> cannot read the data [-1].  If I just type in pg_dump -Fc --file=***
> username=**** database
> it restores fine.  So something is going on with the
> except script, posted below.  Can someone send me a working
> cron or except script so I can get an hourly snapshot
> of our databases?
>
> #!/usr/bin/expect -f
>
> ###############################################
> ###############################################
> # Script to backup PostgreSQL Database
> # usage:
> # ./dbbackup.exp [username] [password] [dbname] # # Use this
> script in a
> cron job to do nightly # backups # Date format is MMHHDDMMYYYY
> ###############################################
> ###############################################
>
> set username [lindex $argv 0]
> set password [lindex $argv 1]
> set database [lindex $argv 2]
> set time [timestamp -format %M%H%d%m%Y]
>
> spawn {pg_dump} -Fc --username=dbdevel
> --file=/var/lib/pgsql/backups/$database.$time $database # expect
> "Username:"
> # send "$username\r"
> expect "Password:"
> send "$password\r"
> expect eof
>
> Jason Tesser
> Web/Multimedia Programmer
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>