Thread: Reliable and scrptable backup to Postgresql databases.

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

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
>

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