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?

1 Answer 1


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 http://www.mkyong.com/database/backup-restore-database-in-postgresql-pg_dumppg_restore/

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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