The two storage locations which matter the most when creating a PostgreSQL cluster storage snapshot are the data directory and any tablespaces associated with the cluster. A r_volume_and_file_system_architectural_layout_02.html#r_volume_and_file_system_architectural_layout_02__r_volume_and_file_system_architectural_layout_02-tbl1will have only a single storage location while a layout separating database storage locations using tablespaces could have multiple other volumes to be considered.
Data Directory
PostgreSQL keeps track of the physical location of its data directory in the configuration parameters. To retrieve the data directory location use the show command:
show data_directory;
data_directory
------------------------
/var/lib/pgsql/13/data
(1 row)
Tablespaces
To identify any tablespaces apart of the cluster query the pg_tablespace view for the relevant information:
SELECT *, pg_tablespace_location(oid) FROM pg_tablespace;
A PostgreSQL cluster with the default tablespace will not show any values other than pg_default or pg_global.
postgres=# SELECT *, pg_tablespace_location(oid) FROM pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions | pg_tablespace_location
------+------------+----------+--------+------------+------------------------
1663 | pg_default | 10 | | |
1664 | pg_global | 10 | | |
(2 rows)If there are additional tablespaces present then the output should show them and their location in the pg_tablespace_location column:
oid | spcname | spcowner | spcacl | spcoptions | pg_tablespace_location
--------+-----------------+----------+--------+------------+------------------------
1663 | pg_default | 10 | | |
1664 | pg_global | 10 | | |
780355 | database02_tbps | 10 | | | /postgres/database02
780356 | database03_tbps | 10 | | | /postgres/database03
780357 | database04_tbps | 10 | | | /postgres/database04
780358 | database01_tbps | 10 | | | /postgres/database01
If required the tablespace to database mapping can be seen when using the output of /l+ in psql:
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
------------+----------+----------+-------------+-------------+-----------------------+---------+-----------------+------------------------------------------
--
database01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +| 7753 kB | database01_tbps |
| | | | | postgres=CTc/postgres+| | |
| | | | | doesuser=CTc/postgres | | |
database02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +| 7753 kB | database02_tbps |
| | | | | postgres=CTc/postgres+| | |
| | | | | doesuser=CTc/postgres | | |
database03 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +| 7753 kB | database03_tbps |
| | | | | postgres=CTc/postgres+| | |
| | | | | doesuser=CTc/postgres | | |
database04 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +| 7753 kB | database04_tbps |
| | | | | postgres=CTc/postgres+| | |
| | | | | doesuser=CTc/postgres | | |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7917 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7753 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7753 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(7 rows)