Load GeoTIFF into PostGIS with raster2pgsql

raster2pgsql turns a GDAL-readable raster into SQL, and the wrong flag can quietly drop a table you meant to keep. This walks through generating that SQL, reviewing it, loading it into a PostGIS raster table, and checking the result actually matches the source: about 20 minutes for a small raster.

• You need: the postgis package, a readable input file, the psql client, and a database with the PostGIS extension already enabled.
• Permissions: generating SQL is an ordinary user command. Loading it needs a PostgreSQL role that can create or write the target table. Nothing here needs a root shell.

The examples use the installed raster2pgsql from PostGIS 3.4.2, package version 3.4.2+dfsg-1ubuntu3, with GDAL 3.8 support.

1. Check the loader and the input format

The version line hides in the help output rather than behind a conventional --version flag:

$ 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]

Then confirm the source file is readable and the format is actually supported, before you generate a single line of SQL:

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

That -G list belongs to this executable and its GDAL build. A format supported on another machine is not automatically supported here. If your file format is missing, stop and get a loader built with the right GDAL driver.

2. Choose a target and a tile size

Use a schema and table name with no existing data for the first load. The command takes one or more raster paths, shell wildcards, and an optional schema.table destination.

• Pick a tile size deliberately. A raster table is stored as rows of tiles. 256x256 is a practical starting point for many datasets, not a universal optimum.
• Set the SRID when source metadata is missing or you have confirmed the real coordinate reference system. The -s value is not a reprojection, it only labels the raster metadata. Leave it out or set it to zero and the loader reads the raster's own metadata instead.

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

3. Generate SQL you can read before it touches the database

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 file rather than piping 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 your input rasters don't share the properties those constraints require.
• -I also triggers an ANALYZE for the created index.
• Read the file before running it. A generated SQL file is data that can create or alter database objects, so never run one from an untrusted source.

4. Load the new table

Run the reviewed file with the connection details for your environment. This example uses a local database and PostgreSQL's normal password and connection setup:

$ 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, and PostGIS may add its own notices. Treat a non-zero psql exit status as a failed load, and inspect the first error rather than assuming later messages repaired it.

5. Verify the table and the raster metadata

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

$ 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. Add -P if every tile must have the same dimensions; it pads the right-most and bottom-most tiles, which changes the stored edge data, so only use it when your downstream processing benefits from regular blocks.

Compare the SRID against the source dataset's documented CRS. A plausible-looking number does not prove the raster is correctly located. If reprojection is needed, do it as a separate, reviewed GDAL operation first: raster2pgsql does not reproject pixels just because -s was supplied.

6. Handle bands, filenames and overviews

By default every band is extracted.

• Select particular bands with -b, one-based, including ranges such as 1-3 or a comma-separated list.
• Store the source filename with -F, or use -n source_file, which implies -F as well.

$ 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 of them, which makes the order and contents of a wildcard meaningful: 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 live in the database, not as out-of-database file references.

7. Recover from a failed or unwanted load

If SQL generation failed, the input file was never touched. If psql failed partway through a generated script, inspect the database before retrying, since some statements may already have run. 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 never run that against a shared table without confirming the dependency impact first. For a production load, restore from 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.