データベース構築｜オープンソースのリレーショナルデータベース管理システム(RDBMS)｜

データベース
テクノロジー

公開日時: 2021-10-22 　更新日時: 2022-05-23

データベース構築｜オープンソースのリレーショナルデータベース管理システム(RDBMS)｜

この記事でわかること

PostgreSQLによるデータベース構築

ここからはWindows版PostgreSQLを使用したデータベース構築例について説明します。構築後はPHPによるサンプルコードでアクセスできることを確認します。

PostgreSQLとは？

オープンソースのリレーショナルデータベース管理システム(RDBMS)の一つです。
Linux、FreeBSD、OSXなどのUnix系OSやWindowsにも対応してます。

公開されているソースコードの修正等も自由で、無償、有償を問わず利用可能なBSDライセンスとなっています。

以下、Windows版PostgreSQLを使ってデータベース構築をおこなっていきます。

データベース構築のための設計は行わずにサンプルデータを使用します。ER図はサンプルデータのページに掲載されているものを参照してください。

データベース構築後動作を確認するための必要最小限の設定のみをおこなっています。業務に使用する際には、セキュリティ等の検討をお願いします。

インストールを行う

PostgreSQL Database Downloadより13.2 – Windows x86-64をクリックしてファイルのダウンロードを行ってください。

ダウンロードしたpostgresql-13.2-2-windows-x64.exeをクリックして以下の手順でインストールを進めてください。

・Installation Directory:C:\Program Files\PostgreSQL\13

・Select Components:全て選択した状態

・Data Directory:C:\Program Files\PostgreSQL\13\data

・Password:任意

・Port:5432

・Locale:C

・Pre Installation Summaryを目視後Nextボタンを押下します。

・Ready to InstallでNextボタンを押下するとインストール開始されます。

・インストール終了後にスタックビルダのインストール画面が表示されますが、キャンセルボタンを押下して終了してください。

以上でインストール作業の終了になります。

・システム環境変数のPATHに以下を追加してください。
C:\Program Files\PostgreSQL\13\bin

WindowsPowershellを起動後、psql -lU postgresの結果が表示されれば正常にインストールされています。デフォルトでは、localhostからのアクセスが可能な設定になっています。

PS C:\> psql -lU postgres
ユーザ postgres のパスワード:上記で入力したパスワード
                                        データベース一覧
   名前    |  所有者  | エンコーディング | 照合順序 | 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
(3 行)

PS C:\> psql -lU postgres

ユーザ postgres のパスワード:上記で入力したパスワード

データベース一覧

-----------+----------+------------------+----------+-------------------+-----------------------

postgres | postgres | UTF8 | C | C |

template0 | postgres | UTF8 | C | C | =c/postgres +

template1 | postgres | UTF8 | C | C | =c/postgres +

(3 行)

ここまでの手順によりlocalhost内で使用するためのデータベース構築が完了しました。

設定ファイルを修正する

データベース構築後以下のファイルの内容のチェック及び必要に応じて修正します。
・postgresql.conf
・pg_hba.conf

postgresql.confをチェックする

C:\Program Files\PostgreSQL\13\data\postgresql.confの内容をチェックします。
・デフォルト値からの変更はありません。

listen_addresses = '*'		# what IP address(es) to listen on;
password_encryption = scram-sha-256		# md5 or scram-sha-256

1 2	listen_addresses = '*' # what IP address(es) to listen on; password_encryption = scram-sha-256 # md5 or scram-sha-256

pg_hba.confを修正する

C:\Program Files\PostgreSQL\13\data\pg_hba.confの内容を修正します。
・ADDRESS部はネットマスクもしくはIPアドレスになります。

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# “local” is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
#以下の行を追加する
host    all             all             xxx.xxx.xxx.0/24        scram-sha-256
#上記の行を追加する
# IPv6 local connections:
～

# TYPE DATABASE USER ADDRESS METHOD

# “local” is for Unix domain socket connections only

local all all scram-sha-256

# IPv4 local connections:

host all all 127.0.0.1/32 scram-sha-256

#以下の行を追加する

host all all xxx.xxx.xxx.0/24 scram-sha-256

#上記の行を追加する

# IPv6 local connections:

～

これにより、remotehostからもアクセス可能なデータベース構築が完了しました。

Windows Defender ファイアウォールの設定

