[gdal-dev] ogr2ogr Postgres upload performance

[Even Rouault]

Copy mode is not enabled by default for append, because of the potential 
risks of some columns being computed ones (among other thingsà, whereas 
when GDAL creates a table, it knows that such thing cannot happen.

If you do basic use, you can definitely explicitly enable copy mode for 
append operations by adding --config PG_USE_COPY YES

Le 27/05/2022 à 16:58, Alexandre Gacon a écrit :
> Hi Jukka,
>
> The problem is not related to a multi-table geopackage but to a set of 
> geopackages with several tables in each : the first geopackage creates 
> the tables so it can use the PG_USE_COPY but for the other files it 
> has to go with the INSERT.
>
> Regards
> Alexandre
>
> Le ven. 27 mai 2022 à 15:12, Rahkonen Jukka 
> <jukka.rahkonen at maanmittauslaitos.fi> a écrit :
>
>     Hi,
>
>     If the problem is that PG_USE_COPY is applied only to the first
>     layer of the multi-table GeoPackage then I would make a test by
>     writing a separate ogr2ogr command for each layer.
>
>     -Jukka Rahkonen-
>
>     *Lähettäjä:* gdal-dev <gdal-dev-bounces at lists.osgeo.org> *Puolesta
>     *Alexandre Gacon
>     *Lähetetty:* maanantai 23. toukokuuta 2022 17.39
>     *Vastaanottaja:* Jan Heckman <jan.heckman at gmail.com>
>     *Kopio:* gdal-dev <gdal-dev at lists.osgeo.org>
>     *Aihe:* Re: [gdal-dev] ogr2ogr Postgres upload performance
>
>     I will try this way. To turn of SI creation : *SPATIAL_INDEX*=NONE
>
>     Le lun. 23 mai 2022 à 15:15, Jan Heckman <jan.heckman at gmail.com> a
>     écrit :
>
>         Perhaps the spatial index update (in de DB) slows the insertion.
>
>         It might be more efficient to not create the SI in the first run,
>
>         then insert more rows and create the SI after all is done.
>
>         I don't know how to turn off SI creation in the command line.
>
>         On Mon, May 23, 2022 at 3:04 PM Alexandre Gacon
>         <alexandre.gacon at gmail.com> wrote:
>
>             Hello,
>
>             I am using ogr2ogr to upload data from several geopackages
>             to a postgis database. Some tables contain thousands of
>             rows (buildings for example).
>
>             The import of the first file is quite fast (tables are
>             created for the first file so PG_USE_COPY is used) but the
>             following file is much slower (using INSERT instead of COPY).
>
>             How could I data insertion for the other files? Force
>             PG_USE_COPY ? Increase the value of GT ? Postpone spatial
>             index creation ?
>
>             Should I concatenate all the geopackages first and then
>             insert the data in Postgis?
>
>             Thank you for your help
>
>
>             -- 
>
>             Alexandre Gacon
>
>             _______________________________________________
>             gdal-dev mailing list
>             gdal-dev at lists.osgeo.org
>             https://lists.osgeo.org/mailman/listinfo/gdal-dev
>             <https://eur06.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fgdal-dev&data=05%7C01%7Cjukka.rahkonen%40maanmittauslaitos.fi%7C6fa46f068e26430b7f9908da3cca0f7a%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637889135792134784%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2Bmpy0HNtP0mb%2FzvSpD4E9U0Cipge3NviqQs%2B7D6hZcQ%3D&reserved=0>
>
>
>
>     -- 
>
>     Alexandre Gacon
>
>
>
> -- 
> Alexandre Gacon
>
> _______________________________________________
> 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/20220527/6084a944/attachment.htm>