Use PostgreSQL’s ‘custom format’ to efficiently backup and restore tables

PostgreSQL allows you to easily dump tables for backup purposes into plain text files using code such as:

pg_dump mytable > mytable_backup.sql

But this process is not very flexible and can create very large output files. As an alternative, PostgreSQL has a nice little feature that allows users to export to what is called a 'custom format', a format that is zipped by default (with no extra steps) and permits significant flexibility particularly on re-import.

In terms of flexibility the custom format permits some nifty tricks when restoring. In particular, you can run simultaneous jobs for loading the data significantly reducing the amount of time required to restore the data. Most importantly, the argument -j allows the user to specify the number of concurrent jobs. In this post Dan Langille shows that with a 13 GB database using the -j argument in pg_restore with 8 simultaneous jobs cut the restore time in half! He also shows that using the custom format cuts the size of the dumps to 1/3 of the original size

Here is an example showing the export and then re-import of a database using the -j:

--Create the dump of the database
pg_dump -U postgres -Fc myDB > myDB.dump

--Now restore using concurrent import
pg_restore -j 8 -U postgres -d myDB myDB.dump

One final note for those who use PostGIS. By default, creating a dump file dumps all the schema including the tiger, tiger_data and topology schema and I've found restoring these results in a lot of error messages. To dump only the public schema use:

pg_dump -U postgres -n public -Fc myDB > myDB.dump

3 responses

    • I haven’t done this explicitly and haven’t had issues, but I’m not certain — if you find out more let me know.

  1. On the final snippet, you don’t want to run the -U command as that connects as the user for the dump. If you do use it (the system user running the process is different then the PostgreSQL user) then you will need to specify your specific username and perhaps a password as well.

Leave a Reply

Your email address will not be published. Required fields are marked *