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 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

1 Answer 1

up vote 8 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 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

share|improve this answer

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.