Skip site navigation (1) Skip section navigation (2)

Re: Reliable and scrptable backup to Postgresql databases.

From: John Nix <maximum(at)shreve(dot)net>
To: Eric Naujock <naujocke(at)abacusii(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Reliable and scrptable backup to Postgresql databases.
Date: 2002-06-23 18:25:34
Message-ID: Pine.LNX.4.44.0206231314070.17548-100000@server.sblug.org (view raw or flat)
Thread:
Lists: pgsql-novice
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(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 NaujockDate: 2002-06-24 01:46:00
Subject: Expect and scripting.
Previous:From: Eric NaujockDate: 2002-06-23 17:18:00
Subject: Reliable and scrptable backup to Postgresql databases.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group