この記事ではPostgreSQLの導入方法と、テーブルを作成して使用できるまでについてコマンドとともに解説しています。
RDBMSやSQLに関しては特に解説できていません。あくまで環境構築のみとなっています。。
この記事でできること
PostgreSQLをインストールしてpsqlコマンドを実行することができるようになる。- ロール・データベースを作成して接続することができる。
- 作成したロールでテーブル操作ができることを確認する。
- 設定を変更して、パスワード認証・外部からの接続ができるようになる。
インストール
それではインストールしていきます。PostgreSQLをインストールするにはいつも通りapt-getコマンドを利用してインストールすることができます。apt-getコマンドの実行には管理者権限が必要なのでここではsudoを使用します。
$ sudo apt-get install postgresql
Raspberry PiのOSのバージョンがBusterであれば11、Stretchであれば9.4がインストールされます。
インストールが完了するとpostgresユーザーとpsqlコマンド・PostgreSQL用のコマンド(createdbなど)が追加されます。
psqlユーザーにはパスワードが設定されていないのでそのままではログインすることができません。パスワードを設定するか、rootユーザー経由でsuする必要があります。以下のコマンドでパスワードを設定します。
$ sudo passwd postgres
環境構築
PostgreSQL上で使用するユーザーを追加する
まずはPostgreSQL用のユーザーを追加します。ここで追加するユーザーはLinux上で使用しているユーザー(suなどでログインできるユーザー)とは異なり、同じ名前のユーザーでも新たに作成する必要があります。
以下コマンドを実行してユーザーを追加することができます。実行するユーザーはpostgresである必要があります。
$ su - postgres $ createuser [ユーザー名] # データベースを管理するLinuxユーザーがtaneyatsの場合、 # ここもtaneyatsにそろえておくと便利です。
データベースを作成する
データベースを作成することによって、テーブルを作成することができるようになります。概念としてのデータベースと同じ名前でややこしいですが、ここではデータベースはエクセルの1ファイル、テーブルがその中の1シート、レコードがシート内の行というイメージが分かりやすいかもしれません。
今からそのエクセルファイルを新規に作成していきます。
以下のコマンドをコマンドライン上で実行してデータベースを作成します。こちらもpostgresユーザーである必要があります。
$ createdb [データベース名]
データベース名の名前を間違えてしまった場合はdropdb [間違えたデータベース名]で削除することができます。
作成したデータベースに接続する
以下のコマンドで作成したデータベースに接続して中身を見るSQLを発行することができます。このコマンドpostgresユーザーでなくてもOKです。
$ psql [データベース名] # ユーザーを指定する場合は、 $ psql -U [ユーザー名] [データベース名]
接続できると以下のように表示されます。
psql (11.5 (Raspbian 11.5-1+deb10u1)) Type "help" for help. [DB名]=>
この状態でSQLを発行することができます。=>が=#となっているときはPostgreSQL上での管理者権限を有している状態です。
試しにいくつかSQLを実行してみます。文頭の>は入力しません。
-- 登録されているユーザー一覧 > SELECT rolname from pg_roles; -- 現在時刻を表示 > SELECT current_timestamp; current_timestamp ------------------------------- 2019-12-11 00:35:47.403313+09 (1 row)
ちゃんと実行されていますね。
対向認証を解除する
インストールしたままの状態だとPostgreSQL上でのユーザーとLinux上でのユーザー名が一致していないとデータベースに接続することができない状態になっています。
$ psql -U postgres psql: FATAL: Peer authentication failed for user "postgres"
また一致しているとパスワード無しで接続できるので、どのような状態でもパスワード認証するようにします。
設定ファイルは/etc/postgresql/11/main/pg_hba.confにあります。11の部分はバージョンによって変動します。
vimなどで開くと以下のような感じになっています。
# PostgreSQL Client Authentication Configuration File # =================================================== # ... # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer
この末尾のlocal all allのほうの末尾にあるpeerをmd5に変更します。これによりパスワード認証が可能となります。(postgresのほうはそのままでOKです。postgresユーザは管理用なのでターミナル上でしか使わない(別サーバーからアクセスしない)ので)
書き換えたら以下のコマンドでDBサーバーを再起動します。
$ sudo service postgresql restart
次の章でパスワードを設定した時に一緒に設定できているか確認します。
ユーザー(ロール)にパスワードを設定
今のままだとパスワードが追加したユーザーに設定されていないので設定します。再度postgresユーザーでpsqlコマンドを実行してパスワードを付与していきます。
$ su - postgres $ psql # ALTER ROLE [ユーザー名] PASSWORD ’[パスワード]’;
で完了です。taneyatsにhogehogeというパスワードを設定するにはALTER ROLE taneyats PASSWORD 'hogehoge';と入力して実行します。
ALTER ROLEと表示されたら成功です。\qと入力して接続を解除します。
再び接続してパスワード認証が有効か確認します。
$ psql -U [ユーザー名] [DB名] Password for user xxxxx :
と表示されれば対向認証の解除が成功しています。続いて正しいパスワードが設定できているか確認してください。パスワードを入力して今まで通り接続できればOKです。
テーブル作成・削除
テスト用のテーブルを作成してCRUD操作できるか試してみます。以下のコマンドを実行してテーブルを作成します。SQLの詳細は省きます。各アプリケーション側の解説や、Qiitaの初心者向けのSQLコマンドについて参照してみてください。。
$ psql -U [ユーザー名] [DB名] -- テーブルを作成 > CREATE TABLE test (id int primary key, name varchar(10) not null, age numeric(3)); CREATE TABLE -- レコード追加 > INSERT INTO test VALUES (1,'taneyats',26); INSERT 0 1 > INSERT INTO test VALUES (2,'debian',26); INSERT 0 1 -- レコード更新 > UPDATE test SET age = age + 1 WHERE id = 2; UPDATE 1 -- レコード取得 > SELECT * FROM test; id | name | age ----+----------+----- 1 | taneyats | 26 2 | debian | 27 (2 rows) -- レコード全削除 > DELETE FROM test; DELETE 2 > SELECT * FROM test; id | name | age ----+------+----- (0 rows)
一通り実行できることを確認しました。最後にテーブルを削除します。
> DROP TABLE test; DROP TABLE > SELECT * FROM test; ERROR: relation "test" does not exist
これで後始末完了です😃
外部からのアクセスを許可する
ローカルから接続するのではなく、他ホストのサーバーからアクセスするためには設定を変更する必要があります。(DBを使用するAPサーバー(apacheやnodejsなど))が同じRaspberry Pi内にある場合はこの設定は不要です。)
以下のファイルを編集して外部からのアクセスを許可するようにします。とりあえずローカル内の同じセグメント(192.168.11.*)のみ許可します。
$ sudo vim /etc/postgresql/11/main/postgresql.conf ... # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on;
開いて50行目ぐらいにlisten_addressesという項目がありますので、その値を'*'に変更します。
もう一つファイルを編集します。
$ sudo vim /etc/postgresql/11/main/pg_hba.conf ... # IPv4 local connections: host all all 127.0.0.1/32 md5 host all all 192.168.11.0/24 md5
もともとの設定では自環境からしか接続できないようになっているので、その下に192.168.11.*からの接続を許可するようにします。
変更後はsudo service postgresql restartで再起動します。もし他のpsqlコマンドを使用できる環境がある場合は、Windows上でDBクライアント(pgAdminやA5M2など)がある場合は接続を確認してみてください。
参考
公式ドキュメント
