How to export raster from PostGIS enabled DB as GTiff with gdal_translate

When I want to export a GTiff from my PostGIS DB with this code:

gdal_translate -of GTiff PG":host='localhost' port:'5432' dbname='landslide' user='postgres' password='pass' schema='public' table='dgm' mode='2'" test.tiff

I get the following error:

Illegal filename. character <'> not allowed.Illegal filename. character <'> not allowed.ERROR 4: `PG:host='localhost' port:'5432' dbname='landslide' user='postgres' password='pass' schema='public' table='dgm' mode='2'' does not exist in the file system, and is not recognised as a supported dataset name.

GDALOpen failed - 4 `PG:host='localhost' port:'5432' dbname='landslide' user='postgres' password='pass' schema='public' table='dgm' mode='2'' does not exist in the file system, and is not recognised as a supported dataset name.

I followed the instructions from gdal.

Is the syntax incorrect? I tried to change the quotes, but no success.

Answer

I would suggest you to use this alternative way to do the same:

1. 
   

   To export raster as TIFF, export it to a lob(large object) and return it’s oid using the query:

   
   

   SELECT oid, lowrite(lo_open(oid, 131072), tiff) As num_bytes
   FROM
   ( VALUES (lo_create(0),
   ST_Astiff( (SELECT rast FROM raster_table WHERE rid = 1) )
   ) ) As v(oid,tiff);
   



2. 
   
   

   Use the PostgreSQL \lo_export command to output your raster as a tiff file:

   
   

   \lo_export 147303 '/tmp/myraster.tiff'
   



3. 
   

   As you probably don't use the lob anymore you should probably unlink (remove) it by calling:

   
   

   SELECT lo_unlink(147303);
   

Later you can GeoReference it using gdal_translate:

gdal_translate /tmp/myraster.tiff -of GTiff -a_srs 'PROJCS[AUTHORITY["EPSG","900913"]]' -a_nodata 0 /tmp/myrastergeo.tiff

Source: How to: Create a Heatmap Raster in PostGIS and Render in GeoServer