Thread: Few extensions, pg_stat_statements not available

Few extensions, pg_stat_statements not available

From

Mauricio Fernandez

Date:

26 March, 23:58:29

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

Re: Few extensions, pg_stat_statements not available

From

Ron Johnson

Date:

27 March, 00:08:00

On Wed, Mar 26, 2025 at 4:58 PM Mauricio Fernandez <mmauricio.fernandez@gmail.com> wrote:

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
-

[snip]

-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

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!

Re: Few extensions, pg_stat_statements not available

From

Mauricio Fernandez

Date:

27 March, 00:23:00

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

El mié, 26 mar 2025 a las 18:08, Ron Johnson (<ronljohnsonjr@gmail.com>) escribió:

On Wed, Mar 26, 2025 at 4:58 PM Mauricio Fernandez <mmauricio.fernandez@gmail.com> wrote:
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
-
[snip]
-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

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!

Re: Few extensions, pg_stat_statements not available

From

Laurenz Albe

Date:

27 March, 13:19:19

On Wed, 2025-03-26 at 18:23 -0300, Mauricio Fernandez wrote:
> Should I install postgresql17-contrib package?? 
>
> [root@pgsql01 ~]# dnf install postgresql17-contrib

Yes.

Yours,
Laurenz Albe

Re: Few extensions, pg_stat_statements not available

From

Mauricio Fernandez

Date:

27 March, 13:55:43

Thanks Laurenz, and that was the solution

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

regards

Mauricio Fernández

El jue, 27 mar 2025 a las 7:19, Laurenz Albe (<laurenz.albe@cybertec.at>) escribió:

On Wed, 2025-03-26 at 18:23 -0300, Mauricio Fernandez wrote:
> Should I install postgresql17-contrib package??
>
> [root@pgsql01 ~]# dnf install postgresql17-contrib

Yes.

Yours,
Laurenz Albe

Re: Few extensions, pg_stat_statements not available

From

Devrim Gündüz

Date:

01 April, 12:20:26

Hi,

On Wed, 2025-03-26 at 17:58 -0300, Mauricio Fernandez wrote:
> I've been investigating about postgresql monitoring tools and I've
> 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

Attachment

signature.asc

Re: Few extensions, pg_stat_statements not available

From

Mauricio Fernandez

Date:

01 April, 13:44:26

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

El mar, 1 abr 2025 a las 6:20, Devrim Gündüz (<devrim@gunduz.org>) escribió:

Hi,

On Wed, 2025-03-26 at 17:58 -0300, Mauricio Fernandez wrote:
> I've been investigating about postgresql monitoring tools and I've
> 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