Profile Applicability:

  • Level 1

Description:
 Enabling the log_checkpoints parameter ensures that PostgreSQL logs every checkpoint, which is a crucial part of PostgreSQL's write-ahead log mechanism. These logs help track the progress of the database’s recovery process and can be invaluable for troubleshooting, detecting performance issues, and ensuring data consistency.

Rationale:
 Enabling log_checkpoints ensures that each checkpoint is logged, providing better visibility into how frequently data is being written to the disk and when checkpoints are happening. This is critical for monitoring system performance, detecting potential issues, and ensuring compliance with auditing and security standards.

Impact:

  • Pros:

    • Provides insight into database recovery operations.

    • Helps monitor the frequency and performance of checkpoints.

    • Essential for troubleshooting and performance tuning.

  • Cons:

    • Additional log entries can increase the storage requirements.

    • High-traffic systems could produce large amounts of log data.

Default Value:

The default value for log_checkpoints is OFF.

Pre-requisites:
 Ensure that logging is enabled for PostgreSQL and that the logs are being managed properly to avoid excessive storage consumption.

Remediation

Test Plan:

Using Azure Portal:

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

  2. Navigate to Azure Database for PostgreSQL servers.

  3. Select the desired PostgreSQL database.

  4. Under Settings, click on Server parameters.

  5. Search for the log_checkpoints parameter.

  6. Ensure the value is set to ON.

Using Azure CLI:
1. Run the following command to verify the log_checkpoints setting:

az postgres server configuration show --resource-group <resource-group-name> --server-name <server-name> --name log_checkpoints


  • The output should show log_checkpoints as ON.

Implementation Plan:

Using Azure Portal:

  1. Go to Azure Database for PostgreSQL servers.

  2. Select the database server.

  3. Under Settings, click on Server parameters.

  4. Set the log_checkpoints parameter to ON and save.

Using Azure CLI:
1.  To enable the log_checkpoints parameter, use the following command:

az postgres server configuration set --resource-group <resource-group-name> --server-name <server-name> --name log_checkpoints --value ON


Backout Plan:

Using Azure Portal:

  1. Go to Azure Database for PostgreSQL servers.

  2. Under Settings, click on Server parameters.

  3. Set the log_checkpoints parameter back to OFF if necessary.

Using Azure CLI:
 1. To disable the log_checkpoints parameter, use the following command:

az postgres server configuration set --resource-group <resource-group-name> --server-name <server-name> --name log_checkpoints --value OFF


References: