Home PostgreSQL PostgreSQL Administration Commands – (Version 9,10,11,12,13)-Part3 (Monitoring Disk Usage)

PostgreSQL Administration Commands – (Version 9,10,11,12,13)-Part3 (Monitoring Disk Usage)

0
92

Each table has a primary heap disk file where most of the data is stored. If the table has any columns with potentially-wide values, there also might be a TOAST file associated with the table, which is used to store values too wide to fit comfortably in the main table. There will be one valid index on the TOAST table, if present. There also might be indexes associated with the base table. Each table and index is stored in a separate disk file — possibly more than one file, if the file would exceed one gigabyte.

Monitoring Methods:

You can monitor disk space in three ways. Use of SQL functions is the easiest one among three.

  • Using the SQL functions
  • Using the oid2name module
  • Using manual inspection of the system catalogs

SQL Function

  1. Display the space used by TOAST tables
postgres=# SELECT relname, relpages
FROM pg_class,
     (SELECT reltoastrelid
      FROM pg_class
      WHERE relname = 'customer') AS ss
WHERE oid = ss.reltoastrelid OR
      oid = (SELECT indexrelid FROM pg_index WHERE indrelid = ss.reltoastrelid)
ORDER BY relname;

relname | relpages ----------------------+---------- pg_toast_16806 | 0 pg_toast_16806_index | 1

(2 rows)

2. Display index sizes as well.

postgres=# SELECT c2.relname, c2.relpages
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'customer' AND
      c.oid = i.indrelid AND
      c2.oid = i.indexrelid
ORDER BY c2.relname;

       relname        | relpages
----------------------+----------
 customer_id_indexdex |       26


3. Display the table sizes in desc order

postgres=# SELECT relname, relpages
FROM pg_class
ORDER BY relpages DESC;

relname | relpages
-----------------------------------------------------+----------
rental | 150
payment | 108
pg_proc | 81
idx_unq_rental_rental_date_inventory_id_customer_id | 64
pg_attribute | 60
pg_toast_2618 | 60

4. Using some basic sql functions

select pg_database_size(11913);
Select pg_database_size(‘postgres’);
select pg_tablespace_size(1663);
select pg_tablespace_size('pg_default');

 

oid2name

oid2name is a utility program that helps administrators to examine the file structure used by PostgreSQL. To make use of it, you need to be familiar with the database file structure. Following is the help utility of the same.

oid2name --help
oid2name helps examining the file structure used by PostgreSQL.
Usage:
  oid2name [OPTION]...

Options:
  -f, --filenode=FILENODE    show info for table with given file node
  -i, --indexes              show indexes and sequences too
  -o, --oid=OID              show info for table with given OID
  -q, --quiet                quiet (don't show headers)
  -s, --tablespaces          show all tablespaces
  -S, --system-objects       show system objects too
  -t, --table=TABLE          show info for named table
  -V, --version              output version information, then exit
  -x, --extended             extended (show additional columns)
  -?, --help                 show this help, then exit

Connection options:
  -d, --dbname=DBNAME        database to connect to
  -h, --host=HOSTNAME        database server host or socket directory
  -H                         same as -h, deprecated option
  -p, --port=PORT            database server port number
  -U, --username=USERNAME    connect as specified database user
The default action is to show all database OIDs.
  1. Find Oid of all the databases as follows.

oid2name -p 5432 -U “postgres”

All databases:
    Oid   Database Name  Tablespace
-----------------------------------
  16384       dvdrental  pg_default
  24585  dvdrental_test  pg_default
  13709        postgres  pg_default
  13708       template0  pg_default
      1       template1  pg_default

2. Then go the desired old location and get top 10 db objects in the default tablespace ordered by size.

$ cd $PGDATA/base/<oid> 
$ ls -lS * | head -10

Disk Full Failure

  • The most important disk monitoring task of a database administrator is to make sure the disk doesn’t become full. A filled data disk will not result in data corruption, but it might prevent useful activity from occurring. If the disk holding the WAL files grows full, database server panic and consequent shutdown might occur.
  • If you cannot free up additional space on the disk by deleting other things, you can move some of the database files to other file systems by making use of tablespace.
  • Tip: Some file systems perform badly when they are almost full, so do not wait until the disk is completely full to take action.
    If your system supports per-user disk quotas, then the database will naturally be subject to whatever quota is placed on the user the server runs as. Exceeding the quota will have the same bad effects as running out of disk space entirely.