Reliable and scrptable backup to Postgresql databases.

Reliable and scrptable backup to Postgresql databases.

Post by John N » Tue, 25 Jun 2002 03:28:22

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


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


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


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


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

> Phone - 419-885-0082  X 241
> Fax : 419-885-2717
> AOL IM: erlic

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command


1. Reliable and scrptable backup to Postgresql databases.

Hi !

I use two scheduled crons to first vacuum and then dump all dbs.

Then I have a simple shell script to tarball the dump and to move it to
another machine with rsync. Not sure if this works with blobs.



cd /work/backup #directory where the files come

? ? ? ?tar cfz pg_daily_dumps.gz /usr/share/pgdumps/all

? ? ? ?

? ? ? ? export RSYNC_PASSWORD="xxxxxxxxxxxxxxxxxxxxxxxxxx"


---------------------------(end of broadcast)---------------------------

2. Anybody Know of Connecticut Citizen?

3. Good CD brand for reliable backup?

4. check for netscape

5. Postgresql backups

6. Filter help needed

7. Please Help - HP Scanjet 4c ands NT

8. WTB: Linker/locater for 386EX project - new or surplus!

9. comp.database, comp.databases,,,, comp.databases.sybase, comp.databases.olap, comp.editors

10. PostgreSQL or RedHat Database 2.1

11. Porting existing postgresql databases to another

12. Porting existing postgresql databases to another machine

13. Copying a PostgreSQL Database from local machine to Web Site Host