Export a PostGIS Table Safely with pgsql2shp

You will finish with a repeatable way to export a PostGIS table, or the result of a SQL query, to an ESRI Shapefile using pgsql2shp. The examples use the installed PostGIS 3.4.2 package, version 3.4.2+dfsg-1ubuntu3, and its bundled pgsql2shp release 3.4.2.

Allow about fifteen minutes for a known-good database connection and a small table. You need the PostGIS command-line utility, network or local access to PostgreSQL, a database login that can read the source, and a writable destination directory. The commands below do not need sudo. This guide reads data and writes export files; it does not alter the database.

1. Check the installed command

Start by confirming which executable will run and asking it for its built-in help. These are ordinary, read-only checks:

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

The exact release identifier can differ between machines. The local manpage describes older 1.1.5-era behaviour, while this installed binary also offers -q for quiet mode. For a real export, trust the help from the binary you are about to run and keep the package version with your export notes:

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

2. Choose a destination without overwriting an export

The -f option supplies the output filename. Use a new, explicit path in a directory you can write. Treat that path as a set of output files: the Shapefile format uses related files with the same base name, so do not place an unrelated export beside it under the same 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

The mkdir command changes your own home directory, but does not require elevation. The final test prevents an existing path from being silently selected by your workflow. If it reports nothing, choose another base name or inspect the existing files first. Do not remove them merely to make the example fit.

3. Export a schema-qualified table

The required positional arguments are the database name and either a table name or a query. A schema-qualified table avoids ambiguity when more than one schema contains the same table. Supply connection details explicitly when the local PostgreSQL defaults are not correct:

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

Replace db.example.test, gis_reader, spatial and roads with values from your environment. The database name is the final positional value in this example, spatial is the schema, and roads is the table. The host, port and user options only describe how to connect; they do not grant permissions.

If you omit -h or -p, PostgreSQL connection defaults are used. If you omit -u, the client chooses its normal PostgreSQL username behaviour. When authentication needs a password, prefer PostgreSQL's normal password mechanisms or a controlled prompt. Passing -P places the password in the command line and can expose it through shell history or process inspection, so reserve that option for a controlled case and do not put a real password in a shared script.

A successful command normally prints progress and exits with status zero. Confirm the status and inspect files without assuming a particular progress message:

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

The file list should show the related Shapefile outputs using your chosen base name. If the command exits non-zero, preserve the diagnostic and check the database connection, table privileges, geometry data and destination permissions before rerunning.

4. Select the geometry column when needed

A table with one spatial column can use the basic form. If the table has multiple geometry columns, use -g with the exact column to export:

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

Do not guess the column name from the table name. Ask the database owner or inspect the table definition with your approved PostgreSQL tooling first. Choosing the wrong geometry column can produce a valid-looking export in the wrong coordinate system or with the wrong feature type. Keep the exported column in your run notes.

5. Export a query result

Use the query form when the export should contain a filtered or joined result rather than every row in a table. Quote the complete SQL statement so the shell passes it as one positional 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 the selected columns and give it a clear, unambiguous name if the query contains joins. Test a complex query in PostgreSQL first, using a read-only account where possible. This avoids confusing SQL errors with pgsql2shp connection or output errors.

Shell quoting is the common trap here. The outer double quotes keep spaces in the SQL together, while the SQL string literal uses single quotes. If your query contains shell expansion characters, use a carefully quoted script or a temporary SQL-building step that you review before execution. Do not paste untrusted input into the query.

6. Handle useful options and failures

-k keeps PostgreSQL identifier case in the exported fields. -r selects raw mode, which is useful when the table was not created by the normal PostGIS loader and you need its identifiers and gid handling left alone. Choose these deliberately because downstream GIS tools may expect particular field names.

DBF field names are limited in length. If the source has long column names, -m mapping-file supplies mappings to ten-character output names. The mapping file contains one source name and one output name per line, separated by whitespace:

road_class ROADCLASS
surface_type SURFACE

Use -q when a script must keep standard output quiet, but still capture the exit status and log standard error. Quiet output is not the same as successful output.

If authentication fails, verify the host, port, database and username, then test the same connection with your normal PostgreSQL client. If the table is not found, check the database name and schema qualification. If writing fails, run test -w on the destination directory and choose a new base name. None of these errors is fixed by running the exporter as root.