How can I transfer data from one PostGIS database to another?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.