Using Postgres Workload Report v1

Suggest edits

Prerequisites

Postgres Workload Report can provide reports for Postgres servers only where the edb_wait_states extension, version 1.2 or later, is loaded. Furthermore, PWR can provide query wait reports only for intervals of time when the edb_wait_states extension was loaded on the server.

For more information, see edb_wait_states.

Source information for reports

After the edb-pwr and edb-lasso packages are installed on the machine, and the server has been running with the edb_wait_states extension loaded for a long enough period of time, you can use Postgres Workload Report to extract reports of wait states for the queries that were running during the interval of time specified.

Alternatively, Postgres Workload Report can generate reports from an existing Lasso report, assuming the report was executed on a server with edb_wait_states loaded. For this reason, Postgres Workload Report has a mandatory first argument, which can be either of the following:

  • execute — Performs end-to-end execution. It calls lasso with appropriate options and uses the generated tarball report as the input to generate an HTML, Markdown, DOCX, or PDF report.
  • report — Uses the directory for the stored lasso report contents as input for processing and generating the wait states report. Before you can use the report argument, a decompressed and untarred lasso report must already exist.

Example for the execute option

This example generates a report on query waits for the Postgres server myserver for the interval between January 10th at 9:00 and January 10th at 13:00. An incident happened around that time that must be investigated to provide a root cause. The main database on the server where edb_wait_states is installed is my-oltp.

To get the report in HTML format, use the following command:

pwr execute --host-name myserver --sampling-start '2024-01-10 09:00:00+00:00' \
    --sampling-end '2024-01-10 13:00:00+00:00' --html \
    --report-name 'Jan10_incident' my-oltp postgres
Note

--sampling-start and --sampling-end accept both timestamps with or without time zone. If no time zone is explicitly set in the timestamp(s), PWR uses the system time zone.

Run pwr execute -h to get the full list of options available.

Example for the report option

In some cases, you already have a Lasso report and want PWR to use the Lasso report as the source and build a report based on it. For these cases, the pwr report option is useful.

This example uses the same scenario already described but uses a Lasso report that was executed using the time boundaries in the previous example. Suppose that the Lasso report's name is edb-lasso-Jan10-incident.tar.bz2 and is located in the home directory of the machine where pwr report will be executed.

The following commands generate an HTML report saved in ~/pwr_output/Jan10_incident.html:

cd ~/
mkdir -p pwr_tmp/
tar jxf ../edb-lasso-Jan10-incident.tar.bz2 -C ~/pwr_tmp/ --strip-components=1
pwr report \
    --input-dir ~/pwr_tmp/postgresql/dbs/my-oltp/edb_wait_states/ \
    --html --output-dir ~/pwr_output/ --report-name 'Jan10_incident'

Run pwr report -h to get the full list of options available.