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…")
 
m (Fix typo)
Line 2: Line 2:
 
are sub-optimal for bulk imports.  To make a database restore go faster, set
 
are sub-optimal for bulk imports.  To make a database restore go faster, set
 
the following parameters in <code>postgresql.conf</code>. '''NOTE:''' Make sure
 
the following parameters in <code>postgresql.conf</code>. '''NOTE:''' Make sure
to keep the old values around in comments, because your ''MUST'' restore the old
+
to keep the old values around in comments, because you ''MUST'' restore the old
 
values prior to putting the database into production!
 
values prior to putting the database into production!
  

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