備忘録的プログラミングリファレンス

データベースの管理(一覧、作成、接続、削除)

 データベースとは、実際にデータを収める複数のテーブルやビュー,トリガといったスクリプトを統括し管理するものです。

 ここではデータベースにおける、データベースの一覧、作成、削除を解説します。 PostgreSQL におけるデータベースの操作は、システムから実行できる psql シェルコマンドや、psql にログインしてからの psql コマンドや SQL のクエリーで行います。

データベースを操作する上での注意点

ユーザーについて

 データベースの作成はスーパーユーザーもしくはデータベースを作成することが許可されたユーザーであることが必要です。
 UbuntuでPostgreSQLを標準インストールするとスーパーユーザーはpostgresです。
 ここでは話を簡単にするためにユーザーはpostgresとします。

 ただし、サーバーとして PostgreSQL を運用する場合はスーパーユーザーはpostgresは使用しない方がよいでしょう。外部からデータベースに接続するようになったらスーパーユーザー、ユーザーの追加を検討すべきです。
 データベースの操作、特にデーターの追加、更新、削除といった操作をスーパーユーザーのみが行えるようにしておくのはセキュリティー上好ましくありません。
 外部からデータベースに接続できるのがスーパーユーザーのみにしておくと、そのIDとパスワードでデータベースのすべてが改変できるためです。

 データベースの作成や操作権限は別のユーザーで行えるようにしておいた方がよいでしょう。詳しくは以下の「オーナーを指定したデータベース作成」を参照してください。

データベース間の通信における注意点

 データベースごとにデータは独立して管理され、外部キーなどでデータベースをまたいだテーブル間の参照ができません。
 あるデータベースから他のデータベースを参照することはできません。インターフェイスなどでデータベースの外から参照することはできます。

データベースの一覧

 PostgreSQL で、既に作成されたのデーターベースの一覧を確認するには、システム上からシェルコマンドで、psql 上から psql コマンドで、SQL クエリーを実行するといった方法があります。PostgreSQL 上のシステムカタログのテーブルが表示されます。

シェルコマンド

 OS の端末から既存のデータベースを確認してみましょう。端末を開いて以下のシェルコマンドを実行します。

psql -l -p ポート番号 -h ホスト名

 端末はプログラム名が端末またはコマンドプロンプトと呼ばれており、デスクトップメニューにあります。* shellと呼ばれているかもしれません。

 OS 上からシェルコマンドで利用する場合はポート番号やホスト名を指定することができます。

$ psql -l
...
 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 template0 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
...

 以上の例では、ポート番号やホスト名を指定していません。ローカルにあるデータベース一覧を表示します。

psql コマンド

 以下の例は、psql にログインをした上で psql コマンド\lを実行します。

\l

 psql にログインしコマンドプロンプトで \l を実行しデータベースの一覧を表示します。

$ psql
...
=# \l

 その他のコマンドに関しては、\?でコマンドの一覧が表示されます。

SQL クエリー

 psql にログインしたら、以下のような SQL クエリーを実行することができます。

SELECT * FROM pg_database;

 psql コマンドでログインまたは SQL コマンドが使える環境で pg_database テーブルを参照するSQLクエリーを実行します。 pg_database テーブルで既存のデータベース情報を取得することができます。

$ psql
...
SELECT * FROM pg_database;
...
  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace |               datacl
-----------+--------+----------+-------------+-------------+---------------+------
 postgres  |     10 |        6 | ja_JP.UTF-8 | ja_JP.UTF-8 | f             | t            |           -1 |         13052 |          548 |          1 |          1663 |
 template1 |     10 |        6 | ja_JP.UTF-8 | ja_JP.UTF-8 | t             | t            |           -1 |         13052 |          548 |          1 |
...

データベースの作成

 新規にデータベースを作成する方法です。データベースの作成はスーパーユーザーまたはデータベースの作成権限をもつユーザーでなければなりません。
 以下はスーパーユーザーによるデータベースの作成例です。

