Re: Reliable and scrptable backup to Postgresql databases.

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
perl scripts...

pgback.pl:

#!/usr/bin/perl

#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;\"");

print "Done!\n";

EOF

That is set to run every night a 10pm.  This next script is setup to run
every night at 10:30.

copytonet.pl

#!/usr/bin/perl

# 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");
print "Done!\n";

EOF

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
fstab.

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...

John

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
> <http://www.abacusii.com>
> E-mail - naujocke@abacusii.com
> Phone - 419-885-0082  X 241
> Fax : 419-885-2717
> AOL IM: erlic
>