Stop logging INSERT INTO statements in PostgreSQL (but keep SELECT)

Question

Using logging_collector = on and log_statement = 'all' (from postgresql.conf), currently my log files are correctly populated with every single query that has been executed.

This works great. Except... I don't want to log the INSERT INTO queries, because in this case, such queries are very big, and very frequent. They tend to 1. pollute all the logs and 2. take an enormous amount of space on the disk.

Among the different options for log_statement, which are none, ddl, mod and all, I can't see anything that would log all statement except data-modifying ones such as INSERT INTO.

More concretely, here are some of the things I need to log, or not to log:

statement                                          desired action
--------------------------------------------------------------------
INSERT INTO ...                                    DO NOT LOG
UPDATE ...                                         DO NOT LOG
SELECT                                             LOG
BEGIN; declare cursor with hold for SELECT ...     LOG
EXPLAIN SELECT                                     whatever
CREATE TABLE                                       whatever (preferably don't log)

Is there a way to achieve something that resembles this?

If there is no way to do it through settings, I'll have to resort to some ugly workaround such as running a cron job every 10 minutes which goes into each log file and deletes everything that resembles an INSERT INTO statement... Would be nice if PostgreSQL could avoid me that.

Answer 1

There is no built-in way to log DML statements selectively.

The pgAudit extension does probably what you want, through its pgaudit.log configuration setting:

Specifies which classes of statements will be logged by session audit logging. Possible values are:

READ: SELECT and COPY when the source is a relation or a query.

WRITE: INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.

FUNCTION: Function calls and DO blocks.

ROLE: Statements related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.

DDL: All DDL that is not included in the ROLE class.

MISC: Miscellaneous commands, e.g. DISCARD, FETCH, CHECKPOINT, VACUUM, SET.

MISC_SET: Miscellaneous SET commands, e.g. SET ROLE.

ALL: Include all of the above.