[postgis-users] import raster

[Eloi Ribeiro]

> On 2014-02-27 11:19, Rémi Cura wrote:
>> Maybe you can try to separate raster2pgsql and psql .
>> 
>> First use raster2pgsqland output it to a file on you filesystem.
>> Then use psql
>> to execute this file.
>> 
>>  You could see how much time each step takes, and how much space the
>> sql temp file is.

The sql file went up to 6.6 GB.

>> Maybe you could use the   "-Y" flag to make it faster?

(...)
INSERT 0 1
INSERT 0 1
INSERT 0 1

# Til here with the -Y flag was much faster, jumped from 1h 30 min to 
only 13 min.

CREATE INDEX
ANALYZE
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding SRID 
constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding 
scale-X constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding 
scale-Y constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding 
blocksize-X constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding 
blocksize-Y constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding 
alignment constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding number 
of bands constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding pixel 
type constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding nodata 
value constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding 
out-of-database constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN

# Til here took about 30 minutes. And from here starts the slow part.

psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Unable to get 
the extent of a sample raster. Attempting memory efficient (slower) 
approach
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 100 at 
assignment
PL/pgSQL function "addrasterconstraints" line 53 at RETURN

# 3h 30min have passed and still the same.

The message is clear and searching for this message I came across with 
the code I think is executing [1].
There I can see that when this notice is raised it try again with 
function 'st_memunion' (memory friendly but slower) after failing with 
'st_collect'.
The machine where I am running this has 16 GB RAM and even so it was not 
able to preform this task with 'st_collect'.

Any suggestions?

Thanks.

Cheers,

Eloi Ribeiro

[1] 
https://git.osgeo.org/postgis/tags/2.0.3/raster/rt_pg/rtpostgis.sql.in.c