Last time ended with log_min_duration_statement set to a threshold you would actually investigate. The cost of that threshold is that everything below it is invisible, and the queries below it are frequently the story: one query that runs for a minute is a problem you can see, but twenty million queries at a millisecond each are five and a half
Every other statement-logging parameter in this cluster decides whether a query goes into the log. These two decide whether your users’ data goes with it.
Under the extended query protocol the statement text arrives with $1, $2 placeholders and the values arrive separately, in the Bind message; those values are the bind parameters. When log_statement, log_min_duration_statement
Every message on its way to the server log passes three gates: whether it gets in at all, whether the SQL that provoked it rides along, and how many of its fields get printed. One parameter per gate. All three control only the log’s copy of a message; what the client sees is client_min_messages’s business, and the two streams
A log line has two parts: the message, which PostgreSQL decides, and everything in front of it, which you do. log_line_prefix is the join key. Every fact that lets you connect a line to a session, a transaction, a client, or a moment in another system’s logs has to be in the prefix, because the message itself doesn’t carry it.
These three decide when the logging collector closes one file and opens another, and they only make sense together with log_filename, because the collector never deletes anything: rotation is just “compute a name from the pattern and open it.” What happens next depends entirely on whether that name is new, and the three parameters here control the timing and
All three of these do nothing unless logging_collector is on; they describe the files the collector writes. Where, what they’re called, and who can read them. The defaults for all three are fine for a laptop and wrong for a server, and the reasons are more mechanical than the documentation lets on.
Unless you’ve asked for syslog, every message PostgreSQL logs is a write to standard error. That’s the whole mechanism, and the two dozen log_* parameters that follow this one are elaborations on it: what gets written, in what format, and what catches it on the other side. These two parameters are the “what catches it” part, so this post carries
Most of the log_* cluster is about what happened inside a session. These three are about the session itself: who showed up, when, how long it took to let them in, how long they stayed, and (if you insist) what their machine was called. The defaults log none of it. That is a defensible choice for a laptop and the
And now, we put on our waders and venture into the swamp that is all of the PostgreSQL logging GUCs.
PostgreSQL 8.3 is the release in which the server started doing its own housekeeping in earnest: autovacuum on by default, checkpoints spread out over the interval instead of dumped at the end of it, HOT updates. The same release
The question usually arrives as a security question: “Should I use bind parameters, or is it OK to build the SQL string myself?” The answer to that question is that you should use bind parameters, the string-building approach is how you get a SQL injection, and we can all move on with our lives.