PGCLUU(1p)

📦
version 4.0 · 5 versions
, in ubuntu resolute, ubuntu stonking

💖 NAME 💖

pgCluu — PostgreSQL Cluster utilization. That's the whole vibe, bestie. 🐘✨

✨ DESCRIPTION ✨

Okay so pgCluu is a PostgreSQL performance monitoring and auditing tool. It is literally that girl. 💅

It's a Perl program, and what it does is a full audit of a PostgreSQL Cluster and System performance. And the way it's built? Two parts, two moods:

  • A collector that grabs statistics on the PostgreSQL cluster using the psql command line utility and sar from the sysstat package. 👑
  • A pure Perl grapher that generates all the HTML and charts output, and it needs absolutely nothing else. No requirements. Zero. Iconic behavior honestly. 🌈

Now, if you don't need system utilization reports, or you just don't want to install the sysstat package, you can disable it right at the command line. Then you'll only have reports about your PostgreSQL Cluster. Also! If you're running pgCluu from a central server and using option -h to monitor a PostgreSQL Cluster remotely, the call to sar is automatically disabled. Autopilot, fr fr. 🦋

And if you just want system utilization reports, or you want to generate graphs from a sar data file? Yeah, that's possible too. We love options. ✨

🎀 SYNOPSIS 🎀

PostgreSQL and System metrics collector:

pgcluu_collectd [options] output_dir

Report generator:

pgcluu [options] -o report_dir input_dir

💜 REQUIREMENT 💜

So pgCluu comes with two Perl scripts. You need a modern Perl distribution, the psql client, and the sar command line utility (sysstat). But listen — the sysstat package is optional, you can still use pgCluu to generate reports about your PostgreSQL Cluster without it. No gatekeeping here. 🫶

The charts are rendered using a Javascript library, so you don't need anything else. Your browser will do all the work. Main character energy from your browser, honestly. 👑

⭐ INSTALLATION ⭐

📦 Installation from package

You can install pgCluu through the PostgreSQL Global Development Group (PGDG) repositories. Go peek at how to install the PGDG repositories at these URLs for Debian and Ubuntu:

https://wiki.postgresql.org/wiki/Apt

and for Redhat, CentOs, Fedora, Scientific Linux and Oracle Enterprise Linux:

https://yum.postgresql.org/

Once that's done you can simply install pgCluu with the commands:

sudo apt install pgcluu
sudo yum install pgcluu

Look at the package information to know where files are specifically installed. Then check the next two chapters to see which default installation paths are used. 👀

🛠️ Installation from sources

Download the tarball from GitHub and unpack the archive:

tar xzf pgcluu-3.x.tar.gz
cd pgcluu-3.x/
perl Makefile.PL
make && sudo make install

This will copy the Perl scripts pgcluu_collectd and pgcluu into the /usr/local/bin directory, and the man page to /usr/local/share/man/man1/pgcluu.1p.gz. Those are the default installation directories for 'site' install on some well-known distributions, but ngl the path could change. 🤷

If you want to install all under the /usr location, use INSTALLDIRS='vendor' as an argument of Makefile.PL. The script will be installed into /usr/bin/pgcluu and the manpage into /usr/share/man/man1/pgcluu.1p.gz.

For example, to install everything just like Debian does, proceed as follows:

perl Makefile.PL INSTALLDIRS=vendor

By default INSTALLDIRS is set to site. 💅

The directory where statistics will be saved is /var/lib/pgcluu/data and the default directory for reports is /var/lib/pgcluu/report.

The CGI script is installed into /var/lib/cgi-bin/pgcluu.cgi and the Apache configuration file into /etc/apache/conf-available/pgcluu.conf. This file allows access to resources files (CSS and JS files) from the installation directory /usr/local/share/pgcluu/rsc/. Access is granted to local user only by default. Locked down, as it should be. 🔒

All scripts (pgcluu_collectd, pgcluu and pgcluu.cgi) are reading the configuration file from /usr/local/etc/pgcluu.conf. This file is mainly used by the CGI script, but some directives are dedicated to the pgcluu_collectd and pgcluu scripts, to define the retention days for example. 🗓️

🌸 Custom installation

The installation of pgCluu can be fully customized through environment variables: RSCDIR, CGIDIR, CONFDIR, PIDDIR, STATDIR, APACHECONF, MANDIR, DOCDIR, SYSTEMDDIR, RETENTION. These variables are passed to Makefile.PL as command line arguments or can be exported as environment variables before running perl Makefile.PL.

