[FEATURE REQUEST] Implement log rotation for PGBouncer

[plirglo]

Is your feature request related to a problem? Please describe.
Configure pgbouncer to write logs in standard location searched by filebeat. Also configure log and log rotation to limit log size for pgbouncer.

Describe the solution you'd like
Please see how it's done for Postgres, search what needs to be logged and for how long logs should be stored.

Describe alternatives you've considered

Additional context
Same basic knowledge about log rotation and pgbouncer:
https://www.redhat.com/sysadmin/setting-logrotate
https://www.enterprisedb.com/postgres-tutorials/pgbouncer-logs-rotation-linux-and-windows-tutorial
https://www.postgresql.org/message-id/[email protected] (old, but might be useful)

[ghost]

I was able to deploy epiphany, with separated HA PostgreSQL machine. To enable pgbouncer log rotation, we need to add the following code into our main build YAML file:

---

kind: configuration/postgresql
title: PostgreSQL
name: default
specification:
config_file:
parameter_groups:
(...)
extensions:
pgaudit:
enabled: yes
(...)
pgbouncer:
enabled: yes
replication:
enabled: yes
(...)
repo.
config: |-
/var/log/postgresql/postgresql*.log {
(...)
}
/var/log/postgresql/pgbouncer.log {
missingok
notifempty
sharedscripts
copytruncate
size 10M
create 0640 postgres postgres
nodateext
postrotate
/bin/kill -HUP cat /run/postgresql/pgbouncer.pid 2>/dev/null 2> /dev/null || true
endscript
}

[ghost]

PGBouncer log rotation, and sending logs into ELK stack works properly on Ubuntu host.
Will check the RedHat deployment now.

[ghost]

Checked on Azure - logs can be rotated, and they're pushed into ELK. Kibana is able to access those logs.

[ar3ndt]

PR #1823

[to-bar]

Some suggestions after reverting the PR.

RHEL

1. We have rotation configured out of the box (provided by rpm package), the config file is: /etc/logrotate.d/pgbouncer. I think we can keep it as is without making it configurable.
2. The log file path is different than on Ubuntu, i.e /var/log/pgbouncer/pgbouncer.log. It's not handled by filebeat, so we need add it to filebeat.yml.j2 as it was in the PR.

Ubuntu
Option 1.
We could reuse RHEL's config file (provided by rpm package) but this would require:

• Change logfile in /etc/pgbouncer/pgbouncer.ini to use the same location as on RHEL.
• Change 'pidfile' in the same file.
• Create /var/log/pgbouncer directory if needed.
• Check whether pgbouncer user should be created (since it's present in the config file).

Option 2. (easier to implement)
Use RHEL's config file as base for separate (in Epiphany sources) adjusted to Ubuntu specific configuration.

Both
I don't think we need multi-line support in Filebeat but checked only on Ubuntu and haven't found any multi-line entries.

The logrotate config should not be concatenated to /etc/logrotate.d/postgresql but stored in separate file /etc/logrotate.d/pgbouncer.

[ar3ndt]

new PR created

[przemyslavic]

I found an issue with duplicate log entries for PgBouncer on Ubuntu. This is because in the filebeat configuration for PostgreSQL the path /var/log/postgresql/*.log* was specified first, and then /var/log/postgresql/pgbouncer.log* for PgBouncer, and the second path is included in the first, which leads to multiplication.

cat /etc/filebeat/filebeat.yml

# -------------------------- PostgreSQL input --------------------------

# Filebeat postgresql module doesn't support custom log_line_prefix (without patching), see https://discuss.elastic.co/t/filebeats-with-postgresql-module-custom-log-line-prefix/204457
# Dedicated configuration to handle log messages spanning multiple lines.

- type: log
  enabled: true
  paths:
    - /var/log/postgresql/*.log*
  exclude_files: [".gz$"]
  multiline:
    pattern: '^\d{4}-\d{2}-\d{2} '
    negate: True
    match: after

- type: log
  enabled: true
  paths:
    - /var/log/postgresql/pgbouncer.log*
  exclude_files: [".gz$"]

[to-bar]

The issue with duplicate log entries for PgBouncer on Ubuntu fixed in PR #1896.

[ar3ndt]

@przemyslavic Can we move this to test again?

[przemyslavic]

Fix tested.
✔️ Logs from PgBouncer are visible in Kibana.
✔️ Log rotation works as configured.
✔️ Filebeat configuration adjusted to avoid log duplication.