Load GeoTIFF into PostGIS with raster2pgsql

You will turn a GDAL-supported raster file into SQL, load it into a PostGIS raster table, and check that the imported table contains the data you expected. The examples use the installed raster2pgsql from PostGIS 3.4.2, package version 3.4.2+dfsg-1ubuntu3, with GDAL 3.8 support.

Allow about 20 minutes for a small raster and a working PostgreSQL database. You need the postgis package, a readable input file, the psql client, and a database in which the PostGIS extension is already enabled. The commands that generate SQL are ordinary user commands. The database load needs a PostgreSQL role with permission to create or write the target table. Nothing here needs a root shell.

1. Check the installed loader and input format

Start with read-only checks. The loader's version line is printed as part of its help output rather than by a conventional --version option:

$ command -v raster2pgsql
/usr/bin/raster2pgsql
$ raster2pgsql -? 2>&1 | head -4
RELEASE: 3.4.2 GDAL_VERSION=38 (c19ce56)
USAGE: raster2pgsql [options] raster [raster ...] [schema.table]

Confirm the source file is readable before producing SQL:

$ test -r /path/to/input.tif && echo readable
readable
$ raster2pgsql -G | grep -E 'GeoTIFF|Virtual Raster'
  Virtual Raster
  GeoTIFF

The -G list belongs to this executable and its GDAL build. Do not assume that a format supported by another machine is supported here. If your file format is absent, stop and use a loader built with the required GDAL driver.

2. Choose a target and tile size

Use a schema and table name that do not already contain data for the first load. The command accepts one or more raster paths, shell wildcards, and an optional schema.table destination. A raster table is stored as rows of tiles, so choose a tile size deliberately. 256x256 is a practical starting point for many datasets, but it is not a universal optimum.

Set the SRID when the source metadata is absent or when you have confirmed the correct coordinate reference system. The -s value is not a reprojection: it labels the raster metadata. If it is omitted or set to zero, the loader checks the raster metadata.

Checkpoint: write down these values before running a load:

• Input: /path/to/input.tif
• Database: gisdb
• Target: public.elevation
• Known SRID: 27700, only if that is genuinely the source CRS

3. Inspect SQL without changing the database

Generate a reviewable SQL file first. The default -c mode creates and populates a new table, and -I adds a GiST spatial index. The command writes SQL to standard output, so redirect it to a new file rather than piping it straight into a production database on the first run:

$ raster2pgsql -c -s 27700 -t 256x256 -I -C \
    /path/to/input.tif public.elevation > /tmp/elevation.sql
$ test -s /tmp/elevation.sql && echo 'SQL generated'
SQL generated
$ sed -n '1,24p' /tmp/elevation.sql

-C adds the standard raster constraints after loading. They can fail when the input rasters do not share the properties those constraints require. -I also causes an ANALYZE for the created index. Read the file and check the target name before continuing. A generated SQL file is data that can create or alter database objects, so do not run one received from an untrusted source.

4. Load the new table

Run the reviewed file with the connection details appropriate to your environment. This example uses a local database and relies on PostgreSQL's normal password and connection configuration:

$ psql --dbname=gisdb --file=/tmp/elevation.sql
CREATE TABLE
INSERT 0 1
CREATE INDEX
ANALYZE

The exact number of INSERT messages depends on the raster and tile size. Your output may also contain notices from PostGIS. Treat a non-zero psql exit status as a failed load and inspect the first error, rather than assuming later messages repaired it.

Warning: do not substitute -d for -c casually. The -d mode drops the named table and recreates it, which is destructive and irreversible unless you have a backup or can rebuild the data. Use -c for a new table, -a only when the existing table has exactly the same schema, and -p when you only want table-creation SQL.

5. Verify the table and raster metadata

Check the table exists, has rows, and reports the expected raster properties:

$ psql --dbname=gisdb --command="\d+ public.elevation"
$ psql --dbname=gisdb --command="
SELECT count(*) AS tiles,
       ST_Width(rast) AS tile_width,
       ST_Height(rast) AS tile_height,
       ST_SRID(rast) AS srid
FROM public.elevation
GROUP BY ST_Width(rast), ST_Height(rast), ST_SRID(rast)
ORDER BY tiles DESC;"
 tiles | tile_width | tile_height | srid
-------+------------+-------------+------
    ...|        256 |         256 | 27700

The final tile on an edge can be smaller than 256x256. If every tile must have the same dimensions, add -P to pad the right-most and bottom-most tiles. Padding changes the stored edge tiles, so use it only when your downstream processing benefits from regular blocks.

Compare the SRID with the source dataset's documented CRS. A plausible-looking number does not prove that the raster is correctly located. If the source needs reprojection, perform that as a separate, reviewed GDAL operation before loading; raster2pgsql does not reproject the pixels merely because -s was supplied.

6. Handle bands, filenames and overviews

By default all bands are extracted. Select particular one-based bands with -b, including ranges such as 1-3 or a comma-separated list. Add -F when you want the source filename stored in a column, or use -n source_file, which also implies -F.

$ raster2pgsql -c -s 27700 -b 1 -t auto -n source_file \
    /path/to/tiles/*.tif public.dem > /tmp/dem.sql
$ psql --dbname=gisdb --file=/tmp/dem.sql

The auto tile size is calculated from the first raster and applied to all rasters. That makes the order and contents of a wildcard meaningful, so use explicit file paths or review the expansion when reproducibility matters. An overview factor such as -l 2,4 creates overview tables named with the documented o_<factor>_<table> pattern. Overviews are stored in the database and are not out-of-database file references.

7. Recover from a failed or unwanted load

If SQL generation failed, the input file was not changed. If psql failed during a normal generated script, inspect the database before retrying: some statements may have completed. For a disposable table, remove it explicitly after checking the name:

$ psql --dbname=gisdb --command="DROP TABLE IF EXISTS public.elevation CASCADE;"

CASCADE can remove dependent objects, so do not run that command against a shared table without confirming the dependency impact. For a production load, restore from the database backup or use a new table name, validate it, then arrange a reviewed cutover. Keep the original raster files until the SQL load and metadata checks pass.