Restore postgis backup from 1.5 to 2.1.2

3
votes

I have a database backup of a postgres 9.1 and postgis 1.5. Many of the databases were built using a spatial enabled database, created using:

createdb spacial psql -d spacial -f postgis.sql psql -d spacial -f spatial_ref_sys.sql psql -d spacial -f postgis_comments.sql

Now I jave a postgres 9.3 and postgis 2.1. All spatial databases were not restored correctly because my spacial database (created with this new version) is created with only one of the two tables (created with the old version).

Does anyone knows what is going on? Thanks.

I have made the steps I needed, at least I think I've made it. After run: SELECT postgis_full_version(); I've got this result: "POSTGIS="2.1.2 r12389" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.9.1" LIBJSON="UNKNOWN""

As the POSTGIS 2.1.2 is not covered in the link provided, I figured I should run only the script postgis_upgrade_21_minor.sql, as I don't have topology installed. But a few tables were not imported at all. During importing I got a few messages: type "geometry" is undefined.

Any new ideas?

1
postgresqlpostgis
1.5 to 2.x requires a hard upgrade. See bostongis.com/blog/index.php?/archives/… for more details. – John Powell
Thanks for your help John Barça! I am reading the link, but I don't know if I installed postgres with extensions. Do you know how can I check if its installed or not? – Gustavo Sooeiro
select postgis_version(); Also if you do a \d from psql prompt, you should see geometry_columns, geography_columns and spatial_ref_sys view and possibly other things too, like raster. – John Powell
I have made the steps I needed, at least I think I've made it. After run: <code>SELECT postgis_full_version();</code> I've got this result: <code> "POSTGIS="2.1.2 r12389" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.9.1" LIBJSON="UNKNOWN"" </code> – Gustavo Sooeiro
Hi John, would you know what else could be done? – Gustavo Sooeiro

1 Answers

6
votes

Follow the hard upgrade instructions in the manual. These steps, in brief, are:

  1. Dump the old database to a file
  2. Spatially enable a new empty database (there are several different ways to do this)
  3. Use a special utility postgis_restore.pl that came with your new version of PostGIS to translate the old format to the new format.