Missing tables when migrating postgres DB

[knightaj]

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 ?

[knightaj]

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)

[robe]

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

[robe]

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.