Opened 19 months ago

Closed 19 months ago

Last modified 19 months ago

#5265 closed defect (wontfix)

Missing tables when migrating postgres DB

Reported by: knightaj Owned by: pramsey
Priority: medium Milestone: PostGIS 3.1.8
Component: postgis Version: 3.1.x
Keywords: Cc: knightaj

Description

We are currently migrating a postgres database with postgis extension enabled.

Versions as below

Source Postgres 9.6 Postgis 2.3.3 Destination Postgres 13 Postgis 3.1.7

The process uses a dumpall backup and during the restore we get the following error

ERROR:  relation "public.raster_columns" does not exist ERROR:  relation "public.raster_overviews" does not exist

Is there something extra we need to do to restore these ?

Change History (4)

comment:1 by knightaj, 19 months ago

Milestone: PostGIS 3.3.2PostGIS 3.1.8
Version: 3.3.x3.1.x

comment:2 by knightaj, 19 months ago

postgres=# SELECT version() postgres-#
' '
postgis_full_version();

?column?



PostgreSQL 13.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit POSTGIS="3.1.7 aafe1ff" [EXTENSION] PGSQL="130" GEOS="3.9.2-CAPI-1.14.3" SFCGAL="1.4.1" PROJ="8.2.1" LIBXML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)" TOPOLOGY

(1 row)

comment:3 by robe, 19 months ago

Resolution: wontfix
Status: newclosed

In PostGIS 3+, postgis_raster was split off as a separate extension.

What you need to do to be able to restore, is create the extensions beforehand. This is because only the command CREATE EXTENSION postgis; is in your restore, so will cause raster functionality to be dropped.

So to fix, first create the database

Then in the database do:

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_raster;

Then restore your backup ontop.

If you have further questions, please ask on the mailing list https://lists.osgeo.org/mailman/listinfo/postgis-users

comment:4 by robe, 19 months ago

I should add, if you have no raster tables in your backup, you can safely ignore the error. You only need the postgis_raster extension if you have raster tables or need raster functionality for other reasons.

Note: See TracTickets for help on using tickets.