Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have quite a bit of GIS data in a old PostGIS Database (version "1.1.6") and I need to transfer it to another PostGIS Database ("POSTGIS="1.5.3").

I tried creating a backup and restoring that on the new Database, but it throws several hundred errors.

What is the correct way of transferring the data?

share|improve this question
    
Have you tested if it works despite the errors? –  R.K. Sep 28 '12 at 12:37
    
@R.K. Yes it does work; I felt that there might be some subtle data corruption, which I might not be aware of. I was also wondering if there was a better way of transferring the data without any errors. –  Devdatta Tengshe Oct 1 '12 at 5:25
add comment

3 Answers

up vote 6 down vote accepted

According to Paul Ramsey:

First, for patch version increases (e.g. X.Y.Z -> X.Y.(Z+1)) in PostgreSQL and PostGIS you do not need to do anything at all other than install the new software. The data can remain in place and everything will Just Work.

For minor version increases in PostgreSQL (e.g. X.Y.Z -> X.(Y+1).Z) you need to dump and restore. For minor version increases in PostGIS, you need to do a "soft upgrade", which means leaving the data in place, but running the upgrade scripts (e.g. postgis_upgrade_14_to_15.sql) after you install the software update.

Finally, for major version increases in PostgreSQL and PostGIS (e.g. X.Y.Z -> (X+1).Y.Z) you need to dump and restore.

Did you store data in the public schema?

Which brings me to the actual point I want to make: you can ensure the greatest ease in doing dump and restore of PostGIS data if you ensure that you store no data in the "public" schema.

Not all is lost though if you stored data in the public schema

"But Paul," you say, "I already have a full database dump, does that mean I'm SOL?" No, but you will need a strong stomach. First, set up your new PostgreSQL. Create a blank database, load PostGIS into it. Now, load your backup file into that database. You will see lots of errors. However, these errors will be caused by old PostGIS function and type definitions from your dump file conflicting with the existing type definitions in your database. And since you want the new definitions, not the old ones, that's OK. Your load, despite all the noise and errors, should actually work. Once it's done, you can move your data into a nice separate schema, so that next time you can do a clean, error-free restore.

Have you tested if it works despite the errors?

The PostGIS site also has a section on upgrading PostGIS.

share|improve this answer
    
So basically the public schema is for temp tables? –  nickves Sep 28 '12 at 12:19
2  
Nope. It's just that the public "is where the PostGIS functions and system tables get installed, so if you dump that schema you get all those definitions in your dump." Which is just okay if you're moving to another PostGIS database with the same version as the original but might be a problem when the definition database is a different version. The new version might have different function definitions which could conflict with the ones in the public schema. –  R.K. Sep 28 '12 at 12:23
add comment

Check usual tables and "tables with geometry", then use (at shell/UNIX terminal)

  • pg_dump for export the usual tables (import with psql)
  • pgsql2shp to export the geo tables, and shp2pgsql to import.

The last one (pgsql2shp) is for the "bug" of pg_dump when exporting "old to new PostGIS data" versions. Of course, if no bugs, you need only pg_dump.

Check first the @R.K. recomendation, psql -f postgis_upgrade_1X_to_15.sql -d your_spatial_database... In general (bot not always) is ok... The pgsql2shp is the LAST option.

share|improve this answer
add comment

I agree with the other answers and have used the pg_dump method. One additional advantage is that you can compress the dump to a zip or tar for example and move it to another server if you need to.

share|improve this answer
    
pg_dump exports PostGIS data with some "bugs"... pgsql2shp export better the geometries, when exporting old postGIS versions to new versions. –  Peter Krauss Sep 28 '12 at 15:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.