"function public.pg_stat_statements_reset() does not exist" error on restoring dump

Question

I am taking dump of a database using pg_dump using this command

pg_dump -U [username] -h <[hostname] -p [port] -v [database_name] > dump.sql

and restoring to a new database with

psql -U [username] -h [hostname] -p [port] [database_name] < dump.sql

and I am getting this error when restoring the dump:

ERROR: function public.pg_stat_statements_reset() does not exist

I found some answers related to this which does the following:

1. Install pg_stat_statements extension - In my case this is already installed

SELECT * FROM pg_available_extensions 
WHERE 
    name = 'pg_stat_statements' and 
    installed_version is not null;

this query yields:

name        | default_version | installed_version |                                comment                                 
--------------------+-----------------+-------------------+------------------------------------------------------------------------
 pg_stat_statements | 1.9             | 1.9               | track planning and execution statistics of all SQL statements executed

2. My pg.conf file already contains the line:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Tried backup in pgadmin in custom, Tar and Plain Formats and it didn't help Also with pg_restore. Restoring with 'postgres' user which has superuser privileges

Please help resolve this. TIA.

Answer 1

Please check the extension availability in the same database in which you are restoring and postgres database.