PostGIS to PostGIS via ogr2ogr

OGR2OGR is a hugely useful GIS utility. Today I was using it to copy data between an Amazon Web Services (AWS) based PostGIS database and a local PostGIS database. This is a slightly weird use case, but I wanted to pull down some data locally for editing via Quantum GIS (QGIS).

Here’s the command I used:

ogr2ogr -f PostgreSQL --config PG_USE_COPY YES  PG:"dbname='localDb' host='localhost' port='5432' user='postgres' password='postgres'" PG:"dbname='remoteDb' host='' port='5432' user='yourUser' password='yourPassword'" layer1 layer2

This command will pull down 2 layers (layer1 and layer2) into your ‘localDB’ database. You should create the database first. Note also the ” –config PG_USE_COPY YES ” config option which in most cases makes things go WAY faster!