Dear customer,

We provide a cron job and a systemd timer and service, which might help you 
implementing an automatism for pgbadger on your local systems. 

Security considerations:
 a) Running pgbadger as root might be simple, but is in no way secure.
    We do NOT recommend to do this. 

 b) Running pgbadger as user postgres is possible, but - depending on 
    your local security guideline - also not allowed. 

 c) Running pgbadger as dedicated user is the most secure and 
    recommended way of operation. This requires some adjustments on 
    your postgresql.conf and at least a restart of your database 
    service. 
    We will follow this solution in our documentation below. 
    Please note, however, that to make use of such a setting, you'll 
    need to alter the 'log_directory' to store the log files somewhere 
    outside the cluster data directory. 
    In any case, it's unwise to make the log files world-readable, 
    since they might contain sensitive data.


= Prerequisite

1. Think about a (new) user that executes the script.

   We will create a new user called 'pgbadger', who will later be used 
   to execute the script:

     /usr/sbin/useradd  -r -g postgres -M -d /srv/www/htdocs/pgbadger/ -s /bin/false -c "User for pgbadger" pgbadger


2. Prepare a local directory, that can be used to store the generated 
   reports of pgbadger.

   We suggest to use /srv/www/htdocs/pgbadger/ here in the examples. 
   This directory is below the usual WEBROOT of any webserver. which 
   would allow to view the files directly in a browser. 

   A command to create such a directory therefor looks like:

     install -o pgbadger -g wwwrun -m 0750 -d /srv/www/htdocs/pgbadger/

3. Create a new directory to store postgresql logs.

   Think about a secure directory for storing your log files and create
   this directory via:

     install -o postgres -g postgres -m 0700 -d /var/log/pgsql/

4. Adjust the configuration of your postgresql server.

   You need the following settings in a standard postgresql-server installation 
   in /var/lib/pgsql/data/postgresql.conf for this:

    log_directory = '/var/log/pgsql'
    log_filename = 'postgresql-%Y-%m-%d.log'
    log_file_mode = 0640
    log_rotation_age = 1d 
    log_rotation_size = 0
 
  Note: these changes require a restart of your postgresql server. If you 
        have SELinux or Apparmor enabled, please adjust the configuration 
        before you restart the service. 
        Same applies obviously to any service, which connects to your 
        database. 

   Once all preparations are done, execute:

      rcpostgresql restart


As result, your postgresql server should log now into this new directory. 
The files in this directory should belong to the user 'postgres', 
group 'postgres' with file permissions 0640:

-rw-r----- 1 postgres postgres 997 Jan  1 05:58 /var/log/pgsql/postgresql-2024-01-31.log

Now it's time to work on the automation.


= Using a cron job

For historical reasons, we provide a small cron script for older distrubutions 
in the following
folder, that you can adjust to your needs:

/etc/cron.d/


= Using systemd timer

This is the recommended way.

Please adjust the following systemd timer and unit (see comments inline) to 
your needs:

     systemctl edit pgbadger.timer
     systemctl edit pgbadger.service

After that, please enable the systemd timer with the following two 
commands:

     systemctl daemon-reload
     systemctl enable pgbadger.timer


----
 And remember to have a lot of fun!
  Your SUSE Team.