pg_stat_statements not found even with "shared_preload_libraries = 'pg_stat_statements'

Question

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

Answer 1

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)