CODEWITHSUNDEEP
postgresqlpg-dumppg-restore
Arun
Arun

Reputation: 87

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

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

Upvotes: 1

Views: 741

Answers (2)

Lo&#239;c Faure-Lacroix
Lo&#239;c Faure-Lacroix

Reputation: 13600

The issues seems to come from some breaking change between versions of postgresql.

If you look at the documentation for postgres 11. The signature is the one as used in the SQL query that was executed: public.pg_stat_statements_reset().

But if you were to restore this dump to a version of postgres above 11. Then the signature becomes this: pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint).

In reality, the arguments are implemented with default parameters and could be used like in pg11 and below. But if you had a GRANT statement on the function. Then it would fail because the function with empty parameters doesn't exist anymore.

The solution would be to modify the statement to not use any parameters or to use the new signature.

Documentation: https://www.postgresql.org/docs/current/pgstatstatements.html

One simpler alternative is to dump the extension pg_stat_statements before calling pg_dump and then recreate the extension on the target database after restoring the database if it's not already there.

Unless you have something that depends on the extension during export/import of the database. Dropping the extension shouldn't cause any issues.

Upvotes: 1

Siddheshwar Soni
Siddheshwar Soni

Reputation: 430

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

Upvotes: 0

Related Questions