Enable and download server slow query logs of an Azure Database for PostgreSQL server using Azure CLI

This sample CLI script enables and downloads the slow query logs of a single Azure Database for PostgreSQL server.

Use Azure Cloud Shell

Azure hosts Azure Cloud Shell, an interactive shell environment that you can use through your browser. You can use either Bash or PowerShell with Cloud Shell to work with Azure services. You can use the Cloud Shell preinstalled commands to run the code in this article without having to install anything on your local environment.

To start Azure Cloud Shell:

Option Example/Link
Select Try It in the upper-right corner of a code block. Selecting Try It doesn't automatically copy the code to Cloud Shell. Example of Try It for Azure Cloud Shell
Go to https://shell.azure.com, or select the Launch Cloud Shell button to open Cloud Shell in your browser. Launch Cloud Shell in a new window
Select the Cloud Shell button on the menu bar at the upper right in the Azure portal. Cloud Shell button in the Azure portal

To run the code in this article in Azure Cloud Shell:

  1. Start Cloud Shell.

  2. Select the Copy button on a code block to copy the code.

  3. Paste the code into the Cloud Shell session by selecting Ctrl+Shift+V on Windows and Linux or by selecting Cmd+Shift+V on macOS.

  4. Select Enter to run the code.

If you choose to run the CLI locally, this article requires Azure CLI version 2.0 or later. Check the version by running az --version. See Install Azure CLI to install or upgrade your version of Azure CLI.

Sample script

In this sample script, edit the highlighted lines to update the admin username and password to your own. Replace the <log_file_name> in the az monitor commands with your own server log file name.

#!/bin/bash

# Create a resource group
az group create \
--name myresourcegroup  \
--location westus

# Create a PostgreSQL server in the resource group
# Name of a server maps to DNS name and is thus required to be globally unique in Azure
# Substitute the <server_admin_password> with your own value
az postgres server create \
--name mydemoserver \
--resource-group myresourcegroup \
--location westus \
--admin-user myadmin \
--admin-password <server_admin_password> \
--sku-name GP_Gen4_2 \

# List the configuration options for review
az postgres server configuration list \
--resource-group myresourcegroup  \
--server mydemoserver

# Turn on statement level log
az postgres server configuration set \
--name log_statement \
--resource-group myresourcegroup \
--server mydemoserver \
--value all

# Set log_min_duration_statement time to 10 sec
az postgres server configuration set \
--name log_min_duration_statement \
--resource-group myresourcegroup \
--server mydemoserver \
--value 10000

# List the available log files and direct to a text file
az postgres server-logs list \
--resource-group myresourcegroup \
--server mydemoserver > log_files_list.txt

# Download log file from Azure 
# Review log_files_list.txt to find the server log file name for the desired timeframe
# Substitute the <log_file_name> with your server log file name
# Creates the postgresql-<date>_000000.log file in the current command line path
az postgres server-logs download \
--name <log_file_name> \
--resource-group myresourcegroup \
--server mydemoserver

Clean up deployment

Use the following command to remove the resource group and all resources associated with it after the script has been run.

#!/bin/bash
az group delete --name myresourcegroup

Script explanation

This script uses the commands outlined in the following table:

Command Notes
az group create Creates a resource group in which all resources are stored.
az postgres server create Creates a PostgreSQL server that hosts the databases.
az postgres server configuration list List the configuration values for a server.
az postgres server configuration set Update the configuration of a server.
az postgres server-logs list List log files for a server.
az postgres server-logs download Download log files.
az group delete Deletes a resource group including all nested resources.

Next steps