Snowflake Postgres logging¶

Retrieving Postgres log data¶

All Postgres servers on Snowflake Postgres instances log locally to syslog. The log data is collected from there and sent to your account’s active event table.

To view the Postgres logs for a given instance, query the event table for the TIMESTAMP field and the MESSAGE portion of the VALUE field of the records with record_type = 'LOG' using the initial portion of the instance’s hostname, aka the instance ID (or cluster_id). For example, this will pull the last 10 minutes of log entries for instance with id oyrpb2cwtvbu5al5vtbyrsnkgy:

SELECT TIMESTAMP, VALUE:MESSAGE as log_line
FROM SNOWFLAKE.TELEMETRY.EVENTS
WHERE resource_attributes['snowflake.o11y.logtype'] = 'postgres-otelcol-vm-agent'
  AND resource_attributes['instance.id'] = 'oyrpb2cwtvbu5al5vtbyrsnkgy'
  AND record_type = 'LOG'
  AND TIMESTAMP > CURRENT_TIMESTAMP() - INTERVAL '10 MINUTES'
LIMIT 100;

Each row of the output will contain a single log-line entry that was logged by the Postgres server on the given Snowflake Postgres instance with the timestamp when it was originally logged. Note that it can take up to a few minutes between the time Postgres makes a log entry and it is available in the event table.

Understanding Postgres log-line interleaving¶

Note that Postgres uses multi-line logging and since multiple Postgres server processes will be making log entries concurrently, full log entries from different Postgres server processes will often be interleaved. For example, let’s consider these log line entries:

In each log line entry:

• The first bracketed values are the command number for the session that ran the command and the log line for that command separated by a hyphen. For example, [1-1] and [1-2] would be two log lines from the first command run in a session.

• The second bracketed value is the process ID (pid) for the session that logged the line. Postgres uses a process-based (vs. thread-based) concurrency model so each session is run on its own server process.

In this example, you can see that:

• Command 14 was run by the session with pid 1592908 as the cancellation of a select pg_sleep(10); query.

• Logging of command 14 by pid 1592908 added two log lines, [14-1] and [14-2].

• A single log line from the 10th command run by the session with pid 1593992 ended up between the two lines from command 14 on pid 1592908.

• The next command run by the session with pid 1592908 was a show log_min_duration_statement query and required only one log line, [15-1].