[BUG] PostgreSQL generates a large amount of audit logs

[ldming]

Describe the bug

When I use a PostgreSQL cluster that created by kbcli, its audit log is on. Execute a large dump sql(11MB), the audit logs grew rapidly and eventually reached 14GB. And the SQL execution time is 800+ seconds.

root@apecloud-pg-postgresql-0:/home/postgres/pgdata/pgroot/data/log# ll -h
total 14G
drwx------  2 postgres postgres 4.0K Jun 18 07:03 ./
drwx------ 20 postgres postgres 4.0K Jun 18 14:13 ../
-rw-r--r--  1 postgres postgres  14G Jun 18 14:13 postgresql-2024-06-18.csv
-rw-r--r--  1 postgres postgres  412 Jun 18 07:04 postgresql-2024-06-18.log

The pgaudit parameters:

postgres=# show pgaudit.log;
  pgaudit.log   
----------------
 ddl,read,write
(1 row)

If I disable the audit logs, the logs will only grow by 11MB, and the sql execution time is 200s.

# disable the sql audit
kbcli cluster configure apecloud-pg --set pgaudit.log=none

# check the parameters
postgres=# show pgaudit.log;
  pgaudit.log   
----------------
 none
(1 row)

# execute sql and check the log size
root@apecloud-pg-postgresql-0:/home/postgres/pgdata/pgroot/data/log# ls -alh
total 11M
drwx------  2 postgres postgres 4.0K Jun 18 07:03 .
drwx------ 20 postgres postgres 4.0K Jun 18 13:55 ..
-rw-r--r--  1 postgres postgres  11M Jun 18 13:55 postgresql-2024-06-18.csv
-rw-r--r--  1 postgres postgres  412 Jun 18 07:04 postgresql-2024-06-18.log

To Reproduce Steps to reproduce the behavior:

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Expected behavior A clear and concise description of what you expected to happen.

Screenshots If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version [e.g. 22]

Additional context Add any other context about the problem here.

[github-actions[bot]]

This issue has been marked as stale because it has been open for 30 days with no activity

[shanshanying]

@ldming pls check settings, such as log level/log retention period