Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

According to the docs for my version of Postgres (8.4.2), pg_dump can "output in script or archive file formats." The script-formatted dump (4GB) takes 1.5 hours to restore, and I'm wondering if the archive format is any faster.

My script-formatted dump is using COPY commands, by the way, so it's not like I'm doing one insert at a time.

Thanks in advance.

share|improve this question
up vote 7 down vote accepted

Archive format has small impact on restore time.

That's because actually, when you restore from plain format, it is equivalent to:

cat backup.file | psql emptydb

and when you restore from "compressed" format, it is equivalent to:

pg_restore backup.file | psql emptydb

, and the cost of decompression with pg_restore is usually negligible, compared to write-intensive COPY/CREATE INDEX commands which happen during restore.

For large dumps, restoring from compressed dumps may be a bit faster (less disk reads).

It might be even faster with -j (--jobs) option. It can be used only with "compressed" format, and it allows paralell restore with N concurrent jobs (sessions).

For more details about -j option, see Example:

pg_restore -j 4 -d emptydb backup.file 
share|improve this answer
Using pg_restore -j 2 decreased my restore time from 90 minutes to 45 minutes! I'll try -j 4 next. Thanks! – Jared Beck Dec 7 '11 at 6:50
In simple terms -j x allows you to throw x processes at the problem allowing you to use more CPU power / cores to restore. Eventually you'll overwhelm your IO subsystem and then the restore will start to get slower. On machines with fast IO subsystems it's well worth experimenting with. – Scott Marlowe Dec 9 '11 at 18:41

Your Answer


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.