pg_hba.confを修正した場合でも、localhost内でのPostgreSQLへのアクセスは有効になりますが、他PCからのアクセスは無効になります。

そのため外部からのアクセスが有効になるように、Windows Defender ファイアウォールの設定を追加します。

[コントロールパネル] -> [システムとセキュリティ] -> [Windows Defender ファイアウォール] -> [詳細設定] -> [受信の規則] を順にクリックしていきます。

受信の規則(TCP)の追加
・右側ペインの[新しい規則]をクリックする。

・規則の種類:ポート

・プロトコルおよびポート:TCPをチェック
特定のローカルポートをチェック後 5432 を入力

・操作:接続を許可する

・プロファイル:そのまま

・名前:postgres-tcp

受信の規則(UDP)の追加
・右側ペインの[新しい規則]をクリックする。

・規則の種類:ポート

・プロトコルおよびポート:UDPをチェック
特定のローカルポートをチェック後 5432 を入力

・操作:接続を許可する

・プロファイル:そのまま

・名前:postgres-udp

データベース構築後外部からのアクセスが可能となります。

サンプルデータベースを作成する

PostgreSQL Sample Database以下にある[Download DVD Rental Sample Database]をクリックして、サンプルデータをダウンロードしてください。

ダウンロードしたdvdrental.zipを解凍して適当なフォルダに配置してください。

サンプル用のデータベースとしてdvdrentalを作成します。

PS C:\> createdb --locale=C --encoding=UTF-8 --template=template0 -U postgres dvdrental
パスワード:

1 2	PS C:\> createdb --locale=C --encoding=UTF-8 --template=template0 -U postgres dvdrental パスワード:

サンプル用のデータを取り込みます。

PS > pg_restore -U postgres -d dvdrental dvdrental.tar
パスワード:

1 2	PS > pg_restore -U postgres -d dvdrental dvdrental.tar パスワード:

取込んだデータを目視で確認します。

PS C:\> psql -U postgres
ユーザ postgres のパスワード:
psql (13.2)
“help”でヘルプを表示します。

※データベースの一覧を表示すると、dvdrentalデータベースが追加されていることが確認できます。
postgres=# \l
                                        データベース一覧
   名前    |  所有者  | エンコーディング | 照合順序 | Ctype(変換演算子) |     アクセス権限
-----------+----------+------------------+----------+-------------------+-----------------------
 dvdrental | postgres | UTF8             | C        | C                 |
 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
(4 行)

postgres=# \c dvdrental
データベース”dvdrental”にユーザ”postgres”として接続しました。

※テーブル一覧を表示します
dvdrental=# \d
                       リレーション一覧
 スキーマ |            名前            |   タイプ   |  所有者
----------+----------------------------+------------+----------
 public   | actor                      | テーブル   | postgres
 public   | actor_actor_id_seq         | シーケンス | postgres
 public   | actor_info                 | ビュー     | postgres
 public   | address                    | テーブル   | postgres
～
 public   | store                      | テーブル   | postgres
 public   | store_store_id_seq         | シーケンス | postgres
(35 行)

PS C:\> psql -U postgres

ユーザ postgres のパスワード:

psql (13.2)

“help”でヘルプを表示します。

※データベースの一覧を表示すると、dvdrentalデータベースが追加されていることが確認できます。

postgres=# \l

データベース一覧

-----------+----------+------------------+----------+-------------------+-----------------------

dvdrental | postgres | UTF8 | C | C |

postgres | postgres | UTF8 | C | C |

template0 | postgres | UTF8 | C | C | =c/postgres +

template1 | postgres | UTF8 | C | C | =c/postgres +

(4 行)

postgres=# \c dvdrental

データベース”dvdrental”にユーザ”postgres”として接続しました。

※テーブル一覧を表示します

dvdrental=# \d

リレーション一覧

スキーマ | 名前 | タイプ | 所有者

----------+----------------------------+------------+----------

public | actor | テーブル | postgres

public | actor_actor_id_seq | シーケンス | postgres

public | actor_info | ビュー | postgres

public | address | テーブル | postgres

～

public | store | テーブル | postgres

public | store_store_id_seq | シーケンス | postgres

(35 行)

データベース構築後、サンプルデータの取込が完了しました。
データベース構築のためのSQL等はdvdrental.tarを解凍したファイル内のrestore.sqlに記述されています。

サンプルデータベースへのアクセス環境を設定する

