Blog About Contact

Useful shell scripts: nightly postgresql backup

Published Sun, 11 Jan 2015

Here's a shell script which does a daily backup of a postgresql database and keeps a weeks worth of backups locally.

You can then rsync this folder over ssh from another server or something like that.

Run with ./ [databasename] - assuming you have access to the database from your user account.

pg_dump -Fc $1 > /var/lib/pgsql/9.3/backups/$1_$(date +"%Y-%m-%d").dump
find /var/lib/pgsql/9.3/backups/* -mtime +7 -exec rm {} \;

This is a custom format archive which you can use multi-job concurrency to restore in parallel (useful for big databases), e.g.

pg_restore -d myrestoredb -j 8 mydb_2015-01-01.dump

Would restore the dump into myrestoredb with 8 concurrent processors.

About the Author

Richard Nichols is an Australian software engineer with a passion for making things.

Follow him on twitter or subscribe by RSS or email.

You might also enjoy reading -

Discuss / Comment

No one has commented yet.

Add a comment

  • {{e.error}}

Thanks for your comment!/

Valid email address required.
Posting message, please wait...