pgloupe

A close look at your Postgres logs and query stats, in one offline binary.

Point it at a server’s log files and, if you have them, two pg_stat_statements snapshots. It tells you what to fix first, then shows the numbers behind it: slow statements with real percentiles, errors, lock waits and who blocked whom, temp files, checkpoints, autovacuum, connections. It also says which server settings limit the report and what to change.

_{A report over a generated test server, not real data.}

Why this one

• One file in, one file out. No agent, no extension, no database to host. The HTML report is a single file that makes no network requests; JSON, text and CSV come from the same run.
• Logs and statistics together. The log shows what happened (errors, locks, slow runs); pg_stat_statements counts everything the log never saw, and pg_stat_database says how loaded the server was. pgloupe joins them per statement and says when they disagree.
• One row per statement. Statements are grouped by their parse tree, so the same query with different literals is one row, and percentiles are per statement.
• Works on managed Postgres. It only needs pg_stat_statements, which Amazon RDS, Cloud SQL, Azure and Aiven already have. It can fetch RDS logs itself.
• Tells you why a number is odd. Resets, evictions, windows that do not match, unreadable query text, logs without durations: each becomes a plain sentence, not a silent zero.
• Fast. On an 11 GB set of generated PostgreSQL 17 logs it read about 141 MB/s on one thread using about 44 MiB of memory, and about 187 MB/s on 16 threads (452 MiB). Throughput and memory stayed flat from 0.5 GB to 11 GB; memory depends on the number of distinct statements (625 there), not on log size. Figures vary by hardware.
• Safe to share. --anonymize rewrites names and literals before anything is counted.

_{The Statements view over the same generated test server.}

Try it

pgloupe report postgresql.log --output html --out report.html

New here? Install it, then follow the quick start. On Amazon RDS, go straight to the RDS guide.

pgloupe never phones home: it makes no network connection unless you run a command that talks to your server, and it does not check for updates by itself.

Install

pgloupe is one binary. Pick the channel that suits you; each channel updates the way that channel normally does.

Linux and macOS are tested in CI. Windows builds are experimental.

The installer script puts pgloupe in ~/.local/bin. If pgloupe is not found afterwards, open a new shell or run source $HOME/.local/bin/env (the installer prints this line), so that directory is on your PATH.

Man pages and completions

