PostgreSQLのデータベース一覧とディスク使用量を調べる方法｜pg_databaseとpg_database_sizeで容量を把握する

この記事のポイント

・pg_database_size()関数でDBごとのサイズをバイト単位で取得できる
・psqlの\l+でSQL不要でデータベース一覧＋サイズをまとめて確認できる
・pg_size_pretty()と組み合わせると「1547 MB」形式で読みやすくなる
・template0/template1はシステム用DBなので監視対象から除外してよい

# OSのpostgresユーザーに切り替える
$ sudo -i -u postgres

# psqlを起動する
$ psql
psql (16.4)
Type "help" for help.

postgres=#

postgres=# \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+----------+----------+-------------+-------------+-----------------------
 appdb      | appuser  | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | 
 postgres   | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | 
 template0  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=# \l+
                                                List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace 
------------+----------+----------+-------------+-------------+-------------------+---------+------------
 appdb      | appuser  | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |                   | 1547 MB | pg_default 
 postgres   | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |                   | 8293 kB | pg_default 
 template0  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres      +| 7993 kB | pg_default 
 template1  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres      +| 7993 kB | pg_default 
(4 rows)

postgres=# SELECT datname FROM pg_database ORDER BY datname;
  datname  
-----------
 appdb
 postgres
 template0
 template1
(4 rows)

postgres=# SELECT datname FROM pg_database WHERE datistemplate = false ORDER BY datname;
  datname  
-----------
 appdb
 postgres
(2 rows)

postgres=# SELECT pg_database_size('appdb');
 pg_database_size 
------------------
       1621360640
(1 row)

postgres=# SELECT
    datname AS "データベース名",
    pg_size_pretty(pg_database_size(datname)) AS "サイズ",
    pg_database_size(datname) AS "バイト数（ソート用）"
FROM pg_database
WHERE datistemplate = false
ORDER BY pg_database_size(datname) DESC;

 データベース名 |  サイズ  | バイト数（ソート用）
----------------+----------+---------------------
 appdb          | 1547 MB  |          1621360640
 postgres       | 8293 kB  |             8492032
(2 rows)

postgres=# \c appdb
You are now connected to database "appdb" as user "postgres".
appdb=#

appdb=# SELECT
    schemaname AS "スキーマ",
    tablename  AS "テーブル名",
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS "合計サイズ",
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename))       AS "テーブル本体",
    pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename))        AS "インデックス"
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

 スキーマ |     テーブル名     | 合計サイズ | テーブル本体 | インデックス 
----------+--------------------+------------+--------------+--------------
 public   | order_histories    | 892 MB     | 712 MB       | 180 MB
 public   | access_logs        | 421 MB     | 389 MB       | 32 MB
 public   | users              | 67 MB      | 52 MB        | 15 MB
 public   | products           | 12 MB      | 9112 kB      | 3072 kB
 public   | categories         | 384 kB     | 256 kB       | 128 kB
(5 rows)

# postgresスーパーユーザーで接続する
$ sudo -u postgres psql

# 文字列（テキスト型）で渡す → OK
SELECT pg_database_size('appdb');

# current_database()関数でカレントDBのサイズを取得 → OK
SELECT pg_database_size(current_database());