PostgreSQLサーバーの詳細情報をSQLだけで取得する方法は？まとめて解説！

PostgreSQLサーバーの詳細情報をSQLだけで取得する方法は？まとめて解説！ Database

PostgreSQLサーバーの様々な情報をSQLで取得する方法を紹介します。本番環境のPostgreSQLサーバーにSSHログインできない場合には、SQLだけでPostgreSQLサーバーの情報を取得できると大変便利です。どのような設定でサーバーが稼働しているのか確認することで、パフォーマンス向上などの参考になります。ここで紹介するSQLは、PostgreSQLのバージョン9以上で検証したSQLとなっています。

PostgreSQLのバージョン情報を取得

PostgreSQLのバージョンを取得するには、次のようなSQLを実行します。バージョンの完全な情報や、バージョン番号だけを取得することが出来ます。

SELECT
    version(),
    current_setting('server_version') AS "server_version",
    current_setting('server_version_num') AS "server_version_num";

+--------------------+------------------------------------------------------+
| version            | PostgreSQL 13.18 on x86_64-pc-linux-gnu, ..., 64-bit |
| server_version     | 13.18                                                |
| server_version_num | 130018                                               |
+--------------------+------------------------------------------------------+

PostgreSQLサーバーが起動された日時を取得

PostgreSQLサーバーがいつ起動されたのかを調べるには、次のSQLを実行します。

SELECT pg_postmaster_start_time();

+-------------------------------+
|   pg_postmaster_start_time    |
+-------------------------------+
| 2024-12-20 14:35:32.022951+09 |
+-------------------------------+

PostgreSQLサーバーの設定ファイルが最後に読み込まれた日時を取得

PostgreSQLサーバーの設定ファイルが最後に読み込まれた日時を取得するには次のSQLを実行します。これはPostgreSQLサーバーが起動された日時ではなく、PostgreSQLサーバーの設定ファイルが最後に読み込まれた日時を指しています。PostgreSQLの設定ファイルは、「pg_ctl reload」コマンドや「SELECT pg_reload_conf();」コマンドを実行することで、設定ファイルだけを再読み込みさせることが出来ます。

SELECT pg_conf_load_time();

+-------------------------------+
|       pg_conf_load_time       |
+-------------------------------+
| 2024-12-25 04:44:34.229445+09 |
+-------------------------------+

PostgreSQLサーバーの設定ファイルの場所を取得する

PostgreSQLサーバーの設定ファイルがどこにあるか場所を確認することが出来ます。postgresql.conf、pg_hba.conf、pg_ident.confというファイルの位置やファイル名を確認することが出来ます。

SELECT
    current_setting('config_file') AS "config_file",
    current_setting('hba_file') AS "hba_file",
    current_setting('ident_file') AS "ident_file";

+-------------+----------------------------------------+
| config_file | /var/lib/pgsql/13/data/postgresql.conf |
| hba_file    | /var/lib/pgsql/13/data/pg_hba.conf     |
| ident_file  | /var/lib/pgsql/13/data/pg_ident.conf   |
+-------------+----------------------------------------+

PostgreSQLサーバーのデータが格納されているディレクトリを確認

PostgreSQLデータベースのデータがどのディレクトリに保存されているのか場所を確認することができます。

SELECT current_setting('data_directory') AS "data_directory";

+------------------------+
|     data_directory     |
+------------------------+
| /var/lib/pgsql/13/data |
+------------------------+

PostgreSQLサーバーのログ出力設定を取得

以下のSQLで、PostgreSQLサーバー上でログファイルへの出力が有効化されているか、ログがどのディレクトリに州つりょくされているか、現在ログが出力されているログファイル名は何か等の設定情報を確認することが出来ます。

SELECT
    current_setting('logging_collector') AS "logging_collector",
    current_setting('log_destination') AS "log_destination",
    current_setting('log_directory') AS "log_directory",
    current_setting('log_filename') AS "log_filename";

+--------------------+-------------------+
| logging_collector  | on                |
| log_destination    | stderr            |
| log_directory      | log               |
| log_filename       | postgresql-%a.log |
+--------------------+-------------------+

PostgreSQL-10以上のバージョンであれば、次のように現在書き込みが行われているログファイル名を取得することも可能です。

SELECT pg_current_logfile();

+--------------------+------------------------+
| pg_current_logfile | log/postgresql-Sat.log |
+--------------------+------------------------+

PostgreSQLサーバーのメモリー関連情報を取得

PostgreSQLサーバーのメモリー情報を取得するには次のSQLを実行します。

