【PostgreSQL】インストール、初期設定、データベース・テーブルの作成、SQL実行まで

業務でデータベース（PostgreSQL）に対してSQLを実行しています。このたび「特定期間内のみ特定の値へ更新できないか」という依頼を受けました。

さすがに本番環境のデータベースでは検証できないため、まずは検証のための基盤を構築することを目指します。
（今までPostgreSQLをインストールしたことはないため、基本的な箇所が間違っている可能性もありますが個人的な備忘ですので参考にされる方はその旨ご了承ください）

構成は、VirtualBox上のCentOSへPostgreSQLをインストール、テスト用のデータベースとテーブルを作成し、テーブルへテスト用のデータを挿入します。
ここでSQLが実行できれば、まずは達成ということになります。

今回、VirtualBoxとCentOSの設定は完了しているという前提で進めさせていただきます。

環境

VirtualBox　　　7.0.4
CentOS Linux　　7.9.2009 (Core)
PostgreSQL　　　9.2.24

PostgreSQLインストール

yumコマンドでPostgreSQLをインストールします。

　　yum install postgresql-server

[root@testpostgresdb ~]# yum install postgresql-server
読み込んだプラグイン:fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: download.nus.edu.sg
 * extras: ftp-srv2.kddilabs.jp
 * updates: download.nus.edu.sg

  検証中  : postgresql-server-9.2.24-4.el7_8.x86_64     5/6
  検証中  : postgresql-libs-9.2.24-4.el7_8.x86_64       6/6

更新:
  postgresql-server.x86_64 0:9.2.24-8.el7_9

依存性を更新しました:
  postgresql.x86_64 0:9.2.24-8.el7_9   postgresql-libs.x86_64 0:9.2.24-8.el7_9

完了しました!
[root@testpostgresdb ~]#

PostgresSQLのバージョン確認

以下コマンドを実行してバージョンを確認します。

　　psql –version

[root@testpostgresdb ~]# psql --version
psql (PostgreSQL) 9.2.24

データベースの起動

はじめてのためすいませんあまり詳しいことは理解できておらずググった内容で進めています。

まず以下コマンドでデータベースクラスタを作成します。

　　postgresql-setup initdb

次に以下コマンドで自動起動（enable）を設定します。

　　systemctl enable postgresql.service

最後に以下コマンドでサービスを起動します。

　　systemctl start postgresql.service

以下実行履歴です。

データベースの確認

サービスが起動したので初期設定でどのようなデータベースが存在するか確認してみます。

※rootユーザーではFATALとなり確認できないのでご注意ください

rootユーザーではなく、postgresユーザーで確認するため、以下コマンドでpostgresユーザーにスイッチします。

　　su – postgres

次に psql コマンドでデータベースを確認します。

psqlとは、PostgreSQLのターミナル型フロントエンドです。 対話的に問い合わせを入力し、それをPostgreSQLに対して発行して結果を確認することができます。

　　psql -l

※この時点では psql コマンド実行時にパスワードを求められません。覚えておいてください。

以下コマンドでポートも確認しておきます。5432ポートがLISTENされていることがわかります。

　　ss -name

データベースの作成

今回データベースを作成する際、psqlでPostgreSQLへ接続してからcreate文でデータベースを作成します。psqlでPostgreSQLへ接続するとプロンプトが「postgres=#」に変わります。今回は検証で「testdb01」というデータベースを作成します。SQLは文末に「;」セミコロンが必要ですので忘れないでください。

　　psql

　　create database testdb01 ;

PostgreSQLから切断する時は「\q」とEnterキーを押下します。

psql コマンドでデータベース「testdb01」が作成されたことを確認します。

　　psql -l

postgresユーザー（OS、DB）のパスワード変更

PostgreSQLをインストールした時点で自動的にデータベースの管理ユーザーであるpostgresユーザーが作成されます。ここで言うpostgresユーザーは、CentOSに作成されたpostgresユーザーとなります。
このユーザーのパスワードは未設定の状態であるため、アカウントロック状態となっており、このままではrootユーザーからsuコマンドでスイッチする方法以外でのログインができません。そこで、postgresユーザーでログインできるようにパスワードを設定します。

パスワードの設定は「passwd ＜ユーザー名＞」コマンドで行えますので「passwd postgres」となります。

　　passwd postgres

前述でpostgresユーザーのパスワードを設定しましたが、そのユーザーはOS（CentOS）のユーザーです。
次に設定するのはミドルウェア(PostgreSQL)が管理するpostgesユーザーです。
同じユーザー名でも両者は異なるユーザーとなるため混合しないように注意して下さい。
まず、OSのpostgresユーザーにスイッチします。

　　su – postgres

続いて、psqlコマンドを実行してPostgreSQLへ接続します。プロンプトが「postgres=#」に変わることを確認します。

　　psql

PostgreSQLが管理するユーザーの方のpostgresユーザーに対してパスワードを設定します。パスワードは「P@ss!234db」としています。

　　alter role postgres with password ‘P@ss!234db’;

