【PostgreSQL】基本的な運用コマンド一覧

今回は、オープンソースソフトウェア（OSS）のデータベースでもとくに有名な PostgreSQL の基本的な運用コマンドについて解説します。

データベースへのアクセス、情報の取得、データベース一覧の表示、テーブル一覧の表示など運用に役立つコマンドや操作が中心となります。

PostgreSQL のインストール手順及び初期設定については以下を参考にしてください。

【PostgreSQL】【CentOS7】yum コマンドでインストール＆初期設定（ユーザー作成、パスワード設定、データベース作成、権限割り当て）手順

100%レンタルサーバーを使いこなすサイト

【PostgreSQL】【CentOS7】yum コマンドで PostgreSQL インストール＆初期設定（ユ...

https://go-journey.club/archives/7742

PostgreSQL のインストールと初期設定手順です。yum コマンドで PostgreSQL サーバーをインストール後に初期設定をします。インストール後の基本的な運用コマンドについては以下のページを参考にしてください。【PostgreSQL】基本的な運用コマンド一覧&n...

PostgreSQL のプロセスの自動起動の確認及び自動起動の設定

OS 起動時に PotgreSQL のプロセスも自動起動するかの設定確認です。

[root@ip-172-31-38-38 data]# systemctl list-unit-files | grep postgresql

インストール直後は、デフォルトで自動起動の設定がされていない「disabled」状態になっています。

[root@ip-172-31-38-38 data]# systemctl list-unit-files | grep postgresql
postgresql.service disabled　←　自動起動しません。
[root@ip-172-31-38-38 data]#

自動起動 ON の設定手順

以下のコマンドで PostgreSQL の自動起動を「ON」にします。

[root@ip-172-31-38-38 data]# systemctl enable postgresql.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql.service to /usr/lib/systemd/system/postgresql.service.
[root@ip-172-31-38-38 data]#

設定を確認します。

[root@ip-172-31-38-38 data]# systemctl list-unit-files | grep postgresql
postgresql.service enabled　
[root@ip-172-31-38-38 data]#

PostgreSQL プロセスの起動手順

以下のコマンドでプロセスを起動します。

[root@ip-172-31-38-38 data]# systemctl start postgresql.service
[root@ip-172-31-38-38 data]#

以下のコマンドでプロセスの起動確認ができます。

[root@ip-172-31-38-38 data]# systemctl status postgresql.service
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2018-11-17 03:42:45 UTC; 56s ago
  Process: 15218 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
  Process: 15213 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 15221 (postgres)
   CGroup: /system.slice/postgresql.service
           tq15221 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
           tq15222 postgres: logger process
           tq15224 postgres: checkpointer process
           tq15225 postgres: writer process
           tq15226 postgres: wal writer process
           tq15227 postgres: autovacuum launcher process
           mq15228 postgres: stats collector process

Nov 17 03:42:43 ip-172-31-38-38.ap-northeast-1.compute.internal systemd[1]: S...
Nov 17 03:42:45 ip-172-31-38-38.ap-northeast-1.compute.internal systemd[1]: S...
Hint: Some lines were ellipsized, use -l to show in full.
[root@ip-172-31-38-38 data]#

データベースへのアクセス

データベースへのアクセスです。

-bash-4.1$ psql
psql (9.3.4)
help でヘルプを表示します.
　
postgres=#

psql コマンドを実行するためには postgres アカウントにスイッチします。

# su - postgres

-bash-4.1$

psql: サーバに接続できませんでした: そのようなファイルやディレクトリはありません

以下のように「psql: サーバに接続できませんでした: そのようなファイルやディレクトリはありません」というエラーが出力される場合です。

-bash-4.1$ psql
psql: サーバに接続できませんでした: そのようなファイルやディレクトリはありません　　
ローカルにサーバが稼動していますか? 　　　
Unixドメインソケット"/tmp/.s.PGSQL.5432"で通信を受け付けていますか?
　　　　　　　　　　　
-bash-4.1$ psql

■原因

PostgreSQL のプロセスが起動していない

以下のコマンドで PostgreSQL のプロセスを起動します。

[root@ip-172-31-38-38 data]# systemctl start postgresql.service
[root@ip-172-31-38-38 data]#

再度「psql」コマンドを実行してデータベースにアクセスできることを確認します。

-bash-4.1$ psql
psql (9.3.4)
help でヘルプを表示します.
　
postgres=#

PosgreSQL が起動しない場合【PANIC】

PostgreSQL プロセスを起動しようとした際に以下のように「PANIC: could not locate a valid checkpoint record」が出力された場合です。

[2018-11-15 09:31:55 JST][0][00000]LOG: database system was interrupted; last known up at 2018-11-07 01:51:08 JST
[2018-11-15 09:31:55 JST][0][00000]LOG: invalid primary checkpoint record
[2018-11-15 09:31:55 JST][0][00000]LOG: invalid secondary checkpoint record

[2018-11-15 09:31:55 JST][0][XX000]PANIC: could not locate a valid checkpoint record
[2018-11-15 09:32:00 JST][0][00000]LOG: startup process (PID 4723) was terminated by signal 6: Aborted
[2018-11-15 09:32:00 JST][0][00000]LOG: aborting startup due to startup process failure

■原因

WAL ログが壊れていると上記のような PANIC のメッセージが出力されて起動エラーになることがあります。

■修復手順

pg_resetxlog コマンドで修復します。

pg_resetxlog コマンドの場所：/usr/pgsql-9.3/bin/pg_resetxlog

PostgreSQL データ格納場所：/data

-bash-4.1$ /usr/pgsql-9.3/bin/pg_resetxlog -f /data
トランザクションログをリセットします。
-bash-4.1$

pg_resetxlog コマンド実行後に再度 PostgreSQL プロセスが起動できる確認します。

データベース一覧の表示

以下のコマンドでデータベース一覧を表示することができます。

-bash-4.1$ psql -l
                                        データベース一覧
    名前    |  所有者  | エンコーディング | 照合順序 | Ctype(変換演算子) |      アクセス権
------------+----------+------------------+----------+-------------------+-----------------------
postgres   | postgres | UTF8             | C        | C                 |
template0  | postgres | UTF8             | C        | C                 | =c/postgres          +
            |          |                  |          |                   | postgres=CTc/postgres
template1  | postgres | UTF8             | C        | C                 | =c/postgres          +
            |          |                  |          |                   | postgres=CTc/postgres
　
-bash-4.1$

■データベースにアクセスしている場合

データベースにアクセスしている場合は以下のコマンドでデータベース一覧を表示できます。

■SELECT 文でデータベース一番を取得する場合

以下の SELECT 文でデータベース一覧を表示できます。

postgres=# SELECT * FROM pg_database;
template1 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | t            |           -1 |         12921 |
       1882 |          1663 | {=c/postgres,postgres=CTc/postgres}
template0 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | f            |           -1 |         12921 |
       1882 |          1663 | {=c/postgres,postgres=CTc/postgres}
postgres  |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         12921 |
       1882 |          1663 |

postgres=#