14

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?

16

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:

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.

  • After setting checkpoint_segments to 30, no more of these messages appear in the log. Thanks. – Sébastien Clément Oct 15 '15 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.