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

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
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Why don't you just use a simple shell script run via cron.hourly:

#!/bin/sh
cd /opt/backups
pg_dump -R -O -a -i -Umyuser mydatabase > db_backup`date +'%Y%m%d'`.sql
gzip --best *.sql


I'm using the above for a daily backup in /etc/cron.daily. Look at the options
to pg_dump - they may not be what you want. The above will dump data only, no
structure.

Just a thought.

UC


On Wednesday 04 August 2004 06:29 am, Jason Tesser wrote:
> 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

- --
    UC

- --
Open Source Solutions 4U, LLC    2570 Fleetwood Drive
Phone:  +1 650 872 2425        San Bruno, CA 94066
Cell:   +1 650 302 2405        United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBEdw6jqGXBvRToM4RAr73AJ9xQ4kTLRuIAKgq57mqvv+9HZh5UwCgx2lL
6EkUiLWqrsZaADkN41i/dK4=
=2mXn
-----END PGP SIGNATURE-----