Postgres 15.6+恢复含earthdistance扩展的数据库报错：类型earth不存在

我有一张包含cube列的表，用pg_dump备份后在Postgres 15.6（Postgres 16.3同样存在此问题）上恢复失败，报错指向earthdistance扩展的ll_to_earth函数。

pg_dump会在备份脚本中设置search_path为空：

SELECT pg_catalog.set_config('search_path', '', false);

尝试设置search_path无法解决问题：无法为表设置search_path，即使给函数设置search_path（比如ALTER FUNCTION public.ll_to_earth SET search_path = public;），pg_dump备份时也不会保留这个配置。

复现脚本 #

set -x

psql postgresql://postgres@localhost:5432 -c "CREATE DATABASE earth_test"

psql postgresql://postgres@localhost:5432/earth_test <<EOF
CREATE EXTENSION IF NOT EXISTS cube WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS earthdistance WITH SCHEMA public;

CREATE TABLE town (
    name varchar,
    lon float,
    lat float,
    location public.cube GENERATED ALWAYS AS (public.earth_box(public.ll_to_earth(lat, lon), 0)) STORED
);
EOF

pg_dump postgresql://postgres@localhost:5432/earth_test > earth_test.sql


psql postgresql://postgres@localhost:5432 -c "CREATE DATABASE earth_test2";
psql postgresql://postgres@localhost:5432/earth_test2 -f earth_test.sql

执行报错输出 #

+ psql postgresql://postgres@localhost:5432 -c 'CREATE DATABASE earth_test'
CREATE DATABASE
+ psql postgresql://postgres@localhost:5432/earth_test
CREATE EXTENSION
CREATE EXTENSION
CREATE TABLE
+ pg_dump postgresql://postgres@localhost:5432/earth_test
+ psql postgresql://postgres@localhost:5432 -c 'CREATE DATABASE earth_test2'
CREATE DATABASE
+ psql postgresql://postgres@localhost:5432/earth_test2 -f earth_test.sql
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
ALTER SCHEMA
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
SET
SET
psql:earth_test.sql:69: ERROR:  type "earth" does not exist
LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
                                                                  ^
QUERY:  SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
CONTEXT:  SQL function "ll_to_earth" during inlining

解决方案 #

方法1：恢复时指定search_path #

执行恢复命令前先设置search_path为public，避免因search_path为空找不到类型：

psql postgresql://postgres@localhost:5432/earth_test2 -c "SET search_path = public;" -f earth_test.sql

或者手动修改备份脚本，在创建表的语句前添加SET search_path = public;。

方法2：显式指定类型的schema #

修改原表的生成列定义，把ll_to_earth返回值的类型显式指定为public.earth，这样即使search_path为空，PostgreSQL也能定位到类型：

CREATE TABLE town (
    name varchar,
    lon float,
    lat float,
    location public.cube GENERATED ALWAYS AS (public.earth_box(public.ll_to_earth(lat, lon)::public.earth, 0)) STORED
);

修改完成后重新备份，恢复时就不会报错。

方法3：使用pg_dump的--no-sync-schemas参数（仅PostgreSQL 16+） #

PostgreSQL 16及以上版本新增了--no-sync-schemas参数，备份时不会重置search_path为空，直接用这个参数备份即可：

pg_dump --no-sync-schemas postgresql://postgres@localhost:5432/earth_test > earth_test.sql

内容的提问来源于stack exchange，提问作者VargaD