Note on PostgreSQL backups

So, Postgres has a neat feature to backup in parallel by dumping the tables in multiples at a time. This isn’t difficult to find online, in fact here you go:

pg_dump -j num -Fd  out.dir -f dbname

What is IMPOSSIBLE to find is this stupid -j switch for restores. You need it when restore the DB back. Notice that the -j is at the front of the dump. That won’t do when you restore it, you need to put it right before the file you’re restoring from. See below:

pg_restore -h localhost -d dbname -j fileRestoringFrom.tar

Sorry, correction needed. Turns out this won’t do the trick. Here’s what really needs to done:

pg_restore -h localhost -Fd dbname | psql

dbname is what you used on the -f switch above to save the dumpdir for the export. So if at the end of the pg_dump you have dir called mydatabase and it contains the table dumps and the toc.dat file, this is what you’d reference in the “dbname” above. Keep in mind, if you tar this file up to move it, you’ll need to untar it to do the restore.

Best of luck!





Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s