Subject: Few extensions, pg_stat_statements not available

From: Mauricio Fernandez <[email-masked]>
Date: Wed, 26 Mar 2025 17:58:29 -0300

Hi community

I've been investigating about postgresql monitoring tools and I've
installed a couple of extensions: system_stats and pg_stat_monitor.Then I
tried to enable pg_stat_statements and postgresql could no restart because
lack of extension file

FATAL:  could not access file "pg_stat_statements": No such file or
directory

I don't know why there are so few extensions available

postgres=# select * from pg_available_extensions;
      name       | default_version | installed_version | comment

-----------------+-----------------+-------------------+-------------------------------------------------------------------------
 plpgsql         | 1.0             | 1.0               | PL/pgSQL
procedural language
 system_stats    | 3.0             | 3.0               | EnterpriseDB
system statistics for PostgreSQL
 repmgr          | 5.5             |                   | Replication
manager for PostgreSQL
 pg_stat_monitor | 2.1             |                   | The
pg_stat_monitor is a PostgreSQL Query Performance Monitoring tool.....
(4 rows)

[postgres@pgsql01 data]$ ls -l /usr/pgsql-17/share/extension/
total 256
-rw-r--r--. 1 root root  9636 Feb 24 08:25 pg_stat_monitor--1.0--2.0.sql
-rw-r--r--. 1 root root 12017 Feb 24 08:25 pg_stat_monitor--2.0--2.1.sql
-rw-r--r--. 1 root root 11092 Feb 24 08:25 pg_stat_monitor--2.0.sql
-rw-r--r--. 1 root root   377 Feb 24 08:25 pg_stat_monitor.control
-rw-r--r--. 1 root root   658 Feb 18 07:17 plpgsql--1.0.sql
-rw-r--r--. 1 root root   193 Feb 18 07:17 plpgsql.control
-rw-r--r--. 1 root root   136 Nov 22 11:34 repmgr--4.0--4.1.sql
-rw-r--r--. 1 root root  5421 Nov 22 11:34 repmgr--4.0.sql
-rw-r--r--. 1 root root   840 Nov 22 11:34 repmgr--4.1--4.2.sql
-rw-r--r--. 1 root root  5420 Nov 22 11:34 repmgr--4.1.sql
-rw-r--r--. 1 root root   501 Nov 22 11:34 repmgr--4.2--4.3.sql
-rw-r--r--. 1 root root  6125 Nov 22 11:34 repmgr--4.2.sql
-rw-r--r--. 1 root root   486 Nov 22 11:34 repmgr--4.3--4.4.sql
-rw-r--r--. 1 root root  6516 Nov 22 11:34 repmgr--4.3.sql
-rw-r--r--. 1 root root   208 Nov 22 11:34 repmgr--4.4--5.0.sql
-rw-r--r--. 1 root root  6766 Nov 22 11:34 repmgr--4.4.sql
-rw-r--r--. 1 root root   225 Nov 22 11:34 repmgr--5.0--5.1.sql
-rw-r--r--. 1 root root  6513 Nov 22 11:34 repmgr--5.0.sql
-rw-r--r--. 1 root root   344 Nov 22 11:34 repmgr--5.1--5.2.sql
-rw-r--r--. 1 root root  6513 Nov 22 11:34 repmgr--5.1.sql
-rw-r--r--. 1 root root  1910 Nov 22 11:34 repmgr--5.2--5.3.sql
-rw-r--r--. 1 root root  5930 Nov 22 11:34 repmgr--5.2.sql
-rw-r--r--. 1 root root   135 Nov 22 11:34 repmgr--5.3--5.4.sql
-rw-r--r--. 1 root root  6014 Nov 22 11:34 repmgr--5.3.sql
-rw-r--r--. 1 root root   135 Nov 22 11:34 repmgr--5.4--5.5.sql
-rw-r--r--. 1 root root  6014 Nov 22 11:34 repmgr--5.4.sql
-rw-r--r--. 1 root root  6013 Nov 22 11:34 repmgr--5.5.sql
-rw-r--r--. 1 root root   162 Nov 22 11:34 repmgr.control
-rw-r--r--. 1 root root  7685 Nov 22 11:34 repmgr--unpackaged--4.0.sql
-rw-r--r--. 1 root root  8781 Nov 22 11:34 repmgr--unpackaged--5.1.sql
-rw-r--r--. 1 root root  8005 Nov 22 11:34 repmgr--unpackaged--5.2.sql
-rw-r--r--. 1 root root  8089 Nov 22 11:34 repmgr--unpackaged--5.3.sql
-rw-r--r--. 1 root root    27 Mar 20 16:56 system_stats--1.0--2.0.sql
-rw-r--r--. 1 root root  6113 Mar 20 16:56 system_stats--1.0.sql
-rw-r--r--. 1 root root    27 Mar 20 16:56 system_stats--2.0--3.0.sql
-rw-r--r--. 1 root root  6113 Mar 20 16:56 system_stats--2.0.sql
-rw-r--r--. 1 root root  6113 Mar 20 16:56 system_stats--3.0.sql
-rw-r--r--. 1 root root   167 Mar 20 16:56 system_stats.control
-rw-r--r--. 1 root root   373 Mar 20 16:56 uninstall_system_stats.sql
[postgres@pgsql01 data]$

