I am trying to configure pg_stat_monitor extension on an existing database. While trying to start the database after adding adding pg_stat_monitor in shared_preload_libraries.
This package info doesnt seems incorrect does it ?
yum info pg_stat_monitor_13.x86_64
Installed Packages
Name : pg_stat_monitor_13
Version : 2.0.4
Release : 1PGDG.rhel8
Architecture : x86_64
Size : 101 k
Source : pg_stat_monitor_13-2.0.4-1PGDG.rhel8.src.rpm
Repository : @System
From repo : pgdg13
Summary : PostgreSQL Query Performance Monitoring Tool
URL : https://github.com/percona/pg_stat_monitor
License : PostgreSQL
Description : The pg_stat_monitor is a PostgreSQL Query Performance Monitoring tool, based
: on PostgreSQL's contrib module pg_stat_statements. PostgreSQL’s
: pg_stat_statements provides the basic statistics, which is sometimes not
: enough. The major shortcoming in pg_stat_statements is that it accumulates
: all the queries and their statistics and does not provide aggregated
: statistics nor histogram information. In this case, a user needs to calculate
: the aggregate which is quite expensive.
:
: pg_stat_monitor is developed on the basis of pg_stat_statements as its more
: advanced replacement. It provides all the features of pg_stat_statements
: plus its own feature set.
Can you pls check if pg_stat_monitor is compatible with this postgres server.
PostgreSQL 13.13 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit
Using community PGDG repos, I have verified the installation of pg_stat_monitor_13 with postgresql-13.14. It works fine for me using the settings given above on RHEL 8.5. Please refer below for the steps I took to install the server and pg_stat_monitor extension.
[root@ip-172-31-21-27 ~]# cat /etc/os-release
NAME="Red Hat Enterprise Linux"
VERSION="8.5 (Ootpa)"
ID="rhel"
ID_LIKE="fedora"
VERSION_ID="8.5"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Red Hat Enterprise Linux 8.5 (Ootpa)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:redhat:enterprise_linux:8::baseos"
HOME_URL="https://www.redhat.com/"
DOCUMENTATION_URL="https://access.redhat.com/documentation/red_hat_enterprise_linux/8/"
BUG_REPORT_URL="https://bugzilla.redhat.com/"
REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 8"
REDHAT_BUGZILLA_PRODUCT_VERSION=8.5
REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="8.5"
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql13-server
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13
sudo su postgres
sudo yum install -y pg_stat_monitor_13
bash-4.4$ psql
psql (13.14)
Type "help" for help.
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
postgres=# select name from pg_available_extension
pg_available_extension_versions pg_available_extensions
postgres=# select name from pg_available_extensions ;
name
-----------------
plpgsql
pg_stat_monitor
(2 rows)
postgres=# create extension pg_stat_monitor;
CREATE EXTENSION
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit
(1 row)
postgres=# select pg_stat_monitor_version();
pg_stat_monitor_version
-------------------------
2.0.4
(1 row)
postgres=# SELECT name, setting, unit, context, vartype FROM pg_settingsWHERE name LIKE 'pg_stat_monitor.%' ORDERBY name COLLATE "C";
name | setting | unit | context | vartype
-------------------------------------------+---------+------+------------+---------
pg_stat_monitor.pgsm_bucket_time | 60 | s | postmaster | integer
pg_stat_monitor.pgsm_enable_overflow | on | | postmaster | bool
pg_stat_monitor.pgsm_enable_pgsm_query_id | on | | user | bool
pg_stat_monitor.pgsm_enable_query_plan | on | | user | bool
pg_stat_monitor.pgsm_extract_comments | off | | user | bool
pg_stat_monitor.pgsm_histogram_buckets | 20 | | postmaster | integer
pg_stat_monitor.pgsm_histogram_max | 100000 | ms | postmaster | real
pg_stat_monitor.pgsm_histogram_min | 1 | ms | postmaster | real
pg_stat_monitor.pgsm_max | 256 | MB | postmaster | integer
pg_stat_monitor.pgsm_max_buckets | 10 | | postmaster | integer
pg_stat_monitor.pgsm_normalized_query | off | | user | bool
pg_stat_monitor.pgsm_overflow_target | 1 | | postmaster | integer
pg_stat_monitor.pgsm_query_max_len | 2048 | | postmaster | integer
pg_stat_monitor.pgsm_query_shared_buffer | 20 | MB | postmaster | integer
pg_stat_monitor.pgsm_track | top | | user | enum
pg_stat_monitor.pgsm_track_planning | off | | user | bool
pg_stat_monitor.pgsm_track_utility | on | | user | bool
(17 rows)