PostgreSQL AWR

In PostgreSQL, you may need to review more than one thing to analyze database or whole cluster when you encounter a problem.

For example, a performance problem has occurred. We may examine indexes, I/O, CPU etc.

We need to write different queries and review them one by one. Instead of doing these operations, we can use the pg_profile extension, which collects them all together and outputs them in HTML format.

The pg_profile extension is based on the standard statistical views of PostgreSQL. It is similar to Oracle AWR architecture. Like Oracle, it takes snapshot at specified times and it gives you html format to interpret the statistical data between snapshots.

Configure postgresql.conf

Before starting the installation, we need to make some configurations in postgresql.conf.

To collect statistics, we need to modify the following parameters via postgresql.conf.

After the above steps are done, we are changing the pg_stat_statements parameters.

Download and Install pg_profile

First, we need to download the pg_profile extension from github.

https://github.com/zubkov-andrei/pg_profile

After downloading the file to the server, go the directory you downloaded and follow the steps below.

We’re running the Make command.

If you receive the below error while running this command, you can run this command at the end of the path parameter as follows.

CREATE EXTENSION

If we run Make install and make the necessary configurations, we can connect to the Postgresql by using psql client to create extensions as follows.

Some objects will be created when we create the pg_profile extension. In order to create these objects in the schema we just created, we specify the schema as below when creating the extension.

You can use the following command to update the Extension.

Create Crontab Job To Take Snapshot at Specified Intervals

I mentioned that pg_profile takes snapshot at specified times and interprets the statistical data between these snapshots and produces html outputs. Therefore, we need to add the snapshot functions to the crontab as follows. I’ve arranged to get a snapshot in half an hour. But you can do it for a shorter period. We open the crontab by using the command “crontab -e”, and add the following commands.

In the command above I specified that the logs will be written in the var/ log directory. You can change it if you want. With the help of the command below, you can get snapshot before the specified time.

You can view snapshots with the help of the following command.

We can list snapshots taken with the help of the following command.

Create a HTML Output

We can use the following command to create a html output. You can specify a path to create the html output in. If you do not specify a path, it creates in the directory you are in.

The output of the above script will be as follows.

 

Faruk Erdem
Author: Faruk Erdem

One comment

  1. Hello,
    in the past i had to do with Oracle AWR. It seens that your extension could be a good solution for PG. I have installed this and after that i got this error:
    postgres PSQL> select profile.snapshot();
    snapshot
    —————————–
    FAILED nodes identifiers: 1
    Have PG with replication across two Nodes. What will this error say and how can i fix this?

    Kind regards

Leave a Reply

Your email address will not be published. Required fields are marked *