Checkpoints are occurring too frequently during pg_restore

Question

Under PostgreSQL 9.2.2 (Windows 32 bits), I have a pg_restore command that systematically results in log warnings about the checkpoint frequency, for example:

LOG:  checkpoints are occurring too frequently (17 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".

The database is about 3.3 Gb in size with 112 tables/160 views and restores in about 14 minutes.

Is it normal for that to occur during a pg_restore?

Answer 1

Not uncommon during a whole-DB restore because that's an exceptionally huge operation. If you see this during normal operation, consider raising your setting for checkpoint_segments permanently, just like the error message hints.

You might go to the trouble of setting checkpoint_segments higher just before the restore and then lower it again. This is even what the manual suggests (including an explanation):

Temporarily increasing the checkpoint_segments configuration variable can also make large data loads faster. This is because loading a large amount of data into PostgreSQL will cause checkpoints to occur more often than the normal checkpoint frequency (specified by the checkpoint_timeout configuration variable). Whenever a checkpoint occurs, all dirty pages must be flushed to disk. By increasing checkpoint_segments temporarily during bulk data loads, the number of checkpoints that are required can be reduced.

Related answer with more details:

• Configuring PostgreSQL for write performance

Postgres 9.5

The upcoming new release has a smarter approach. Quoting the beta release notes:

Replace configuration parameter checkpoint_segments with min_wal_size and max_wal_size (Heikki Linnakangas)

This allows the allocation of a large number of WAL files without keeping them if they are not needed. Thus the default for max_wal_size has been increased to 1GB.

Aside: the number of views is barely relevant, those do not contain any data, just the "recipe", i.e.: the query and some attributes of the view. For the question at hand, basically only the total size of the backup file matters.