これでパスワードの設定は完了したので、「\q」と入力しEnterキーを押下して切断します。ちなみに「Ctrl+d」でも切断することができます。

PostgreSQLが管理するユーザーの方のpostgresユーザーに対してパスワードを設定したので接続する際にパスワードを求められるかなと思いますがこれだけでは反映されておらず接続時にパスワードは求められません。

後から分かったのですが「peer認証」という設定になっていることが原因のようです。せっかくですので「peer認証」を解除していきます。

postgresのpeer認証からmd5認証へ変更

現時点で「peer認証」や「md5認証」について、説明できるほど知識はありませんのでご了承ください。ただ、パスワード認証にするためにはデフォルトの「peer認証」設定ではダメということです。

設定ファイルの　/var/lib/pgsql/data/pg_hba.conf　を編集します。

local接続の peer を md5 に変更します。

設定変更後はPostgreSQLを再起動し、設定を反映させます。なお、PostgreSQLの再起動はrootユーザーで行います。

　　systemctl stop postgresql.service

　　systemctl start postgresql.service

postgresユーザーへスイッチします。

検証用データベース「testdb01」へpostgresユーザーで接続するとパスワードが求められるようになりました。

　　psql -U postgres -d testdb01

postgres接続ホスト許可

PostgreSQLへの接続について、現状ではローカルホスト（127.0.0.1）からしかLISTENしていません。そのため、リモートサーバーからPostgreSQLには接続できません。目標とするシェルスクリプトからpsqlを実行するためにはリモートサーバーからの接続を受け付ける（LISTEN）必要があるので設定を変更します。

2つの設定ファイルを変更します。

/var/lib/pgsql/data/pg_hba.conf　を以下のとおり編集します。

次に　/var/lib/pgsql/data/postgresql.conf　を以下のとおり編集します。

設定変更後はPostgreSQLを再起動し、設定を反映させます。なお、PostgreSQLの再起動はrootユーザーで行います。

systemctl stop postgresql.service
systemctl start postgresql.service

以下コマンドでPostgreSQLがリモートサーバーからも受け付けられる（LISTEN）ようになったことを確認します。

　　netstat -nat

テーブルの作成

検証用のテーブルを作成します。テーブル情報は以下のとおりです。できるだけ本番用に近い形式としました。テーブルを作成するうえで必要な赤枠の情報を登録します。

検証用データベース「testdb01」へ接続してから検証用テーブル「test_tbl」を作成します。

まず、検証用データベースを指定してPostgreSQLへ接続します。

以下の create 文を実行します。

create table test_tbl (userid character varying(8),busyo character varying,syozoku character varying,saiyobi date,taisyokubi date,atobusyo character varying,atosyozoku character varying,flg1 character varying,flg2 character varying ) ;

作成したテーブルを確認するために psql メタコマンドの ¥dt コマンドを実行します。

　　\dt

最初に作成したテーブルに含まれるカラムに関する情報を取得する方法です。 psql メタコマンドの ¥d コマンドにテーブル名を指定して実行します。

　　\d test_tbl

test_tbl には、まだ値が入力していないことを select文 を実行して確認します。

　　select * from test_tbl;

テーブルへ値を入れる

検証用テーブルが作成できたので値を入れていきます。insert 文を使用して値を指定します。

検証用データベースを指定してPostgreSQLへ接続します。

※PostgreSQLにかぎらずSQLの構文として、文字列はシングルクオートで括る必要があります。これを知らなかったので構文エラーが多発していました。。

insert into test_tbl (userid,busyo,syozoku,saiyobi,taisyokubi,atobusyo,atosyozoku,flg1,flg2) values ('user001','9n','9n99','2023-04-01','2024-03-31','99','9999','0','1');

select文 を実行して確認します。

　　select * from test_tbl;

後の検証を想定して追加で値を入れました。先ほどはPostgreSQLへ接続してからselect文を実行して確認しましたが、今回はPostgreSQLへ接続せずに psql コマンドを利用して確認してみます。

psql -U postgres -d testdb01 --pset format=unaligned --field-separator="," -t -c "select * from test_tbl order by userid" ;

■psql コマンドのオプションの意味

–pset format=unaligned　⇒　出力形式を指定
–field-separator=”,”　⇒　フィールドのセパレータにカンマ区切りを指定
-t　⇒　カラム行を非表示
-c　⇒　SQLを指定
order by　⇒　ソートする（オプションを指定してないので昇順）

まとめ

少し長くなりましたが、PostgreSQLのインストール、初期設定、データベース・テーブルの作成、SQL実行までを記載しました。
業務ではSQLを作成、実行するだけでしたのでよい勉強になりました。やっとシェルスクリプトとpsqlを組み合わせた処理の基盤が完成しました。シェルスクリプトを作成する際も何点かはまりましたので自身の備忘としてものちのち当ブログへ残しておきたいと思います。
はじめてのため、本番環境でどれくらい役立つものかはわかりませんが同じような検証をしたいと思う方のお役に立てれば幸いです。