Difference between revisions of "Speeding up PostgreSQL Restore"

From Roaring Penguin
Jump to: navigation, search
(Created page with "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 para…")
(No difference)

Revision as of 11:07, 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 your 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.