createdbで作成したデータベースは作成したrole(今回はpostgres)のみアクセスおよび操作が可能となります。

PostgreSQLではroleを作成し、スキーマやテーブルに対する権限を付与します。
以下に実行例を示します。

・dvdrentalデータベース用のroleを作成します。

PS C:\> psql -U postgres
ユーザ postgres のパスワード:

postgres=# create role dvdrental with login password 'passwd';
CREATE ROLE

PS C:\> psql -U postgres

ユーザ postgres のパスワード:

postgres=# create role dvdrental with login password 'passwd';

CREATE ROLE

・dvdrental roleでログイン後SQL文を発行してみます。
アクセス権が無いためにエラーとなります。

PS C:\Users\doumae> psql -U dvdrental
ユーザ dvdrental のパスワード:

dvdrental=> select count(*) from actor;
ERROR:  permission denied for table actor

PS C:\Users\doumae> psql -U dvdrental

ユーザ dvdrental のパスワード:

dvdrental=> select count(*) from actor;

ERROR: permission denied for table actor

・dvdrental roleに対し権限を付与します。

PS C:\> psql -U postgres
ユーザ postgres のパスワード:

postgres=# \c dvdrental
データベース”dvdrental”にユーザ”postgres”として接続しました。
dvdrental=# grant select on actor to dvdrental;
GRANT

PS C:\> psql -U postgres

ユーザ postgres のパスワード:

postgres=# \c dvdrental

データベース”dvdrental”にユーザ”postgres”として接続しました。

dvdrental=# grant select on actor to dvdrental;

GRANT

・dvdrental roleでログイン後SQL文を発行してみます。
アクセス権が付与されているので、正常に実行されます。

PS C:\Users\doumae> psql -U dvdrental
ユーザ dvdrental のパスワード:

dvdrental=> select count(*) from actor;
 count
-------
   200
(1 行)

PS C:\Users\doumae> psql -U dvdrental

ユーザ dvdrental のパスワード:

dvdrental=> select count(*) from actor;

count

-------

200

(1 行)

以上、データベース構築、サンプルデータの投入及び外部からアクセスができることがチェックできました。

PHPと連携してみる

ここからは構築したPostgreSQLデータベースとPHPによる連携を確認します。
PHPをインストール後、設定ファイルを修正して基本的な動作を確認した後サンプルコードにてアクセスできるでまでの例を示します。

PHPをCLIモードで作動することを確認する

PHPのCLIモードでサンプルコードの動作を確認するための最小限の手順を記述します。
注)あくまで最小限の設定です。必要に応じてphp.iniを設定してください。

・PHP 8.0 (8.0.3)より[VS16 x64 Thread Safe]のzip版をダウンロードします。

・ダウンロードしたphp-8.0.3-Win32-vs16-x64.zipを解凍後C:\以下に配置してphp8とリネームします。

・c:\php8以下のphp.ini-productionをコピーしてphp.iniとリネームします。

・php.iniの以下の２か所をコメントアウトします。
extension_dir = “ext”
extension=pdo_pgsql

・c:\php8\libpq.dllをC:\Windows以下にコピーします。

正しく設定されていれば、以下の結果が表示されます。

PS C:\> c:\php8\php -v
PHP 8.0.3 (cli) (built: Mar  2 2021 23:34:05) ( ZTS Visual C++ 2019 x64 )
Copyright (c) The PHP Group
Zend Engine v4.0.3, Copyright (c) Zend Technologies

PS C:\> type C:\Apache24\htdocs\phpinfo.php

<?php phpinfo(); ?>
</code> PS C:\> c:\php8\php -f C:\Apache24\htdocs\phpinfo.php | Select-String libpq PostgreSQL(libpq) Version => 11.4 PS C:\> c:\php8\php -f C:\Apache24\htdocs\phpinfo.php | Select-String pgsql PDO drivers => pgsql

PS C:\> c:\php8\php -v

PHP 8.0.3 (cli) (built: Mar 2 2021 23:34:05) ( ZTS Visual C++ 2019 x64 )

Zend Engine v4.0.3, Copyright (c) Zend Technologies

PS C:\> type C:\Apache24\htdocs\phpinfo.php

<?php phpinfo(); ?>

