11

I have followed the instructions given below:

http://www.postgresql.org/docs/9.3/static/pgstatstatements.html

... to the effect of adding the following lines:

# postgresql.conf shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all

In file postgresql.conf and then restarting the server but the table pg_stat_statements is still not visible:

$ cat /usr/share/postgresql/9.3/postgresql.conf | grep -A3 ^shared_preload_libraries shared_preload_libraries = 'pg_stat_statements' # (change requires restart) pg_stat_statements.max = 10000 pg_stat_statements.track = all $ sudo /etc/init.d/postgresql restart * Restarting PostgreSQL 9.3 database server [ OK ] $ psql -U postgres psql (9.3.10) Type "help" for help. postgres=# select count(*) from pg_stat_activity; count ------- 1 (1 row) postgres=# select count(*) from pg_stat_statements; ERROR: relation "pg_stat_statements" does not exist

update

After executing:

sudo apt-get install postgresql-contrib-9.3

And then doing:

$ psql -U postgres psql (9.4.5, server 9.3.10) Type "help" for help. postgres=# create extension pg_stat_statements; CREATE EXTENSION postgres=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+----------------------------------------------------------- pg_stat_statements | 1.1 | public | track execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows) postgres=# \quit $ sudo /etc/init.d/postgresql restart * Restarting PostgreSQL 9.3 database server [OK]

... I now get:

postgres=# select * from pg_stat_statements ; ERROR: pg_stat_statements must be loaded via shared_preload_libraries

system details

I am running on Ubuntu 14.04.03 LTS. PostgreSQL was installed with apt-get install.

log trace of PostgreSQL during a restart

While doing a sudo /etc/init.d/postgresql restart I get the following log trace:

$ tail -f /var/log/postgresql/postgresql-9.3-main.log 2015-12-21 11:11:31 EET [23790-2] LOG: received fast shutdown request 2015-12-21 11:11:31 EET [23790-3] LOG: aborting any active transactions 2015-12-21 11:11:31 EET [7231-1] esavo-user@RegTAP FATAL: terminating connection due to administrator command 2015-12-21 11:11:31 EET [23903-5] postgres@postgres FATAL: terminating connection due to administrator command 2015-12-21 11:11:31 EET [23822-7] esavo-user@RegTAP FATAL: terminating connection due to administrator command 2015-12-21 11:11:31 EET [23795-2] LOG: autovacuum launcher shutting down 2015-12-21 11:11:31 EET [23815-1] esavo-user@RegTAP FATAL: terminating connection due to administrator command 2015-12-21 11:11:31 EET [23792-1] LOG: shutting down 2015-12-21 11:11:31 EET [23792-2] LOG: database system is shut down 2015-12-21 11:11:32 EET [16886-1] LOG: database system was shut down at 2015-12-21 11:11:31 EET 2015-12-21 11:11:32 EET [16886-2] LOG: MultiXact member wraparound protections are now enabled 2015-12-21 11:11:32 EET [16885-1] LOG: database system is ready to accept connections 2015-12-21 11:11:32 EET [16890-1] LOG: autovacuum launcher started 2015-12-21 11:11:33 EET [16892-1] [unknown]@[unknown] LOG: incomplete startup packet
Improve this question
11
  • 2
    Have you actually installed the extension? (You can check this using \dx in psql.) Commented Dec 18, 2015 at 12:31
  • @dezso no, I seem to miss that part in the documentation regarding the pg_stat_statements table Commented Dec 18, 2015 at 12:40
  • 3
    Adding shared_preload_libraries is a pre-requisite in order to be able to run create extension pg_stat_statements Commented Dec 18, 2015 at 12:42
  • @a_horse_with_no_name that did move things forward a bit but am not quite there yet (see update to my question) Commented Dec 18, 2015 at 12:55
  • 1
    The only thing I can think of is that the postgres.conf file you edited is not the conf file for the database you are connecting to and have installed the extension on. Try executing SHOW config_file; when connected and see what it says. Commented Dec 29, 2015 at 23:41

1 Answer 1

Reset to default
6

Recent versions of Postgres install conf files in different locations than they used to be in. There is sometimes a template .conf file that exists (but is not actually used), and then also an active .conf file (in a completely different location). I discovered this by scouring through the /etc/init.d files. It's possible that the .conf file you edited is not actually the one that is being used by the database.

You can find the location of the conf file used by your database by connecting to the database and querying SHOW config_file

Here is a link to your follow up question (in case anyone in the future finds it useful)

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.