0

I have a PostgreSQL version 10 and a PostGIS extension. In this old version of PostgreSQL, the PostGIS has also support for raster. But in the newer versions of PostGIS, the raster support in a separate extension called: postgis_raster.

Thus, we wont have this line in our dumpall file:

CREATE EXTENSION postgis_raster;

And when I restore it it tells me it does not recognize the raster type!

My file is very big if I do not zip it before storing it on the disk. If I zip it, I wont be able to change the lines in the file to add this extension to the dump file manually.

I was thinking to do a pgdumpall with --global-only flag. Then, later dump each DB one by one using pgdump.

However, I was afraid that I may miss a detail from my DB cluster.

Is there a way to ask the pgdumpall to consider that postgis_raster is a separate extension and should be added to the dump file?

How can I safely dump and restore my cluster?

Improve this question
4
  • 1
    Do you have the option to upgrade postgis in-place at this time? Commented Jul 29 at 15:36
  • 1
    Before you restore your database, create a new database as a target database and create the extension postgis_raster. When this is done, you can restore your backup, using this new database. --global-only does something very different. It doesn't backup anything from a database, only the globals from your cluster. Commented Jul 29 at 15:43
  • @PeterVandivier I looked at this page, it looks higher than my level. Why you prefer this over pg_dump and then restore in the newer version? are there other potential problems than the extension of postgis_raster? Commented Jul 29 at 17:53
  • 1
    Your question as written is about upgrading postgis as an in-line part of a dump-restore process. If you upgrade the extension prior to the dump, then you follow a documented procedure for both steps rather than making it up as you go. Commented Jul 29 at 18:09

2 Answers 2

Reset to default
1

Upgrading with dump and restore is called a hard upgrade in PostGIS terms. You should follow the documentation. For that, you should first use pg_dumpall -g to dump the roles and tablespaces and restore that to the new cluster. Then manually create the databases in the new cluster and create the postgis and postgis_raster extensions as needed. Then take a custom format dump of each database and restore it. There are some additional things to consider; follow the documentation.

Improve this answer
0

Perhaps you want --schema-only rather than --globals-only.

Dump the schema without compression and then modify the DDL as needed. If your schema is too big to fit on the available storage you have, then that's a bigger problem.

Follow up by dumping --data-only with compression and restoring from there (probably also using --disable-triggers).

Obligatory docs link

Improve this answer
3
  • I want to do a pg_dumpall with --globals-only to have global objects of my cluster. The, do a pg_dump for the database itself. Then, manual creation of the DB and manual creation of the postgis_raster extension. Finally, restore the dump into the new database that also has the necessary extension. Commented Jul 29 at 17:56
  • Your point about triggers is absolutey right. Commented Jul 29 at 17:57
  • 1
    That solution will be slow, because you create the indexes before loading the data. Moreover, the way you propose it, the restore is likely to fail, because foreign key constraints are created before the data are loaded. You had better load the three --sections separately. Commented Jul 31 at 7:35

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.