postgresql 无法从备份恢复Postgres数据库- public.uuid_generate_v1()不存在

wn9m85ua  于 2022-11-29  发布在  PostgreSQL
关注(0)|答案(2)|浏览(181)

我正在通过.backup文件将postgres数据库从一个postgres-14示例恢复到另一个示例:
pg_restore -h localhost -p 5432 -U postgres -d mydatabase -v "mybackupfile.backup"
备份失败,并抱怨
pg_restore: error: could not execute query: ERROR: function public.uuid_generate_v1() does not exist
但是,包含相应函数的扩展uuid-ossp安装在目标系统上。
我能做什么呢?
导致错误的语句:

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()
);
zte4gxcn

zte4gxcn1#

我自己找到了解决办法,但我不确定是哪一部分造成的:
在psql中,连接到mydbatabase,然后调用语句
SET search_path TO data, public;
再次调用扩展语句:
create statement if not exists "uuid-ossp"
之后,已成功调用restore语句。

wwtsj6pe

wwtsj6pe2#

您需要添加扩展名;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

类似的回答在这里:链接

相关问题