forked from pool/pgbadger
Accepting request 1130189 from home:lrupp:branches:server:database:postgresql
Move the cron and systemd files out into the documentation directory and
include an extended README.SUSE to show how to work with them.
- update to 12.3
This is a maintenance release of pgBadger that fixes issues reported by
users since last release. It also adds some new features:
* Add option --include-pid to only report events related to a session
pid (%p). Can be used multiple time. Thanks to Henrietta Dombrovskaya
for the feature request.
* Add option --include-session to only report events related to the
session id (%c). Can be used multiple time. Thanks to Henrietta Dombrovskaya
for the feature request.
* Add option --dump-raw-csv to only parse the log and dump the information
into CSV format. No further processing is done, no report is generated.
Thanks to Henrietta Dombrovskaya for the feature request.
Here is the complete list of changes and acknowledgments:
* Update pgFormatter to version 5.5
* Fix end date of parsing with jsonlog format. Thanks to jw1u1 for the report.
* Fix typo in "Sessions per application". Thanks to fairyfar for the patch.
* Fix "INSERT/UPDATE/DELETE Traffic" chart bug. Thanks to fairyfar for the
patch.
* Fix parsing of orphan lines with bind queries. Thanks to youxq for the
report.
* Fix Analyze per table report with new PG versions. Thanks to Jean-Christophe
Arnu for the patch.
* Fix syslog entry parser when the syslog timestamp contains milliseconds.
Thanks to Pavel Rabel for the report.
- add systemd timer examples: beside the existing cron job example, this
allows people to get an idea how to automate the generation of reports
- introduce local cron macro: only recommend cron on older distributions
and leave the cron example file where it was historically (but place it
into the examples folder on newer distributions).
- get rid of old suse_version 11.3 check
OBS-URL: https://build.opensuse.org/request/show/1130189
OBS-URL: https://build.opensuse.org/package/show/server:database:postgresql/pgbadger?expand=0&rev=4
This commit is contained in:
Emiliano Langella
Git OBS Bridge
parent
5e76415513
commit
28423a5bac
119
README.SUSE
Normal file
119
README.SUSE
Normal file
@ -0,0 +1,119 @@
|
||||
Dear customer,
|
||||
|
||||
we provide two examples in this directory, 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 in the following
|
||||
folder, that can be adjusted to your needs:
|
||||
/usr/share/doc/packages/pgbadger/example/pgbadger-cron.sh
|
||||
|
||||
Just place the file into the /etc/cron.d/ directory and adjust it to your needs:
|
||||
cp -v /usr/share/doc/packages/pgbadger/example/pgbadger-cron.sh /etc/cron.d/pgbadger
|
||||
|
||||
= Using systemd timer
|
||||
|
||||
This is the recommended way.
|
||||
|
||||
Copy the needed files to your /etc/systemd/system directory:
|
||||
|
||||
cp -v /usr/share/doc/packages/pgbadger/example/pgbadger.timer /etc/systemd/system/
|
||||
cp -v /usr/share/doc/packages/pgbadger/example/pgbadger.service /etc/systemd/system/
|
||||
|
||||
Once done, please adjust the files (see comments inline) to your needs.
|
||||
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.
|
||||
|
||||
|
||||
|
||||
|
||||
|
||||
|
||||
@ -1,3 +0,0 @@
|
||||
version https://git-lfs.github.com/spec/v1
|
||||
oid sha256:86677cb11d0fbcd80ed984c253318cf4b5f2e9ae11211c4b40606cf4536fb4b3
|
||||
size 3044596
|
||||
3
pgbadger-12.3.tar.gz
Normal file
3
pgbadger-12.3.tar.gz
Normal file
@ -0,0 +1,3 @@
|
||||
version https://git-lfs.github.com/spec/v1
|
||||
oid sha256:c3d5a583d12b09f7b47e628760ccb7409362c54b5ca574de4cfd3ccf51c35106
|
||||
size 3047907
|
||||
@ -1,22 +1,30 @@
|
||||
|
||||
# The following example will auto-generate an incremental report from a
|
||||
# postgresql log file, which is rotated on a daily base, every day at 04:00.
|
||||
#
|
||||
# You need the following settings in a standard postgresql-server installation
|
||||
# in /var/lib/pgsql/data/postgresql.conf for this:
|
||||
# The following example will auto-generate an incremental report from a
|
||||
# postgresql log file, which is rotated on a daily base,
|
||||
# every day at 04:00.
|
||||
#
|
||||
# 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
|
||||
#
|
||||
# Once you created a new user pgbadger, you can place the script below
|
||||
# /etc/cron.d/
|
||||
# Please have a look at /usr/share/doc/packages/pgbadger/README.SUSE
|
||||
# for setup details.
|
||||
#
|
||||
# The output will be placed below /srv/www/htdocs/pgbadger/
|
||||
#
|
||||
# See https://github.com/darold/pgbadger/ for more details
|
||||
#
|
||||
#-0 4 * * * /usr/bin/pgbadger -I -q /var/lib/pgsql/data/pg_log/postgresql-$(date "+%Y-%m-%d" -d "yesterday").log -O /srv/www/htdocs/pgbadger/
|
||||
#-0 4 * * * pgbadger /usr/bin/pgbadger -I -q /var/log/pgsql/postgresql-$(date "+%Y-%m-%d" -d "yesterday").log -O /srv/www/htdocs/pgbadger/
|
||||
|
||||
|
||||
#
|
||||
# The following example will generate a report every week using incremental behavior
|
||||
#
|
||||
#-0 4 * * 1 /usr/bin/pgbadger -q `find /var/lib/pgsql/data/ -mtime -7 -name "postgresql.log*"` -o /srv/www/htdocs/pgbadger/reports/pg_errors-`date +\%F`.html -l /srv/www/htdocs/pgbadger/reports/pgbadger_incremental_file.dat
|
||||
#-0 4 * * 1 pgbadger /usr/bin/pgbadger -q `find /var/log/pgsql/ -mtime -7 -name "postgresql.log*"` -o /srv/www/htdocs/pgbadger/reports/pg_errors-`date +\%F`.html -l /srv/www/htdocs/pgbadger/reports/pgbadger_incremental_file.dat
|
||||
|
||||
|
||||
@ -1,3 +1,38 @@
|
||||
-------------------------------------------------------------------
|
||||
Mon Nov 27 11:48:16 UTC 2023 - lars@linux-schulserver.de - 12.3
|
||||
|
||||
- update to 12.3
|
||||
This is a maintenance release of pgBadger that fixes issues reported by
|
||||
users since last release. It also adds some new features:
|
||||
* Add option --include-pid to only report events related to a session
|
||||
pid (%p). Can be used multiple time. Thanks to Henrietta Dombrovskaya
|
||||
for the feature request.
|
||||
* Add option --include-session to only report events related to the
|
||||
session id (%c). Can be used multiple time. Thanks to Henrietta Dombrovskaya
|
||||
for the feature request.
|
||||
* Add option --dump-raw-csv to only parse the log and dump the information
|
||||
into CSV format. No further processing is done, no report is generated.
|
||||
Thanks to Henrietta Dombrovskaya for the feature request.
|
||||
Here is the complete list of changes and acknowledgments:
|
||||
* Update pgFormatter to version 5.5
|
||||
* Fix end date of parsing with jsonlog format. Thanks to jw1u1 for the report.
|
||||
* Fix typo in "Sessions per application". Thanks to fairyfar for the patch.
|
||||
* Fix "INSERT/UPDATE/DELETE Traffic" chart bug. Thanks to fairyfar for the
|
||||
patch.
|
||||
* Fix parsing of orphan lines with bind queries. Thanks to youxq for the
|
||||
report.
|
||||
* Fix Analyze per table report with new PG versions. Thanks to Jean-Christophe
|
||||
Arnu for the patch.
|
||||
* Fix syslog entry parser when the syslog timestamp contains milliseconds.
|
||||
Thanks to Pavel Rabel for the report.
|
||||
- add systemd timer examples: beside the existing cron job example, this
|
||||
allows people to get an idea how to automate the generation of reports
|
||||
- introduce local cron macro: only recommend cron on older distributions
|
||||
and leave the cron example file where it was historically (but place it
|
||||
into the examples folder on newer distributions).
|
||||
- get rid of old suse_version 11.3 check
|
||||
- Package rename: provide and obsolete the old PgBadger package
|
||||
|
||||
-------------------------------------------------------------------
|
||||
Fri Nov 17 15:34:14 UTC 2023 - Emiliano Langella <emiliano.langella@suse.com>
|
||||
|
||||
|
||||
38
pgbadger.service
Normal file
38
pgbadger.service
Normal file
@ -0,0 +1,38 @@
|
||||
#
|
||||
# The following two examples will auto-generate an report from your
|
||||
# postgresql log files.
|
||||
#
|
||||
# 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
|
||||
#
|
||||
# The output will be placed below /srv/www/htdocs/pgbadger/
|
||||
#
|
||||
# See https://github.com/darold/pgbadger/ for more details
|
||||
#
|
||||
# Note 1: Only ONE ExecStart line should be active at a time.
|
||||
# If you uncomment more than one, the last line 'wins'.
|
||||
# Note 2: As the 2nd example is expected to be run once a week, the
|
||||
# corresponding pgbadger.timer needs to be adjusted. Please use
|
||||
# systemctl edit pgbadger.timer
|
||||
# to do this (have a look at the comments there).
|
||||
#
|
||||
|
||||
[Unit]
|
||||
Description=Auto-Generate an incremental report from a postgresql logfile
|
||||
|
||||
[Service]
|
||||
Type=oneshot
|
||||
User=pgbadger
|
||||
Group=postgres
|
||||
## 1. The following example will generate an incremental report from a
|
||||
## log file, which is rotated on a daily base, every day at 04:00.
|
||||
# ExecStart=/usr/bin/pgbadger -I -q /var/log/pgsql/postgresql-$(date "+%Y-%m-%d" -d "yesterday").log -O /srv/www/htdocs/pgbadger
|
||||
## 2. The following example will generate a report every week
|
||||
## using incremental behavior
|
||||
#ExecStart=/usr/bin/pgbadger -q `find /var/log/pgsql/ -mtime -7 -name "postgresql.log*"` -o /srv/www/htdocs/pgbadger/reports/pg_errors-`date +\%F`.html -l /srv/www/htdocs/pgbadger/reports/pgbadger_incremental_file.dat
|
||||
|
||||
@ -21,8 +21,14 @@
|
||||
%bcond_with jsonxs
|
||||
%endif
|
||||
|
||||
%if 0%{?suse_version} > 01510
|
||||
%bcond_without cron
|
||||
%else
|
||||
%bcond_with cron
|
||||
%endif
|
||||
|
||||
Name: pgbadger
|
||||
Version: 12.2
|
||||
Version: 12.3
|
||||
Release: 0
|
||||
License: MIT
|
||||
Summary: A fast PostgreSQL log analyzer
|
||||
@ -31,6 +37,9 @@ Group: System/Monitoring
|
||||
Source0: https://github.com/darold/%{name}/archive/refs/tags/v%{version}.tar.gz#/%{name}-%{version}.tar.gz
|
||||
Source1: %{name}-cron
|
||||
Source2: %{name}-rpmlintrc
|
||||
Source3: %{name}.timer
|
||||
Source4: %{name}.service
|
||||
Source5: README.SUSE
|
||||
BuildRequires: cron
|
||||
BuildRequires: perl
|
||||
BuildRequires: perl(Getopt::Long)
|
||||
@ -59,7 +68,13 @@ Requires: perl(Encode)
|
||||
Requires: perl(Text::Wrap)
|
||||
Requires: perl(Time::Local)
|
||||
Requires: perl = %{perl_version}
|
||||
%if %{with cron}
|
||||
Recommends: cron
|
||||
%endif
|
||||
# handle package rename at 2023-11-27:
|
||||
Obsoletes: PgBadger < %{version}
|
||||
Provides: PgBadger = %{version}-%{release}
|
||||
#
|
||||
BuildRoot: %{_tmppath}/%{name}-%{version}-build
|
||||
BuildArch: noarch
|
||||
|
||||
@ -97,26 +112,35 @@ make test
|
||||
%perl_make_install
|
||||
%perl_process_packlist
|
||||
rmdir %{buildroot}%{perl_vendorarch} || :
|
||||
# prepare docdir and install examples and documentation files
|
||||
mkdir -p %{buildroot}%{_defaultdocdir}/%{name}/examples/
|
||||
mv tools %{buildroot}%{_defaultdocdir}/%{name}/
|
||||
mv ChangeLog README* %{buildroot}%{_defaultdocdir}/%{name}/
|
||||
%if %{with cron}
|
||||
# keep the old behavior for now and install the cron job example into the cron directory:
|
||||
install -Dm 0644 %{SOURCE1} %{buildroot}%{_sysconfdir}/cron.d/%{name}
|
||||
%if 0%{?suse_version} >= 01130
|
||||
%perl_gen_filelist
|
||||
%endif
|
||||
|
||||
%if 0%{?suse_version} >= 01130
|
||||
%files -f %{name}.files
|
||||
%defattr(-, root, root, -)
|
||||
%doc ChangeLog README* tools
|
||||
%license LICENSE
|
||||
%else
|
||||
%files
|
||||
%defattr(-, root, root, -)
|
||||
%doc ChangeLog README* LICENSE tools
|
||||
%{_bindir}/pgbadger
|
||||
%dir %{perl_vendorarch}/auto/pgBadger
|
||||
%{perl_vendorarch}/auto/pgBadger/.packlist
|
||||
%{_mandir}/man1/pgbadger.1*
|
||||
/var/adm/perl-modules/pgbadger
|
||||
install -m 0644 %{SOURCE1} %{buildroot}%{_defaultdocdir}/%{name}/examples/pgbadger-cron.sh
|
||||
%endif
|
||||
# install systemd-timer examples into the documentation directory: leave it to our
|
||||
# customers to decide, if and how they want to use them
|
||||
install -m 0644 %{SOURCE3} %{buildroot}%{_defaultdocdir}/%{name}/examples/
|
||||
install -m 0644 %{SOURCE4} %{buildroot}%{_defaultdocdir}/%{name}/examples/
|
||||
install -m 0644 %{SOURCE5} %{buildroot}%{_defaultdocdir}/%{name}/
|
||||
|
||||
%perl_gen_filelist
|
||||
|
||||
%files -f %{name}.files
|
||||
%license LICENSE
|
||||
%defattr(-, root, root, -)
|
||||
%dir %{_defaultdocdir}/%{name}
|
||||
%dir %{_defaultdocdir}/%{name}/tools
|
||||
%dir %{_defaultdocdir}/%{name}/examples
|
||||
%{_defaultdocdir}/%{name}/*
|
||||
%{_bindir}/pgbadger
|
||||
%{_mandir}/man1/pgbadger.1*
|
||||
%if %{with cron}
|
||||
%config(noreplace) %{_sysconfdir}/cron.d/%{name}
|
||||
%endif
|
||||
|
||||
%changelog
|
||||
33
pgbadger.timer
Normal file
33
pgbadger.timer
Normal file
@ -0,0 +1,33 @@
|
||||
#
|
||||
# The following two examples will auto-generate an report from your
|
||||
# postgresql log files.
|
||||
#
|
||||
# You need the following settings in a standard postgresql-server
|
||||
# installation in /var/lib/pgsql/data/postgresql.conf for this:
|
||||
# log_filename = 'postgresql-%Y-%m-%d.log'
|
||||
# log_rotation_age = 1d
|
||||
# log_rotation_size = 0
|
||||
#
|
||||
# The output will be placed below /srv/www/htdocs/pgbadger/
|
||||
#
|
||||
# See https://github.com/darold/pgbadger/ for more details
|
||||
#
|
||||
# Note 1: Please have a look at the corresponding pgbadger.service file
|
||||
# systemctl edit pgbadger.service
|
||||
# and adjust it to your needs.
|
||||
#
|
||||
|
||||
[Unit]
|
||||
Description=Auto-Generate an incremental report from a postgresql logfile
|
||||
|
||||
[Timer]
|
||||
## 1. This timer runs every day at 04:00 - and should match the first
|
||||
## command in the pgbadger.service file
|
||||
# OnCalendar=*-*-* 04:00:00
|
||||
## 2. This timer runs every week - and should match the 2nd
|
||||
## command in the pgbadger.service file
|
||||
# OnCalendar=weekly
|
||||
Persistent=true
|
||||
|
||||
[Install]
|
||||
WantedBy=timers.target
|
||||
Loading…
x
Reference in New Issue
Block a user