Speeding up PostgreSQL Restore

From Roaring Penguin
Revision as of 13:41, 20 November 2014 by MCoyne (talk | contribs)

Jump to: navigation, search

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.