</code> PS C:\> c:\php8\php -f C:\Apache24\htdocs\phpinfo.php | Select-String libpq PostgreSQL(libpq) Version => 11.4 PS C:\> c:\php8\php -f C:\Apache24\htdocs\phpinfo.php | Select-String pgsql PDO drivers => pgsql

PHPのCLIモードからPostgreSQLにアクセスする

上記の内容でPHPがCLIモードで動作することやpdo_pgsqlが正常に認識できました。
以下のサンプルコードが動作することがチェックできます。


<?php
$dsn = 'pgsql:host=localhost;dbname=dvdrental';
$user = 'dvdrental';
$passwd = '設定されたパスワード';

try{
    $dbh = new PDO($dsn, $user, $passwd);
    $stmt = $dbh->query(“select count(*) from actor”);
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo “<pre>”;
	print_r( $users );
    echo “</pre>”;
}catch (PDOException $e){
    print('Error:'.$e->getMessage());
    die();
}
?>

<?php

$dsn = 'pgsql:host=localhost;dbname=dvdrental';

$user = 'dvdrental';

$passwd = '設定されたパスワード';

try{

$dbh = new PDO($dsn, $user, $passwd);

$stmt = $dbh->query(“select count(*) from actor”);

$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo “<pre>”;

print_r( $users );

echo “</pre>”;

}catch (PDOException $e){

print('Error:'.$e->getMessage());

die();

}

実行結果

PS C:\> c:\php8\php -f C:\Apache24\htdocs\pgsql.php
<pre>Array
(
    [0] => Array
        (
            [count] => 200
        )
)
</pre>

PS C:\> c:\php8\php -f C:\Apache24\htdocs\pgsql.php

<pre>Array

