A009 (merge with A008) File system on disk partition with PGDATA - collect data
Goal
Know what filesystem types are used for PGDATA, for all tablespaces, log directory, and stats_temp_directory directory.
How to implement
df -fT
shows the the types of file system.
df /path/to/dir
shows info (including FS type) for partition, where the dir /path/to/dir
is located. Example – on Ubuntu, here is how we can see FS type for /var/lib/postgresql
$ df -hT /var/lib/postgresql/9.6/main
Filesystem Type Size Used Avail Use% Mounted on
/dev/mapper/cachedev0 ext4 5.4T 1.1T 4.1T 21% /var/lib/postgresql
How to get path to PGDATA:
test=# select setting from pg_settings where name = 'data_directory';
setting
------------------------------
/var/lib/postgresql/9.6/main
(1 row)
To get locations of all tablespaces, use pg_tablespaces
catalog + pg_tablespace_location()
function (see https://stackoverflow.com/questions/35221850/where-in-the-db-is-the-location-of-a-postgres-tablespace-stored)
To get log directory location, use:
test=# select setting from pg_settings where name = 'log_directory';
setting
------------------------------
pg_log
(1 row)
And finally, for stats_temp_directory:
test=# select setting from pg_settings where name = 'stats_temp_directory';
setting
----------
/dev/shm
(1 row)
Acceptance criteria
As a DBA running checkup procedure, for each server (the master and all replicas) I see what FS type is being used to store:
-
PGDATA directory -
all tablespaces -
log directory -
stats_temp_directory -
above for all standbys
UNDECIDED / OPEN QUESTIONS (to discuss)
-
log_directory
setting doesn't show full path, should we make it full (prepending PGDATA)? -
log_directory
is irrelevant in some cases (for instance, if syslog is being used) - do we need to reflect it explicitly in the report? -
pg_xlog (pg_wal in Postgres 10+) can live on a separate drive, this is an old and common practice. However, there is no setting for that - it's done using a symlink. Should we make attempts to detect this?