Unaccent issue when restoring a Postgres database

Question

I want to restore a particular database under another database name to another server as well. So far, so good. I used this command : ``` pg_dump -U postgres -F c -O -b -f maindb.dump maindb ``` to dump the main database on the production server. The I use this command : ``` pg_restore --verbose -O -l -d restoredb maindb.dump ``` to restore the database in another database on our test server. It restore mostly ok, but there are some errors, like : ``` pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3595; 1259 213452 INDEX idx_clientnomclient maindbuser pg_restore: [archiver (db)] could not execute query: ERROR: function unaccent(text) does not exist LINE 1: SELECT unaccent(lower($1)); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT unaccent(lower($1)); CONTEXT: SQL function "cyunaccent" during inlining Command was: CREATE INDEX idx_clientnomclient ON client…

Accepted Answer

I just found the problem, and I was able to narrow it down to a simple test-case.

```
CREATE SCHEMA intranet;
CREATE EXTENSION IF NOT EXISTS unaccent WITH SCHEMA public;
SET search_path = public, pg_catalog;
CREATE FUNCTION cyunaccent(character varying) RETURNS character varying
    LANGUAGE sql IMMUTABLE
    AS $_$ SELECT unaccent(lower($1)); $_$;
SET search_path = intranet, pg_catalog;
CREATE TABLE intranet.client (
    codeclient character varying(10) NOT NULL,
    noclient character varying(7),
    nomclient character varying(200) COLLATE pg_catalog."fr_CA"
 );
ALTER TABLE ONLY client ADD CONSTRAINT client_pkey PRIMARY KEY (codeclient);
CREATE INDEX idx_clientnomclient ON client USING btree (public.cyunaccent((lower((nomclient)::text))::character varying));
```

This test case is from a pg_dump done in plain text.

As you can see, the cyunaccent function is created in the public shcema, as it's later used by other tables in other schema.

psql/pg_restore won't re-create the index, as it cannot find the function, despite the fact that the shcema name is specified to reference it. The problem lies in the

```
SET search_path = intranet, pg_catalog;
```

call. Changing it to

```
SET search_path = intranet, public, pg_catalog;
```

solves the problem. I've submitted a bug report to postgres about this, not yet in the queue.

Unaccent issue when restoring a Postgres database

Solution

Problem

Related problems