PostGIS – Solving Out of Memory Issues When Importing Raster with PSQL

Question

I tried to import a dem.tif in a postgres database. I use postgres 9.5. First step was to create file via raster2pgsql. This file dem.sql (800.000kb), I'd like to import via psql into the database. The process start running, create sequence, and primary key, then CREATE TABLE…stop with ERROR out of memory character string with 859735225 Bytes to long for transformation ERROR Transaktion stopped etc. What can I do to bypass this problem?

Answer 1

As discussed in the comments/chat, you ran into two errors here. Firstly, the conversion between the client and the server encoding, and secondly, a limitation of raster size.

Encoding conversion

The encoding conversion issue has been documented in the PostGIS Raster FAQ

If your database has set an explicit client encoding different from your database encoding, then when loading large raster files (above 30 MB in size), you may run into a bytes is too long for encoding conversion.

It is important here to note the difference between server_encoding and client_encoding. Server encoding is what is used within the database and can be viewed using SHOW server_encoding; the client encoding setting is the encoding that is used when connecting from a client to the PostgreSQL server e.g., via psql in your case. As with the server_encoding, you can query the database for the current client encoding with SHOW client_encoding.

To solve this first issue it is recommended to explicitly set the client_encoding to match that of the database (the server_encoding). There are several methods to change the client encoding which you can find in the PostgreSQL documentation.

Raster size

When working with larger rasters it is recommended to include the -t widhthxheight switch within raster2pgsql. You can alternatively use -t auto to have raster2pgsql compute an appropriate tile size.

As user dustymugs (a PostGIS developer) mentions in their answer to a previous question, the maximum dimensions allowed for a single raster are 65535 x 65535 and the size limit for a field in PostgreSQL is 1GB. Therefore, if your raster exceeds either limitation you will have to tile it.