Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two PostGIS databases on two different servers. The first server is PostGIS 2.0 and the second server is PostGIS 1.5.

On the PostGIS 2.0 I have a table "lakes" with MULTIPOLYGON geometry column. My task is to copy my table from the PostGIS 2.0 database on the first server to a PostGIS 1.5 on the second server.

I can't access the first database through remote connection, and therefore I can only export the table by exporting it to a .sql file.

What command do I use to export my "lakes" table to an .sql file to ensure that the geometry column information and the spatial index is correctly recreated in the second database?

I used the tool shp2pgsql before. Is there a similar tool for "PostGIS to PostGIS" export?

share|improve this question
up vote 11 down vote accepted

I would recommend using pg_dump and pg_restore which work very well in exporting data from one database and then restoring it to another database.

there is a nice tutorial here

But simply you will want to export using a command like

pg_dump -U username databasename > lakes.dump.gz

You can make this command better by compressing it if your data is quite large by using the switch -Fc

Then to restore the data on the second server use a command similar to this

pg_restore -U username -i -h localhost -p 5432 -d databasename lakes.dump.gz

The -i switch here is important because your PostGIS versions are different.

Hope that helps

share|improve this answer

Your Answer


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.