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)

  1. log_directory setting doesn't show full path, should we make it full (prepending PGDATA)?
  2. log_directory is irrelevant in some cases (for instance, if syslog is being used) - do we need to reflect it explicitly in the report?
  3. 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?
Edited by Anna