In this post, we will show you how to enable query logging for Azure Database for PostgreSQL by modifying the associated server parameters.
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 to enable logging
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).
- In Azure Portal, choose Azure databases for PostgreSQL DB instance that you want to enable query logging.
- Open the Server Parameters
- In the filter parameters field, update the following parameter values. here this example: set
log_statementtoDDLandlog_min_duration_statementto1000 - 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
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
Your feedback and questions are welcome! You can always reach out to our team of Postgres experts at Ask Azure DB for PostgreSQL.