The default values for these variables are:

DESTDIR     => /usr/local
INSTALLDIRS => site
CONFDIR     => DESTDIR/etc
PIDDIR      => /var/run/postgres
STATDIR     => /var/lib/pgcluu/data
REPORTDIR   => /var/lib/pgcluu/data
RSCDIR      => DESTDIR/share/pgcluu
CGIDIR      => /usr/lib/cgi-bin
APACHECONF  => /etc/apache2/conf-available
MANDIR      => DESTDIR/share/man
DOCDIR      => DESTDIR/share/doc
SYSTEMDIR   => DESTDIR/lib/systemd/system
RETENTION   => 0

If INSTALLDIRS is set to 'vendor':

CONFDIR     => /etc
DESTDIR     => /usr

The configuration file is auto-generated by the Makefile.PL script and saved into CONFDIR/pgcluu.conf. And heads up: if the destination file exists, it is not overridden. 🙈 The file is also saved as an example in the directory DOCDIR/pgcluu/examples/pgcluu.conf.dist.

The directory where pgcluu_collectd will store statistics is defined with STATDIR, which is by default /var/lib/pgcluu/data. The directory where pgcluu will generate static html reports is defined by REPORTDIR. The default is /var/lib/pgcluu/report. Both directories must be owned by the postgres user. Important detail, don't skip it bestie. 💖

The man page is saved as DESTDIR/share/man/pgcluu.1p.gz and a symbolic link pgcluu_collectd.1p.gz is created to this file. The documentation, README, changelog.gz, and LICENSE files are saved under DESTDIR/share/doc/.

For the CGI mode, the resources (css and js files from the cgi-bin/rsc) are saved under the DESTDIR/share/pgcluu/rsc directory. The CGI script is saved under /usr/lib/cgi-bin/pgcluu.cgi. The Apache configuration file goes under /etc/apache2/conf-available/pgcluu.conf with a symbolic link /etc/apache2/conf-enabled/pgcluu.conf created to this file. Its content:

Alias /pgcluu RSCDIR/
<Directory RSCDIR/>
    Options FollowSymLinks MultiViews
    AllowOverride None
    Require local
    #Require ip 192.1.168.0/24
</Directory>

The systemd files (pgcluu_collectd.service, pgcluu.service, pgcluu.timer) are saved as examples into DOCDIR/pgcluu/examples/ and into the systemd directory SYSTEMDDIR/.

The right path to the configuration file is set into all scripts pgcluu, pgcluu_collectd and pgcluu.cgi. The path where the pid file must be saved is replaced into pgcluu_collectd with the value of the PIDFILE variable.

Paths to scripts, pid directory, statistics and reports are replaced in all systemd service files following the values of the environment variables explained here. ✨

🌈 USAGE 🌈

Manually