SQL クエリー

 psql コマンドでログインまたは SQL コマンドが使える環境で CREATE DATABASE クエリーを実行します。

$ psql
...
# CREATE DATABASE dbname;

シェルコマンド

 システム上からシェルコマンドを利用してデータベースを作成する方法です。

$ createdb testdb -U postgres

オーナーを指定したデータベース作成

 任意のユーザーを指定してデータベースを作成することもできます。
 方法としては、データベースを作成するときに任意のユーザーをオーナーとして設定します。普段はこのオーナーユーザーでデータベースと接続します。
 ここでオーナーにするユーザーは事前に追加しておかなければなりません。

オーナーを指定してデータベース作成
# CREATE DATABASE dbname OWNER rolename;

 作成が成功しても何の応答もないので、上記の「データベースの一覧」で作成状況を確認してください。

 スーパーユーザーだけがデータベースを操作できることは危険ですので、他のユーザーでデータベースが操作できるようにした方がよいでしょう。

 rolenameはロール名です。データベースではユーザーは正確にはロールとして扱います。ロールはデータベースの操作権限をもったユーザーのことです。
 ここでは話を簡単にするためにユーザーと表記しています。
 ユーザー、ロールについて詳しくはユーザー(ロール)管理ページを参照してください。

オーナーを設定するべき理由

 外部からデータベースと接続するためには、データベースと接続できるユーザーである必要があります。
 外部からデータベースとの接続において、ユーザーとパスワードで認証する方法と、CGIのsuEXECを使用する方法があります。

 CGIのsuEXECを使用する方法は、CGIを実行するユーザーがそのままデータベースのオーナーであればデータベースと接続できます。

 外部からデータベースに接続するのがスーパーユーザーではPostgreSQL自体が操作できてしまいますのでセキュリティー上好ましくはありません。

データベースにログインユーザを登録

 データベースに任意のログインユーザーを新規登録することができます。

psqlコマンドプロンプト

psql my_database
...
foodatabase=# create role foo_usr with login;
CREATE ROLE

 この例では、ユーザfoo_usrがmy_databaseにアクセスできるようになります。 詳しくはロール(ユーザー)の追加を参照してください。

データベースに接続

 作成したデータベースにテーブルやビューを作成したりデータを編集するためには、データベースを指定し接続しなければなりません。
 データベースに接続する方法には、psql コマンドでデータベースを指定する、psql コマンドプロンプトで \c コマンドを実行する方法があります。

 psql コマンドでデータベースに接続/切り替える方法です

psqlコマンドでデータベース指定
$ psql db_name
...
db_name=#

 psql にログインした状態でデータベースを切り替えるには \c コマンドを使用します。

データベースの切り替え
db_name=# \c other_db
...
other_db=#

データベースの削除

 データベースを削除するには、データベースの作成権限をもつユーザまたはデータベースオーナーで、以下のコマンドを実行します。

$ drop -i -e db_name

 このコマンドには、削除の確認とバックエンドに送られるコマンドを表示するオプションが付いています。

データベース・クラスタ

 PostgreSQL といったデータベース管理システムでは、複数あるデータベースをデータベース・クラスタ(データベース・インスタンス)によって管理しています。

図.データベース・クラスタとデータベース

 データベース・クラスタは複数あるデータベースの管理と外部からのアクセスを担っています。データベース・クラスタの役割には複数のデータベースやアクセスのポート番号、ロール(ユーザー)、アクセスログなどがあります。それらの設定ディレクトリは initdb コマンドによって操作することができます。
 データベース・クラスタの設定データの作成には initdb コマンドが標準ですが、Ubuntu のパッケージインストールによる PostgreSQL では pg_lsclusters、pg_createcluster、pg_upgradecluster、pg_dropclusterといったコマンドを使用します。またパッケージインストールでは、pg_ctl コマンドも想定通りに機能しません。pg_ctl の代わりに pg_ctlcluster を使用します。
 詳しくはデータベース・クラスタ(データベース・インスタンス)を参照してください。