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
Best Answer
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)