Patroni logs with grafana-agent causing a lot of unrelease file and huge syslog

[honghan-wong]

Steps to reproduce

1. install postgresql charm 14/edge, revision 425
2. integrate with COS
3. grafana-agent will keep track of all the logs files under /var/snap/charmed-postgresql/common/var/log/patroni, which causing syslog become several GB huge, spamming with logs like below:

Jun 30 00:00:03 landscapesql-new-1 grafana-agent.grafana-agent[416696]: ts=2024-06-30T00:00:03.940798064Z caller=filetargetmanager.go:181 level=info component=logs logs_config=log_file_scraper msg="received file watcher event" name=/snap/grafana-agent/16/shared-logs/patroni/patroni.log.6545 op=CREATE

4. running lsof -nP | grep '(deleted)' on the postgresql with grafana-agent, we can see if the patroni logs are getting rotated, grafana-agent couldn't handle it correctly and causing a lot of unrelease files.

agent     1740025 1740154 agent                root *510r      REG              252,2         488     568380 /snap/grafana-agent/16/shared-logs/patroni/patroni.log.10080 (deleted)
agent     1740025 1740154 agent                root *511r      REG              252,2         488     561238 /snap/grafana-agent/16/shared-logs/patroni/patroni.log.10080 (deleted)

Expected behavior

1. A smaller number of logs files, either
   a. set by default in /var/snap/charmed-postgresql/115/etc/patroni/patroni.yaml
   b. have an option in charm to configure the log.file_num
2. Logs files are cleaned correctly

Actual behavior

You may find the logs in the summary above.

Versions

Operating system:
Ubuntu 22.04.4 LTS

Juju CLI:
3.1.8-genericlinux-amd64

Juju agent:
3.1.8

Charm revision: 425

LXD: 4.0.9

Log output

Juju debug log:

Additional context

[github-actions]

https://warthogs.atlassian.net/browse/DPE-4823

[taurus-forever]

Invited COS team into the fix implementation/coordination: canonical/grafana-agent-operator#153