How to enable query logging in Azure database for PostgreSQL?

Posted by

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

In this post, we will show you how to enable query logging for Azure Database for PostgreSQL by modifying the associated server parameters.

 

stmt-logging.png

 

Before we deep dive on this topic, let’s first review some important considerations involved with enabling query level logging.

Note: This blog post applies to both Single Server and Flexible Server.


Logging considerations

 

Logging every query will reduce the performance of the database server, especially if its workload consists of many simple queries. The extent of impact varies depending on the logging pipeline and its storage/IO characteristics. In addition, when logging all queries may require more database storage. If the storage is full, then the database will be unavailable.

 

For this reason, you will probably want to disable this once you have obtained the information you need.

 

Server parameters

log_statement

Log_Statement controls which type of queries are logged. The default value is None, and you can modify it following values:

 

 

DDL -  logs all data definition language (DDL) statements such as CREATE, ALTER, and DROP.
MOD - logs all DDL and data modification language (DML) statements such as INSERT, UPDATE, and DELETE.
ALL -  logs all queries

 

 

log_min_duration_statement

Causes each completed statement to be logged if the query ran for at the specified amount of time. The value specified is in milliseconds. For example, if you set log_min_duration_statement value to 1000, Azure will log all queries (regardless of type) that take longer than 1 second. Setting this to zero prints all statement durations. -1 (the default) disables logging statement durations.

 

When using this option together with log_statement, the text of query that are logged because of log_statement will not be duplicated in the duration log message.

Enable query logging - step-by-step

 

In this example we will configure the server to enable logging for all queries taking longer than 1 second (regardless of query type) and all DDL statements (regardless of query duration).

 

  1. In Azure Portal, choose Azure databases for PostgreSQL DB instance that you want to enable query logging.
  2. Open the Server Parameters
  3. In the filter parameters field, update the following parameter values. here this example: set log_statement to DDL and log_min_duration_statement to 1000
  4. Click Save.

Note: These parameters are dynamic and can be modified without server restart.

Now with the parameter changes being saved, let’s run some sample SQL statements and view them in logs.

 

 

-- Sample DDL statement
CREATE TABLE testanalyze (ID INT, name VARCHAR(250));
-- Sample long running query
SELECT GENERATE_SERIES(1,10000000) AS long_query;

 

 

 

Viewing logs

  • In Azure PostgreSQL Single Server, you can view/ download the logs from Server logs under Monitoring section in the sidebar.
  • In Azure PostgreSQL Flexible Server, logs are integrated with Azure Monitor and can be viewed using below Kusto Query (KQL)

 

 

AzureDiagnostics
| where Resource == "TEST-PG1"
| where Category == "PostgreSQLLogs"
| where TimeGenerated > ago(2h)
| where processId_d == "1307"
| project TimeGenerated, Message

 

 

enable_query_level_logging_updt.jpg

 

 

 

Summary

Azure Database for PostgreSQL logs provides useful information about database activity, that can help in performance tuning and troubleshooting. Please note that verbose logging tends to cause performance issues, especially if you log ALL statements or set log_min_duration_statement to 0. This impacts both the server performance and storage consumption. Aslo, Azure database for PostgreSQL has some other options to collect query stats including Query Store and pg_stat_statements extension.  

While a perfect logging configuration will be a matter of trial and error, what I have explained here is how you can log queries and query

If you have any feedback for us or questions on Postgres logs, drop us an email @AskAzureDBforPostgreSQL. Till then, go forth and log.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.