b03df37ae5
Systemd unit files installed during the %install phase and other adjustments. OBS-URL: https://build.opensuse.org/request/show/1153509 OBS-URL: https://build.opensuse.org/package/show/server:database:postgresql/pgbadger?expand=0&rev=7
113 lines
3.5 KiB
Plaintext
113 lines
3.5 KiB
Plaintext
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.
|