Check the next two chapters for a complete description of the command line options. But for the impatient ones (no shade, I'm also impatient 💀), here are some simple commands that could be run as the postgres user:

mkdir /tmp/stat_db1/
pgcluu_collectd -D -i 60 /tmp/stat_db1/
LOG: Detach from terminal with pid: 11323

or with more options:

pgcluu_collectd -D -i 60 /tmp/stat_db1/ -h 10.10.1.1 -U postgres -d mydb
LOG: Detach from terminal with pid: 14671

Wait some time and get some activity going on your PostgreSQL Cluster... Then stop the pgcluu_collectd daemon and generate the report:

pgcluu_collectd -k
LOG: Received terminating signal.
mkdir /tmp/report_db1/
pgcluu -o /tmp/report_db1/ /tmp/stat_db1/

You should obtain something like the example at http://pgcluu.darold.net/example/. Go look, it's cute. 🎀

By default all javascript, css and the webfont fontawesome are automatically generated into the output directory if those files do not already exist.

Using systemd unit files

pgcluu comes with systemd service files:

  • pgcluu_collectd.service: execute pgcluu_collectd as a daemon to collect statistics. 👑
  • pgcluu.service: execute pgcluu to generate reports. ✨
  • pgcluu.timer: run periodically pgcluu.service. ⏰

These files are installed automatically by the install script or the package into /lib/systemd/system/. To activate these services proceed as follows:

systemctl daemon-reload
systemctl enable pgcluu_collectd.service
systemctl enable pgcluu.service
systemctl enable pgcluu.timer
systemctl start pgcluu_collectd.service
systemctl start pgcluu.timer
⚠️ Be warned bestie: storing statistics indefinitely with pgcluu_collectd can fill your disk space in the short or medium term. Not a vibe. 😭 You have to remove obsolete statistics manually using a cron job, or use the embedded retention feature by adding option --retention ndays to the pgcluu_collectd call, or by changing the value of the STATS_COLLECTD_RETENTION configuration directive in the file /usr/local/etc/pgcluu.conf or /etc/pgcluu.conf file, following your installation. You can also set STATS_REPORT_RETENTION to limit the retention of reports directories, or use option --retention ndays with pgcluu. Default is to preserve collected data for the past 30 days. 🗓️

If you want to use the CGI mode you also have to activate pgcluu caching by enabling the value STATS_REPORT_CACHING in the configuration file and using pgcluu.service + pgcluu.timer. If you don't want to use systemd you can simply run the pgcluu script with the -C option and execute it periodically through a cron job. 💅

Note that the systemd service files set the privilege to the data and report directory to user postgres and group www-data. By this way, reports are readable through an httpd server for static reports, and data are readable through the CGI if enabled. Commands executed by the pgcluu_collectd.service file (daemon part) are:

/bin/mkdir -p $STATDIR
/bin/chown postgres:www-data $STATDIR
/bin/chmod u=rwX,g=rsX,o= $STATDIR

The pgcluu.service file (client part) executes the commands:

/bin/mkdir -p $REPORTDIR
/bin/chown postgres:www-data $REPORTDIR
/bin/chmod u=rwX,g=rsX,o= $REPORTDIR

In case you don't want to use systemd service files, you will have to execute these commands manually before running pgCluu. 🙈

For pgcluu reports, the output directory can be defined in the configuration file using the STATS_REPORT_OUTDIR directive. 🦋

🧚 COLLECTING STATISTICS 🧚

To generate reports about your PostgreSQL Cluster Utilization you must collect statistics first. pgcluu_collectd is here for that. 👑

It can be run in a daemon mode (option -D) or in interactive mode for debugging purpose. All you need is to provide a directory where data will be stored. Statistics will be pooled at a default interval of 60 seconds, and using option -i you can customize it. See below for a complete list of command line options.

💖 pgcluu_collectd usage

usage: pgcluu_collectd [options] output_dir

output_dir</p>: the full path to the directory where pgcluu_collectd is gonna stash all those sweet sweet statistics. 💾

options:

  • -B, --enable-buffercache — turns on buffercache statistics, but only if the pg_buffercache extension is installed bestie, no extension no stats 😌
  • -c, --capture — makes a whole snapshot of the PostgreSQL installation and drops it into tmp/pgcluu_capture.tar.gz. cute lil time capsule fr.
  • -C, --end-counter=NUM — the program just dips out after NUM reports. bye bestie 👋
  • -d, --dbname=DATABASE — which database to connect to. defaults to the current user, iconic default behavior 💅
  • -D, --daemonize — detaches from the console and goes full daemon mode. independent queen ✨
  • -E, --end-after=NUM — self-terminates after a given number of seconds. you can write it like 7200 or 120M or 2H, and for days use 7D — so like stop collecting after seven days. so flexible, we love range 🎀
  • -f, --pid-file=FILE — path to the pid file. Default: /var/run/postgresql/pgcluu_collectd.pid.
  • -h, --host=HOSTNAME — database server host or socket directory 🏠
  • -i, --interval=NUM — how long to wait between each run, the lil breather between rounds ⏳
  • -k, --kill — stops the currently running pgcluu_collectd daemon. lights out periodt 💀
  • -m, --metric=METRIC — set a comma separated list of the metrics you wanna run, pick and choose your faves 🌈
  • -M, --max-size=SIZE — self-terminates the program when the output directory gets bigger than a given size. write it like 2GB or 2000MB. protect the disk bestie 🛑
  • -n, --no-pg_stats-dump — don't collect stats from the pg_stats view. skipping that one, respectfully 🙈
  • -p, --port=PORT — database port(s) to connect to. Defaults to 5432.
  • -P, --psql=BIN — path to the psql command. Default: psql.
  • -Q, --no-statement — do NOT collect stats from pg_stat_statements. hard pass on that table.
  • -r, --rotate-daily — force daily rotation of data files. fresh files every day, self care 🧖
  • -R, --rotate-hourly — force hourly rotation of data files. even more frequent, so thorough 💫
  • -s, --sar=BIN — path to the sar sysstat command. Default: sar.
  • -S, --disable-sar — disable collecting system statistics with sar. not today satan.
  • -t, --lock-timeout=NUM — terminate that metric SQL query after N second(s) in case it waits too long because of a lock. Default: 3. don't let it just sit there waiting, we have places to be 🚦
  • -T, --no-tablespace — disables the tablespace lookup when the connecting user isn't a superuser, so you don't get a sad error message printed at you. no error spam, we stay thriving 🌸
  • -U, --dbuser=USERNAME — the database user to connect as. defaults to the current user.
  • -v, --verbose — prints out debug informations. spill the tea, tell us everything 🫖
  • -V, --version — shows the pgcluu_collectd version and exits.
  • -w, --no-waitevent — don't collect wait event stats from pg_wait_sampling. gotta keep it moving 🏃
  • -W, --password=pass — the database password. 🔐
  • -z, --compress — forces compression of the rotated data files. zip zip, saving that space 💾✨
  • --included-db=DATABASE — collect statistics ONLY for the databases in this comma separated list of database names. exclusive invite list 💌
  • --list-metric — lists all the available metrics actions that can be performed. window shopping the metrics 🛍️
  • --sysinfo — get operating system infos and exit (drops a sysinfo.txt).
  • --no-sysinfo — do not collect operating system information at all. nope, not doing it.
  • --no-database — do not collect database statistics at all. none. zero. zip.
  • --pgbouncer-args=OPTIONS — the options used to connect to the pgbouncer system database. Ex: -p 6432 -U postgres -h 192.168.1.100. you MUST give at least one parameter to enable pgbouncer monitoring — like it's mandatory bestie, one param minimum or nothing happens 🔑
  • --sar-file=FILE — path to the sar output data file for sysstat stats. Defaults to output_dir/sar_stats.dat.
  • --stat-type all|user — sets which stats tables to read. Values: 'all' or 'user' to look at pg_stat_(all|user) tables. Default: user.
  • --pgversion X.Y — force the PostgreSQL version to the given value. manifesting a version, mine now 🔮
  • --pgservice NAME — name of the service inside the pg_service.conf file.
  • --exclude-time RANGE — excludes a laps of time by giving the start and end hours. bye for now, don't collect during this window 🕐
  • --cron-user=USERNAME — collect crontab settings for the given username (in this case pgcluu_collectd needs to be run as root, so like, root energy required 👑). Default is to use the USERNAME environment variable, or postgres when it isn't defined.
  • --package-list=CMD — command to list PostgreSQL packages. Default is to autodetect the package type and use the command rpm -qa or dpkg -l. If you're on some other system you can set a custom command. A filter on the keyword postgres is appended to the command: | grep postgres.
  • --retention NDAYS — number of rolling days to keep in the data directory in incremental mode. Default is to store indefinitely — forever and ever, no expiry 🥰
  • --disable-pidstat — do not collect metrics from the pidstat command.
  • --help — prints usage. the classic.

Use these options to run sar on the remote host defined by the -h option — otherwise it's just gonna run locally bestie:

  • --enable-ssh — activates using ssh to run sysstat remotely. long distance relationship with your server 💞
  • --ssh-program ssh — path to the ssh program to use. Default: ssh.
  • --ssh-user username — connection login name. Defaults to the running user.
  • --ssh-identity file — path to the identity file to use.
  • --ssh-timeout second — timeout for an ssh connection failure. Default 10.
  • --ssh-options options — list of -o options to use for the ssh connection. Options always used:
    • -o ConnectTimeout=$ssh_timeout
    • -o PreferredAuthentications=hostbased,publickey

So for example, as the postgres user, to monitor a full PostgreSQL cluster locally:

mkdir /tmp/stat_db1/
pgcluu_collectd -D -i 60 /tmp/stat_db1/

to collect statistics from pgbouncer too, and limit the database statistics to a single database:

pgcluu_collectd -D -i 60 /tmp/stat_db1/ -h 10.10.1.1 -U postgres -d mydb
              --pgbouncer-args='-p 5342'

to disable statistics collecting between 22:30 and 06:30 the next day:

pgcluu_collectd -D -i 60 /tmp/stat_db1/ --exclude-time "22:30-06:30"

to collect statistics from a remote server:

pgcluu_collectd -D -i 60 /tmp/statdb1/ -h 10.0.0.1 -U postgres --disable-sar

the same but collecting system statistics using remote sar calls:

pgcluu_collectd -D -i 60 /tmp/statdb1/ -h 10.0.0.1 -U postgres --enable-ssh
      --ssh-user postgres --ssh-identity /var/lib/postgresql/.ssh/id_rsa.pub

You might need a .pgpass and you gotta be able to establish passwordless ssh connections to be able to collect statistics from remote hosts. no cap, that's the requirement 🫶

Then after some time and some activity on the database, stop the daemon like this:

pgcluu_collectd -k

or by sending sigterm to the pgcluu_collectd's pid. say goodbye gently 😭

You can run the collector in incremental mode using a daily or an hourly statistics rotation:

pgcluu_collectd -D -i 60 /tmp/stat_db1/ --rotate-daily

On a server with huge activity you might wanna use --rotate-hourly and compression mode with the --compress option. and if you've got limited disk space you can restrict the retention time of statistics files using the --retention option with the storage day limit. protect that storage bestie 💅

🗂️ Statistics files 🗂️

The output directory with all the statistics collected should look like:

/tmp/stat_db1/
|-- commit_memory.csv
|-- end-pg_statio_user_indexes.csv
|-- end-pg_statio_user_sequences.csv
|-- end-pg_statio_user_tables.csv
|-- end-pg_stat_user_functions.csv
|-- end-pg_stat_user_indexes.csv
|-- end-pg_stat_user_tables.csv
|-- end-pg_stat_xact_user_functions.csv
|-- end-pg_stat_xact_user_tables.csv
|-- fs_stat_use.csv
|-- pg_class_size.csv
|-- pg_database_size.csv
|-- pg_db_role_setting.csv
|-- pg_hba.conf
|-- pg_ident.conf
|-- pg_nondefault_settings.csv
|-- pg_prepared_xact.csv
|-- pg_settings.csv
|-- pg_stat_archiver.csv
|-- pg_stat_bgwriter.csv
|-- pg_stat_connections.csv
|-- pg_stat_count_indexes.csv
|-- pg_stat_database_conflicts.csv
|-- pg_stat_database.csv
|-- pg_stat_hash_indexes.csv
|-- pg_stat_invalid_indexes.csv
|-- pg_statio_user_indexes.csv
|-- pg_statio_user_sequences.csv
|-- pg_statio_user_tables.csv
|-- pg_stat_locks.csv
|-- pg_stat_missing_fkindexes.csv
|-- pg_stat_redundant_indexes.csv
|-- pg_stat_replication.csv
|-- pg_stat_unlogged.csv
|-- pg_stat_unused_indexes.csv
|-- pg_stat_user_functions.csv
|-- pg_stat_user_indexes.csv
|-- pg_stat_user_tables.csv
|-- pg_stat_xact_user_functions.csv
|-- pg_stat_xact_user_tables.csv
|-- pg_tablespace_size.csv
|-- pg_xlog_stat.csv
|-- postgresql.auto.conf
|-- postgresql.conf
|-- sar_stats.dat
|-- sysinfo.txt

Then now you can proceed with pgcluu to generate reports. the dopamine hit of a folder full of data ✨

🌟 Additional statistics from extensions 🌟

To be able to report top statements, wait events and subxact statistics, you need to create the related extensions into a single database and then use this database for the primary connection to pgcluu_collectd using the -d option.

The related extension are pg_stat_statements, pg_wait_sampling and pg_subxact_counters. those three, no others, periodt 💖

🔄 Rotation and compression 🔄

When used, the --rotate-daily or --rotate-hourly command line option will force pgcluu_collectd to rotate daily or hourly all statistic's files. In this case, statistics files will be created in a subdirectory based on the rotation frequency, output_dir/year/month/day[/hour]. This is called the incremental mode. 🗓️

To save filesystem space it's possible to enable compression of all rotated files during the rotation process. Just activate the -z or --compress command line option. You can also use the --retention option to set the storage time limit in days.

📈 Incremental mode 📈

This mode is enabled when the --rotate-daily or --rotate-hourly command line options are used. It allows pgcluu to build reports incrementally by days or hours. In this mode you don't have to build reports per day or hour — pgcluu will do the work automatically, just give it the top statistics directory. it's giving delegation, we love that for us 💁

pgcluu -o /var/www/pgcluu/reports/ /var/lib/pgcluu/data/

pgcluu will detect that --rotate-daily or --rotate-hourly have been used to collect data and generate reports for each day or hours stored. Previous directories already processed will not be processed again unless this was the last one. it remembers what it already did, so smart 🧠

Storing your stats on a daily or hourly basis?? That also unlocks the CGI script pgcluu.cgi, which gives you dynamic reports AND temporal search 🕰️✨. Peep the "Using dynamic mode" chapter for the whole story, bestie.

When you pass the --rotate-daily or --rotate-hourly command line option, it literally forces pgcluu_collectd to rotate all of its statistic's files daily or hourly, no questions asked 💅. And where do those files live after rotation, you ask? In a subdirectory based on the rotation frequency, laid out like this:

output_dir/year/month/day[/hour]

That whole situation is what we call the incremental mode 🌈.

Now, bestie, filesystems are NOT infinite and we gotta be nice to them, so if you wanna save some space you can turn on compression for all those rotated files right there during the rotation process — just activate the -z or --compress command line option 🎀. And if you want to cap how long stuff hangs around, the --retention option sets the storage time limit in days ⏳.

🦋 Capture mode 🦋

Okay so this mode is giving simple report energy — the whole goal is to get a cute lil report about your PostgreSQL installation without collecting any metrics other than database and tablespace size 📊. This report is perfect for ITs who wanna actually understand the configuration and figure out what needs tuning 👑.

Wanna enable it? Just run pgcluu_collectd with the single option -c or --capture:

Heads up bestie — when you do this, other command line options will not be taken in account. It's a one-flag era, no cap 🙈.

Once it's running, pgcluu_collectd creates a temporary directory /tmp/pgcluu_capture to store temporary data, and then it removes it after building a compressed tar archive: /tmp/pgcluu_capture.tar.gz 📦✨.

That archive right there?? That's the one you can use with pgcluu to build a snapshot report of the instance — and don't even stress, pgcluu will automatically detect this mode, like it just knows 🧚💖.

✨ GENERATING REPORTS ✨

okay bestie this is the part where the magic actually happens 💖 you've been collecting all these stats and now we're gonna turn them into actual reports. two flavors: static HTML reports (the chill, pre-built ones) and dynamic CGI reports (the interactive main character ones). let's get into it 🫶

🌸 Static HTML reports 🌸

so to generate a pgCluu report about a PostgreSQL Cluster you need, at minimum, a directory that has all the data files that pgcluu_collectd or pgstats made for you. no directory, no report, that's just the rules 💅

and listen, if in that directory you've got a file named sar_stats.dat or sadc_stats.dat (that second one's for binary sadc data), pgCluu will pick it up and build a report about system utilization too. iconic. but if you literally just want a report from a sar file and nothing else? then use the -i or -I options. easy.

