Score:0

Can you configure PostgreSQL to distinguish between client and server logs?

nz flag

Scenario: You are hosting PostgreSQL for others and want to offer a good service.

As someone who hosts the database, I want to see when Postgres crashes and other more admin-related information. However, the "ERROR" and "FATAL" markers both contain errors generated by client behavior.

  • "ERROR" is particularly polluted and cannot be taken into account for alerts.
  • "FATAL" is less polluted and contains important Postgres errors. (Like startup and memory issues)

Is there a way to configure Postgres to have the distinction between server and client logs?

Score:0
za flag

The server can be configured (per session) which messages are sent to a client. "Per session" means that any client can set up the variable in the beginning of the session to the appropriate value. The setting is client_min_messages:

Controls which message levels are sent to the client. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING, and ERROR. Each level includes all the levels that follow it. The later the level, the fewer messages are sent. The default is NOTICE. Note that LOG has a different rank here than in log_min_messages.

INFO level messages are always sent to the client.

So let the client do in the beginning of the session:

SET client_min_messages to 'DEBUG5';

and they will receive everything possible:

postgres=# select * from pg_views;
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  rehashing catalog cache id 7 for pg_attribute; 257 tups, 128 buckets
DEBUG:  rehashing catalog cache id 7 for pg_attribute; 513 tups, 256 buckets
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0

This is totally independent from what server logs which is configured with other settings.

I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.