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

[Even Rouault]

Robin,

- output of "gdalinfo --version" ?

- try adding ``--config PG_USE_COPY YES`` to  the command line where you 
append to the existing table. Cf 
https://gdal.org/drivers/vector/pg.html#configuration-options (copy mode 
is enabled by default at table creation, but not in append scenarios)

- if the above didn't help, try also setting ``--config 
OGR2OGR_USE_ARROW_API NO`` (please report if that makes a difference)

Even

Le 14/03/2024 à 18:28, Robin Wilson via gdal-dev a écrit :
> 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
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev

-- 
http://www.spatialys.com
My software is free, but my time generally not.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20240314/df14643a/attachment.htm>