AuroraDB instance is not recognising `raster` data type

[aazaff]

The import of the table gisdata.rasters fails because the type raster is not recognized. Luckily we have never actually used this table in the past and it is empty of data anyway so nothing should actually be affected. Nevertheless we should address this eventually.

CREATE TABLE gisdata.rasters (
    raster_id integer NOT NULL,
    collection_id integer,
    metadata_id integer,
    raster raster,
    srid integer,
    tile_size text
);

CREATE SEQUENCE gisdata.rasters_raster_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE gisdata.rasters_raster_id_seq OWNED BY gisdata.rasters.raster_id;
ALTER TABLE ONLY gisdata.rasters ALTER COLUMN raster_id SET DEFAULT nextval('gisdata.rasters_raster_id_seq'::regclass);
ALTER TABLE ONLY gisdata.rasters ADD CONSTRAINT rasters_pkey PRIMARY KEY (raster_id);
SELECT pg_catalog.setval('gisdata.rasters_raster_id_seq', 1, true);
CREATE INDEX rasters_collection_id_idx ON gisdata.rasters USING btree (collection_id);
CREATE INDEX rasters_id_idx ON gisdata.rasters USING btree (raster_id);

ALTER TABLE ONLY gisdata.rasters 
    ADD CONSTRAINT rasters_collection_id_fkey 
    FOREIGN KEY (collection_id) 
    REFERENCES public.collections(collection_id);

ALTER TABLE ONLY gisdata.rasters 
    ADD CONSTRAINT rasters_metadata_id_fkey 
    FOREIGN KEY (metadata_id) 
    REFERENCES gisdata.metadata(metadata_id);

[aazaff]

Easy explanation -- postgis extension no longer includes raster support by default as of 3.x. You need to explicitly install it separately now. Details here. CREATE EXTENSION postgis_raster;

[aazaff]

I tested this successfully on the new devazlib database. I also created the extension on prod, BUT the actual raster tables and associated triggers have not been brought over. Leaving that set top @NoisyFlowers

[NoisyFlowers]

Wait, what?

Why weren't they brought over? Would they have been if the extension was installed?

The sql above bears only passing resemblance to the sql that is executed by azlibConfigPG. For instance, I never explicitly create a sequence or mess with ownership or constraints. I don't know what this means.

[aazaff]

Yes, it would have worked the extension was installed in advance. But I'm not doing another pg_restore...

[NoisyFlowers]

The only raster setup I execute is

create table gisdata.rasters
(
    raster_id serial PRIMARY KEY,
    collection_id integer REFERENCES public.collections(collection_id),
    metadata_id integer REFERENCES gisdata.metadata(metadata_id),
    raster raster,
    srid integer, 
    tile_size text

);
CREATE INDEX rasters_id_idx ON gisdata.rasters (raster_id);
CREATE INDEX rasters_collection_id_idx ON gisdata.rasters (collection_id);

No idea what all that other stuff is. I assume that's code generated by pg_backup? I also assume we can ignore it and stick with this?

[NoisyFlowers]

Pretty sure that's all handled by the PRIMARY KEY and REFERENCES specifiers.

[aazaff]

Yes, that's what's generated by pg_dump, which I think is just a more formal/full-length way of saying what you've already stated. At least for the create and index, but aren't there related triggers too?

[NoisyFlowers]

None that I create.

[NoisyFlowers]

Done. Table and indexes created in prod db