Dealing with common PostgreSQL extension errors

Backup restoration error

Problem

The following error appears when restoring a database from a logical backup:

 pg_restore: while PROCESSING TOC: pg_restore: ... pg_restore: error: could not execute query: ERROR: function unaccent(character varying) does not exist

Cause

The unaccent extension is installed in a public schema, the search_path is not set to public, and the namespace was not defined when running the function.

Solution

For security reasons only the pg_catalog namespace is checked during backup restoration. Therefore you must check in which namespace the faulty function is defined. The function should be defined with an explicit namespace when it is used.

Run the function again while defining the namespace.

For more information and examples refer to the PostgreSQL extensions reference page.