[gdal-dev] Hanging (or very slow) when ogr2ogr appending to PostGIS table

[Robin Wilson]

Hi,

I’ve been using ogr2ogr to import vector datasets into PostGIS tables.

I originally tried this using a local Postgres server, and ran the following commands to import one file into a PostGIS table, and then append the contents of another file to the same table:

ogr2ogr --debug ON -f PostgreSQL PG:”host=localhost user=postgres password=blah dbname=test_db" buildings1.gpkg -nln ogr_test
ogr2ogr -append -update --debug ON -f PostgreSQL PG:”host=localhost user=postgres password=blah dbname=test_db" buildings2.gpkg -nln ogr_test

This works fine, and both commands run quickly.

I then tried the same thing, but with a PostGIS server hosted on Azure. Obviously I expect things to take longer when accessing across the internet, but this time the first command completed quickly, but the second command seems to hang. Looking at the debug output, it shows:

GPKG: GeoPackage v1.2.0
GDAL: GDALOpen(buildings2.gpkg, this=0x131011800) succeeds as GPKG.
PG: Client encoding: 'UTF8'
PG: PostGIS schema: 'public'
PG: PostgreSQL version string : 'PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit'
PG: PostGIS version string : '3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1'
GDAL: GDALOpen(PG:<connection string>, this=0x140f25850) succeeds as PostgreSQL.
PG: Primary key name (FID): fid, type : int4
PG: Using column 'fid' as FID for table 'ogr_test'
OGR2OGR: Using WriteArrowBatch()

and then nothing else.

I’m intrigued as to why it seems to hang, and what it is doing, or trying to do. I’ve tried adding buildings2.gpkg to a new table on the Azure PostGIS server and it completes very quickly, so it’s not just that buildings2 is larger and takes a long time to upload. Similarly, merging the two buildings files with ogrmerge.py and then running the ogr2ogr command to import to PostGIS also works, and runs quickly.

I’m also intrigued as to why this only seems to be happening with the Azure server - is there some configuration option I need to set? I’ve tried connecting as the ‘postgres’ root user, so it shouldn’t be a permissions issue.

Any ideas or suggestions of things to try?

Any help gratefully received,

Best regards,

Robin
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20240314/8c3b7e7d/attachment.htm>