Copy a spatial table from one PostGIS database to another PostGIS database

Question

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?

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