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.

Leave a Reply

Your email address will not be published. Required fields are marked *