In order to get some help with the tool just type the following in the console:

    pgsql2shp

The general syntax for the tool is as follows:

    pgsql2shp [<options>] <database> [<schema>.]<table>
    pgsql2shp [<options>] <database> <query>

Shapefile is a format that is made up of a few files. The minimum set is SHP, SHX, and DBF. If PostGIS is able to determine the projection of the data, it will also export a PRJ file that will contain the SRS information, which should be understandable by the software able to consume a shapefile.

If a table does not have a geometry column, then only a DBF file that is the equivalent of the table data will be exported.

Let's export a full table first:

pgsql2shp -h localhost -p 5434 -u postgres -f full_earthquakes_dataset mastering_postgis data_import.earthquakes_subset_with_geom

The following output should be expected:

Initializing...
Done (postgis major version: 2).
Output shape: Point
Dumping: X [50 rows].

Now let's do the same, but this time with the result of a query:

pgsql2shp -h localhost -p 5434 -u postgres -f full_earthquakes_dataset mastering_postgis "select * from data_import.earthquakes_subset_with_geom limit 1"

The output will be very similar to what we have already seen:

Initializing...
Done (postgis major version: 2).
Output shape: Point
Dumping: X [1 rows].

In the data we previously imported, we do not have examples that would manifest shapefile limitations. It is worth knowing about them, though. You will find a decent description at https://en.wikipedia.org/wiki/Shapefile#Limitations. The most important ones are as follows:

• Column name length limit: The shapefile can only handle column names with a maximum length of 10 characters; pgsql2shp will not produce duplicate columns, though if there were column names that would result in duplicates when truncated, then the tool will add a sequence number.
• Maximum field length: The maximum field length is 255; psql will simply truncate the data upon exporting.

In order to demonstrate the preceding limitations, let's quickly create a test PostGIS dataset:

Create a schema if an export does not exist:

CREATE SCHEMA IF NOT EXISTS data_export;
CREATE TABLE IF NOT EXISTS data_export.bad_bad_shp (
  id character varying,
  "time" timestamp with time zone,
  depth numeric,
  mag numeric,
  very_very_very_long_column_that_holds_magtype character varying,
  very_very_very_long_column_that_holds_place character varying,
  geom geometry);
INSERT INTO data_export.bad_bad_shp select * from data_import.earthquakes_subset_with_geom limit 1;
UPDATE data_export.bad_bad_shp
SET very_very_very_long_column_that_holds_magtype = 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce id mauris eget arcu imperdiet tristique eu sed est. Quisque suscipit risus eu ante vestibulum hendrerit ut sed nulla. Nulla sit amet turpis ipsum. Curabitur nisi ante, luctus nec dignissim ut, imperdiet id tortor. In egestas, tortor ac condimentum sollicitudin, nisi lacus porttitor nibh, a tempus ex tellus in ligula. Donec pharetra laoreet finibus. Donec semper aliquet fringilla. Etiam faucibus felis ac neque facilisis vestibulum. Vivamus scelerisque at neque vel tincidunt. Phasellus gravida, ipsum vulputate dignissim laoreet, augue lacus congue diam, at tempus augue dolor vitae elit.';

Having prepared a vigilante dataset, let's now export it to SHP to see if our SHP warnings were right:

pgsql2shp -h localhost -p 5434 -u postgres -f bad_bad_shp mastering_postgis data_export.bad_bad_shp

When you now open the exported shapefile in a GIS client of your choice, you will see our very, very long column names renamed to VERY_VERY_ and VERY_VE_01. The content of the very_very_very_long_column_that_holds_magtype field has also been truncated to 255 characters, and is now Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce id mauris eget arcu imperdiet tristique eu sed est. Quisque suscipit risus eu ante vestibulum hendrerit ut sed nulla. Nulla sit amet turpis ipsum. Curabitur nisi ante, luctus nec dignissim ut.

For the sake of completeness, we'll also export a table without geometry, so we can be certain that pgsql2shp exports only a DBF file:

    pgsql2shp -h localhost -p 5434 -u postgres -f a_lonely_dbf mastering_postgis "select id, place from data_import.earthquakes_subset_with_geom limit 1"