Difference between revisions of "Speeding up PostgreSQL Restore"
From Roaring Penguin
| Line 15: | Line 15: | ||
<div style="float:right; clear:both; margin-right:0.5em">[[Support Wiki | [Home]]]</div> | <div style="float:right; clear:both; margin-right:0.5em">[[Support Wiki | [Home]]]</div> | ||
| − | [[category:All]][[category:Database]][[category:Best Practices]] | + | [[category:All]][[category:Database]][[category:Best Practices]][[category:Upgrade]] |
Revision as of 14:46, 20 November 2014
The default PostgreSQL configuration parameters are fine for normal operation, but
are sub-optimal for bulk imports. To make a database restore go faster, set
the following parameters in postgresql.conf. NOTE: Make sure
to keep the old values around in comments, because you MUST restore the old
values prior to putting the database into production!
- fsync = off (This is very dangerous for production)
- checkpoint_segments = 128 # large value, such as 128 or 256
- checkpoint_timeout = 30min
- checkpoint_completion_target = 0.9
- wal_level = minimal
Once you've changed the parameters, restart PostgreSQL and perform the restore. Once the database has been restored, change the parameters back to their original values and restart PostgreSQL.
