Thread
-
Reliable and scrptable backup to Postgresql databases.
Eric Naujock <naujocke@abacusii.com> — 2002-06-23T17:18:00Z
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
-
Re: Reliable and scrptable backup to Postgresql databases.
John Nix <maximum@shreve.net> — 2002-06-23T18:25:34Z
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 > -
Re: Reliable and scrptable backup to Postgresql databases.
Aarni Ruuhimäki <aarni.ruuhimaki@kymi.com> — 2002-06-24T06:03:10Z
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. BR, aarni #!/bin/bash # #Backupscript # cd /work/backup #directory where the files come tar cfz pg_daily_dumps.gz /usr/share/pgdumps/all export RSYNC_PASSWORD="xxxxxxxxxxxxxxxxxxxxxxxxxx" rsync -azR . psw8326@212.94.64.10::my.server.com #eof On Sunday 23 June 2002 08:18 pm, you 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