PostgreSQL の全体像を、実際に触ってみてサクッと学ぶ

postgresql

PostgreSQL とは、オープンソースのデータベース管理システム (Relational Database Management System) です。

MySQL と並び人気のある DBMS ですね。

Django (Python のウェブフレームワーク) でよく使われてもいるようです。

違いが気になる方は、「MySQL と PostgreSQL の違いを知る」も参考にしてください。

本記事では、Vagrant で起動した Ubuntu 14.04 に PostgreSQL をインストールし、PostgreSQL についてサクッと学んで行きたいと思います。

環境

Windows 上の Vagrant を使って Ubuntu をインストールし、そこで PostgreSQL をインストールします。

Windows 7 SP1
Vagrant 2.1.1
Ubuntu 14.04 (Trusty)
PostgreSQL 9.3.23

インストール

まずは、Vagrant で Ubuntu 14.04 (Trusty) 環境を準備します。こちらのサイトから Vagrantfile のダウンロードしてください。

「Vagrant を使うと仮想マシン構築がすごいラク！まずはインストールしよう！」

からインストールの詳細は確認できますので、参考にしてくださいね。

Vagrantfile をダウンロード/生成しましたら、コマンドプロンプトからそのファイルの場所に移動し、

vagrant up
vagrant ssh

を実行し、Ubuntu に接続します。

Ubuntu のレポジトリはデフォルトで、PostgreSQL パッケージを含んでいますので、apt-get で簡単にインストールすることができます。

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

PostgreSQL Role と Database

PostgreSQL においては接続の為のログインの管理やデータベースなどへの各権限の設定を Role 単位で行います。

Role は「ユーザ」とユーザを論理的にまとめた「グループ」の２つの用途で利用ができます。説明をしやすくするため、本記事では Role = ユーザーとして説明します。

PostgreSQL へのログインする際の認証の方法はいくつかありますが、

Ident ： DB の所有者がシェルを実行している OS のユーザ名と一致するかで認証
MD5 ： MD5-hashed のパスワード認証
Trust ：認証せず、DB への接続許可

などがあり、デフォルトでは、”Ident” 認証となっています。

PostgreSQL インストール時に、自動的に “postgres” アカウントが作成され、この “postgres” アカウントは、デフォルトの “Postgres” role に紐付いています。

なので、Ubuntu (OS) 側のユーザーアカウントを “postgres” に変更しないと、認証されないことになります。

sudo -i -u postgres

これで Ubuntu のユーザー “postgres” に変更できました。これで Postgres にアクセスする準備が整いました。。(いやー、初心者泣かせのフローですよね。。笑)

psql

これで、PostgreSQL にログインができました。

ログアウトするには、

\q

とすれば、OK です。

いちいちアカウントを変更するの面倒な場合、”postgres” アカウントに “sudo” を使ってログインすることも可能です。

sudo -u postgres psql

以降の説明では、”postgres” アカウントに変更した場合、の説明を行いますが、sudo -u postgres を付ければ同じ結果が得られますので、”postgres” アカウントに変更したくない場合は、必要に応じてそう読み取ってくださいね。

Role の作成

Role (ユーザー) はまだ、デフォルトで作成された “postgres” しかありません。

それでは、新しい Role(ユーザー) を追加していきましょう。まずは Ubuntu ターミナルにてこのようにタイプします。

createuser --interactive

オプションの –interactive を追加したので、下記のように対話型で Role(ユーザー) が作成できまうす。ひとまず “postgres-test” role を作成します。

Enter name of role to add: postgres-test
Shall the new role be a superuser? (y/n) y

createuser に関して詳しく内容を確認したい場合は、man コマンドで確認できます。

man createuser

“q” でマニュアルページから Ubuntu プロンプトに戻れます。

データベースの作成

PostgreSQL はデフォルトでは、ログイン時の Role 名と同じ名前のデータベースがあることを前提に、そのデータベースに接続をしようとします。

なので、Role 名と同じデータベースを作成しておく必要があります。

createdb postgres-test

仮に、前もってデータベースを作成していなかった場合、この様なエラーがでます。

psql: FATAL: database “postgres-test” does not exist

ここで思い出してほしいのですが、PostgreSQL はデフォルトで “Ident” 認証です。

“postgres-test” Role を PostgreSQL に作成しましたが、Ubuntu のユーザーとしてまだ存在しないため、”postgres-test” ユーザーでログインしようとするとエラーがでます。

sudo: unknown user: postgres-test
sudo: unable to initialize policy plugin

なので、Ubuntu 上のユーザーとしても作成する必要があります。

sudo adduser postgres-test

Enter the new value, or press ENTER for the default
Full Name []:
Room Number []:
Work Phone []:
Home Phone []:
Other []:
Is the information correct? [Y/n]

