logging custom messages in azure postgres

Question

I my stored procedures, I have inserted custom raise messages like this:

raise warning 'proc executed for schema %',schema_name

I want to be able to view these logs as I execute these procedures, but, I wonder how to go about doing this. I did find the this on microsoft's page, and I have enabled diagnostics and sent to the log analytics workspace: , but, the logs after I run the query, output nothing:

AzureDiagnostics
| where Resource == "myprivateserver"
| where Category == "PostgreSQLLogs"

I wonder if I'm doing it correctly or whether custom log messages as above are also logged or not.

On the left pane, I get this table containing many different things:

Did you try running AzureDiagnostics table? and checked if you got any output? Also, Can you directly check logs from PostgreSQL left pane > Logs? and then query AzureDiagnostics table? — SiddheshDesai, Mar 15 '23 at 09:12
@SiddheshDesai , I've updated my screenshot, and there are some tables that I don't understand on the left pane, could you give me a bit more designation? — mleko, Mar 15 '23 at 10:43

score 1 · Accepted Answer · edited May 19 '23 at 10:11

I created stored procedure in my Azure PostgreSQL with a warning like below:-

CREATE OR REPLACE PROCEDURE my_proc(schema_name TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE WARNING 'my_proc executed for schema %', schema_name;
  -- add your procedure logic here
END;
$$;

CALL my_proc('my_schema');

enter image description here

Sent the data to Log Analytics workspace like below:-

enter image description here

I checked the logs in Azure Postgresql > Left pane > logs section and ran this query but no logs on Stored procedure were present as default Diagnostic logs deals with the Azure PostgreSQL connections and error metrics in authenticating to the Azure Postgre SQL:-

AzureDiagnostics
| where Category == "PostgreSQLLogs"
| where Message contains "my_proc executed for schema"

No output:-

enter image description here

I tried to inspect AzureDiagnostics table to fetch the Stored Procedure data but did not find any, You can also inspect each and every column from the table in left to right. Refer below:-

enter image description here

As stored procedure falls under audit logs, You need to enable Azure PostgreSQL Pgaudit extension to view the audit logs like below:-

enter image description here

Run this command to check if Pgaudit extension is loaded from shared library in your server like below:-

show shared_preload_libraries;

CREATE EXTENSION pgaudit;

enter image description here

Now, Go to server parameters> Search for pgaudit and add WRITE permission to pgaudit.log

enter image description here

Now, I went to my Log analytics workspace where my diagnostics logs are sent and ran the below query to get the Procedure log and received the Warning message in the table output refer below:-

AzureDiagnostics
| where Category == "PostgreSQLLogs"
| where Message contains "my_proc"

enter image description here

Note- For the warning to reflect wait for some time after the pg audit extension is enabled and the warning is raised again.

As per your comment:- In order to understand all the columns designation of the table, Export the query as a CSV(all columns) and inspect the Excel csv sheet like below:-

https://i.stack.imgur.com/BDzxQ.png

https://i.stack.imgur.com/1T6Nz.png https://i.stack.imgur.com/RBLNU.png

Reference:- Audit logging - Azure Database for PostgreSQL - Flexible server | Microsoft Learn

logging custom messages in azure postgres

1 Answers1