3

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 USING btree (public.cyunaccent((lower((nomclient)::text))::character varying));

cyunaccent is a function that is in the public shcema and does gets created with the restore.

After the restore, I am able to re-create those indexs perfecly with the same sql, without any errors.

I've also tried to restore with the -i option of pg_restore to do a single transaction, but it doesn't help.

What am I doing wrong ?

fagiani
  • 2,293
  • 2
  • 24
  • 31
nicolasross
  • 71
  • 1
  • 6
  • It looks like the source of the problem is missing function(s). unaccent(text) is the first one it's complaining about. Are you sure it's being recreated from the backup? – wilsotc May 29 '14 at 17:51
  • Yes it's beeing recreated. When the pg_restore is finished, the function exists, and I am able to do the create index without errors. I did the restore into a newly created database, and at that point (before thre restore), it didn't. – nicolasross May 29 '14 at 17:58
  • Perhaps it's recreated in the wrong order. – wilsotc May 29 '14 at 18:41
  • Unaccent appears to be part of the text-search functionality. Is that installed on the target machine? Do you run the restore as user postgres ? – wildplasser May 29 '14 at 18:49
  • Yes it is, as other database on the target machine do have it. Yes I first tried to restore as the owner of the DB, but parts of the restore gave problems, like creating extensions. So I ended up doing it as the postgres user. @wilsotc that's what I beleive since I can re-create the indexes afterward. But it's verry non-scriptable to do so... – nicolasross May 29 '14 at 23:53

1 Answers1

4

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.

nicolasross
  • 71
  • 1
  • 6
  • 3
    Further more, changing the function as this : SELECT **public.** unaccent(lower($1)) solves the problem completly. So, in the end it just missing a shchema specification inside the function that was missing. – nicolasross Jun 02 '14 at 19:01
  • Did you bug report get any attention? I am still hitting this in 2021 – isset Apr 29 '21 at 06:04
  • 1
    @isset No. It's that much of a bug in PG itseft, but in the maner that the function is defined.The function is now defied as : `CREATE OR REPLACE FUNCTION public.cyunaccent(character varying) RETURNS character varying AS ' SELECT lower(public.unaccent($1)); ' LANGUAGE sql IMMUTABLE COST 100;` In our databases, hense solving the problem. – nicolasross Apr 30 '21 at 12:20
  • @nicolasross there is some documentation around about this need of explicitly set the schema specification? In my case the function and the pg_restore are already in the right schema, so I don't get why we need to explicit it. – Nicholas May 21 '21 at 10:29
  • nevermind, found it: https://stackoverflow.com/a/63518355/6731248 – Nicholas May 21 '21 at 10:46