When you read through the PostGIS documentation carefully, you might find a really short section on using psql to export rasters. Basically, psql does not provide the functionality to easily output binary data, so the approach here is a bit hackish, and relies on the large object support in PostgreSQL (it is worth noting, though, that large object support is considered obsolete in the PgSQL documentation). The steps required to export a raster this way are as follows:

• Create a large object
• Output the raster data as bytea
• Open the large object and write the output bytea
• Export the large object
• Unlink it to clean the resources

First, let's make this work in interactive psql mode. Type the following in psql, once connected to our mastering_postgis database:

select 
   loid,
   lowrite(lo_open(loid, 131072), gtiff) as bytesize
from (
   select 
      lo_create(0) as loid,
      ST_AsGDALRaster(rast, 'GTiff') as gtiff
   from (
      select
         ST_Union(rast) as rast
      from
         data_import.gray_50m_partial
      where
         filename = 'gray_50m_partial_bl.tif'
   ) as combined
) as gdal_rasterised;
You should get a similar output to the following:
  loid  | bytesize
--------+----------
 145707 | 14601978
(1 row)

Once you know your large object identifier, you can export it:

\lo_export 145707 'F:/mastering_postgis/chapter06/gray_50m_partial_bl_psql_interactive.tif'

The output will just be the following:

lo_export

Finally, we'll release the large object storage:

SELECT lo_unlink(145707);

The output should be as follows:

lo_unlink
-----------
         1
 (1 row)

I am purposefully not explaining the preceding code in detail because we will use its variation to perform the very same operation in one go. To do this, we can put the previous code into one SQL (you'll find the code in the chapter resources):

--Step 1: prepare the data for export 
------------------------------------- 
--Note: read this query starting at the inner most sql 

--4. store the metadata in a temp table, so it can be used later to drive the xport; 
drop table if exists data_export.psql_export_temp_tbl; 
create table data_export.psql_export_temp_tbl as 
--3. open large object for writing, and write to it the GeoTiff data prepared at lvl 2 
select  
   loid, 
   lowrite(lo_open(loid, 131072), gtiff) as bytesize --pen large object  in wrtie    mode, write gtiff data to it; write outputs the byte size 
   --note: 131072 is a decimal value of the INV_WRITE flag hex value of 0x00020000 (see libpq-fs.h for details) 
from ( 
   --2. create a large object, so it can be written to later and prepare GeoTiff binary data 
   select  
      lo_create(0) as loid, --note the param here is 'mode' which is unused and ignored as of pgsql 8.1 but left for backwards compatibility 
      ST_AsGDALRaster(rast, 'GTiff') as gtiff 
   from ( 
      --1. extract the gray_50m_partial_bl raster tiles and union them back into one raster 
      select 
         ST_Union(rast) as rast 
      from 
         data_import.gray_50m_partial 
      where 
         filename = 'gray_50m_partial_bl.tif' 
   ) as combined 
) as gdal_rasterised; 

--step 2: export the data 
------------------------- 
select lo_export((select loid from data_export.psql_export_temp_tbl limit 1), 'F:\mastering_postgis\chapter06\gray_50m_partial_bl.tif'); 


--step 3: cleanup, cleanup, everybody cleanup...  
------------------------------------------------ 
--release large object resources 
select lo_unlink((select loid from data_export.psql_export_temp_tbl limit 1)); 
--drop temp table 
drop table data_export.psql_export_temp_tbl; 

The preceding code uses a temporary table to save some large object data, most importantly the large object identifier that is next used to perform the actual export and cleanup. This is because otherwise we would not be able to access the large file by its ID, and the export function would fail.

You can now execute the code in psql:

psql -h localhost -p 5434 -U postgres -d mastering_postgis -f psql_export_raster.sql

The output should be similar to the following:

DROP TABLE
SELECT 1
 lo_export
-----------
         1
(1 row)

 lo_unlink
-----------
         1
(1 row)

DROP TABLE

Obviously, the very same code could be executed in PgAdmin too.