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.
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 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.
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
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
- 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;
- 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
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.