Where does PostGIS store data loaded shp2pgsql?

Question

When I install PostGIS, and its address_standardizer and postgis_tiger_geocoder extensions there are many tables created which I can see with \dt. What do these tables do and where does shp2pgsql load the shapefile?

To show this, an empty db, first what have the db? nothing, checking with \dt.

testgis=# -- Enable PostGIS (as of 3.0 contains just geometry/geography)
CREATE EXTENSION postgis;
-- enable raster support (for 3+)
CREATE EXTENSION postgis_raster;
-- Enable Topology
CREATE EXTENSION postgis_topology;
-- Enable PostGIS Advanced 3D
-- and other geoprocessing algorithms
-- sfcgal not available with all distributions
CREATE EXTENSION postgis_sfcgal;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- rule based standardizer
CREATE EXTENSION address_standardizer;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
ERROR:  can not open the control file of extension «/usr/share/postgresql-13/extension/postgis_sfcgal.control»: File does not exist
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION

Now check with \dt.

With postgres user:

                  List of relations
 Schema |           Name           | Type  |  Owner   
--------+--------------------------+-------+----------
 public | spatial_ref_sys          | table | postgres
 public | us_gaz                   | table | postgres
 public | us_lex                   | table | postgres
 public | us_rules                 | table | postgres
 tiger  | addr                     | table | postgres
 tiger  | addrfeat                 | table | postgres
 tiger  | bg                       | table | postgres
 tiger  | county                   | table | postgres
 tiger  | county_lookup            | table | postgres
 tiger  | countysub_lookup         | table | postgres
 tiger  | cousub                   | table | postgres
 tiger  | direction_lookup         | table | postgres
 tiger  | edges                    | table | postgres
 tiger  | faces                    | table | postgres
 tiger  | featnames                | table | postgres
 tiger  | geocode_settings         | table | postgres
 tiger  | geocode_settings_default | table | postgres
 tiger  | loader_lookuptables      | table | postgres
 tiger  | loader_platform          | table | postgres
 tiger  | loader_variables         | table | postgres
 tiger  | pagc_gaz                 | table | postgres
 tiger  | pagc_lex                 | table | postgres
 tiger  | pagc_rules               | table | postgres
 tiger  | place                    | table | postgres
 tiger  | place_lookup             | table | postgres
 tiger  | secondary_unit_lookup    | table | postgres
 tiger  | state                    | table | postgres
 tiger  | state_lookup             | table | postgres
 tiger  | street_type_lookup       | table | postgres
 tiger  | tabblock                 | table | postgres
 tiger  | tract                    | table | postgres
 tiger  | zcta5                    | table | postgres
 tiger  | zip_lookup               | table | postgres
 tiger  | zip_lookup_all           | table | postgres
 tiger  | zip_lookup_base          | table | postgres
 tiger  | zip_state                | table | postgres
 tiger  | zip_state_loc            | table | postgres
(37 rows)

With normal user:

            Listado de relaciones
 Esquema |     Nombre      | Tipo  |  Dueño   
---------+-----------------+-------+----------
 public  | spatial_ref_sys | tabla | postgres
 public  | us_gaz          | tabla | postgres
 public  | us_lex          | tabla | postgres
 public  | us_rules        | tabla | postgres
(4 filas)

Answer 1

shp2pgsql will by default create one table for each imported shapefile. However, you can have it load multiple shapefiles into the same table using the -a option.

The table spatial_ref_sys, as the name suggests, only keeps the data for the spatial reference systems, which are vital for operations like coordinates transformation.

The tables us_gaz, us_lex, and us_rules are related to the extension address_standardizer.

The other tables in the schema tiger are related to the extension postgis_tiger_geocoder - also system tables that shouldn't really worry about.