模式导入失败，不存在“类型”"ltree“

Question

当试图将架构从Postgres 9.4导入Postgres RDS 9.6时，会出现以下错误：

ERROR:  type "ltree" does not exist
LINE 8:     path ltree,
                 ^

在源上安装了ltree：

ltree   | 1.0     | pg_catalog | data type for hierarchical tree-like structures

在目标上安装了ltree：

ltree   | 1.1     | public     | data type for hierarchical tree-like structures

从Postgres转储：

pg_dump -h db0 -U deployer staging --schema public --schema-only > schema.dump

恢复到RDS：

psql -h staging.us-east-1.rds.amazonaws.com -U deployer -d staging < schema.dump

模式转储中发生ltree的唯一位置是以下语句：

CREATE TABLE public.table (
    id integer NOT NULL,
    customer_id integer DEFAULT 0,
    ordinal integer NOT NULL,
    root_id integer,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    path ltree,
    premium boolean DEFAULT false
);

转储中没有CREATE EXTENSION。我试过在导入之前添加它，但它没有解决问题。

有什么建议吗？

Answer 1

如果deployer (用于“导入”转储的用户)是rds_superuser，那么您应该能够做到这一点，否则，您将看到：

错误:拒绝创建扩展"ltree“提示:必须是超级用户才能创建此扩展。

例：

mon=> set role stan;
SET
mon=> create extension ltree;
ERROR:  permission denied to create extension "ltree"
HINT:  Must be superuser to create this extension.
mon=> reset role;
RESET
mon=> create extension ltree;
CREATE EXTENSION
mon=> select session_user;
 session_user
--------------
 rdsadm
(1 row)

mon=> \du rdsadm
                           List of roles
  Role name  |          Attributes           |      Member of
-------------+-------------------------------+----------------------
 rdsadm      | Create role, Create DB       +| {rds_superuser,stan}
             | Password valid until infinity |