CODEWITHSUNDEEP
postgresql
user1471465
user1471465

Reputation: 347

How to see the actual sql statements executed by POSTGRES?

I want to log the actual sql statements executed against a POSTGRES instance. I am aware that I can enable logging of the sql statements. Unfortunately, this doesn't log the actual sql, but rather a parsed version, with certain parameters stripped out and listed separately.

Is there a tool for reliably reconstituting this output into executable sql statements?

Or is there a way of intercepting the sql that is send to the postgres instance, such that that sql can be logged?

We want to be able to replay these sql statements against another database.

Thanks for your help!

Upvotes: 1

Views: 2184

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324325

Actually, PostgreSQL does log exactly the SQL that got executed. It doesn't strip parameters out. Rather, it doesn't interpolate them in, it logs what the application sent, with bind parameters separate. If your app sends insert into x(a,b) values ($1, $2) with bind params 42 and 18, that's what gets logged.

There's no logging option to interpolate bind parameters into the query string.

Your last line is the key part. You don't want logging at all. You're trying to do statement based replication via the logs. This won't work well, if at all, due to volatile functions, the search_path, per-user settings, sequence allocation order/gap issues, and more. If you want replication don't try to do it by log parsing.

If you want to attempt statement-based replication look into PgPool-II. It has a limited ability to do so, with caveats aplenty.

Upvotes: 1

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4572

Via setting log_statement to all on postgresql.conf. See the documentation for runtime-config-logging

Upvotes: 1

Related Questions