How to read Postgres transaction log

Question

I will want to read all SQL query only committed transaction. I have Postgres 9.5 or can will update on PG 11. I need to get Transaction Log on SQL format

Answer 1

The transaction log (WAL) doesn't contain the SQL statements that ran, it contains the binary changes to the data files: things like “in block 42 of file 76183, change 24 bytes to something else”.

From PostgreSQL 9.6 on, there is logical decoding which enables a logical decoding plugin to parse this WAL information and “reverse engineer” logical information (like SQL statements) from the binary WAL information and the database metadata.

That is the direction you will have to take. Look at test_decoding and wal2json, maybe you'll find what you are looking for.

No luck with PostgreSQL 9.5 on that.

Answer 2

I don't understand your question but I know how to log all PostgreSQL queries including transaction queries such as BEGIN and COMMIT with PostgreSQL(Version 14).

To do that, you need to run either of the queries below then restart PostgreSQL to enable logging persistently. *The parameter with ALTER SYSTEM SET is set to postgresql.auto.conf rather than postgresql.conf:

ALTER SYSTEM SET log_statement = 'all';

Or:

ALTER SYSTEM SET log_min_duration_statement = 0;

Then, all queries including transaction queries such as BEGIN and COMMIT are logged as shown below. *My answer explains more about logging in PostgreSQL:

2022-08-20 22:09:12.549 JST [26756] LOG:  duration: 0.025 ms  statement: BEGIN
2022-08-20 22:09:12.550 JST [26756] LOG:  duration: 1.156 ms  statement: SELECT "store_person"."id", "store_person"."first_name", "store_person"."last_name" FROM "store_person" WHERE "store_person"."id" = 33 LIMIT 21
2022-08-20 22:09:12.552 JST [26756] LOG:  duration: 0.178 ms  statement: UPDATE "store_person" SET "first_name" = 'Bill', "last_name" = 'Gates' WHERE "store_person"."id" = 33
2022-08-20 22:09:12.554 JST [26756] LOG:  duration: 0.784 ms  statement: INSERT INTO "django_admin_log" ("action_time", "user_id", "content_type_id", "object_id", "object_repr", "action_flag", "change_message") VALUES ('2022-08-20T13:09:12.553273+00:00'::timestamptz, 1, 20, '33', 'Bill Gates', 2, '[]') RETURNING "django_admin_log"."id"
2022-08-20 22:09:12.557 JST [26756] LOG:  duration: 1.799 ms  statement: COMMIT