8

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
  • 2
    Have you actually installed the extension? (You can check this using \dx in psql.) – dezso Dec 18 '15 at 12:31
  • @dezso no, I seem to miss that part in the documentation regarding the pg_stat_statements table – Marcus Junius Brutus Dec 18 '15 at 12:40
  • 2
    Adding shared_preload_libraries is a pre-requisite in order to be able to run create extension pg_stat_statements – a_horse_with_no_name Dec 18 '15 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) – Marcus Junius Brutus Dec 18 '15 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. – Joishi Bodio Dec 29 '15 at 23:41
3

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 | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.