3

I'm trying to upload a 37 GB raster dataset to PostGIS. The raster is out-of-db (-R option), so the actual pixel values are not stored in PostGIS. I'm using this command:

raster2pgsql -s 25833 -C -I -R -t auto /data/norway/terrain/10m/*.dem dem.10m | psql -h localhost -U postgres -p 5432 -d norway

Everything works fine, but it's veeery slow: There are 254 raster files in my folder, and in 24 hours only 8 are added. With this speed it will take me a month to upload the dataset!

Is there any way I could speed up the process?

1

I think it's the -C that often takes up most of the processing. Just to verify that is the case, can youtry without the -C. -C just adds constraints which are queryable from raster_columns. You may or may not need that but can alsoways add after if you do. Though given -C runs after the load that may not be the issue you are running into.

How big are you tiles and how much mem you have? Even though you are using -R it still may be useful to use the -t option (for tiling). Tiling as I recall of a -R just creates a virtual tile which may mean when its oding other checks, it may just inspeact that part of the tile making performance better even in load.

  • I've tried different options, leaving out -C didn't make a big difference, but dropping the -R option did. So it seems that storing the raster out-of-db really slows down the loading process, at least on my computer. I hoped to save some space on my precious hard drive, instead I'm now uploading the rasters to PostGIS, and keep the originals on an external drive. Each of the 256 tiles is 150 MB so there might be a memory issue. – Bjørn Sandvik Apr 3 '16 at 12:38
  • Could be a bug in our out-db handling. Worth posting to our ticket tracker - postgis.net/support The outdb support is not as stress tested as in-db so it might be a real issue. If you can provide an example dataset or link to one that would be great. – LR1234567 Apr 4 '16 at 6:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.