Restoring Postgres Database from backup does not work - public.uuid_generate

I am restoring a postgres database via .backup file from one postgres-14 instance to another:

pg_restore -h localhost -p 5432 -U postgres -d mydatabase -v "mybackupfile.backup"

The backup fails, complaining about

pg_restore: error: could not execute query: ERROR: function public.uuid_generate_v1() does not exist

However, the extension uuid-ossp that contains the respective function is installed on the target system.

What can I do about that?

The statment causing the error:

pg_restore: creating TABLE "data.mytable"
pg_restore: from TOC entry 215; 1259 155973 TABLE mytable superuser
pg_restore: error: could not execute query: ERROR:  function public.uuid_generate_v1() does not exist
LINE 53:     uuid uuid DEFAULT public.uuid_generate_v1(),
                               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
Command was: CREATE TABLE data.mytable (
    my_id integer DEFAULT nextval('data.my_id_seq'::regclass) NOT NULL,
    [...]
    uuid uuid DEFAULT public.uuid_generate_v1()
);

CodePudding user response：

I found the solution myself, but I am not sure which part of it was responsible:

In psql, connecting to mydbatabase and then calling the statement

SET search_path TO data, public;

calling the extension statement again:

create statement if not exists "uuid-ossp"

After that, the restore statement has been called successfully.

CodePudding user response：

You need to add an extension;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

A similar answer here: LINK