Getting error "could not access file "pg_stat_statements": No such file or directory"

Question

I have the package postgresql11-contrib installed.

$ yum list installed | grep contrib
postgresql11-contrib.x86_64     11.11-1PGDG.rhel7        @pgdg11

Here is the version of postgres.

psql (PostgreSQL) 11.6

I have the below entries in postgresql.conf which is causing the error.

shared_preload_libraries = 'pg_stat_statements'         # (change requires restart)
pg_stat_statements.max = 10000
pg_stat_statements.track = all

Upon some online searching I found that, I need to run "Run CREATE EXTENSION pg_stat_statements in the database(s) of my choice". But to do that I first commented above mentioned 3 lines from conf file because my psql server was failing to start with error could not access file "pg_stat_statements": No such file or directory".

https://www.oreilly.com/library/view/mastering-postgresql-11/9781789537819/a6a44124-558b-42f9-a0f3-eb52ea2799d4.xhtml

https://www.postgresql.org/docs/11/pgstatstatements.html

Now when I execute the command CREATE EXTENSION pg_stat_statements; I see error "could not open extension control file "/usr/postgresql/share/extension/pg_stat_statements.control": No such file or directory" and upon looking into the mentioned directory, these is no file pg_stat_statements.control

What am I missing here? Please help.

Answer 1

You seem to have installed two versions of Postgres on your Redhat/CentOS machine. You probably have installed postgresql11 from the PGDG repository (yum.postgresql.org) as well as the default postgresql provided by CentOS 7. If you do rpm -aq | grep postgres I'll bet you'll see a v. 11 and a v. 9.2.

Just yum erase postgresql, which will delete the v. 9.2 instance that's in the CentOS repository. However, before you do that you'll need to dump the database(s) and stop Postgres. After you delete v. 9.2, you'll need to do a fresh initdb before starting Postgres, and re-load the data you had dumped.