Export a PostGIS Table Safely with pgsql2shp

pgsql2shp turns a PostGIS table, or the result of a query, into an ESRI shapefile, the format every ancient GIS tool still expects. Give it fifteen minutes and you get a repeatable export you can rerun with confidence instead of nervously eyeballing it each time.

The examples below use the installed PostGIS 3.4.2 package, version 3.4.2+dfsg-1ubuntu3, and its bundled pgsql2shp release 3.4.2. You need the PostGIS command-line utility, network or local access to PostgreSQL, a login that can read the source table, and a writable destination directory. None of this needs sudo. The guide reads data and writes export files; it never touches the database itself.

1. Check the installed command

Confirm which binary you are about to run before you trust anything it tells you. Both of these are read-only:

$ command -v pgsql2shp
/usr/bin/pgsql2shp
$ pgsql2shp -?
RELEASE: 3.4.2 (c19ce56)
USAGE: pgsql2shp [<options>] <database> [<schema>.]<table>
       pgsql2shp [<options>] <database> <query>

The release identifier will differ between machines, and the local manpage still describes older 1.1.5-era behaviour. This installed binary also has a quiet mode, -q, that the manpage never mentions. Trust the help text from the binary you are actually running, and note the package version alongside your export:

$ dpkg-query -W -f='${Package} ${Version}\n' postgis
postgis 3.4.2+dfsg-1ubuntu3

2. Choose a destination without overwriting an export

-f sets the output base path. A shapefile is not one file but a family of them sharing that base name, so pick a fresh one in a directory you can write to, and never let two exports share a base name.

$ mkdir -p "$HOME/exports"
$ export OUT="$HOME/exports/roads-2026-09-26"
$ test ! -e "$OUT" && echo "destination base is unused"
destination base is unused

mkdir only touches your own home directory, no elevation needed. That final test stops an existing path being silently reused. If it prints nothing, pick another base name or go and look at what is already there. Do not delete it just to make the example fit.

3. Export a schema-qualified table

The command wants a database name and then either a table or a query. Qualify the table with its schema so there is no ambiguity when two schemas share a table name, and spell out the connection details when the local defaults are not the ones you want:

$ pgsql2shp -h db.example.test -p 5432 -u gis_reader \
    -f "$OUT" spatial roads
Initializing...
Done (or an error message)

Swap in your own host, user, schema and table for db.example.test, gis_reader, spatial and roads. Here the database name is the final positional value, spatial is the schema and roads is the table. The host, port and user only describe how to connect; they grant nothing on their own.

Leave out -h or -p and PostgreSQL falls back to its usual connection defaults. Leave out -u and the client uses its normal username behaviour. When you need a password, reach for PostgreSQL's own password mechanisms or a controlled prompt rather than -P, which puts it straight into the command line where shell history and process listings can see it. Keep that option for a controlled case, never a shared script.

A clean run prints progress and exits zero. Check the status and look at the files rather than trusting a particular line of output:

$ printf 'exit status: %s\n' "$?"
exit status: 0
$ ls -lh "$OUT".*

You should see the family of shapefile parts under your chosen base name. A non-zero exit means something upstream broke: check the connection, table privileges, geometry data and destination permissions before you run it again.

4. Select the geometry column when needed

A table with one geometry column is the easy case. With more than one, name the column you want with -g:

$ pgsql2shp -h db.example.test -p 5432 -u gis_reader \
    -g geom_3857 -f "$HOME/exports/roads-web" \
    spatial roads

Never guess the column from the table name. Ask whoever owns the database, or check the table definition yourself first. Pick the wrong one and you get an export that looks perfectly valid and is in the wrong coordinate system, or the wrong feature type entirely. Write down the column you used in your run notes so future-you does not have to guess too.

5. Export a query result

When you want a filtered or joined subset rather than the whole table, hand it a query instead. Quote the whole statement so the shell treats it as one argument:

$ pgsql2shp -h db.example.test -p 5432 -u gis_reader \
    -f "$HOME/exports/active-roads" spatial \
    "SELECT id, name, geom FROM roads WHERE status = 'active'"

Keep the geometry column in your selected columns and give it a clear name if the query joins across tables. Test the query in PostgreSQL first, ideally on a read-only account, so a SQL mistake never gets confused with a pgsql2shp problem.

The shell quoting is the usual trap: outer double quotes hold the SQL together, single quotes belong to the SQL string literal inside it. If your query has shell expansion characters in it, build it in a script you can review rather than typing it live, and never paste in untrusted input.

6. Handle useful options and failures

• -k keeps PostgreSQL's identifier case in the exported field names instead of normalising it.
• -r selects raw mode, useful when the table was not built by the normal PostGIS loader and you need its gid handling and identifiers left alone.
• -q keeps standard output quiet for scripts, but still check the exit status and capture standard error: quiet is not the same as successful.

DBF field names are short, so a table with long column names needs -m mapping-file to map them down to ten characters. One source name and one output name per line, separated by whitespace:

road_class ROADCLASS
surface_type SURFACE

If authentication fails, check host, port, database and username, then test the same connection with your normal PostgreSQL client. Table not found usually means a wrong database or schema. If writing fails, run test -w against the destination directory and pick a new base name. Running as root fixes none of these.