Manually trace executed statements in YSQL
To trace executed statements in a session, you can use session identifiers. Session identifiers can be used to filter PostgreSQL log files for statements executed in a specific session and are unique in a YB-TServer node. A session identifier is a combination of process start time and PostgreSQL process ID (PID) and is output to the logs in hexadecimal format.
Note that in a YugabyteDB cluster with multiple nodes, session identifier is not guaranteed to be unique; both process start time and the PostgreSQL PID can be the same across different nodes. Be sure to connect to the node where the statements were executed.
Set logging options
To log the appropriate session information, you need to set the following configuration flags for your YB-TServers:
- Set ysql_log_statement YB-TServer configuration flag to all to turn on statement logging in the PostgreSQL logs.
- Set the
log_line_prefix
PostgreSQL logging option to log timestamp, PostgreSQL PID, and session identifier. Refer to PostgreSQL server options.
Review logs
Session information is written to the PostgreSQL logs, located in the YugabyteDB base folder in the yb-data/tserver/logs
directory. For information on inspecting logs, refer to Inspect YugabyteDB logs.
Example session
Create a local cluster and configure ysql_log_statement
to log all statements, and log_line_prefix
to log timestamp, PostgreSQL PID, and session identifier as follows:
./bin/yb-ctl create --tserver_flags="ysql_log_statement=all,ysql_pg_conf_csv=\"log_line_prefix='timestamp: %m, pid: %p session: %c '\"" --rf 1
For local clusters created using yb-ctl, postgresql
logs are located in ~/yugabyte-data/node-1/disk-1/yb-data/tserver/logs
.
Connect to the cluster using ysqlsh as follows:
./bin/ysqlsh
ysqlsh (11.2-YB-2.15.2.1-b0)
Type "help" for help.
yugabyte=#
Trace statement execution
Execute the following commands:
yugabyte=# CREATE TABLE my_table ( h int, r int, v int, primary key(h,r));
CREATE TABLE
yugabyte=# INSERT INTO my_table VALUES (1, 1, 1);
INSERT 0 1
Your PostgreSQL log should include output similar to the following:
timestamp: 2022-10-24 16:49:42.825 UTC --pid: 1930 session: 6356c208.78a LOG: statement: CREATE TABLE my_table ( h int, r int, v int, primary key(h,r));
timestamp: 2022-10-24 16:51:01.258 UTC --pid: 1930 session: 6356c208.78a LOG: statement: INSERT INTO my_table VALUES (1, 1, 1);
Trace an explicit transaction
Start an explicit transaction as follows:
yugabyte=# BEGIN;
BEGIN
yugabyte=# INSERT INTO my_table VALUES (2,2,2);
INSERT 0 1
yugabyte=# DELETE FROM my_table WHERE h = 1;
DELETE 1
yugabyte=# COMMIT;
COMMIT
Your PostgreSQL log should include output similar to the following:
timestamp: 2022-10-24 16:56:56.269 UTC --pid: 1930 session: 6356c208.78a LOG: statement: BEGIN;
timestamp: 2022-10-24 16:57:05.410 UTC --pid: 1930 session: 6356c208.78a LOG: statement: INSERT INTO my_table VALUES (2,2,2);
timestamp: 2022-10-24 16:57:25.015 UTC --pid: 1930 session: 6356c208.78a LOG: statement: DELETE FROM my_table WHERE h = 1;
timestamp: 2022-10-24 16:57:27.595 UTC --pid: 1930 session: 6356c208.78a LOG: statement: COMMIT;
Trace concurrent transactions
Start two sessions and execute transactions concurrently as follows:
Client 1 | Client 2 |
---|---|
|
|
|
|
|
Your PostgreSQL log should include output similar to the following:
timestamp: 2022-10-24 17:04:09.007 UTC --pid: 1930 session: 6356c208.78a LOG: statement: BEGIN;
timestamp: 2022-10-24 17:05:10.647 UTC --pid: 1930 session: 6356c208.78a LOG: statement: INSERT INTO my_table VALUES (5,2,2);
timestamp: 2022-10-24 17:05:15.042 UTC --pid: 2343 session: 6356c4a4.927 LOG: statement: BEGIN;
timestamp: 2022-10-24 17:05:19.227 UTC --pid: 2343 session: 6356c4a4.927 LOG: statement: INSERT INTO my_table VALUES (6,2,2);
timestamp: 2022-10-24 17:05:22.288 UTC --pid: 2343 session: 6356c4a4.927 LOG: statement: COMMIT;
timestamp: 2022-10-24 17:05:25.404 UTC --pid: 1930 session: 6356c208.78a LOG: statement: COMMIT;
Next steps
Use pg_audit
to enable logging for specific databases, tables, or specific sets of operations. See Configure audit logging in YSQL.