Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Is there any simple way to backup all your local databases daily (same postgresql server)? I found some bash script that I can use but they seem very complex for such a job.

Any easy suggestions ?

share|improve this question

migrated from stackoverflow.com Mar 16 '11 at 20:36

This question came from our site for professional and enthusiast programmers.

add comment

3 Answers 3

Since my databases are fairly small I "cheat" and use logrotate to do my postgresql-backups. Something like this in /etc/logrotate.d/postgresql-backup :

/var/backups/postgresql-dump.sql {
        daily
        nomissingok
        rotate 30
        compress
        delaycompress
        ifempty
        create 640 root adm
        dateext
        postrotate
                /usr/bin/sudo -u postgres /usr/bin/pg_dumpall --clean > /var/backups/postgresql-dump.sql
        endscript
}

This gives me a conveniently available complete dump, no more than a day old, and automatically compresses and rotates previous dumps.

Obviously this won't scale with larger databases, but for my purpose it's just about perfect. (I also use BackupPC to handle off-site backups which include the local postgresql-backups)

share|improve this answer
    
I really like that, excellent idea. –  hulbs9nw Mar 16 '11 at 21:15
    
@olemd, why won't this scale for larger databases? I'm new to this and want to understand it. –  Rob Bednark Aug 13 '12 at 18:42
    
Large databases would typically take a long time to dump and would most likely cause timeout-problems with logrotate. That said, I haven't actually tried it on anything larger than ~200MB databases. If you have "enough" local disk, io and CPU there's no reason it shouldn't work. –  olemd Oct 4 '12 at 8:24
add comment

I set up my daily backup like this:

mkdir /home/dbdump

vim /etc/cron.daily/dbdump

#!/bin/sh
pg_dumpall --globals-only --username=postgres > /home/dbdump/globals.dump
pg_dump --format=custom --username=postgres > /home/dbdump/postgres.dump

chmod 700 /etc/cron.daily/dbdump

I then use rsnapshot from a different site to keep copies of these dumps

The reason I chose not to use pg_dumpall is that pg_dump has more flexible options. You will need to use multiple pg_dump commands if you have multiple databases

share|improve this answer
1  
Note that pg_dump and pg_dumpall acquire write locks for the tables they're dumping. This means that any program trying to write to those tables will block on that lock (this can mean substantial locking delays for dumping big tables, and is compounded if you compress the stream rather than the results (not an issue in the above script as the output streams aren't being compressed, but it can chew up a lot of disk if your databases are big.) –  voretaq7 Jul 13 '11 at 22:20
add comment

If you have /etc/cron.daily, put there a one-liner that calls pg_dumpall.

Note: this is the simplest way, but creates an uncompressed SQL script, to be executed from psql.

For compressing, gzip (faster), bzip2 or lzma (slower, smaller output)

Keeping a different copy per each week day could be enough in simple cases. Storing weekly, monthly, or incremental backups are up to you.

I suppose the complex scripts you have seen back up the databases in binary format using pg_dump/pg_restore, which is more flexible.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.