PostgreSQL

PostgreSQL(DBMS)のインストールから運用までデータベースの詳細について記載していきます。

DBMSの選定

本サイトではDBMSにはPostgreSQLを使用していますが、通常は設計フェーズでDBMSを比較して選定・導入していく流れです。

DBMSは、データを取り出したいときやデータを保存するときに中間のソフトウエアとって簡単に行えるようにしてくれるものです。
DBMSを利用することで、 今回はDBMSをPostgreSQLにしていますが、実際に選定する場合はシステム・顧客要件の内容に沿って選定することが多いです。
選定する条件として以下を記載します。
※PostgreSQLはOSSですので費用は発生しません。

[条件]

  1. (1) 費用{ショット(初期費用)、ランニングコスト(月額費用)}
  2. (2) 対応OS(Windows系ならMicrosoftのSQL、LinuxならPostgreSQL・MySQL等)
  3. (3) 監査要件
  4. (4) サポート要件(OSSには基本ない、必要ならOracleまたはMicrosoftのSQL)
  5. (5) 運用要件(操作・バックアップ・冗長化など)

PostgreSQLを利用しますので、PostgreSQLの特徴も一部ですが次に記載します。

[PostGreSQL特徴]

  1. (1) ライセンスはPostgreSQL（BSDスタイルのライセンス）
  2. (2) メジャーバージョンアップは互換性がない
  3. (3) メモリ容量の制限なし
  4. (4) ファイルシステムレベルのI/Oアクセス
  5. (5) 追記型の性能特性

    (INSERT,DELETEは速いが、VACUUMが必要、同時実行でロック待ち少ない)

  6. (6) マルチプロセス
  7. (7) 多版型同時実行制御 (MVCC)

DBMSは利用する用途に応じて選定をする必要があります。
比較資料(決定表など)を作成して、その要件にはどのDBMSが良いのかを考えます。
OSSのデータベースは費用は発生しませんが、サポートが受けれませんので自力で対応する必要があります。
OSSと入っても、ライセンス形態は複数あります。ライセンスにはGNU・Apacheなどがあり、個人・商用で利用するなどライセンスの利用方法を把握していないとライセンス違反になる場合があります。
また、MicrosoftのSQLは無償版もありますが、制限事項があり要件に合致するかの確認も必要です。
サポートも受けられて性能面を考えるとOracleですが、ショットとランニングのコストが発生します。
導入するシステムも新規で構築するのかリプレイスして入れ代えるのかによっても選定は変わってきます。
※リプレイスであれば、同じDBMSを利用することが多く、既存のシステムを流用するケースが多いです。
要件に応じては、DBMS以外のデータベースを選定する場合もあります。
DBMS以外にはNoSQLがあります。

OSSのコミュニティ

OSSはサポートは受けれませんが、PostGreSQLでは日本でもコミュニティ「日本PostgreSQLユーザ会」があります。
最近では、Slackのコミュニティなどもありますので気軽に確認することができます。
バグが発生したら、メーリングリストでバグを報告することで次回バージョンでバグが解消されるケースもあります。

DBMSの導入

DBMSの導入はそのDBMSにより異なりますが、最近ではWEBからパッケージなどをダウンロードしてインストールするのが一般的になってきています。
一般的なDBMSではメジャーバージョンアップを行うと互換性がなくなることが多いです。 そのため、導入するときはそのバージョンが最新であることを確認してからインストールすることをお勧めします。
理由としては、最新のバージョンの方が利便性が高く、性能も向上している可能性が高いからです。
しかし、運用要件などで安定したバージョンを推奨されている場合は最新ではなく安定バージョンで対応することもあります。
脆弱性などは新しい機能の不備などで発生することもありますので、安全な運用の場合は安定したバージョンの方が良い場合もあります。

PostgreSQLのバージョン