SELECT
    current_setting('shared_buffers') AS "shared_buffers",
    current_setting('work_mem') AS "work_mem",
    current_setting('temp_buffers') AS "temp_buffers",
    current_setting('maintenance_work_mem') AS "maintenance_work_mem",
    current_setting('effective_cache_size') AS "effective_cache_size";

+----------------------+------+
| shared_buffers       | 4GB  |
| work_mem             | 4MB  |
| temp_buffers         | 8MB  |
| maintenance_work_mem | 1GB  |
| effective_cache_size | 12GB |
+----------------------+------+

PostgreSQLサーバーのデータベース接続関連情報取得

最大接続数関連情報やパスワード暗号方式などを取得することが出来ます。

SELECT
    current_setting('max_connections') AS "max_connections",
    current_setting('max_files_per_process') AS "max_files_per_process",
    current_setting('track_activities') AS "track_activities",
    current_setting('track_counts') AS "track_counts",
    current_setting('password_encryption') AS "password_encryption";

+-----------------------+------+
| max_connections       | 2048 |
| max_files_per_process | 1000 |
| track_activities      | on   |
| track_counts          | on   |
| password_encryption   | md5  |
+-----------------------+------+

PostgreSQLサーバーの日時を確認する

PostgreSQLサーバーの時刻を確認するSQLです。様々な関数で取得することが出来ますので、可能な限りの関数で日時を取得するSQLを記載します。

SELECT
    now(),
    timeofday(),
    current_timestamp,
    current_date,
    current_time,
    localtime,
    localtimestamp,
    clock_timestamp(),
    statement_timestamp(),
    transaction_timestamp();

+-----------------------+-------------------------------------+
| now                   | 2025-01-11 15:39:23.26964+09        |
| timeofday             | Sat Jan 11 15:39:23.270388 2025 JST |
| current_timestamp     | 2025-01-11 15:39:23.26964+09        |
| current_date          | 2025-01-11                          |
| current_time          | 15:39:23.26964+09                   |
| localtime             | 15:39:23.26964                      |
| localtimestamp        | 2025-01-11 15:39:23.26964           |
| clock_timestamp       | 2025-01-11 15:39:23.2704+09         |
| statement_timestamp   | 2025-01-11 15:39:23.26964+09        |
| transaction_timestamp | 2025-01-11 15:39:23.26964+09        |
+-----------------------+-------------------------------------+

PostgreSQLサーバーに接続している自分の接続のネットワーク情報

PostgreSQLに接続している現在の自分の接続に関するネットワーク情報を取得します。自分のIPアドレスはクライアント(client)で、PostgreSQLサーバー側のIPアドレスはサーバー側(server)として表示されます。

SELECT
    inet_client_addr(),
    inet_client_addr(),
    inet_server_addr(),
    inet_server_port();

+------------------+----------------+
| inet_client_addr | 192.168.120.11 |
| inet_client_addr | 192.168.120.11 |
| inet_server_addr | 10.10.10.8     |
| inet_server_port | 5432           |
+------------------+----------------+

PostgreSQLサーバーに接続している現在のセッション情報を取得

今現在自分で接続しているPostgreSQLに関する情報を取得するSQLです。あくまでも今自分が接続している接続に関する情報を表示するSQLとなっています。

SELECT
    user,
    current_role,
    current_user,
    session_user,
    current_database(),
    current_catalog,
    current_schema,
    pg_backend_pid(),
    current_query();

+------------------+-------------------------+
| user             | postgres                |
| current_role     | postgres                |
| current_user     | postgres                |
| session_user     | postgres                |
| current_database | radiuslog               |
| current_catalog  | radiuslog               |
| current_schema   | public                  |
| pg_backend_pid   | 3091576                 |
| current_query    | SELECT                 +|
|                  |     user,              +|
|                  |     current_role,      +|
|                  |     current_user,      +|
|                  |     session_user,      +|
|                  |     current_database(),+|
|                  |     current_catalog,   +|
|                  |     current_schema,    +|
|                  |     pg_backend_pid(),  +|
|                  |     current_query()     |
+------------------+-------------------------+

まとめ

PostgreSQLのSQLだけでPostgreSQLサーバーの様々な情報を取得する方法を紹介してきました。情報確認出来次第、随時追加して最新化していきます。

本番環境データベースにはログインできない場合がありますが、ここで紹介するSQLを使うことでサーバーにログインできなくてもサーバーの各種設定を確認することが出来るようになり大変重宝します。

タイトルとURLをコピーしました