ogr2ogr: PostGIS to PostGIS

I recently^ had to update a live database with updated tables from a staging database and then continue to update on a daily basis. As it is a regular update and the source and destination tables won’t change I generated a text file with a list of layers to process and tables to write. Like this:

list.txt
srcTable1, destTable1
srcTable2, destTable2
...

The first column is the list of layers in the staging database to process. This is the %G variable in the shell script. The second column is the new table to write, the %H variable.

The initial load read in the layers from the staging database and created them in the live database. I set the progress flag to check it was doing something (this can be deleted), set the geometry column and output schema.

FOR /F "tokens=1,2 delims=," %G IN (list.txt) DO ogr2ogr -progress -lco GEOMETRY_NAME=geometry -lco SCHEMA=outputSchema -nln %H -f PostgreSQL --config PG_USE_COPY YES PG:"dbname='destdbName' host='srcHost' port='5432' user='srcUserName' password='srcPassWord'" PG:"dbname='srcdbName' host='destHost' port='5432' user='destUserName' password='destPassWord'" %G

Subsequent loads overwrite the tables in update mode.

FOR /F "tokens=1,2 delims=," %G IN (list.txt) DO ogr2ogr -update -overwrite -progress -lco GEOMETRY_NAME=geometry -lco SCHEMA=outputSchema -nln %H -f PostgreSQL --config PG_USE_COPY YES PG:"dbname='destdbName' host='srcHost' port='5432' user='srcUserName' password='srcPassWord'" PG:"dbname='srcdbName' host='destHost' port='5432' user='destUserName' password='destPassWord'" %G

Set the appropriate values in the scripts above: database name, host, port if different, username and password.

^ Originally posted at http://words.mixedbredie.net/archives/2309 on 27 May 2014