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.

Improve this question

1 Answer 1

Reset to default
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
Improve this answer
2
  • 1
    Using pg_restore -j 2 decreased my restore time from 90 minutes to 45 minutes! I'll try -j 4 next. Thanks! 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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.