Description of problem:

As part of the Satellite 6.9 Pulp 3 High-Touch Beta, observed Postgres logging long duration queries regularly hit and writing a single line of 15MB.

During this HTB, a Satellite 6.9 instance went through the process of migration to Pulp 3, a switchover to Pulp 3 was performed and content management workflows executed.

Version-Release number of selected component (if applicable):
6.9.2

Behavior observed:

The default out-of-the-box config of postgres is:
log_min_duration_statement = 1000

This threshold is reached very regularly when running basic use cases like:

• RedHat Repos Sync
• ContentViews using content from the the RH Repos

The problem is that the queries are huge:

root@hostname:/var/opt/rh/rh-postgresql12/lib/pgsql/data# ls -lh log/postgresql-Mon.log
rw------. 1 postgres postgres 251M Jul 26 13:14 log/postgresql-Mon.log

root@hostname:/var/opt/rh/rh-postgresql12/lib/pgsql/data# cat log/postgresql-Mon.log | wc -l
8545

root@hostname:/var/opt/rh/rh-postgresql12/lib/pgsql/data# grep -E 'duration: \S+ ms' log/postgresql-Mon.log | wc -l
370

root@hostname:/var/opt/rh/rh-postgresql12/lib/pgsql/data# grep -E 'duration: \S+ ms' log/postgresql-Mon.log | wc -L
15197722
root@hostname:/var/opt/rh/rh-postgresql12/lib/pgsql/data#
On top comes teh Duplicate Key errors that write always 3 lines that can also be very long:

root@hostname:/var/opt/rh/rh-postgresql12/lib/pgsql/data# cat log/postgresql-Mon.log | grep -v 'duration: ' | wc -l
8164
root@hostname:/var/opt/rh/rh-postgresql12/lib/pgsql/data# cat log/postgresql-Mon.log | grep -v 'duration: ' | wc -L
5574975
root@hostname:/var/opt/rh/rh-postgresql12/lib/pgsql/data#

I have uploaded the used postgres log file used above to the Case 02972622 for analysis.

Maybe now with the increased use of postgres the configuration needs to be tuned better.

The postgresql.conf at my system is not touch since 2020-Nov and not changed in the 6.9.x upgrade or doing the pulp3 enabling. It is also available already the same case in the backup tar i uploaded earlier already.