I use a regular user account to manage my database, not root. So I've
cron'd 2 perl scripts with that account that runs a backup and copy to a
remote Windows 2000 file server that's backed up nightly. Here are my 2
#Perform Backup/Vacum on database1
print "Backup/Vacum on database1\n";
system("/usr/bin/pg_dump database1 > /usr/local/backup/postgres/database1.dump");
system("/usr/bin/psql database1 -c \"vacuum;\"");
#Perform Backup/Vacum on database2
print "Backup/Vacum on database2\n";
system("/usr/bin/pg_dump database2 > /usr/local/backup/postgres/database2.dump");
system("/usr/bin/psql database2 -c \"vacuum;\"");
That is set to run every night a 10pm. This next script is setup to run
every night at 10:30.
# Mount remote filesystem and copy to it for main backup.
print "Tar/GZip the html folder...\n";
system("/bin/tar -czf /usr/local/backup/postgres/htmlback.tar.gz /var/www/html/");
print "Done with the Tar/GZip...\n";
print "Mount/Copy backups\n";
system("/usr/bin/sudo /bin/mount /mnt/smb");
system("/usr/bin/sudo /bin/rm -f /mnt/smb/*.dump");
system("/usr/bin/sudo /bin/rm -f /mnt/smb/*.tar.gz");
system("/usr/bin/sudo /bin/cp /usr/local/backup/postgres/*.dump /mnt/smb/");
system("/usr/bin/sudo /bin/cp /usr/local/backup/postgres/*.tar.gz /mnt/smb/");
system("/usr/bin/sudo /bin/umount /mnt/smb");
I have to run sudo because mount isn't allowed by normal users. I have
the location of the file server with the correct username/password in
To restore the database, all you have to do is create the database name,
then type 'psql -e database1 < database1.dump' and it will restore the
tables and contents to the exact minute you backed them up.
Hope this helps...
On 23 Jun 2002, Eric Naujock wrote:
> I am looking to setup a reliable and automatic way to backup my
> Postgresql database to a remote server.
> What I have tried is to use the PG_Dumpall function to dump the contents
> of my databases. And then have a remote machine use SMB to back up the
> files using our backup system. My problem is that pg_dumpall wants a
> password for the account I want to use to run the backup.
> Is there a better way to do this. If my understanding is correct, It
> would be a bad thing for me to just try to tarball the /pgsql folder
> when the databases is running. I cannot readily take the database
> offline to run the backup as it is a key part of running a webserver and
> some network utilities.
> Does anyone have an suggestions on how to do this well. I also have to
> be able to backup my BLOBS that are in the database.
> Thanks in advance.
> Eric Naujock CCNA, CCDA, A+, Network +, I-Net +
> Abacus II
> 5610 Monroe St.
> Sylvania, Ohio 43560
> E-mail - naujocke(at)abacusii(dot)com
> Phone - 419-885-0082 X 241
> Fax : 419-885-2717
> AOL IM: erlic
In response to
pgsql-novice by date
|Next:||From: Eric Naujock||Date: 2002-06-24 01:46:00|
|Subject: Expect and scripting.|
|Previous:||From: Eric Naujock||Date: 2002-06-23 17:18:00|
|Subject: Reliable and scrptable backup to Postgresql databases.|