Closed jsabater closed 1 year ago
Sorry for the reponse delay, commit b765900 might fix this issue.
Dear Gilles,
I updated pgBadger to version 12 and executed the following command:
# pgbadger --version
pgBadger version 12.0
# pgbadger --extra-files --month-report 2022-08 --jobs 6 --start-monday /var/www/pg_reports/
WARNING: --rebuild require incremental mode, activating it.
LOG: Ok, generating global index to access incremental reports...
The program did nothing. I assume the --rebuild
option is necessary as it needs to go through all the incrementally generated reports (one for each day of the past month of August) to both generate the monthly report and to somehow update the daily reports, but I somehow expected pgBadger would have to read from the daily reports, but not rebuild them, to generate the monthly report.
Anyway, I made a backup of my current reports directory and executed the following command:
# pgbadger --rebuild --extra-files --month-report 2022-08 --jobs 6 --start-monday /var/www/pg_reports/
WARNING: --rebuild require incremental mode, activating it.
LOG: Ok, generating global index to access incremental reports...
The program did nothing either. What seems to be the issue?
Thanks in advance.
If I use the same command as you to generate the incremental reports I obtain the following structure:
$ tree pg_reports/2022/
pg_reports/2022/
├── 09
│ ├── 08
│ │ ├── 2022-09-08-26776.bin
│ │ ├── 2022-09-08-26777.bin
│ │ ├── 2022-09-08-26778.bin
│ │ ├── 2022-09-08-26779.bin
│ │ └── index.html
│ ├── 09
│ │ ├── 2022-09-09-26779.bin
│ │ └── index.html
│ ├── 10
│ │ ├── 2022-09-10-26779.bin
│ │ └── index.html
│ ├── 11
│ │ ├── 2022-09-11-26779.bin
│ │ └── index.html
│ ├── 12
│ │ ├── 2022-09-12-26779.bin
│ │ └── index.html
│ ├── 13
│ │ ├── 2022-09-13-26779.bin
│ │ └── index.html
│ ├── 14
│ │ ├── 2022-09-14-26779.bin
│ │ └── index.html
│ ├── 15
│ │ ├── 2022-09-15-26779.bin
│ │ └── index.html
│ └── 16
│ ├── 2022-09-16-26779.bin
│ └── index.html
├── week-36
│ └── index.html
└── week-37
└── index.html
Command was:
perl ./pgbadger --extra-files --incremental --jobs 4 --prefix '%m [%p]: user=%u,db=%d ' --start-monday \
--iso-week-number --outdir /home/git/pgbadger/pg_reports/ /var/log/postgresql/postgresql-14-main.log
Now if I want to build a monthly report using command:
perl ./pgbadger --extra-files --month-report 2022-09 /home/git/pgbadger/pg_reports/
I obtain a directory structurethat appears as follow:
pg_reports/2022/
├── 09
│ ├── 08
│ │ ├── 2022-09-08-26776.bin
│ │ ├── 2022-09-08-26777.bin
│ │ ├── 2022-09-08-26778.bin
│ │ ├── 2022-09-08-26779.bin
│ │ └── index.html
│ ├── 09
│ │ ├── 2022-09-09-26779.bin
│ │ └── index.html
│ ├── 10
│ │ ├── 2022-09-10-26779.bin
│ │ └── index.html
│ ├── 11
│ │ ├── 2022-09-11-26779.bin
│ │ └── index.html
│ ├── 12
│ │ ├── 2022-09-12-26779.bin
│ │ └── index.html
│ ├── 13
│ │ ├── 2022-09-13-26779.bin
│ │ └── index.html
│ ├── 14
│ │ ├── 2022-09-14-26779.bin
│ │ └── index.html
│ ├── 15
│ │ ├── 2022-09-15-26779.bin
│ │ └── index.html
│ ├── 16
│ │ ├── 2022-09-16-26779.bin
│ │ └── index.html
│ └── index.html
├── week-36
│ └── index.html
└── week-37
└── index.html
The difference is that there is a file pg_reports/2022/index.html that contains the monthly report for 2022-09. The report information says Log start from 2022-09-08 11:01:18 to 2022-09-16 08:41:50
which are the days with traces in my log file.
You don't need to set --rebuild
, it is forced by pgbadger anyway.
Tricky.
Here is the output of my 2022/
subdirectory:
~# tree /var/www/pg_reports/2022/
/var/www/pg_reports/2022/
├── 07
│ └── 31
│ └── index.html
├── 08
│ ├── 01
│ │ └── index.html
│ ├── 02
│ │ └── index.html
│ ├── 03
│ │ └── index.html
│ ├── 04
│ │ └── index.html
│ ├── 05
│ │ └── index.html
│ ├── 06
│ │ └── index.html
│ ├── 07
│ │ └── index.html
│ ├── 08
│ │ └── index.html
│ ├── 09
│ │ └── index.html
│ ├── 10
│ │ └── index.html
│ ├── 11
│ │ └── index.html
│ ├── 12
│ │ ├── bean.min.js
│ │ ├── bootstrap.min.css
│ │ ├── bootstrap.min.js
│ │ ├── fontawesome.min.css
│ │ ├── index.html
│ │ ├── jqplot.barRenderer.min.js
│ │ ├── jqplot.canvasAxisTickRenderer.min.js
│ │ ├── jqplot.canvasTextRenderer.min.js
│ │ ├── jqplot.categoryAxisRenderer.min.js
│ │ ├── jqplot.cursor.min.js
│ │ ├── jqplot.dateAxisRenderer.min.js
│ │ ├── jqplot.highlighter.min.js
│ │ ├── jqplot.pieRenderer.min.js
│ │ ├── jqplot.pointLabels.min.js
│ │ ├── jquery.jqplot.min.css
│ │ ├── jquery.jqplot.min.js
│ │ ├── jquery.min.js
│ │ ├── pgbadger.min.css
│ │ ├── pgbadger.min.js
│ │ ├── pgbadger_slide.min.js
│ │ └── underscore.min.js
│ ├── 13
│ │ └── index.html
│ ├── 14
│ │ └── index.html
│ ├── 15
│ │ └── index.html
│ ├── 16
│ │ └── index.html
│ ├── 17
│ │ └── index.html
│ ├── 18
│ │ └── index.html
│ ├── 19
│ │ └── index.html
│ ├── 20
│ │ └── index.html
│ ├── 21
│ │ └── index.html
│ ├── 22
│ │ └── index.html
│ ├── 23
│ │ └── index.html
│ ├── 24
│ │ └── index.html
│ ├── 25
│ │ └── index.html
│ ├── 26
│ │ └── index.html
│ ├── 27
│ │ └── index.html
│ ├── 28
│ │ └── index.html
│ ├── 29
│ │ └── index.html
│ ├── 30
│ │ └── index.html
│ └── 31
│ └── index.html
├── 09
│ ├── 01
│ │ └── index.html
│ ├── 02
│ │ └── index.html
│ ├── 03
│ │ └── index.html
│ ├── 04
│ │ └── index.html
│ ├── 05
│ │ └── index.html
│ ├── 06
│ │ └── index.html
│ ├── 07
│ │ └── index.html
│ ├── 08
│ │ └── index.html
│ ├── 09
│ │ └── index.html
│ ├── 10
│ │ └── index.html
│ ├── 11
│ │ └── index.html
│ ├── 12
│ │ ├── 2022-09-12-4166814.bin
│ │ ├── 2022-09-12-4166815.bin
│ │ └── index.html
│ ├── 13
│ │ ├── 2022-09-13-567394.bin
│ │ └── index.html
│ ├── 14
│ │ ├── 2022-09-14-1153629.bin
│ │ └── index.html
│ └── 15
│ ├── 2022-09-15-1731229.bin
│ └── index.html
├── week-31
│ └── index.html
├── week-32
│ └── index.html
├── week-33
│ └── index.html
├── week-34
│ └── index.html
├── week-35
│ └── index.html
├── week-36
│ └── index.html
└── week-37
└── index.html
57 directories, 79 files
There is no index.html
file.
I run the exact command you posted above and it worked:
~# pgbadger --version
pgBadger version 12.0
root@postgresql2:~# pgbadger --extra-files --month-report 2022-09 /var/www/pg_reports/
WARNING: --rebuild require incremental mode, activating it.
LOG: Ok, generating HTML monthly report into /var/www/pg_reports//2022/09/index.html
LOG: Ok, generating global index to access incremental reports...
Now there is an index.html
file:
~# ls -lh /var/www/pg_reports/2022/09/
total 19M
drwxr-xr-x 2 root root 4.0K Sep 7 04:00 01
drwxr-xr-x 2 root root 4.0K Sep 7 04:00 02
drwxr-xr-x 2 root root 4.0K Sep 7 04:00 03
drwxr-xr-x 2 root root 4.0K Sep 7 04:00 04
drwxr-xr-x 2 root root 4.0K Sep 14 04:00 05
drwxr-xr-x 2 root root 4.0K Sep 14 04:00 06
drwxr-xr-x 2 root root 4.0K Sep 14 04:00 07
drwxr-xr-x 2 root root 4.0K Sep 14 04:00 08
drwxr-xr-x 2 root root 4.0K Sep 14 04:00 09
drwxr-xr-x 2 root root 4.0K Sep 14 04:00 10
drwxr-xr-x 2 root root 4.0K Sep 14 04:00 11
drwxr-xr-x 2 root root 4.0K Sep 13 04:15 12
drwxr-xr-x 2 root root 4.0K Sep 14 04:17 13
drwxr-xr-x 2 root root 4.0K Sep 15 04:16 14
drwxr-xr-x 2 root root 4.0K Sep 16 04:18 15
-rw-r--r-- 1 root root 18M Sep 16 10:17 index.html
Then I tried to run the same command but for the past month of August, and it didn't fail, but it did nothing (it ended almost immediately):
~# pgbadger --extra-files --month-report 2022-08 /var/www/pg_reports/
WARNING: --rebuild require incremental mode, activating it.
LOG: Ok, generating global index to access incremental reports...
~# ls -lh /var/www/pg_reports/2022/08/
total 124K
drwxr-xr-x 2 root root 4.0K Aug 10 04:00 01
drwxr-xr-x 2 root root 4.0K Aug 10 04:00 02
drwxr-xr-x 2 root root 4.0K Aug 10 04:00 03
drwxr-xr-x 2 root root 4.0K Aug 10 04:00 04
drwxr-xr-x 2 root root 4.0K Aug 10 04:00 05
drwxr-xr-x 2 root root 4.0K Aug 10 04:00 06
drwxr-xr-x 2 root root 4.0K Aug 10 04:00 07
drwxr-xr-x 2 root root 4.0K Aug 16 04:00 08
drwxr-xr-x 2 root root 4.0K Aug 16 04:00 09
drwxr-xr-x 2 root root 4.0K Aug 16 04:00 10
drwxr-xr-x 2 root root 4.0K Aug 16 04:00 11
drwxr-xr-x 2 root root 4.0K Sep 14 20:00 12
drwxr-xr-x 2 root root 4.0K Aug 16 04:00 13
drwxr-xr-x 2 root root 4.0K Aug 16 04:00 14
drwxr-xr-x 2 root root 4.0K Aug 24 04:00 15
drwxr-xr-x 2 root root 4.0K Aug 24 04:00 16
drwxr-xr-x 2 root root 4.0K Aug 24 04:00 17
drwxr-xr-x 2 root root 4.0K Aug 24 04:00 18
drwxr-xr-x 2 root root 4.0K Aug 24 04:00 19
drwxr-xr-x 2 root root 4.0K Aug 24 04:00 20
drwxr-xr-x 2 root root 4.0K Aug 24 04:00 21
drwxr-xr-x 2 root root 4.0K Aug 31 04:00 22
drwxr-xr-x 2 root root 4.0K Aug 31 04:00 23
drwxr-xr-x 2 root root 4.0K Aug 31 04:00 24
drwxr-xr-x 2 root root 4.0K Aug 31 04:00 25
drwxr-xr-x 2 root root 4.0K Aug 31 04:00 26
drwxr-xr-x 2 root root 4.0K Aug 31 04:00 27
drwxr-xr-x 2 root root 4.0K Aug 31 04:00 28
drwxr-xr-x 2 root root 4.0K Sep 7 04:00 29
drwxr-xr-x 2 root root 4.0K Sep 7 04:00 30
drwxr-xr-x 2 root root 4.0K Sep 7 04:00 31
What do you think could be the difference?
Incidentally, just to be super-ultra-sure, generating the month report does not make use of the log files in /var/log/postgresql/
, does it? I mean, it only uses what's inside /var/www/pg_reports/2022/08/
, correct?
Please let me know if you require anything else.
The problem I guess is that you have a retention that have removed the binaries from August. As you are expecting month reports are generated from all .bin files not from logs so if they are not present there is no reports.
Where is such retention configured?
-- Jaume Sabater Ubi sapientas ibi libertas
On Fri, Sep 16, 2022, 12:37 Gilles Darold @.***> wrote:
The problem I guess is that you have a retention that have removed the binaries from August. As you are expecting month reports are generated from all .bin files not from logs so if they are not present there is no reports.
— Reply to this email directly, view it on GitHub https://github.com/darold/pgbadger/issues/749#issuecomment-1249208110, or unsubscribe https://github.com/notifications/unsubscribe-auth/AABGHTXPXEB5DWQYBFDLI33V6REXTANCNFSM6AAAAAAQFW4VUU . You are receiving this because you authored the thread.Message ID: @.***>
you don't use the -R | --retention
option in your incremental run? Looking at your tree output you just keep the binary files from the current week, you must preserve the .bin files from the whole month you want to build report.
No, I don't. The documentation says that the default value is 0 (disabled). I guessed that it would mean "not going to cycle or deal with them". Now I realise it means "not going to keep the .bin
after generating the report", doesn't it?
If it is week-based (as per the documentation), I take it that I should set it to 5 (or 6, to be extra-sure) if I plan to generate a montly report the 1st of each month?
-R | --retention N : number of weeks to keep in incremental mode. Defaults
to 0, disabled. Used to set the number of weeks to
keep in output directory. Older weeks and days
directories are automatically removed.
My PostgreSQL log files are rotated every day and kept for 70 days, so I should still be in time to recreate the daily reports for the month of August and then I would have the chance to generate the monthly report (for August). August is a month I am very interested in, indeed.
No, normally disabled mean that the binary files are always kept. Maybe you have an external cleanup?
Yes, if you are interest in getting a monthly report for August you will have to process your log again.
I have given a try and it looks that disabled mean only keep the last week :-D
I can't think about a commit sometime ago that changing the behavior but visibly the documentation is not up to date with current one.
I have done some archaeologic search and found that you must add an undocumented option added in 2014 called --noclean
to prevent pgbadger to remove binary files older than the current week :-D This change was introduced in August 2014 to preserved disk space. The old behavior (keep them all) was leading to too much disk space problem on users machines.
The default is to remove all binary files that are not necessary to build the current weekly report. This behavior can be disable using the --noclean
option. The -R | --retention
is used to remove all reports and binary files older than the number of week specified.
I'm fixing the documentation.
Nice that the archeology tasks bore fruit :-)
So if I want to keep the binary files of the last, say 6 weeks, I should use --retention 6
, correct? But what if I want to keep all past reports?
I mean, --noclean
seems to keep both the reports and the binary files, correct?
No, if you want to keep the last 6 weeks reports with all related binary files you must use: --retention 6 --noclean
If you want to keep all past reports don't set --retention
, if you want to keep all binary files use --noclean
.
Then if you want to keep only the binary files for the current month you should set your own cleanup command executed by cron like: find /var/www/pg_reports/ -name '*.bin' -mtime +31 -delete
Now that we can build monthly reports I should consider changing the default and keep the whole month instead of the current week. I will think about that.
Thanks for the clarification. I've added the following tasks to my to-do list:
--noclean
to the current script.find /var/www/pg_reports/ -name '*.bin' -mtime +45 -delete
pgbadger --extra-files --month-report 2022-08 --jobs 6 --start-monday /var/www/pg_reports/
Cheers!
Regarding recreating the incremental reports for past days, I cannot manage to achieve it. I am executing the following script but no .bin
files are being generated:
for i in $(seq 74 -1 2)
do
echo "Processing log file postgresql-13-main.log.$i.gz..."
/usr/bin/pgbadger --extra-files --format stderr --incremental --jobs 8 \
--prefix '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' --noclean \
--start-monday --iso-week-number --outdir /var/www/pg_reports/ \
/var/log/postgresql/postgresql-13-main.log.$i.gz
done
Any idea what I am doing wrong? My /var/log/postgresql/
directory contains all log files, from postgresql-13-main.log.74.gz
dated July, 31st 00:00 GMT (first day of logs configured for pgBadger) until yesterday, postgresql-13-main.log.2.gz
, dated Oct 11 00:01 GMT, and then postgresql-13-main.log.1
and postgresql-13-main.log
, both dated today as of right now.
Is it because of the --noclean
option I added in order to keep all binary files until manually cleaned up (by another cron job)?
Thanks in advance.
Hi, Gilles! Did you get this message?
Hi. Since I had to look at the code managing the "--retention" param, I can tell that in pgBadger 11.8 (in incremental mode) 1/ when --retention is not set or set to 0 then pgBadger does NO clean-up neither on outdir nor on html-outdir 2/ when --retention is set to a value (week count) then pgBadger: DOES clean-up on outdir but DOES NOT clean-up on html-outdir
files and folders in outdir (bin files) were properly deleted but html files and folders in the html reports folder tree are not deleted
Maybe this could be considered as a bug since you seem to imply that the --retention param is supposed to act first on html tree and second on bin tree (if noclean is not used)?
Regards
I have since then moved to the following format:
pgbadgerdaily
, which is run every day.pgbadgermonthly
, which is run the first day of every month.In the pgbadgerdaily
script I am executing the following code:
pgbadger --extra-files --format stderr --incremental --quiet --jobs 6 \
--prefix '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' --noclean \
--start-monday --iso-week-number --exclude-appname "pg_dump" --outdir /var/www/pg_reports \
--retention 6 /var/log/postgresql/postgresql-13-main.log.1
# Clean up old binary files
# XXX: Does '--retention' make this unnecessary?
find /var/www/pg_reports/ -name '*.bin' -mtime +45 -delete
And in the pgbadgermonthly
script I am executing the following code:
/usr/bin/pgbadger --extra-files --month-report $month --jobs 6 --incremental --start-monday /var/www/pg_reports
From your comment, @bbourgier, I presume that using find
to clean up the binary files is not necessary. Correct?
And also that the HTML output of all previous executions is kept (so to speak, the whole history of what pgbadger
produced). So if I wanted to clean up very old reports to safe space I'd have to do it manually.
Finally, just wanted to add that the curent daily/monthly tandem seems to be working fine. I never managed to produce the original monthly report that led to the issue mentioned in this thread, but subsequent monthly runs have worked fine. Moreover, the amount of RAM I have was insufficient to produce the monthly report so I had to finetune the amount of queries logged (now PostgreSQL only logs queries slower than 50 ms). But that is an entirely different problem.
Thanks for your feedback.
Just checked my pgBadger reports and saw that the old daily reports are not available, which would suggest that the HTML output is deleted, too. The monthly reports are kept, though. See the attached image that should match the parametres in my previous comment.
Hi,
I'm using pgBadger 11.8 on MS Windows but I've checked the perl code and really from what I see pgBadger is working (iterating) on outdir parameter and not on html-outdir parameter.
My command lines are quite similar to yours.
Build of incremental binary files from a list of postgreSQL log files in an input file:
pgbadger --timezone +2 --incremental --pid-dir "C:\pgbadger_incr_report_PBELIVE\__schedule_tasks" --retention 13 --start-monday --outdir "C:\pgbadger_incr_report_PBELIVE" --prefix "%m [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h " --exclude-appname "psql,createdb,pg_dumpall,pg_restore" --exclude-line ".*pgAdmin 4.*" --exclude-line ".*DBeaver.*" --html-outdir "C:\inetpub\wwwroot\pgbadger_PBELIVE" --extra-files --logfile-list "C:\pgbadger_incr_report_PBELIVE\__schedule_tasks\logfilelist_NEW.txt"
As you can see, my retention period is 13 weeks, which means it should keep in theory Binary and HTML files only starting around 27th of january 2023.
Monthly Reports:
pgbadger --timezone +2 --incremental --pid-dir "C:\pgbadger_incr_report_PBELIVE\__schedule_tasks" --retention 13 --start-monday --month-report 2023-04 --extra-files --outdir "C:\pgbadger_incr_report_PBELIVE" --exclude-appname "psql,createdb,pg_dumpall,pg_restore" --exclude-line ".*pgAdmin 4.*" --exclude-line ".*DBeaver.*" --html-outdir "C:\inetpub\wwwroot\pgbadger_PBELIVE"
In BOTH cases, same kind of parameters as yours EXCEPT I do not use the '--noclean' parameter. This is important as the '--noclean' parameter DISABLES the Binary Files (outdir) clean-up!
Binary Files are generated in outdir:
C:\pgbadger_incr_report_PBELIVE
HTML Files are generated in html-outdir:
C:\inetpub\wwwroot\pgbadger_PBELIVE
And here is the interesting part.
I can see that in outdir the clean-up is properly done:
-->
Only folders 2023 > 01,02,03... are kept
BUT regarding the HMTL reports generated in html-outdir the clean-up is definitely NOT done:
-->
HTML reports (daily / weekly / monthly) from 2022 are still there.
Therefore regarding your question:
I presume that using find to clean up the binary files is not necessary
I would say that NO it is not necessary as long as you DO NOT use the '--noclean' pgBadger param.
And about the fact that you do not have anymore old HTML reports, I'm circonspect. Are you sure you did not delete (automatically or manually at times) the generated HTML files and folder? Because really this does not work for me. And I do believe this is a bug as @darold suggested through his answers that:
Regards.
Re- In fact I think I understand. In many places in the pgBadger perl code, the HTML generated files' destination folder is specified as:
my $dest_dir = $html_outdir || $outdir;
which basically means: if html-outdir is specified in params then it takes this folder as destination for HTML files and if it is not defined then it takes outdir.
The problem (and definitely a BUG) with the '--retention' folder/file deletion code is that it does not handle BOTH outdir and html-outdir. The code only manages outdir.
In your case (your command line) you only specify outdir and NOT html-outdir. This means your HTML files are in the outdir folder tree as well as the binary files. And this is why: 1/ your HTML files gets deleted 2/ and because you specify '--noclean' then the binary files do not get deleted --> this means that in your case, simply removing the '--noclean' param will solve your issue: you won't need the extra schedule task to delete the .bin files AND the HTML files gets deleted
In my case (with outdir AND html-outdir params) the current code is missing the part that processes the html-outdir folder tree exactly the same way as the outdir folder tree. I'll declare a bug.
Hope this explanation helps. And hopefully @darold will confirm this.
Regards.
Last commits should fix this issue.
Dear Gilles,
I have been running your awesome pgBadger script for over a month now and I'd like to build a monthly report of the month of August. This is the command I am executing, and the error it's returning:
Reports are being generated daily with this command:
PostgreSQL log file
/var/log/postgresql/postgresql-13-main.log
is being rotated daily, hours in advance of the execution ofpgbadger
.The only report with a similar error that I could find was this, but unfortunately I could not understand the reply you gave there either.
If I understood the documentation correctly, pgBadger should be using the binary files stored inside
/var/www/pg_reports/
to build the monthly report, modifying the existing report to add a link to the month name that will lead to the newly-generated report. Or did I misunderstand something?Also, as can be seen in the command I am executing daily, I am using incremental mode. And I am also using the
--extra-files
parameter when trying to generate the montly report, since it's used when generating the daily reports.Thanks in advance.