I would appreciate any tips or guidance

kind regards

Mauricio Fernández

---

From: Ron Johnson <[email-masked]>
Date: Wed, 26 Mar 2025 17:08:00 -0400

On Wed, Mar 26, 2025 at 4:58 PM Mauricio Fernandez <
[email-masked]> wrote:

> -
[snip]

> I would appreciate any tips or guidance

How did you install?
If using packages, what packages did you install?

(On RHEL, it's in the postgresqlXX-contrib package.)

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Mauricio Fernandez <[email-masked]>
Date: Wed, 26 Mar 2025 18:23:00 -0300

Hi Ron, thanks for the answer.

I've installed postgresql following the instruction in
https://www.postgresql.org/download/linux/redhat/ for redhat 9 and
postgresql 17

[root@pgsql01 ~]$ sudo dnf install -y
https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
[root@pgsql01 ~]# sudo dnf -qy module disable postgresql
[root@pgsql01 ~]# sudo dnf install -y postgresql17-server

Then after I've created the cluster and master /standby replication with
repmgr I have installed dnf install postgresql17-devel following
system_stats installation instructions

[root@pgsql01 ~]#  vi /etc/yum.repos.d/oracle-linux-ol9.repo

[ol9_codeready_builder]
name=Oracle Linux 9 CodeReady Builder ($basearch) - (Unsupported)
baseurl=https://yum
$ociregion.$ocidomain/repo/OracleLinux/OL9/codeready/builder/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1

*enabled=1*
[root@pgsql01 ~]# dnf install postgresql17-devel
[root@pgsql01 ~]# dnf install redhat-rpm-config

Should I install postgresql17-contrib package??

[root@pgsql01 ~]# dnf install postgresql17-contrib

thanks

kind regards

MF

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 27 Mar 2025 11:19:19 +0100

> [root@pgsql01 ~]# dnf install postgresql17-contrib

Yes.

Yours,
Laurenz Albe

---

From: Mauricio Fernandez <[email-masked]>
Date: Thu, 27 Mar 2025 07:55:43 -0300

Thanks Laurenz, and that was the solution

regards

Mauricio Fernández

---

From: Devrim Gündüz <[email-masked]>
Date: Tue, 01 Apr 2025 12:20:26 +0300

Hi,

On Wed, 2025-03-26 at 17:58 -0300, Mauricio Fernandez wrote:
> installed a couple of extensions: system_stats and pg_stat_monitor.T

<snip>

There is a basic documentation about installing extensions via RPMs:

https://yum.postgresql.org/extensions/

These two (and more) are already mentioned there.

Regards,
-- 
Devrim Gündüz
Open Source Solution Architect, PostgreSQL Major Contributor
Twitter: @DevrimGunduz , @DevrimGunduzTR

---

From: Mauricio Fernandez <[email-masked]>
Date: Tue, 1 Apr 2025 07:44:26 -0300

Thank you Devrim, great page about extensions Installations...

Do you have any recommendations about Postgresql Database monitoring tool??
, beside PgAdmin and dbeaver ?? I don't want a general purpose tools, like
zabbix, pmm (percona)....etc, free or not, like Lab128 was for Oracle
databases

These are my first steps in Postgresql....and I'm looking for this kind of
tools

kind regards

Mauricio Fernández