I have a PostGIS query returning geographies and attributes. The result set is deterministic and ordered (so OFFSET and LIMIT work for getting subsets).
The result set of my query would exceed my RAM by many times.
ogr2ogr can read directly from PostGIS and load SQL from a file so I built this command line:
ogr2ogr -f "ESRI Shapefile" output.shp PG:"host=host user=user dbname=gis" -sql \@q.sql
When I run this, my memory is exhausted and the process is killed by the system.
How can I make PostGIS+ogr2ogr "stream" the results and flush them in reasonable intervals instead of trying to get the full result set at once?
I am not looking for scripting, instead I wonder if there is some magic of GDAL I just have to enable to get paging.
A different output format would be fine if that makes it work. But only if I can convert that to Shapefile or GeoPackage without exhausting RAM again.