7

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.

1 Answer 1

9

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 http://www.postgresql.org/docs/8.4/static/app-pgrestore.html. Example:

pg_restore -j 4 -d emptydb backup.file 
2
  • 1
    Using pg_restore -j 2 decreased my restore time from 90 minutes to 45 minutes! I'll try -j 4 next. Thanks!
    – Jared Beck
    Commented Dec 7, 2011 at 6:50
  • 1
    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. Commented Dec 9, 2011 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.