Difference between revisions of "Speeding up PostgreSQL Restore"

From Roaring Penguin
Jump to: navigation, search
 
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:Upgrade]]
+
[[category:All]][[category:Database]][[category:Upgrade]]

Latest revision as of 14:52, 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.