here's the usage, keep it in your notes:

usage: pgCluu [options] [-i sar_file | -I sadc_file] [input_dir]

        input_dir: directory where pgcluu_collectd or pgstats and sar data
                   files are stored.

and now the options, all of them, in order, no skips, we're thorough in this house 👑

  • -b, --begin datetime — start date/time for the data to be parsed (time is in the current timezone).
  • -C, --cache — only generate cache files (.bin), like, no html output at all. just the cache bestie.
  • -d, --db-only dbname — only report for the whole cluster and the given database name. you can use it multiple times OR give a comma separated list of database names. your call 🎀
  • -D, --device-only dev — only report I/O stats for a particular device. again, use it multiple times or pass a comma separated list of device names, ex: sda,sdc.
  • -e, --end datetime — end date/time for the data to be parsed (time of current timezone, same vibe as -b).
  • -i, --sar-file=FILE — path to the sar text data file to read for generating system reports. defaults to input_dir/sar_stats.dat.
  • -I, --sadc-file=FILE — sadc binary data file to read for generating system reports. defaults to input_dir/sadc_stats.dat.
  • -n, --top-number — Top number of tables or indexes I/O stats to show. default is top 10 btw. set it to 0 to show all of them (no cap, literally).
  • -N, --network-only iface — only report stats for a particular network interface. you can use it multiple times or give a comma separated list of network interfaces, ex: eth0,eth1.
  • -o, --output=DIR — output directory 💖
  • -r, --reverse-date — by default pgCluu looks at mm/dd/yy format in the sar file. when enabled, pgCluu will look at dd/mm/yy format instead. (fellow non-americans, this one's for us 😭)
  • -s, --sadf=BIN — path to the sadf sysstat command used to read the sadc binary data file. default: /usr/bin/sadf.
  • -S, --disable-sar — disable collection of system statistics with sar. bye sar.
  • -t, --with-table table — only report for the whole tables and the given table name. you can use it multiple times or give a comma separated list. (yes the manpage says "database name" here but you get it, it's tables 🫶)
  • -T, --no-table — do not report statistics related to tables. at all. periodt.
  • -v, --verbose — print out debug informations.
  • -V, --version — show pgcluu version and exit.
  • -x, --external-menu — save menu in menu.html and load it into each report using the w3-include-html attribut from w3.js. important: this only works if access to HTML reports is through a Web server, NOT using the file:// protocol. so don't be surprised if it's giving broken when you open it locally 🙈
  • -z, --timezone +/-XX — set the number of hour(s) from GMT of the timezone. Eg: -z +02. usually autodetected. use this to adjust date/time from the sar output, because pgcluu uses GMT time to draw charts.
  • -Z, --stats-timezone +/-XX — set the number of hour(s) from GMT of the timezone. Eg: -Z +02. usually autodetected. this one adjusts date/time from the cluster and system stats output, since pgcluu uses GMT time there too.
  • --from-sa-file — instruct pgcluu that the file specified by the -i option uses the standard system activity daily data file. very "i know what i'm doing" energy.
  • --charset — used to set the HTML charset to be used. default: utf-8.
  • --retention NDAYS — number of rolling days to keep in report directory. default is to store indefinitely (yolo, forever and ever).
  • --help — print usage.

okay now the fun part, EXAMPLES because who doesn't love a little copy-paste moment 🦋

so like, say you wanna generate ALL the HTML reports from data files stored in /tmp/stat_db1/. you'd do:

mkdir /tmp/report_db1/
pgcluu -o /tmp/report_db1/ /tmp/stat_db1/

and if you only want reports of SOME databases (we're being picky, it's a vibe):

pgcluu -o /tmp/report_db1/ /tmp/stat_db1/ --db-only "db1,db2,db3"

now if you literally just want to create a report from a sar output file:

sar -p -A 10 60 > /root/my_sar_file.txt
pgcluu -o /tmp/report_sar/ -i /root/my_sar_file.txt

or from a daily sa file:

sar -p -A -f /var/log/sa/sa18 > /root/my_sar_file.txt
pgcluu -o /tmp/report_sar/ -i /root/my_sar_file.txt --from-sa-file

and from a sa binary file:

pgcluu -o /tmp/report_sar/ -i /var/log/sysstat/sa22

or the sa text file, if you don't have the same version of sysstat (been there bestie):

pgcluu -o /tmp/report_sar/ -i /var/log/sysstat/sar23 --from-sa-file

and if pgcluu_collectd was run in incremental mode, you can limit the number of retention days used for the reports:

pgcluu -o /tmp/report_sar/ /tmp/stat_db1/ --retention 30

so a static report gets built for each day or hour, following the rotation you used with pgcluu_collectd. no cap. 🌈

💅 Dynamic reports (CGI) 💅

okay so dynamic reports are built by a CGI script called pgcluu.cgi and you can find it in the cgi-bin repository of the source code. the way it works is it lets you select the time period to build reports and look at all the different reports for that period, just like with static HTML reports. so it's the same energy, but you're the one driving 🚗✨

after installation from sources or binary packages the CGI might be found in /usr/lib/cgi-bin/pgcluu.cgi or /var/www/cgi-bin/pgcluu.cgi depending on your distribution. (iykyk, distros love putting things in different places 😭)

to use pgCluu in CGI mode you need a Web server (here we're using Apache) and a cron task to build the cache periodically. yes, both. it's giving setup but it's worth it.

first, enable CGI mode:

sudo a2enmod cgi
sudo service apache2 restart

then the CGI needs to find the statistics directory where pgcluu_collectd writes, mostly /var/lib/pgcluu/data/. and listen — the content of this repository must be readable by the Apache user (www-data). don't skip this one, it's not optional 🙈

⚠️ take care to restrict access to your server and the CGI — information about your database and server is exposed in the reports. like, protect this. seriously. 🚫

the CGI script pgcluu.cgi uses resources files (CSS and javascript) stored in the cgi-bin/rsc/ directory of the source distribution. install this repository onto the DocumentRoot of your Web server, for example:

sudo mkdir /var/www/pgcluu/
sudo cp -rf cgi-bin/rsc /var/www/pgcluu/

then edit /etc/pgcluu.conf — copy it from sources cgi-bin/pgcluu.conf if it doesn't exist yet. change the RSC_BASE and INPUT_DIR configuration directives to match your installation. here in our example:

RSC_BASE        /pgcluu/rsc/
INPUT_DIR       /var/lib/pgcluu/data

now we can start the data collection 🎀 for the moment, pgcluu.conf is only read by the CGI pgcluu.cgi, but just so you know, this might change in the future. (foreshadowing, bestie 🍓)

to let pgCluu start collecting data, use the following command (you can change it to however you like, it's your world). we'll need the postgres user for this:

sudo su - postgres
/usr/local/bin/pgcluu_collectd -D -i 60 --rotate-daily /var/lib/pgcluu/data

this will rotate your data daily. which is also the default in the systemd service file, fun fact 🧚

okay important — plugging the CGI directly to the CSV statistics files will result in very slow generation reports. not the vibe. to speed things up, caching must be used, so you've gotta execute pgcluu in cache mode periodically. run it manually the first time:

/usr/local/bin/pgcluu --cache /var/lib/pgcluu/data

then add a cron task to execute the command every five or ten minutes:

*/5 * * * * /usr/local/bin/pgcluu --cache /var/lib/pgcluu/data

if you're using the systemd pgcluu.timer service, then caching can be enabled using the STATS_REPORT_CACHING directive. enabling this mode generates cache files (*.bin) in the statistics directory and disables static HTML reports generation. so it's one or the other, choose wisely 💅

to see your reports, go to this URL:

http://localhost/cgi-bin/pgcluu.cgi

or change localhost to your fqdn server name.

and note — here pgcluu_collectd and pgcluu scripts are found in /usr/local/bin/ which is the default for an installation from sources, but with an installation from binary package you might find them in /usr/bin/ instead. so don't panic if the path looks different 👑

the CGI mode lets you select the period of time used to generate the statistics reports. using the default pgcluu_collectd 60 seconds interval, a daily graph will have 1440 points. having too many timeseries to render is genuinely not good for performances, so they limit it to the first seven days of the selected period. this means a maximum of 10080 points for one week, which feels like a safe limit tbh. feel free to increase or reduce the limit following the server and client resources. this is controlled by:

MAX_RENDERED_DAYS       7

which is a configuration directive in pgcluu.conf. and that's the whole CGI situation, gorgeous 🌸

📜 LICENSE 📜

Copyright (c) 2012-2024, Gilles Darold

pgCluu is licenced under the PostgreSQL Licence, a liberal Open Source license, similar to the BSD or MIT licenses. that means all parts of the program are open source and free of charge. we love free and open 💖

Permission to use, copy, modify, and distribute this software and its
documentation for any purpose, without fee, and without a written agreement
is hereby granted, provided that the above copyright notice and this
paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL Dalibo BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT,
SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS,
ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
Dalibo HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Gilles Darold SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS,
AND Gilles Darold HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT,
UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

and this is the case for both — pgcluu_collectd and the grapher pgcluu programs. equal treatment, as they deserve ✨

👑 AUTHORS 👑

okay so who even made this? bestie, gather round 💖

  • pgCluu is an original development of Gilles Darold. Like, the whole thing, his baby, his magnum opus, no cap 👑
  • Some parts of the collector are taken from pgstats, a C program written by Guillaume Lelarge — and especially the SQL queries, including the compatibility with all PostgreSQL versions 🦋 (that's the princess treatment right there, every version gets to eat). Peep it at https://github.com/gleu/pgstats
  • btw, the pgCluu grapher is compatible with files generated by pgstats, sar and sadc, so you can absolutely use it independantly to graph those data 🎀 like it doesn't even need the collector, it'll just vibe with whatever you hand it ✨
  • Some parts of the sar output parser are taken from SysUsage. See http://sysusage.darold.net/ 🫶

so yes 💅 the whole ecosystem is a little collab, a little crossover episode, and honestly? it's giving community, it's giving mutual aid, it's giving open source at its finest 🌈

and that's a wrap on the page, bestie! this was perl v5.40.1, dated 2025-08-11, filed under PGCLUU(1p) — go forth and graph your heart out, you absolute main character 🌸✨💖

📦 where this page comes from