Friday, March 28, 2008

Backing Up and Restoring Postgresql Databases

I recently have defected from Gentoo to Ubuntu, (*Gasp*, but I'm finally sick of the constant waiting from compiling and re-compiling, or suffering from dependencies breakage or conflicting masks preventing me from updating between new and old packages and various other complaints, but I'll leave that to another day), which one of the things that I had to do was to perform a backup of all my stuffs, including the data on my databases.

For Postgresql, it is really straightforward, and if anybody did a RTFM of Postgresql's man pages, you'll have probably found the answer. But if you're just lazy and wanted to consult the 'Oracle of Mountain View' and be spoonfed, here's how. To back it up:

pg_dumpall > db.out


Where db.out is your output file. Here's how you restore the database back into it's original state:

psql -f db.out postgres