(

[0] => Array

(

[count] => 200

)

</pre>

データベースの構築及びPHPでのアクセスの動作がチェックができました。

PostgreSQLでデータベースを構築してみよう

いかがでしたでしょうか。Windows版PostgreSQLを使用してデータベース構築までの手順を説明してきました。

Windows版ではx64版のみというOSの制約はありますが、本格的なRDBMSが手軽に導入できることを提示しました。

ぜひご自身で実際に導入して試してみてください。

FEnetを運営しているネプラス株式会社はサービス開始から10年以上
『エンジニアの生涯価値の向上』をミッションに掲げ、
多くのインフラエンジニア・ネットワークエンジニアの就業を支援してきました。

ネプラス株式会社はこんな会社です

秋葉原オフィスにはネプラス株式会社をはじめグループのIT企業が集結！
数多くのエンジニアが集まります。

インフラ業界に特化

ネットワーク・サーバー・データベース等、ITインフラ業界に特化。Cisco Systemsプレミアパートナーをはじめ各種ベンダーのパートナー企業です。

業界を知り尽くしているからこそ大手の取引先企業、経験豊富なエンジニアに選ばれています。
正社員なのにフリーランスのような働き方

正社員の方でも希望を聞いたうえでプロジェクトをアサインさせていただいており、フリーランスのような働き方が可能。帰社日もありません。

プロジェクト終了後もすぐに次の案件をご紹介させていただきますのでご安心ください。
大手直取引の高額案件

案件のほとんどが大手SIerやエンドユーザーからの直取引のためエンジニアの皆様へに高く還元できています。

Ciscoをはじめ、Juniper、Azure、Linux、AWS等インフラに特化した常時300件以上の案件があります。
スキルアップ支援

不要なコストを削減し、その分エンジニアの方へのスキルアップ支援(ネットワーク機器貸出、合格時の受験費用支給など)や給与で還元しています。

受験費用例）CCNP,CCIE:6-20万円、JNCIS:3-4万円、AWS:1-3万円など

※業務に関連する一定の資格のみ。各種条件がありますので詳しくは担当者へにお尋ねください。
現給与を保証します！※

前職の給与保証しており、昨年度は100%の方が給与アップを実現。収入面の不安がある方でも安心して入社していただけます。

※適用にはインフラエンジニアの業務経験1年以上、等一定の条件がございます。
インセンティブ制度

ネットワーク機器の販売・レンタル事業等、売上に貢献いただいた方にはインセンティブをお支払いしています。

取引先企業とエンジニア側、双方にメリットがあり大変好評をいただいています。
社会保険・福利厚生

社員の方は、社会保険を完備。健康保険は業界内で最も評価の高い「関東ITソフトウェア健康保険組合」です。

さらに様々なサービスをお得に利用できるベネフィットステーションにも加入いただきます。
東証プライム上場企業グループ

ネプラスは東証プライム上場「株式会社夢真ビーネックスグループ」のグループ企業です。

安定した経営基盤とグループ間のスムーズな連携でコロナ禍でも安定した雇用を実現させています。

ネプラス株式会社に興味を持った方へ

ネプラス株式会社では、インフラエンジニアを募集しています。

年収をアップしたい！スキルアップしたい！大手の上流案件にチャレンジしたい！
まずは話だけでも聞いてみたい場合もOK。お気軽にご登録ください。

ネプラス株式会社へ応募する
会員登録不要! カンタン45秒!

データベース構築｜オープンソースのリレーショナルデータベース管理システム(RDBMS)｜

PostgreSQLによるデータベース構築

PostgreSQLとは？

インストールを行う

設定ファイルを修正する

postgresql.confをチェックする

pg_hba.confを修正する

Windows Defender ファイアウォールの設定

サンプルデータベースを作成する

サンプルデータベースへのアクセス環境を設定する

PHPと連携してみる

PHPをCLIモードで作動することを確認する

PHPのCLIモードからPostgreSQLにアクセスする

PostgreSQLでデータベースを構築してみよう

ネプラス株式会社はこんな会社です

インフラ業界に特化

正社員なのにフリーランスのような働き方

大手直取引の高額案件

スキルアップ支援

現給与を保証します！※

インセンティブ制度

社会保険・福利厚生

東証プライム上場企業グループ

ネプラス株式会社に興味を持った方へ

PostgreSQL新着案件New Job

【高額年収】/国内クラウドシステムのサーバ運用保守/東京都新宿区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務

国内クラウドシステムのサーバ構築のテスター/東京都新宿区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務

【高額年収】/データレイク基盤POC運用保守/東京都23区西部/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務

【高額年収】/社内情報システムインフラ運用保守/Windows/東京都多摩北部/【WEB面談可】/在宅勤務/20代～30代の方活躍中

社内情報システムインフラ構築のテスター/Windows/東京都多摩北部/【WEB面談可】/在宅勤務/20代～30代の方活躍中

【高額年収】/クラウド基盤の運用保守/Windows/東京都江東区/【WEB面談可】/在宅勤務/20代～40代の方活躍中

データベース構築｜オープンソースのリレーショナルデータベース管理システム(RDBMS)｜

PostgreSQLによるデータベース構築

PostgreSQLとは？

インストールを行う

設定ファイルを修正する

postgresql.confをチェックする

pg_hba.confを修正する

Windows Defender ファイアウォールの設定

サンプルデータベースを作成する

サンプルデータベースへのアクセス環境を設定する

PHPと連携してみる

PHPをCLIモードで作動することを確認する

PHPのCLIモードからPostgreSQLにアクセスする

PostgreSQLでデータベースを構築してみよう

ネプラス株式会社はこんな会社です

インフラ業界に特化

正社員なのにフリーランスのような働き方

大手直取引の高額案件

スキルアップ支援

現給与を保証します！※

インセンティブ制度

社会保険・福利厚生

東証プライム上場企業グループ

ネプラス株式会社に興味を持った方へ

PostgreSQL新着案件New Job

【高額年収】/国内クラウドシステムのサーバ運用保守/東京都新宿区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務

国内クラウドシステムのサーバ構築のテスター/東京都新宿区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務

【高額年収】/データレイク基盤POC運用保守/東京都23区西部/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務

【高額年収】/社内情報システムインフラ運用保守/Windows/東京都多摩北部/【WEB面談可】/在宅勤務/20代～30代の方活躍中

社内情報システムインフラ構築のテスター/Windows/東京都多摩北部/【WEB面談可】/在宅勤務/20代～30代の方活躍中

【高額年収】/クラウド基盤の運用保守/Windows/東京都江東区/【WEB面談可】/在宅勤務/20代～40代の方活躍中

関連記事Related Posts

PostgreSQLのUPDATEとは？psqlのUPDATEとpgAdmin4のUPDATEを紹介！

[C#]MySQLの接続方法とは？NuGetパッケージ・データ表示・データ検索

MySQLでログファイルをチェックするには？MySQLで使えるログは4種類

[MySQL]ストアドプロシージャ（Procedure）の使い方とは？Procedureの表示や削除