PostgreSQLのバージョンですが、「14.3」や「10.18」など「xx.yy」と表記されています。
このxxの部分が、メジャーバージョンになります。
メジャーバージョンが違うとデータベースクラスタなどの互換性が損なわれることがありますので、気を付ける必要があります。
また、yyの部分がマイナーバージョンになります。
10の前のバージョンは、「xx.yy.zz」の表記で、9.2.3・8.4.2などxx.yyまでがメジャーバージョンでzzがマイナーバージョンでした。
リリースサイクルとしては、1年に1回ぐらいの間隔でメジャーバージョンがリリースされ、3ヵ月に1回ぐらいの間隔でマイナーバージョンがリリースされています。
標準サポートと延長サポートがあります。

PostgreSQLのインストール

PostgreSQLをインストールするには、以下の方法があります。
PostgreSQLはWindowsではなく、Linuxをお勧めしております。


本サイトでのOSはCentOSですので、(2)のyumを利用してインストールを行います。
※2021年7月時点での最新である13をインストールします。
Postgresqlのバージョンを指定したい場合や最新を利用したい場合は、以下のコマンドでレポジトリを追加してからインストールする必要があります。
※CentOSには古いバージョンが標準になっているためです。
※メジャーバージョンが違うと互換性がなくなります。
※管理者権限が必要なコマンドがいくつかありますが、「sudo」を頭につけるなど適正に変換してください。
# yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# yum -y install postgresql13-server
※URLは都度更新されますので、上記コマンドが失敗する場合は適正なrpmを探してURLを変更してください。
インストール後、データベースを初期化しますが、初期化するには「initdb」コマンドを実行します。
通常、yumでインストールを行うとユーザの「postgres」が作成されており、そのユーザにて初期設定を行っていきます。(※ユーザが存在しない場合は作成してください。)
# su postgres
$ /usr/pgsql-13/bin/initdb --encoding=UTF8--no-locale
または、
$ /usr/pgsql-13/bin/postgresql-13-setup initdb

※initdbで使用している「--no-locale」は、ロケールを使用しないオプションです。
ロケールとは、OSの言語や国・地域ごとに異なる単位、記号、日付、通貨などの表記規則を利用することです。
このロケールは主にデータのソート処理等で使用されますが、英語や日本語のデータを扱う場合には特に必要ありませんので、「-—no-locale」にしていますが環境に応じて変更してください。
その後に以下サービス開始コマンドでPostgreSQLのサービスを起動します。
$ exit
# systemctl start postgresql-13

これで、以下コマンドを使用することでPostgreSQLへアクセスできるようになりました。
※データベースに接続するときにユーザを指定しないとOSのログイン名でログインします。
# su postgres
$ psql

使い勝手はよろしくないですので、pgadmin4のインストール方法も記載します。
# yum -y install httpd
# rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm
# yum -y install pgadmin4
# /usr/pgadmin4/bin/setup-web.sh

※アクセスできない場合は、以下が考えられますので、適正設定してください。

データベースクラスタ―

実際にデータベースを利用するには、PostgreSQLをインストールしただけでは利用できません。
「PostgreSQLのインストール」に記述している通り、データベースの初期化を行い、サービスを起動します。
データベースが初期化実行時にデータベースクラスタが作成されます。
このデータベースクラスタにデータベースの設定ファイル・ログ・データなどが保存されます。
その後にデータベースにアクセスして使用してきますが、そのデータベースにリモートでアクセスするプロトコルのことをRDA(Remote Database Access)といいます。
今回、データベースへはGUIツールである 「pgadmin」を利用してアクセスして操作方法についても説明してきます。

データベースクラスタはPostgreSQLであれば、標準で「/var/lib/pgsql/13/data」に作成されます。
どのようなディレクトリがあり、どのようなファイルがあるかは事前に知っておく必要があります。
※PostgreSQLのバージョンによってディレクトリ・ファイル名は多少異なります。
トラブルが発生したときにログ・ファイルなどの参照が必要になります。

[データベースクラスタ]

pgadmin4でデータベースにアクセスしてみます。
下図の画面左のようなメニューが表示されます。
pgadmin4の左メニュ データベースの「postgres」があり、その中にスキーマの「public」があってテーブル「test」があります。
PostgreSQLではデータベースとテーブルの間にはスキーマがあり、スキーマ毎にテーブルを作成します。
スキーマを指定しない場合は、「public」スキーマが利用されます。