これでやっと、新規に作成した Role で PostgreSQL にアクセスできるようになりました。。ふぅ。。

新規に作成した Role で PostgreSQL にアクセス

準備はできましたので、あとは下記コマンドを Ubuntu のターミナルに打って PostgreSQL にアクセスできます。

sudo -i -u postgres-test
psql

デフォルトでは、Role 名と同じデータベースに接続しますが、仮に違うデータベースに接続したい場合は、-d [接続したいデータベース名] で接続が可能です。例えば “postgres” でデータベースに接続したい場合は、下記コマンドで可能です。

psql -d postgres

PostgreSQL にアクセスできたら、下記コマンドで接続情報を確認できます。

\conninfo
You are connected to database "postgres" as user "postgres-test" via socket in "/var/run/postgresql" at port "5432".

デフォルト以外のデータベスに接続する際に、確認すると便利ですね。

テーブルの作成

データベースに接続できましたので、テーブルを作成していきましょ。

テーブルは下記のようにして作成することになります。

CREATE TABLE table_name (
column_name1 col_type (field_length) column_constraints,
column_name2 col_type (field_length),
column_name3 col_type (field_length)
);

テーブル名 (table_name) で名前を指定し、列 (column-name1,2,3…etc) を定義しています。その際、タイプ (col_type) や入力できる長さ (field_length) を定義しています。

例えば、person テーブルで名前と誕生日を管理したい場合、下記のようになります。

CREATE TABLE person (
person_id serial PRIMARY KEY,
name varchar (25) NOT NULL,
birthday date
);

セミコロン (;) を打つまでは何度も改行ができます。

定義した内容は下記コマンドで確認できます。

\dt

  List of relations
Schema | Name | Type | Owner
--------+--------+-------+---------------
public | person | table | postgres-test
(1 row)

テーブルの Owner は、デフォルトでは作成した際のユーザーになります。この場合は、”postgres-test” ですね。

テーブルからデータの追加、検索、削除

それではテーブルができましたので、データを入れていきましょ。

INSERT INTO person (name, birthday) VALUES ('John', '1990-04-20');
INSERT INTO person (name, birthday) VALUES ('Mike', '1995-08-15');

ここで気をつけたいのが、列にはクオート (‘) は必要ないのですが、挿入するデータにはクオート (‘) が必要必要です。

また、person_id は自動で採番されるので入力不要です。

では実際に挿入されたデータを確認しましょ。

SELECT * FROM person;

person_id | name | birthday
-----------+------+------------
1 | John | 1990-04-20
2 | Mike | 1995-08-15
(2 rows)

問題なく挿入されていますね。

では、そのまま削除する方法も確認しておきましょ。削除は下記で可能です。

DELETE FROM person WHERE name = 'John';

実際に削除されているか確認しましょ。

SELECT * FROM person;

person_id | name | birthday
-----------+------+------------
2 | Mike | 1995-08-15
(1 row)

問題なく削除できていますね。

ここで再度データを挿入し、内容を確認します。

INSERT INTO person (name, birthday) VALUES ('Jane', '1997-07-17');
SELECT * FROM person;

person_id | name | birthday
-----------+------+------------
2 | Mike | 1995-08-15
3 | Jane | 1997-07-17
(2 rows)

この結果から、person_id は一度削除された番号を割り振るのではなく、新たな番号を採番していることが確認できます。

テーブルから列の追加、削除

一度作成した列を編集することも簡単にできます。仮に「性別」を新たに追加したい場合、下記コマンドで可能です。

ALTER TABLE person ADD gender varchar(6);
SELECT * FROM person;

では、確認してみましょ。

 person_id | name | birthday | gender
-----------+------+------------+--------
2 | Mike | 1995-08-15 |
3 | Jane | 1997-07-17 |
(2 rows)

想像はつくと思いますが、列の削除は下記コマンドからできます。

ALTER TABLE person DROP birthday;
SELECT * FROM person;

person_id | name | gender
-----------+------+--------
2 | Mike |
3 | Jane |
(2 rows)

問題なく削除できていますね。

データの更新

最後にデータの更新方法を見ていきましょ。

先程新しく追加した “gender” 列に値を入れていきましょ。

UPDATE person SET gender = 'male' WHERE name = 'Mike';
SELECT * FROM person;

person_id | name | gender
-----------+------+--------
3 | Jane |
2 | Mike | male
(2 rows)

問題なく情報が更新されていますね。

まとめ

PostgreSQL はオープンソースで無償で提供されてはいますが、商用でも利用されている大変しっかりとした DBMS です。

本記事では、一般的な利用を紹介しましたが、他にもたくさんの機能があります。

これをきっかけにぜひ一緒に PostgreSQL の世界にはハマりましょ！