This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(243.2, 6%, 33%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHT%3C/text%3E%3C/svg%3E)
Hi there. We use CosmosDB for Postgresql.
PostgreSQL version: 15
Citus version: 11.1
Configuration: single-node
We want our SQL queries to be logged on server side. We configured CosmosDB to log server logs to log analytics:
it works, we can see some server logs in log explorer. In order to get SQL queries to logs, we set 'log_statement' setting to 'ALL'. It changed nothing, no new events in logs. Also tried to set 'log_min_messages' setting to 'INFO', still nothing. Can you please suggest what we should do here to have our SQL queries written to server logs? As per the documentation, log_statement should've been enough. Thank you
Heorhii Teriaiev
Thank you for reaching out. Did you turn this feature to ON?
Please check that the log_statement parameter is set to ALL in the PostgreSQL configuration file (postgresql.conf).
Are you using the citus extension.?
Regards,
Oury
@Oury Ba-MSFT I didn't back then. Now I turned it on, but it's still the same: no SQL statements in logs.
Are you using the citus extension.?
we're using Azure CosmosDB Postgresql, it includes Citus by default as far as I understand.
Please check that the
log_statementparameter is set toALLin the PostgreSQL configuration file (postgresql.conf)
How can I do this? It's a managed service so we do not have access to the server itself.
Heorhii Teriaiev Could you please send an email to azcommunity@microsoft.com and share your cluster name so we can take a closer look at your logs. Please mention in the subject Line ATTN: Oury Regards, Oury
@Oury Ba-MSFT done
Heorhii Teriaiev Sure, Thank you. I am looking into this. Regards, Oury
Heorhii Teriaiev Thank you for being patient while working on this issue. A fix was implemented on your clusters. You should now be able to see the logs. Please check and let me know the result. Sorry for the inconvenience this may have caused. Regards, Oury
Thank you! I can confirm it’s working now. Can you please explain what the fix was? Would it work if I create a new DB instance and the settings myself or would I need to reach out to you again?
One more question — is it possible configure the log format so that we can see name of a user who ran a query?
The issue was with the configuration on the agent that sends logs from the VM (mdsd). Newer instances should get the fixed configuration. As of last week, all regions have been updated with the fix.
Regards,
Oury
Thanks for your reply!
Can you please also suggest if we can configure the log format so that we can see name of a user who ran a query?
Cheers
To log the username executing the query you can modify the log_line_prefix parameter (see docs) %m [%p][%v] : %q[user=%u app=%a]
This parameter can only be set in the postgresql.conf file or on the server command line. The default is '%m [%p] ' which logs a time stamp and the process ID.
See also example in this blog.
log_line_prefix = %m user=%u db=%d pid=%p:
(Postgres default is %m [%p], time and process ID; Azure Postgres default is %t-%c-, time and session ID)
log_line_prefix tells Postgres to add some standard information to the beginning of each log line. The suggested value of %m user=%u db=%d pid=%p: would provide the timestamp, username, database name, and process id for each log. That way when you see a log you can quickly narrow down on who (user) and where (database). Logging the process or session ID allows you to correlate associated events.
Please don't forget to mark as accept answer if the reply was helpful.
Regards,
Oury
Heorhii Teriaiev Checking if the comment above answers your question.
Please let us know.
Regards,
Oury
hi @Oury Ba-MSFT . It worked. Thank you, we can close the ticket
@Heorhii Teriaiev If you think your question has been answered, click "Mark as Accept Answer", This can be beneficial to other community members reading this forum thread.
Regards
Geetha
@Heorhii Teriaiev Thanks for accepting the answer and Thank you for visiting the Microsoft QA forums! Have a great day!
Regards
Geetha