PostgreSQLコマンド操作

「pgadmin」を使用しないでPostgreSQLを利用する場合、 「psql」コマンドでアクセスした後にコマンド操作が必要です。
「pg_ctl」コマンドでサービスの起動も行えます。
例えば、データベースの切替や操作の終了も同じです。
基本的に利用するコマンドを以下に記載します。

[コマンド]

また、「psql」接続後はクエリが実行できるようになっています。
コマンドでデータベースを指定してからクエリを実行してください。

pgadmin4では、左のメニューのデータベースを右クリックすると「Query Tool」が表示されますのでそちらをクリックします。
pgadmin4のQuery tool メニュの右側に選択したデータベースのクエリが実行できる画面が表示されます。 pgadmin4のQuery Editor 「Query Editor」の下にクエリを入力して再生ボタン(▶)でクエリを実行して、下の「Data Output」に結果が表示さます。

PostgreSQL接続図

PostgreSQLのメモリーとプロセスについて記載します。
クライアントが接続して新しいプロセスを開始することをforkするといいます。
どのようなプロセスが起動してどのようにメモリが利用されるかを把握することで性能改善などにも役立ちます。

[メモリ]

[プロセス]

サービス起動時にデータベースクラスタに対して、postmasterが1つ動作して、postgres・wal writer・checkpointerなどのプロセスが起動します。
1接続につき1つのpostgresプロセスが起動します。
レプリケーション環境では、マスタ側にwal senderプロセス、スタンバイ側にwal receiverプロセスが起動します。
リカバリ中は、postgresプロセスの他、startupプロセスなど複数のプロセスが起動します。

[SQL実行のキャンセル]
pg_cancel_backend(pid int)
→実行中の SQL をキャンセルします。(SIGINTと同等)
pg_terminate_backend(pid int)
→セッションを終了させます。(SIGTERMと同等)

[シグナル(TERM/INT/HUP)によるサーバプロセスへの影響]
データベースサーバをシャットダウンする方法は複数あります。
マスターpostgresプロセスに異なるシグナルを送ることで、シャットダウンの方法を制御します。

[SIGTERM]
スマートシャットダウンモードです。
新しい接続を禁止しますが、既に存在するセッションは通常通り動作させます。
全てのセッションが通常に終了するまではシャットダウンしません。
バックアップは待ちますので、正常に完了します。
pg_ctlのモードはsmart

[SIGINT]
高速シャットダウンモードです。 サーバは新しい接続を禁止し全ての存在するサーバプロセスにSIGTERMを送り、この結果サーバプロセスは現在のトランザクションをアボートし、即座に終了します。 そして サーバはサーバプロセスの終了を待って、最後にシャットダウンします。 オンラインバックアップモードも終了させる。
pg_ctlのモードはfast

[SIGQUIT]
即時シャットダウンです。 マスターpostgresプロセスは、全ての子プロセスに SIGQUITを送り、即座に終了します。
子プロセスは同様にSIGQUITを受け取ると即座に終了します。
次回起動時に（WALログを再実行することで）リカバリをすることになります。
pg_ctlのモードはimmadiate

[バックグラウンドプロセス]
PostgreSQLはユーザ提供のコードを別のプロセスとして実行できるように拡張することができます。
このプロセスはpostgresによって起動、終了、監視され、サーバの状態に密接にリンクした寿命を持つことができます。
これらのプロセスはPostgreSQLの共有メモリ領域にアタッチしたり、データベースの内部に接続するオプションを持ちます。
これらはまた、通常のクライアントに接続された実際のサーバプロセスのように複数のトランザクションを連続して実行することができます。
また、アプリケーションはlibpqとリンクすることにより通常のクライアントアプリケーションのようにサーバに接続して動作することができます。
クライアント接続

PostgreSQL設定ファイル

基本的なPostgreSQLの設定は「postgresql.conf」を修正して再起動・再読込などで繁栄させます。
一部の設定を以下に記載します

[WAL]

[バックグラウンドライタ]

← トランザクション
SQL→