[Tile-serving] [openstreetmap/osm2pgsql] osm2pgsql fails on 100MB+ .pbf files when using Aurora Postgres Serverless DB (#1056)

[Chris Bailey]

@kritika121 so the main problem is that Aurora Serverless has a 24 hour query limit. Once you hit that, they close the connection on you. What I've found is that setting up really large size data GIS databases is better to do with a provisioned Aurora Postgres instance, and then once you're done, convert that to a Serverless instance. This is quite easy to do. How you do this depends on if you're using Aurora Servleress v1 or v2. For v1, you have to snapshot the DB and then restore it to a serverless instance. But, for v2, you can simply modify the instance to be Serverless, and it'll do it all in place for you.  If you haven't chosen at this point, I'd recommend going with v2, mainly because you can get newer Postgres version (v14.5). Also I personally ran into issues with the PostGIS version that AWS is now using with Postgres v10/Aurora Serverless v1.
Finally, the other advantage of data import this way is that you have a more stable (scale/memory/etc. wise) instance during all your import.

If you are importing into an existing DB, and you are using Aurora Serverless v2, you can do the reverse too - where you convert your serverless instance to provisioned, do your import, then convert back again. For v1, it's a bigger challenge to deal with an existing DB, and more likely if you are exceeding that 24-hour query limit, you will have to break up your data into multiple imports that can be achieved in much less time.

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/1056#issuecomment-1644323913
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/osm2pgsql/issues/1056/1644323913 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20230720/dab1006b/attachment.htm>