This article explores file system commands for monitoring and managing a PostgreSQL database. It covers data directory location, changing folders, querying database size and table sizes, listing tables and user-defined functions, monitoring query performance, and managing active users. These commands provide essential insights for administrators to optimize performance and troubleshoot issues efficiently.

Size of a specified database.

SELECT pg_database_size('my_database');
SELECT pg_size_pretty(pg_database_size(current_database()))
select datname, pg_size_pretty(pg_database_size(datname)) 
from pg_database;

The size of the 20 largest tables.

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 20;

List of all tables.

SELECT table_name FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','pg_catalog');

List of all user-defined functions. (check required !!!).

select oid, * from pg_namespace ;

Query times and intervals.

SELECT pid, age(query_start, clock_timestamp()), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

Stop a specific query.

SELECT pg_cancel_backend(procpid);

End the query.

SELECT pg_terminate_backend(procpid);

User list.

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity;

Change the folder (carefully!!!).

SET data_directory to new_directory_path;

DB change without rebooting (not for all parameters !!!)

SELECT pg_reload_conf();

Thats all folks 🙂