Monitoring and logs in Managed Service for PostgreSQL

• Where can I track my disk space utilization to avoid exceeding the 95% limit triggering read-only mode?

• Are logs stored on the same disk as PostgreSQL data? How are they billed?

• How longs are logs retained?

• How to enable SQL query logging?

• What are WALs and what is their purpose?

• What does the Cached metric indicate in the RAM monitoring of a PostgreSQL cluster host?

• How do I get alerts on critical PostgreSQL cluster metrics?

• How do I configure an alert for when disk space usage reaches a certain percentage?

Where can I track my disk space utilization to avoid exceeding the 95% limit triggering read-only mode?Where can I track my disk space utilization to avoid exceeding the 95% limit triggering read-only mode?

You can monitor your disk space usage:

• In the management console using the cluster health monitoring tools.
• In Yandex Monitoring that also allows you to set up alerts for specified metrics.

Are logs stored on the same disk as PostgreSQL data? How are they billed?Are logs stored on the same disk as PostgreSQL data? How are they billed?

Logs are stored on separate disks from data and are rotated automatically. You can view log information using the following CLI command:

yc managed-postgresql cluster list-logs <cluster_ID>

Cluster log storage is free of charge.

What is the retention period for logs?What is the retention period for logs?

Cluster logs are stored for 45 days.

How to enable SQL query logging?How to enable SQL query logging?

To enable SQL query logging:

1. Check that the database has the pg_stat_statements extension installed: If the extension is not installed, add it.

   When using the CLI, run this command:

   yc managed-postgresql database update <DB_name> \
      --cluster-name <cluster_name> \
      --extensions <list_of_installed_extensions>,pg_stat_statements
   

   In the --extensions parameter, specify pg_stat_statements and all other extensions that need to remain enabled. If there are no other extensions in the database, only specify pg_stat_statements.

2. Update the DBMS settings:

   • Set Log min duration statement to 60000 ms. The system will log queries of one minute or longer. You can gradually decrease this value if required. The 0 value enables logging of all queries and can significantly increase the amount of logs and cluster load.
   • Set Auto explain sample rate to 1.
   • If you need to log query plans, add auto_explain to Shared preload libraries, set Auto explain log min duration to 60000 ms, and enable Auto explain log analyze.

   You can update all these settings using the management console, CLI, API, or Terraform. For example, to enable logging of queries and query plans via the CLI, run this command:

   yc managed-postgresql cluster update-config <cluster_name_or_ID> \
      --set log_min_duration_statement=60000 \
      --set auto_explain_sample_rate=1 \
      --set auto_explain_log_min_duration=60000 \
      --set auto_explain_log_analyze=true \
      --set shared_preload_libraries=SHARED_PRELOAD_LIBRARIES_AUTO_EXPLAIN
   

   If you do not need query plans, do not provide auto_explain_log_min_duration, auto_explain_log_analyze, and shared_preload_libraries. If other shared libraries are connected to the cluster, specify them together with SHARED_PRELOAD_LIBRARIES_AUTO_EXPLAIN in the shared_preload_libraries setting.

3. View cluster logs. To do this, view access to cluster logs is sufficient. To run queries to the pg_stat_statements view, having a permission for connecting to the database is sufficient: the mdb_monitor role is not required.

What are WALs and what is their purpose?What are WALs and what is their purpose?

Write-Ahead Logs are used for writing data to the disk and for data replication. WALs are created during write requests and occupy disk space until log data is fully written to host disks, which ensures DBMS reliability and fault tolerance.

For more details on how PostgreSQL works, refer to its official documentation.

What does mean in the RAM monitoring of a PostgreSQL cluster host?What does Cached mean in the RAM monitoring of a PostgreSQL cluster host?

Cached stands for cached data. Most operations are performed in RAM where the entire database working set is stored. Since caching occurs both for databases and the disk itself, the amount of cached data can exceed the storage size.

If application RAM consumption increases, some of the memory used for cache can be freed up.

For more details on disk data caching in Linux, refer to the relevant guides.

How do I get alerts on critical PostgreSQL cluster metrics?How do I get alerts on critical PostgreSQL cluster metrics?

Use Yandex Monitoring and set up alerts for the metrics critical for you.

How do I configure an alert for when disk space usage reaches a certain percentage?How do I configure an alert for when disk space usage reaches a certain percentage?

Create an alert for the disk.used_bytes metric in Yandex Monitoring. This metric shows the disk space usage in a Managed Service for PostgreSQL cluster.

For disk.used_bytes, use notification thresholds with the following recommended values:

• Alarm: 90% of disk space.
• Warning: 80% of disk space.

Thresholds are set in bytes only. For example, the recommended values for a 100 GB disk are as follows:

• Alarm: 96636764160 bytes (90%).
• Warning: 85899345920 bytes (80%).