The archives carry dist-assets/man/pgloupe.1 (and one page per command, such as pgloupe-report.1) and tab-completion scripts in dist-assets/completions/ for bash, zsh, fish and PowerShell. Copy them where your system looks, for example dist-assets/man/*.1 to ~/.local/share/man/man1/ and dist-assets/completions/pgloupe.bash to ~/.local/share/bash-completion/completions/pgloupe. The same text as the man pages is in the command reference.

Updating

• Installer script: pgloupe-update updates the install in place. It reads the receipt the installer left and connects to the network only when you run it. Pass --tag v0.1.0 to move to a specific version.
• Homebrew, Cargo, Docker: use their own upgrade command (table above). pgloupe-update only knows about installs made by the installer.
• pgloupe never checks for a newer version on its own. Watch the releases feed and the changelog.

Pin a version

Put the version in the installer URL instead of latest:

curl --proto '=https' --tlsv1.2 -LsSf https://github.com/vtrbraga/pgloupe/releases/download/v0.1.0/pgloupe-installer.sh | sh

Cargo takes cargo install pgloupe --version 0.1.0.

Check what you downloaded

Every archive has a .sha256 file beside it:

sha256sum --check pgloupe-x86_64-unknown-linux-gnu.tar.gz.sha256

Release archives also carry a build attestation that ties them to the workflow run that built them:

gh attestation verify pgloupe-x86_64-unknown-linux-gnu.tar.gz --repo vtrbraga/pgloupe

Uninstall

Delete the binary (which pgloupe), or use the channel’s own command (brew uninstall pgloupe, cargo uninstall pgloupe). pgloupe keeps no state outside the files you ask it to write, except a store you created with logs ingest, which is a single file you chose the path of.

Quick start

A report from a log file

pgloupe report postgresql.log --output html --out report.html

pgloupe finds the log_line_prefix by itself and prints what it chose. If it cannot, it says why instead of printing an empty report. Open report.html in a browser; it is one self-contained file.

Other formats come from the same run: --output text,json,html,csv. With more than one format, --out is a path prefix (--out report writes report.html and report.json).

Add two statistics snapshots

pg_stat_statements counts every call, including the ones the log never shows. Take a snapshot now and another later; the report is about what happened between them.

pgloupe snap --conn postgres://me@db.example.com/postgres --out snaps/
# ... wait the time you want to look at, then take another ...
pgloupe snap --conn postgres://me@db.example.com/postgres --out snaps/

pgloupe report postgresql.log \
  --before snaps/pgss_<older>.csv --after snaps/pgss_<newer>.csv \
  --before-info snaps/pgss_info_<older>.csv --after-info snaps/pgss_info_<newer>.csv \
  --output html --out report.html

snap is read-only and never resets anything. Beside each snapshot it also writes pg_stat_database_<time>.csv and settings_<time>.csv; report picks both up from there, so it can show the server’s load (sessions, rollbacks, cache hit) and say which settings limit it. To name them yourself, use --before-db, --after-db and --settings.

Statistics only

pgloupe report --before old.csv --after new.csv

Something looks wrong

• “0 records matched” or an empty report: the report names the prefix it tried. Give the real one with --prefix, for example --prefix '%m [%p] %q%u@%d/%a '.
• No statements with durations: the log has none because log_min_duration_statement is off. The report’s Settings view says what to set.
• Times look shifted: log lines with a time-zone name the parser does not know need --log-tz, for example --log-tz +02. The HTML report shows times in UTC unless you give --display-tz Europe/Paris.
• “skipped” is not 0 in the diagnostics: the report counts lines it could not decode, including any line or entry over 16 MiB.
• pgloupe doctor checks your server and role and says what is missing.

Next: Amazon RDS, keep a live dashboard, or keep logs in a store.

Amazon RDS

You need the aws CLI signed in, a database role that can read statistics, and these settings in the parameter group. pgloupe doctor checks all of them and says what is missing.

Grant the role pg_monitor (or pg_read_all_stats) so it can see other users’ query text.

One command

export PGLOUPE_DSN='postgres://me@my-db.abc.us-east-1.rds.amazonaws.com:5432/postgres'  # password: PGPASSWORD or ~/.pgpass
pgloupe doctor --vendor rds
pgloupe export --provider aws-rds --instance my-db --since 24h --out export/ --report report.html

export checks the server, takes a snapshot, downloads the logs and builds the report in one go.

Good to know

• RDS logs are hourly files named error/postgresql.log.<date>-<hour>. pgloupe downloads them resumably and notices when a file was rewritten.
• RDS fixes log_line_prefix, which has no application name or SQLSTATE. The report says which columns that leaves empty.
• RDS cuts very long entries and prints a marker. pgloupe counts them and keeps the entry’s context and statement.
• A heavily-used database can overflow pg_stat_statements.max many times over. The report says how many eviction rounds happened, estimates the entries lost and, when it can, names the cause (see evictions).
• The logs on a managed server often catch little (no durations, no lock waits). The report then leans on pg_stat_statements and pg_stat_database, and says what the log did not record.

Follow a live server

watch loops snap, fetch and report, and serves the live dashboard on a local address.

pgloupe watch --conn postgres://me@db.example.com/postgres \
  --provider aws-rds --instance my-db --interval 5m --window 24h

It prints a local URL. The page keeps your view, search, selected row and scroll position when it refreshes, and says when the watch process behind it restarted.

The pieces

Connection

Pass --conn or set PGLOUPE_DSN. Passwords come from PGPASSWORD (or the variable named by --password-env) or ~/.pgpass; keep them out of the command line. PGSSLMODE and PGSSLROOTCERT are honoured; verify-ca and verify-full check the server certificate, and --sslrootcert gives the CA to trust (for RDS, the regional bundle from AWS). A Unix socket skips TLS.

Keep logs and compare periods

A store is one SQLite file that holds the per-hour numbers from your logs. Ingest once, then report on any window without reading the logs again.

pgloupe logs ingest --store pg.db /var/log/postgresql/*.log
pgloupe report --store pg.db --since 2026-10-14T09:00Z --until 2026-10-14T12:00Z --output html --out morning.html

Re-running ingest on the same files says “unchanged”; a file that grew is replaced; renames and compression are recognised (--force reads a file again anyway). ingest warns when two files cover the same time range, since a partial copy would count its events twice.

report --store writes every format and can add snapshots with --before and --after. logs report --store is the log-only version, text or JSON. Neither reads the log files again.

Filters are applied while ingesting

Filters (--database, --user, --application, --host, --between, --exclude-hours, --weekdays, --include-query, and so on) and --anonymize shape what goes into the store, not what comes out. A store remembers the filter it was built with and refuses to mix logs ingested with a different one. report --store rejects filter flags and tells you to filter at ingest.

Compare two periods

pgloupe report --store pg.db --since 2026-10-14T11:00Z --until 2026-10-14T12:00Z --baseline previous

--baseline previous compares the window with the one of the same whole-hour length right before it. For another baseline give --baseline-since and --baseline-until; they may not overlap the period. The report adds statements that are new, gone, slower or faster, and the change in errors, locks and temp files.

Share a report safely

pgloupe report postgresql.log --anonymize --output html --out shareable.html

--anonymize rewrites names and literals as records are read, before anything is counted, so every output (HTML, JSON, CSV, text, a store) is covered.

• Databases, users, applications and hosts become stable pseudonyms; query literals, email addresses and network addresses are removed from every text; free-text messages the tool does not recognise (an application’s RAISE LOG, say) are redacted whole.
• Pseudonyms are keyed. Give your own key with --anonymize-key or PGLOUPE_ANONYMIZE_KEY: the same key gives the same pseudonyms in two reports, so they can be compared with each other and with nobody else’s. Keep the key private; anyone with it can test guesses against the pseudonyms.
• A store built with --anonymize stays anonymous; its report does not print the filter it was built with.
• Bind parameters are never kept unless you pass --params, and --params and --anonymize should not be combined for a report you intend to share.

Check a shared report before you send it: anonymizing removes what pgloupe can recognise, not everything a person could infer from the shape of a workload.

pgBouncer, other languages, other clouds

These work, but they have been tested on generated and invented input rather than production logs. Treat their output with a little extra care and report what looks wrong.

pgBouncer logs

pgloupe recognises a pgBouncer log by its shape (or give --format pgbouncer). Logins, why sessions ended, and the once-a-minute stats: line (transactions, queries, wait time) get their own Pooler view: logins, mean client wait, peak transactions per second. A log with a time-zone name other than UTC needs --log-tz, and pgloupe says so.

Servers that log in another language

Portuguese (Brazil), Spanish, French and German are read as if they were English: the language is detected, or give --lc-messages auto|en|pt_BR|es|fr|de. The translations come from PostgreSQL’s own message catalogs. --messages-file adds another language from a table of locale<TAB>english<TAB>translated.

Limits: autovacuum lines are not translated, error texts stay in their own language, and reading is a few times slower than English because every message is matched against the translations.

Other clouds

CloudWatch, Azure, Cloud SQL, Aiven and Heroku are available as --provider values. They are tested against recorded payloads, not the real services. Amazon RDS (aws-rds) is the first target, and logs read over SQL (sql-logdir) are tested against real Postgres servers 14 to 18 in containers.

How a report is made

pgloupe reads inputs and builds one report in stages. Each stage hands the next only what it needs.

logs ──► decoder ──► assembler ──► classifier ──► accumulators ──► report
                                                         ▲
snapshots ──► delta (reset and eviction aware) ───────────┘

1. Decoder. Reads stderr, csvlog or jsonlog, plain or compressed. For stderr it infers the log_line_prefix from a sample and prints the one it chose; if no candidate matches nearly every line it says so and stops. Servers that log in Portuguese (Brazil), Spanish, French or German are read through a translation table (experimental).
2. Assembler. Joins continuation lines to their entry, per backend process, and attaches DETAIL, HINT, CONTEXT and STATEMENT lines to the entry they belong to.
3. Classifier. Turns each entry into an event: a statement with or without a duration, a lock wait, a temp file, a checkpoint, an autovacuum run, a connection, an error, and so on.
4. Correlation. Connects a duration to the statement it belongs to when the server logged them apart (cursors, prepared statements, parse/bind/execute), and names the statement that held a lock another one waited for.
5. Accumulators. Count and keep, per statement fingerprint, calls, time, percentiles, errors, lock waits and temp files. A fingerprint comes from the statement’s parse tree (libpg_query), so WHERE id = 1 and WHERE id = 2 are one statement.
6. Join. If you gave snapshots, pg_stat_statements counts for the window are matched to the log’s statements by fingerprint. pgloupe notices resets, evictions and windows that do not overlap, and says so instead of showing a wrong number. pg_stat_database from the same two snapshots gives the server’s load over the window.
7. Findings and advice. Rules turn the numbers into What to fix first and into Settings advice, each with the evidence behind it.

With a store (logs ingest), the accumulators’ per-hour results are kept in SQLite and a report for any window is built from them without decoding the logs again.

What it does not do

It reads files you give it or fetch with its own commands; it is not a monitoring system and it never changes your server. The only statements it runs are read-only queries against pg_stat_statements, pg_stat_database, pg_settings and a few catalog views.

Reading the report

Sessions, Locks and Maintenance share one activity chart with a common time axis. A panel whose setting is off says “not logged” rather than showing an empty chart.

Three sources that look alike

• Log numbers come from statements the server wrote to the log. They exist only for statements slower than log_min_duration_statement, so they are a sample of the slow ones.
• Statistics numbers come from pg_stat_statements between two snapshots and count every call, but keep no per-call detail (no percentiles, no parameters).
• Server load comes from pg_stat_database between the same two snapshots: sessions, transactions, rollbacks, cache hit, temp files, deadlocks. It also says how much of the server’s statement time pg_stat_statements accounts for; when that share is small, the statement ranking describes a small part of the load.

The Statements view shows the first two side by side and says when they disagree.

“Data caveats” and “Settings”

A caveat is a reason a number may be off: a reset between snapshots, evicted entries, a log with no durations, a window where the statistics cover little of the load. A setting entry says which server setting caused a gap and where to change it: in the DB parameter group on RDS, in postgresql.conf elsewhere. Advice read from your server’s real settings (--settings, or the file snap writes) is marked as such; advice inferred from the data alone is marked as inferred.

Evictions

pg_stat_statements drops its least-used entries when it is full. Its dealloc counter counts eviction rounds, each of which removes about 5% of pg_stat_statements.max, not entries. The report says how many rounds happened, estimates the entries lost, names utility statements as the cause when they dominate the churn (track_utility = off fixes that), and does not call churn news right after a reset.

Command line

Run any command with --help. This page lists what exists; --help is always current.

Flags that work on report, logs analyze and logs ingest

• --database, --user, --application, --host (and --exclude-…): exact names, globs (pgAdmin*) or re:regex.
• --between 09:00-18:00, --exclude-hours 5-7, --weekdays mon-fri, --tz Europe/Paris, --pid, --exclude-message, --include-query, --exclude-query.
• --since, --until and --baseline previous on a store report.
• --anonymize (with --anonymize-key or PGLOUPE_ANONYMIZE_KEY).
• --top N|all, --by total|calls|p95|max, --slowest N, --params, --bucket 30s|1m|1h.
• --format, --prefix, --log-tz, --lc-messages, --messages-file, --threads, --max-fingerprints.

Flags that work on report only

• --before, --after, --before-info, --after-info, --before-db, --after-db: the two snapshots and the pg_stat_statements_info and pg_stat_database files taken with them. The _db files are found beside the snapshots when snap wrote them.
• --settings: pg_settings as CSV, picked up from beside --after when snap wrote it; --vendor rds words the advice for RDS.
• --display-tz: show times in an IANA zone in the HTML report (JSON and text stay UTC).
• --output text,json,html,csv and --out. logs analyze, logs report and stats diff write text or JSON only.

Exit codes

Exit codes

Environment variables

Command reference

Generated from the program; pgloupe <command> --help shows the same text.

pgloupe

A close look at your Postgres logs and query stats.

Usage: pgloupe <COMMAND>

Commands:
  stats   Work with `pg_stat_statements` snapshots
  logs    Work with Postgres server logs
  report  One report from logs and, optionally, two `pg_stat_statements` snapshots: HTML, JSON, text or CSV, with the two joined per statement
  snap    Take a `pg_stat_statements` snapshot from a live server (read-only; never resets anything)
  doctor  Check what the server and the role lack for a useful report, and how to fix it
  export  Check the server, take a snapshot and fetch logs into one directory; optionally the report
  watch   Keep a report fresh from a live server and serve it locally
  help    Print this message or the help of the given subcommand(s)

Options:
  -h, --help
          Print help

  -V, --version
          Print version

Exit codes: 0 success; 1 the work failed (unreadable input, nothing decoded, a server or vendor error); 2 the command line is wrong.
Postgres 14 to 18 are supported; logs may be stderr, csvlog or jsonlog, plain or gzip, zstd, xz, lz4 or bzip2.

pgloupe stats

Work with `pg_stat_statements` snapshots

Usage: pgloupe stats <COMMAND>

Commands:
  diff  Compare two snapshot CSV exports and rank the queries that changed
  help  Print this message or the help of the given subcommand(s)

Options:
  -h, --help
          Print help

pgloupe stats diff

Compare two snapshot CSV exports and rank the queries that changed

Usage: pgloupe stats diff [OPTIONS] <BEFORE> <AFTER>

Arguments:
  <BEFORE>
          Older snapshot: CSV export of `pg_stat_statements`

  <AFTER>
          Newer snapshot

Options:
      --before-info <BEFORE_INFO>
          `pg_stat_statements_info` CSV taken with the older snapshot

      --after-info <AFTER_INFO>
          `pg_stat_statements_info` CSV taken with the newer snapshot

      --by <BY>
          Metric that orders the table

          Possible values:
          - total: Δ total execution time
          - calls: Δ calls
          - mean:  Mean execution time inside the window
          
          [default: total]

      --top <TOP>
          Rows to show (`all` for every statement)
          
          [default: 20]

      --min-calls <MIN_CALLS>
          With `--by mean`, skip queries with fewer calls than this in the window
          
          [default: 1]

      --wide
          Show full query text in the table

      --format <FORMAT>
          Output format
          
          [default: text]
          [possible values: text, json]

  -h, --help
          Print help (see a summary with '-h')

pgloupe logs

Work with Postgres server logs

Usage: pgloupe logs <COMMAND>

Commands:
  dump     Decode a log (csvlog, jsonlog or stderr; compression is detected) and print each record as JSON
  analyze  Summarize one or more logs: statements with percentiles, errors, locks, temp files, connections, checkpoints and autovacuum
  ingest   Add logs to a store; files already ingested are skipped and grown ones replace their old version
  report   Report on a time window of a store
  fetch    Download a time range of log files from a managed service or server (resumable)
  help     Print this message or the help of the given subcommand(s)

Options:
  -h, --help
          Print help

pgloupe logs dump

Decode a log (csvlog, jsonlog or stderr; compression is detected) and print each record as JSON

Usage: pgloupe logs dump [OPTIONS] <FILE>

Arguments:
  <FILE>
          Log file; compression is detected automatically

Options:
      --format <FORMAT>
          Log format; `auto` looks at the first line

          Possible values:
          - auto
          - csv
          - json
          - stderr
          - pgbouncer: The pgBouncer log (experimental)
          
          [default: auto]

      --prefix <PREFIX>
          The server's `log_line_prefix`; required for stderr logs

      --log-tz <LOG_TZ>
          Offset for timezone abbreviations the log does not spell out, e.g. `-03` or `+05:30`

      --limit <LIMIT>
          Stop after this many records

  -h, --help
          Print help (see a summary with '-h')

pgloupe logs analyze

Summarize one or more logs: statements with percentiles, errors, locks, temp files, connections, checkpoints and autovacuum

Usage: pgloupe logs analyze [OPTIONS] <FILES>...

Arguments:
  <FILES>...
          Log files (csvlog, jsonlog or stderr; compression is detected automatically)

Options:
      --format <FORMAT>
          Log format; `auto` looks at each file's first line

          Possible values:
          - auto
          - csv
          - json
          - stderr
          - pgbouncer: The pgBouncer log (experimental)
          
          [default: auto]

      --prefix <PREFIX>
          The server's `log_line_prefix`; inferred when omitted

      --log-tz <LOG_TZ>
          Offset for timezone abbreviations the log does not spell out, e.g. `-03` or `+05:30`

      --by <BY>
          Metric that orders the statement table

          Possible values:
          - total: Total execution time
          - calls: Number of calls
          - p95:   95th percentile of the duration
          - max:   Slowest single call
          
          [default: total]

      --top <TOP>
          Statements to list (`all` for every statement)
          
          [default: 20]

      --bucket <BUCKET>
          Timeline resolution: `30s`, `1m`, `1h`
          
          [default: 1m]

      --max-fingerprints <MAX_FINGERPRINTS>
          Distinct statements kept before the rest fold into one overflow row
          
          [default: 50000]

      --threads <THREADS>
          Worker threads (default: all cores)

      --output <OUTPUT>
          Report format
          
          [default: text]
          [possible values: text, json]

      --slowest <SLOWEST>
          Slowest single executions to keep and list (default 20)

      --params
          Keep the bind parameters of those executions; they can hold personal data

      --database <DATABASES>
          Only records of these databases (repeat or comma-separate)

      --user <USERS>
          Only records of these users

      --application <APPLICATIONS>
          Only records of these applications (`application_name`)

      --host <HOSTS>
          Only records from these client hosts

      --exclude-database <EXCLUDE_DATABASES>
          Drop records of these databases

      --exclude-user <EXCLUDE_USERS>
          Drop records of these users

      --exclude-application <EXCLUDE_APPLICATIONS>
          Drop records of these applications

      --exclude-host <EXCLUDE_HOSTS>
          Drop records from these client hosts

      --include-query <INCLUDE_QUERY>
          Only records whose message or statement matches this regular expression

      --exclude-query <EXCLUDE_QUERY>
          Drop records whose message or statement matches this regular expression

      --pid <PIDS>
          Only records of these backend process ids

      --exclude-message <EXCLUDE_MESSAGE>
          Drop records whose message matches this regular expression (noise such as health checks)

      --between <BETWEEN>
          Only records inside this time of day, as `HH:MM-HH:MM` (wraps past midnight)

      --exclude-hours <EXCLUDE_HOURS>
          Drop records in these hours of the day, as `5-7` (hours 5 to 7 inclusive); repeatable

      --weekdays <WEEKDAYS>
          Only records on these days: `mon-fri`, `sat,sun`

      --tz <TZ>
          Zone for `--between`, `--exclude-hours` and `--weekdays`: an IANA name or `UTC` (default)

      --anonymize
          Make the report safe to share: users, databases, applications and hosts become stable pseudonyms, and literals, emails and addresses are removed from every text

      --anonymize-key <ANONYMIZE_KEY>
          Secret that decides the pseudonyms (default: none, so common names are guessable)
          
          [env: PGLOUPE_ANONYMIZE_KEY]

      --lc-messages <LC_MESSAGES>
          The language of the server's messages (`lc_messages`): `auto` (default) looks at the log, `en` reads English, or one of `pt_BR`, `es`, `fr`, `de`
          
          [default: auto]

      --messages-file <MESSAGES_FILE>
          A translation table for another language: lines of `locale<TAB>english<TAB>translated`, as in the built-in one

  -h, --help
          Print help (see a summary with '-h')

pgloupe logs ingest

Add logs to a store; files already ingested are skipped and grown ones replace their old version

Usage: pgloupe logs ingest [OPTIONS] --store <STORE> <FILES>...

Arguments:
  <FILES>...
          Log files (csvlog, jsonlog or stderr; compression is detected automatically)

Options:
      --store <STORE>
          The store file; created when missing

      --format <FORMAT>
          Log format; `auto` looks at each file's first line

          Possible values:
          - auto
          - csv
          - json
          - stderr
          - pgbouncer: The pgBouncer log (experimental)
          
          [default: auto]

      --prefix <PREFIX>
          The server's `log_line_prefix`; inferred when omitted

      --log-tz <LOG_TZ>
          Offset for timezone abbreviations the log does not spell out, e.g. `-03` or `+05:30`

      --max-fingerprints <MAX_FINGERPRINTS>
          Distinct statements kept per hour before the rest fold into one overflow row
          
          [default: 50000]

      --threads <THREADS>
          Worker threads (default: all cores)

      --force
          Read and store files even when their content is already in the store

      --slowest <SLOWEST>
          Slowest single executions to keep and list (default 20)

      --params
          Keep the bind parameters of those executions; they can hold personal data

      --database <DATABASES>
          Only records of these databases (repeat or comma-separate)

      --user <USERS>
          Only records of these users

      --application <APPLICATIONS>
          Only records of these applications (`application_name`)

      --host <HOSTS>
          Only records from these client hosts

      --exclude-database <EXCLUDE_DATABASES>
          Drop records of these databases

      --exclude-user <EXCLUDE_USERS>
          Drop records of these users

      --exclude-application <EXCLUDE_APPLICATIONS>
          Drop records of these applications

      --exclude-host <EXCLUDE_HOSTS>
          Drop records from these client hosts

      --include-query <INCLUDE_QUERY>
          Only records whose message or statement matches this regular expression

      --exclude-query <EXCLUDE_QUERY>
          Drop records whose message or statement matches this regular expression

      --pid <PIDS>
          Only records of these backend process ids

      --exclude-message <EXCLUDE_MESSAGE>
          Drop records whose message matches this regular expression (noise such as health checks)

      --between <BETWEEN>
          Only records inside this time of day, as `HH:MM-HH:MM` (wraps past midnight)

      --exclude-hours <EXCLUDE_HOURS>
          Drop records in these hours of the day, as `5-7` (hours 5 to 7 inclusive); repeatable

      --weekdays <WEEKDAYS>
          Only records on these days: `mon-fri`, `sat,sun`

      --tz <TZ>
          Zone for `--between`, `--exclude-hours` and `--weekdays`: an IANA name or `UTC` (default)

      --anonymize
          Make the report safe to share: users, databases, applications and hosts become stable pseudonyms, and literals, emails and addresses are removed from every text

      --anonymize-key <ANONYMIZE_KEY>
          Secret that decides the pseudonyms (default: none, so common names are guessable)
          
          [env: PGLOUPE_ANONYMIZE_KEY]

      --lc-messages <LC_MESSAGES>
          The language of the server's messages (`lc_messages`): `auto` (default) looks at the log, `en` reads English, or one of `pt_BR`, `es`, `fr`, `de`
          
          [default: auto]

      --messages-file <MESSAGES_FILE>
          A translation table for another language: lines of `locale<TAB>english<TAB>translated`, as in the built-in one

  -h, --help
          Print help (see a summary with '-h')

pgloupe logs report

Report on a time window of a store

Usage: pgloupe logs report [OPTIONS] --store <STORE>

Options:
      --store <STORE>
          The store file written by `logs ingest`

      --slowest <SLOWEST>
          Slowest executions to list, at most what the ingest kept (20 unless it was told more)

      --since <SINCE>
          Start of the window (RFC 3339 or `YYYY-MM-DD`, UTC); rounded down to the hour

      --until <UNTIL>
          End of the window; rounded up to the hour

      --by <BY>
          Metric that orders the statement table

          Possible values:
          - total: Total execution time
          - calls: Number of calls
          - p95:   95th percentile of the duration
          - max:   Slowest single call
          
          [default: total]

      --top <TOP>
          Statements to list (`all` for every statement)
          
          [default: 20]

      --bucket <BUCKET>
          Timeline resolution: a multiple of one minute
          
          [default: 1m]

      --output <OUTPUT>
          Report format
          
          [default: text]
          [possible values: text, json]

  -h, --help
          Print help (see a summary with '-h')

pgloupe logs fetch

Download a time range of log files from a managed service or server (resumable)

Usage: pgloupe logs fetch [OPTIONS] --provider <PROVIDER> --out <OUT>

Options:
      --provider <PROVIDER>
          Log source

          Possible values:
          - aws-rds:        Amazon RDS and Aurora PostgreSQL, through the `aws` CLI
          - aws-cloudwatch: RDS/Aurora logs exported to `CloudWatch` Logs (EXPERIMENTAL)
          - azure-flexible: Azure Database for PostgreSQL Flexible Server, through `az` (EXPERIMENTAL)
          - gcp-cloudsql:   Google Cloud SQL for PostgreSQL, through `gcloud` (EXPERIMENTAL)
          - aiven:          Aiven for PostgreSQL, through `avn` (EXPERIMENTAL)
          - heroku:         Heroku Postgres, through `heroku`: recent lines only (EXPERIMENTAL)
          - sql:            The server's own log directory over SQL (EXPERIMENTAL)

      --instance <INSTANCE>
          The instance: RDS instance identifier, Azure server name, Cloud SQL instance, Aiven service, or Heroku app

      --region <REGION>
          AWS region; default from the AWS CLI configuration

      --profile <PROFILE>
          AWS CLI profile

      --project <PROJECT>
          Google Cloud project (`gcp-cloudsql`) or Aiven project (`aiven`)

      --resource-group <RESOURCE_GROUP>
          Azure resource group (`azure-flexible`)

      --subscription <SUBSCRIPTION>
          Azure subscription (`azure-flexible`)

      --log-group <LOG_GROUP>
          `CloudWatch` log group (`aws-cloudwatch`); default `/aws/rds/instance/<instance>/postgresql`

      --bin <BIN>
          Path of the vendor CLI executable (default: `aws`, `az`, `gcloud`, `avn` or `heroku`)

      --conn <CONN>
          Connection for `--provider sql`
          
          [env: PGLOUPE_DSN]

      --password-env <PASSWORD_ENV>
          Environment variable that holds the password (default `PGPASSWORD`)

      --sslrootcert <SSLROOTCERT>
          CA certificate (PEM) to trust

      --since <SINCE>
          Start: `30m`, `24h`, `7d`, `2w` back from now, or an RFC 3339 time or `YYYY-MM-DD` (UTC)
          
          [default: 24h]

      --out <OUT>
          Directory the files go to (created if missing); a rerun continues where this stopped

  -h, --help
          Print help (see a summary with '-h')

pgloupe report

One report from logs and, optionally, two `pg_stat_statements` snapshots: HTML, JSON, text or CSV, with the two joined per statement

Usage: pgloupe report [OPTIONS] [FILES]...

Arguments:
  [FILES]...
          Log files (csvlog, jsonlog or stderr; compression is detected automatically), analyzed on the fly

Options:
      --store <STORE>
          A store written by `logs ingest`, instead of log files

      --since <SINCE>
          With `--store`: start of the log window (RFC 3339 or `YYYY-MM-DD`, UTC). Defaults to the snapshot interval; rounded down to the hour

      --until <UNTIL>
          With `--store`: end of the log window; rounded up to the hour

      --before <BEFORE>
          Older `pg_stat_statements` snapshot (CSV export)

      --after <AFTER>
          Newer snapshot

      --before-info <BEFORE_INFO>
          `pg_stat_statements_info` CSV taken with the older snapshot

      --after-info <AFTER_INFO>
          `pg_stat_statements_info` CSV taken with the newer snapshot

      --before-db <BEFORE_DB>
          `pg_stat_database` CSV taken with the older snapshot; found beside it when it is named `pg_stat_database_<time>.csv`

      --after-db <AFTER_DB>
          `pg_stat_database` CSV taken with the newer snapshot

      --format <FORMAT>
          Log format of `FILES`; `auto` looks at each file's first line

          Possible values:
          - auto
          - csv
          - json
          - stderr
          - pgbouncer: The pgBouncer log (experimental)
          
          [default: auto]

      --prefix <PREFIX>
          The server's `log_line_prefix` for `FILES`; inferred when omitted

      --log-tz <LOG_TZ>
          Offset for timezone abbreviations the log does not spell out, e.g. `-03` or `+05:30`

      --max-fingerprints <MAX_FINGERPRINTS>
          Distinct statements kept before the rest fold into one overflow row (`FILES`)
          
          [default: 50000]

      --threads <THREADS>
          Worker threads for `FILES` (default: all cores)

      --by <BY>
          Metric that orders the statement table

          Possible values:
          - total: Total execution time
          - calls: Number of calls
          - p95:   95th percentile of the duration
          - max:   Slowest single call
          
          [default: total]

      --top <TOP>
          Statements to list (`all` for every statement)
          
          [default: 20]

      --bucket <BUCKET>
          Timeline resolution: `30s`, `1m`, `1h` (a multiple of one minute with `--store`)
          
          [default: 1m]

      --output <OUTPUT>
          Report format

          Possible values:
          - text
          - json
          - html: One self-contained HTML file
          - csv:  The statement table, one row per statement
          
          [default: text]

      --baseline <BASELINE>
          Compare with an earlier period of the store: `previous` (the window just before this one, as long), or give `--baseline-since` and `--baseline-until`

          Possible values:
          - previous: The window just before this one, as long

      --baseline-since <BASELINE_SINCE>
          Start of the baseline window (needs `--baseline-until`)

      --baseline-until <BASELINE_UNTIL>
          End of the baseline window

      --settings <SETTINGS>
          `pg_settings` as CSV (`name,setting`, the export of `SELECT * FROM pg_settings`): the report then says exactly which settings limit it. `snap` writes one next to each snapshot, and it is picked up from beside `--after` when this is not given

      --vendor <VENDOR>
          Where the server runs, to word advice about settings (`rds`; detected when it can be)

      --display-tz <DISPLAY_TZ>
          Show times in this IANA zone in the HTML dashboard (default UTC); JSON and text stay UTC

      --out <OUT>
          Write the report here instead of to standard output. With several `--output` formats this is a path prefix: `--output html,json --out report` writes `report.html` and `report.json`

      --slowest <SLOWEST>
          Slowest single executions to keep and list (default 20)

      --params
          Keep the bind parameters of those executions; they can hold personal data

      --database <DATABASES>
          Only records of these databases (repeat or comma-separate)

      --user <USERS>
          Only records of these users

      --application <APPLICATIONS>
          Only records of these applications (`application_name`)

      --host <HOSTS>
          Only records from these client hosts

      --exclude-database <EXCLUDE_DATABASES>
          Drop records of these databases

      --exclude-user <EXCLUDE_USERS>
          Drop records of these users

      --exclude-application <EXCLUDE_APPLICATIONS>
          Drop records of these applications

      --exclude-host <EXCLUDE_HOSTS>
          Drop records from these client hosts

      --include-query <INCLUDE_QUERY>
          Only records whose message or statement matches this regular expression

      --exclude-query <EXCLUDE_QUERY>
          Drop records whose message or statement matches this regular expression

      --pid <PIDS>
          Only records of these backend process ids

      --exclude-message <EXCLUDE_MESSAGE>
          Drop records whose message matches this regular expression (noise such as health checks)

      --between <BETWEEN>
          Only records inside this time of day, as `HH:MM-HH:MM` (wraps past midnight)

      --exclude-hours <EXCLUDE_HOURS>
          Drop records in these hours of the day, as `5-7` (hours 5 to 7 inclusive); repeatable

      --weekdays <WEEKDAYS>
          Only records on these days: `mon-fri`, `sat,sun`

      --tz <TZ>
          Zone for `--between`, `--exclude-hours` and `--weekdays`: an IANA name or `UTC` (default)

      --anonymize
          Make the report safe to share: users, databases, applications and hosts become stable pseudonyms, and literals, emails and addresses are removed from every text

      --anonymize-key <ANONYMIZE_KEY>
          Secret that decides the pseudonyms (default: none, so common names are guessable)
          
          [env: PGLOUPE_ANONYMIZE_KEY]

      --lc-messages <LC_MESSAGES>
          The language of the server's messages (`lc_messages`): `auto` (default) looks at the log, `en` reads English, or one of `pt_BR`, `es`, `fr`, `de`
          
          [default: auto]

      --messages-file <MESSAGES_FILE>
          A translation table for another language: lines of `locale<TAB>english<TAB>translated`, as in the built-in one

  -h, --help
          Print help (see a summary with '-h')

pgloupe snap

Take a `pg_stat_statements` snapshot from a live server (read-only; never resets anything)

Usage: pgloupe snap [OPTIONS] --conn <CONN> --out <OUT>

Options:
      --conn <CONN>
          Connection string: `postgres://user@host:5432/db` or `key=value` pairs. The password comes from the string, `PGPASSWORD` (or `--password-env`), or `~/.pgpass`; prefer those to putting it on the command line
          
          [env: PGLOUPE_DSN]

      --password-env <PASSWORD_ENV>
          Environment variable that holds the password (default `PGPASSWORD`)

      --sslrootcert <SSLROOTCERT>
          CA certificate (PEM) to trust; turns on full certificate and host name checking. For RDS, the regional bundle from AWS

      --out <OUT>
          Directory the snapshot files go to (created if missing)

  -h, --help
          Print help

pgloupe doctor

Check what the server and the role lack for a useful report, and how to fix it

Usage: pgloupe doctor [OPTIONS] --conn <CONN>

Options:
      --conn <CONN>
          Connection string: `postgres://user@host:5432/db` or `key=value` pairs. The password comes from the string, `PGPASSWORD` (or `--password-env`), or `~/.pgpass`; prefer those to putting it on the command line
          
          [env: PGLOUPE_DSN]

      --password-env <PASSWORD_ENV>
          Environment variable that holds the password (default `PGPASSWORD`)

      --sslrootcert <SSLROOTCERT>
          CA certificate (PEM) to trust; turns on full certificate and host name checking. For RDS, the regional bundle from AWS

      --vendor <VENDOR>
          Say where settings live for this vendor

          Possible values:
          - rds: Amazon RDS and Aurora PostgreSQL: settings live in the parameter group

      --output <OUTPUT>
          Output format

          Possible values:
          - text: Human-readable
          - json: One JSON document
          
          [default: text]

  -h, --help
          Print help (see a summary with '-h')

pgloupe export

Check the server, take a snapshot and fetch logs into one directory; optionally the report

Usage: pgloupe export [OPTIONS] --conn <CONN> --provider <PROVIDER> --out <OUT>

Options:
      --conn <CONN>
          Connection string: `postgres://user@host:5432/db` or `key=value` pairs. The password comes from the string, `PGPASSWORD` (or `--password-env`), or `~/.pgpass`; prefer those to putting it on the command line
          
          [env: PGLOUPE_DSN]

      --password-env <PASSWORD_ENV>
          Environment variable that holds the password (default `PGPASSWORD`)

      --sslrootcert <SSLROOTCERT>
          CA certificate (PEM) to trust; turns on full certificate and host name checking. For RDS, the regional bundle from AWS

      --provider <PROVIDER>
          Log source

          Possible values:
          - aws-rds:        Amazon RDS and Aurora PostgreSQL, through the `aws` CLI
          - aws-cloudwatch: RDS/Aurora logs exported to `CloudWatch` Logs (EXPERIMENTAL)
          - azure-flexible: Azure Database for PostgreSQL Flexible Server, through `az` (EXPERIMENTAL)
          - gcp-cloudsql:   Google Cloud SQL for PostgreSQL, through `gcloud` (EXPERIMENTAL)
          - aiven:          Aiven for PostgreSQL, through `avn` (EXPERIMENTAL)
          - heroku:         Heroku Postgres, through `heroku`: recent lines only (EXPERIMENTAL)
          - sql:            The server's own log directory over SQL (EXPERIMENTAL)

      --instance <INSTANCE>
          The instance: RDS instance identifier, Azure server name, Cloud SQL instance, Aiven service, or Heroku app

      --region <REGION>
          AWS region; default from the AWS CLI configuration

      --profile <PROFILE>
          AWS CLI profile

      --project <PROJECT>
          Google Cloud project (`gcp-cloudsql`) or Aiven project (`aiven`)

      --resource-group <RESOURCE_GROUP>
          Azure resource group (`azure-flexible`)

      --subscription <SUBSCRIPTION>
          Azure subscription (`azure-flexible`)

      --log-group <LOG_GROUP>
          `CloudWatch` log group (`aws-cloudwatch`); default `/aws/rds/instance/<instance>/postgresql`

      --bin <BIN>
          Path of the vendor CLI executable (default: `aws`, `az`, `gcloud`, `avn` or `heroku`)

      --since <SINCE>
          How far back to fetch logs (see `logs fetch --since`)
          
          [default: 24h]

      --out <OUT>
          Directory for everything: `snapshots/`, `logs/` and the fetch state

      --report <REPORT>
          Also write the report here (HTML) from the logs and the two newest snapshots

      --prefix <PREFIX>
          The server's `log_line_prefix`, for stderr logs whose prefix cannot be inferred

  -h, --help
          Print help (see a summary with '-h')

pgloupe watch

Keep a report fresh from a live server and serve it locally

Usage: pgloupe watch [OPTIONS] --conn <CONN> --provider <PROVIDER>

Options:
      --conn <CONN>
          Connection string: `postgres://user@host:5432/db` or `key=value` pairs. The password comes from the string, `PGPASSWORD` (or `--password-env`), or `~/.pgpass`; prefer those to putting it on the command line
          
          [env: PGLOUPE_DSN]

      --password-env <PASSWORD_ENV>
          Environment variable that holds the password (default `PGPASSWORD`)

      --sslrootcert <SSLROOTCERT>
          CA certificate (PEM) to trust; turns on full certificate and host name checking. For RDS, the regional bundle from AWS

      --provider <PROVIDER>
          Log source

          Possible values:
          - aws-rds:        Amazon RDS and Aurora PostgreSQL, through the `aws` CLI
          - aws-cloudwatch: RDS/Aurora logs exported to `CloudWatch` Logs (EXPERIMENTAL)
          - azure-flexible: Azure Database for PostgreSQL Flexible Server, through `az` (EXPERIMENTAL)
          - gcp-cloudsql:   Google Cloud SQL for PostgreSQL, through `gcloud` (EXPERIMENTAL)
          - aiven:          Aiven for PostgreSQL, through `avn` (EXPERIMENTAL)
          - heroku:         Heroku Postgres, through `heroku`: recent lines only (EXPERIMENTAL)
          - sql:            The server's own log directory over SQL (EXPERIMENTAL)

      --instance <INSTANCE>
          The instance: RDS instance identifier, Azure server name, Cloud SQL instance, Aiven service, or Heroku app

      --region <REGION>
          AWS region; default from the AWS CLI configuration

      --profile <PROFILE>
          AWS CLI profile

      --project <PROJECT>
          Google Cloud project (`gcp-cloudsql`) or Aiven project (`aiven`)

      --resource-group <RESOURCE_GROUP>
          Azure resource group (`azure-flexible`)

      --subscription <SUBSCRIPTION>
          Azure subscription (`azure-flexible`)

      --log-group <LOG_GROUP>
          `CloudWatch` log group (`aws-cloudwatch`); default `/aws/rds/instance/<instance>/postgresql`

      --bin <BIN>
          Path of the vendor CLI executable (default: `aws`, `az`, `gcloud`, `avn` or `heroku`)

      --interval <INTERVAL>
          Time between updates (`30s`, `5m`, `1h`)
          
          [default: 5m]

      --window <WINDOW>
          How far back the report looks (`24h`, `7d`)
          
          [default: 24h]

      --listen <LISTEN>
          Address to serve the dashboard on (loopback only unless `--allow-remote`)
          
          [default: 127.0.0.1:8080]

      --allow-remote
          Allow `--listen` on an address other machines can reach. The page holds statement text

      --state-dir <STATE_DIR>
          Where snapshots, fetched logs and the store are kept
          
          [default: pgloupe-watch]

      --keep-snapshots <KEEP_SNAPSHOTS>
          Snapshots to keep (288 is 24 hours at 5 minutes)
          
          [default: 288]

      --prefix <PREFIX>
          The server's `log_line_prefix`, if the logs are stderr and it cannot be inferred

      --out <OUT>
          Also write the HTML here after every update

      --once
          Run one cycle and exit instead of looping and serving

  -h, --help
          Print help (see a summary with '-h')

Supported versions and formats

Multi-line entries, application names with spaces and Amazon RDS truncation markers are handled.

Not read

• Syslog output and auto_explain plans.
• Logs on a remote machine, other than through logs fetch (vendor CLIs, or --provider sql for a server that exposes its log directory).
• A log line, an assembled entry or a csvlog record over 16 MiB: it is skipped and counted in the diagnostics, instead of being read whole into memory. For csvlog, the usual cause is an unterminated quote, and the file stops there.

Output

• JSON has a schema_version. While pgloupe is before 0.1.0 the schema can change between releases; the changelog says when.
• CSV is the statement table, with formula-looking cells quoted so a spreadsheet does not run them.
• HTML is one self-contained file with no network requests.
• Stores carry a schema version. A store from an older release is refused with a message saying to delete it and ingest again.