使用扩展程序cube时出现pg_dump / pg_restore错误

Question

我在导出和恢复我的一个数据库时遇到了一个问题，我认为是因为公共模式中的一些扩展引起的。似乎抛出错误的扩展是Cube扩展或者EarthDistance扩展。这是我收到的错误信息：

pg_restore: [archiver (db)] Error from TOC entry 2983;
pg_restore: [archiver (db)] could not execute query: 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
   Command was: REFRESH MATERIALIZED VIEW public.locationsearch

我曾经遇到了与我自己编写的一些函数有关的类似问题，最后发现问题在于搜索路径，所以将这些函数的搜索路径明确设置为公共路径解决了我的问题。我尝试对ll_to_earth执行同样的操作，但似乎整个扩展名都有问题。我不想安装到pg_catalog的扩展程序，因为那似乎是一种不良做法。

这是我的典型转储命令： pg_dump -U postgres -h ipAddress -p 5432 -w -F t database > database.tar 然后是： pg_restore -U postgres -h localhost -p 5432 -w -d postgres -C "database.tar" 完整的包含数据的转储文件大约有4GB，但我尝试只转储模式用-s和-F p，有趣的是这是开头部分：

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.2
-- Dumped by pg_dump version 12.2

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: cube; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS cube WITH SCHEMA public;


--
-- Name: EXTENSION cube; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION cube IS 'data type for multidimensional cubes';


--
-- Name: earthdistance; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS earthdistance WITH SCHEMA public;


--
-- Name: EXTENSION earthdistance; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION earthdistance IS 'calculate great-circle distances on the surface of the Earth';

我想我被搞糊涂了...从逻辑上讲，这不是和我使用tar格式时一样吗？我知道问题出在当pg_restore到达那个物化视图并尝试使用函数ll_to_earth(float8, float8)时失败了，因为该函数要么不在其搜索路径中，要么尚未被恢复，但这不会表明扩展名是首先恢复的吗？这可以修复吗？

这是我编写的脚本的一部分，它将每天在生产环境上转储数据库并在测试环境上恢复数据库，以便它们匹配。它运行了几个月，直到我开始使用这个扩展名，现在我不知道如何纠正它。

- HelpMeExitVim

Answer 1

为了安全起见，pg_dump将search_path设置为空，因此如果未在模式限定符下引用，只会找到系统模式pg_catalog中的对象。

现在，您的SQL函数在没有模式（可能为public）的情况下使用数据类型earth，因此出现错误消息。

您需要更改函数以使用像public.earth这样的限定名称来处理扩展对象。 或者更好的方法是修复函数的search_path：

ALTER FUNCTION myfun SET search_path = public;

无论如何，这都是一个好主意，因为如果用户更改了search_path，则您的函数将停止工作。根据函数定义或使用方式的不同，这甚至可能构成安全问题（这就是为什么pg_dump要以这种方式执行的原因）。

- Laurenz Albe

---

非常感谢，Laurenz！这完美地解决了我的问题。 - HelpMeExitVim

Answer 2

我也遇到了同样的问题，但是上面的解决方案都没有起作用。在插入数据时，我遇到了以下错误

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

在 pgdump 文件中，我需要替换以下行（在文件顶部）。

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

使用

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

- Torsten Ojaperv

Answer 3

我在这个答案中找到了一种方法。我在pg_catalog模式中创建扩展，正如文档所述，它始终是搜索路径的有效部分。

CREATE EXTENSION IF NOT EXISTS cube SCHEMA pg_catalog; 
CREATE EXTENSION IF NOT EXISTS earthdistance SCHEMA pg_catalog;

人们认为这是一种不好的做法，因为它可能会污染系统目录等。但在我的用例中，它对我很有用 - 现在我可以pg_dump/pg_restore而无需担心。

注意：我发现了这个包含earthdistance扩展补丁的线程。不幸的是，它尚未合并。

- xpuu