\lコマンドでデータベース一覧を確認する
PostgreSQLでデータベース一覧を確認するには、psqlコマンドラインツールの\lコマンドを使う。
\l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+------------+------------+--------+-----------+-----------------------
analytics | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
postgres | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
reportdb | app_user | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
shopdb | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
template0 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(6 rows)
\listと入力しても同じ結果になる。
自分で作成したデータベースに加えて、初期状態から存在するpostgres・template0・template1も表示される。template0とtemplate1はCREATE DATABASEの雛形となるテンプレートデータベースである。
表示される列はPostgreSQLのバージョンによって異なる。
PostgreSQL 14以前はName・Owner・Encoding・Collate・Ctype・Access privilegesの6列である。
PostgreSQL 15以降はロケール関連の列が段階的に追加されており、15でLocale ProviderとICU Locale、16でICU Rulesが加わった。
17ではICU LocaleがLocaleに変わっている。
\l+でサイズと説明も表示する
+を付けるとサイズ・テーブルスペース・説明の列が追加される。
\l+
Name | Owner | ... | Access privileges | Size | Tablespace | Description
-----------+----------+-----+-----------------------+---------+------------+--------------------------------------------
analytics | postgres | ... | | 7353 kB | pg_default |
postgres | postgres | ... | | 7510 kB | pg_default | default administrative connection database
reportdb | app_user | ... | | 7353 kB | pg_default |
shopdb | postgres | ... | | 7353 kB | pg_default |
template0 | postgres | ... | =c/postgres +| 7353 kB | pg_default | unmodifiable empty database
| | | postgres=CTc/postgres | | |
template1 | postgres | ... | =c/postgres +| 7353 kB | pg_default | default template for new databases
| | | postgres=CTc/postgres | | |
(6 rows)
上記は横幅の都合で中央の列を省略している。
サイズの確認方法は以下も参照。
参考: 【PostgreSQL】各データベースのサイズを確認する
Access privileges列はロール名=権限記号/権限を付与したロール名の形式で表示される。
データベースに対する権限記号はCがCREATE、TがTEMPORARY、cがCONNECTを表す。
テンプレートデータベースの=c/postgresは、PUBLIC（全ロール）にCONNECT権限を付与した状態を意味する。
Access privileges列が空欄のデータベースは、権限を変更していないデフォルトの状態である。
デフォルトではPUBLICにCONNECTとTEMPORARYが与えられているため、空欄は「誰も接続できない」意味ではない。REVOKEで権限を変更すると、そのデータベースの権限が明示的に表示されるようになる。
名前で絞り込む
\lの後ろにパターンを指定すると、名前が一致するデータベースだけを表示する。
\l shop*
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
--------+----------+----------+-----------------+------------+------------+--------+-----------+-------------------
shopdb | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
(1 row)
psqlに接続せずに一覧を表示する
psqlコマンドに-lオプションを付けると、対話モードに入らずデータベース一覧だけを表示して終了する。
$ psql -U postgres -l
-lオプションは接続先を指定しなければpostgresデータベースに接続する。postgresデータベースを削除した環境では-dオプションで接続先を指定する。
pg_databaseカタログでデータベース一覧を確認する
SQLでデータベース一覧を取得する場合はpg_databaseシステムカタログを参照する。
psqlの\lも内部ではpg_databaseを参照している。
SELECT datname, pg_get_userbyid(datdba) AS owner
FROM pg_database
ORDER BY datname;
datname | owner
-----------+----------
analytics | postgres
postgres | postgres
reportdb | app_user
shopdb | postgres
template0 | postgres
template1 | postgres
(6 rows)
datdbaは所有者のOIDのため、pg_get_userbyid関数でロール名に変換する。
参考: 【PostgreSQL】テーブルとデータベースのoidを取得する方法
pg_databaseの主なカラム
pg_databaseには以下のようなカラムが存在する。
- oid: データベースのOID
- datname: データベース名
- datdba: 所有者のロールのOID
- encoding: 文字エンコーディングの番号
- datcollate: 照合順序（
LC_COLLATE） - datctype: 文字分類（
LC_CTYPE） - datistemplate: テンプレートとして複製できるか（
trueならCREATEDB権限を持つロールが複製でき、falseなら所有者とスーパーユーザーだけが複製できる） - datallowconn: 接続を許可するか
- datconnlimit: 同時接続数の上限（
-1は無制限、-2はデータベースが無効な状態） - dattablespace: デフォルトのテーブルスペースのOID
encodingは番号で格納されているため、pg_encoding_to_char関数で名前に変換する。
SELECT datname, pg_encoding_to_char(encoding) AS encoding, datcollate,
datistemplate, datallowconn
FROM pg_database
ORDER BY datname;
datname | encoding | datcollate | datistemplate | datallowconn
-----------+----------+------------+---------------+--------------
analytics | UTF8 | en_US.utf8 | f | t
postgres | UTF8 | en_US.utf8 | f | t
reportdb | UTF8 | en_US.utf8 | f | t
shopdb | UTF8 | en_US.utf8 | f | t
template0 | UTF8 | en_US.utf8 | t | f
template1 | UTF8 | en_US.utf8 | t | t
(6 rows)
テンプレートデータベースを除外する
テンプレートデータベースを除外する場合はdatistemplateがfalseの行を取得する。
初期状態から存在するpostgresデータベースはテンプレートではないため結果に残る。
SELECT datname FROM pg_database WHERE NOT datistemplate ORDER BY datname;
datname
-----------
analytics
postgres
reportdb
shopdb
(4 rows)
template0はdatallowconnがfalseで接続を禁止している。
接続できるデータベースだけに絞る場合はdatallowconnを条件に指定する。
ただしdatallowconnがtrueのtemplate1は残るため、両方を組み合わせるとよい。
SELECT datname FROM pg_database
WHERE datallowconn AND NOT datistemplate
ORDER BY datname;
シェルスクリプトから一覧を取得する
-A（非整列出力モード）と-t（ヘッダーと行数の非表示）を組み合わせると、データベース名だけを1行ずつ出力できる。
$ psql -U postgres -At -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate ORDER BY datname"
analytics
postgres
reportdb
shopdb
psql -lの出力を加工するより確実にデータベース名を取得できる。
参考: 【PostgreSQL】psqlコマンドの結果をシェルスクリプトで利用する(psql -qtAF)
すべてのロールが同じ一覧を取得できる
pg_databaseはクラスタ全体で共有されるカタログであり、すべてのロールが読み取れる。
接続権限のないデータベースも一覧に表示される。
\lもpg_databaseをそのまま参照するため、一般ユーザーで実行してもスーパーユーザーと同じ結果になる。
データベース一覧の表示自体は制限されず、実際の接続時に権限がチェックされる。
例外は\l+のSize列である。
接続権限のないデータベースはサイズを計算できないためNo Accessと表示される。
Name | Owner | ... | Access privileges | Size | Tablespace
-----------+----------+-----+-----------------------+-----------+------------
analytics | postgres | ... | =T/postgres +| No Access | pg_default
| | | postgres=CTc/postgres | |
postgres | postgres | ... | | 7510 kB | pg_default
スキーマ一覧の場合はinformation_schema.schemataが権限で絞り込むため挙動が異なる。
参考: 【PostgreSQL】スキーマ一覧を確認する
なおinformation_schemaには接続中のデータベースの情報しか含まれないため、データベース一覧を取得するビューは存在しない。
接続中のデータベースを確認する
一覧ではなく現在接続しているデータベースを確認する場合はcurrent_database関数を使う。
SELECT current_database();
psqlのプロンプトにもデータベース名が表示され、\conninfoコマンドで接続情報をまとめて確認できる。
