Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

1 Answer 1

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

pg_restore -j 4 -d emptydb backup.file 
share|improve this answer
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 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

 
discard

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.