Description:

This control ensures that the PostgreSQL server parameter log_checkpoints is enabled (set to ON). When enabled, PostgreSQL logs information about checkpoints, including the start and end times, the number of buffers written, and the duration. Checkpoint logging helps database administrators monitor database performance, detect unusual activity, and troubleshoot issues such as I/O bottlenecks or long checkpoint durations.


Rationale:

  • Performance Monitoring: Checkpoints can impact database performance; logging them allows administrators to track checkpoint frequency, duration, and buffer writes.

  • Troubleshooting: Helps identify and diagnose performance degradation, I/O spikes, or other database anomalies.

  • Operational Awareness: Provides visibility into internal database operations for proactive optimization.

  • Compliance & Auditing: Logging critical database activities supports auditing requirements and operational transparency.


Impact:

  • Improves visibility into database internal operations, aiding in proactive monitoring and performance tuning.

  • Supports troubleshooting for long-running checkpoints or database slowdowns.

  • Helps meet compliance and audit requirements by capturing operational logs.


Default Value:

  • By default, in PostgreSQL, log_checkpoints is set to ON.

  • PostgreSQL Server Must Use a Flexible Server or a Single Server with Configurable Parameters.


Pre-requisites:

  • Global Administrator or Security Administrator permissions.

  • Must be Azure Database for PostgreSQL – Flexible Server (or Single Server if the parameter is supported).


Test Plan:

  1. Sign in to the Azure Portal at https://portal.azure.com

  2. Search for and open the Azure Database for PostgreSQL

  3. Select the target PostgreSQL server

  4. From the left menu, under settings,  select Server parameters

  5. Search for the parameter log_checkpoints

  6. Verify log_checkpoints is set to ON

  7. If log_checkpoints is not set to ON, follow the implementation steps



Implementation Steps:

  1. Sign in to the Azure Portal at https://portal.azure.com

  2. Search for your Azure Database for PostgreSQL server (Single or Flexible).

  3. On the left-hand menu, under Settings, select server parameters.


                                                  


  1. Search for log_checkpoints in the server parameters.

          

  1. Set its value to ON (If you keep the log_checkpoints OFF). Save the changes


BackOut Plan:

  1. Sign in to the Azure Portal at https://portal.azure.com

  2. Search for and open the Azure Database for PostgreSQL server

  3. From the left-hand menu, under settings, select Server parameters

  4. Locate the parameter log_checkpoints

  5. Set log_checkpoints to OFF

  6. Save the changes


Reference: