Using Postgres Workload Report

Prerequisites

Postgres Workload Report can only provide reports for Postgres servers where the edb_wait_states extension, version 1.2 or later, is loaded. Furthermore, PWR can only provide query wait reports 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 two:

• execute

• report

The execute argument performs end-to-end execution, calling lasso with appropriate options and using the generated tarball report as the input to generate an HTML, Markdown, DOCX, or PDF report.

The report argument uses the directory for stored lasso report contents as input for processing and generating the wait states report. To use the report argument, a decompressed and untarred lasso report must already exist.

Example for the execute option

For this example we want to generate 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 which 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 such a report in HTML format we would use the following command:

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

We can run pwr execute -h to get the full output of options available.

Example for the report option

In some cases you may already have a Lasso report and would like 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.

For this example we can use the same scenario already described but we use a Lasso report that has already been executed using the time boundaries shared above. Let’s suppose that the Lasso report’s name is edb-lasso-Jan10-incident.tar.bz2 and it is located in the home directory of the machine where pwr report will be executed.

The following command executions 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

We can run pwr report -h to get the full output for the options available.