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

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

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

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

 データベースを作成する前に、まずは作成済みのデータベースを確認することから始めます。

データベースを管理する上での注意点

ユーザーについて

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

 データベースの操作、特にデーターの追加、更新、削除といった操作をスーパーユーザーのみが行えるようにしておくのはセキュリティー上好ましくありません。
 外部からデータベースに接続できるのがスーパーユーザーのみにしておくと、そのIDとパスワードでデータベースのすべてが改変できるためです。
 データベースの作成や操作権限は別のユーザーで行えるようにしておいた方がよいでしょう。詳しくは以下の「オーナーを指定したデータベース作成」を参照してください。

データベース間の通信

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

データベースの一覧

 PostgreSQLで、既に作成されたのデーターベースの一覧を確認します。PostgreSQL上のシステムカタログのテーブルも表示されます。

SQLコマンド

SELECT * FROM pg_database;

 psql コマンドでログインまたは SQL コマンドが使える環境で pg_database テーブルを参照するSQLクエリーを実行します。

$ 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 |
...

シェルコマンド

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

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

$ 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コマンドプロンプト

\l

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

$ psql
...
=# \l

データベースの作成

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

SQLコマンド

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

$ psql
...
# CREATE DATABASE dbname;

シェルコマンド

$ createdb testdb -U postgres

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

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

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

SQLコマンド

# CREATE DATABASE dbname OWNER 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 を介してにログインする方法があります。

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

psqlコマンドでデータベース指定

$ psql db_name
...
db_name=#

PostgreSQL上でデータベースを切り替え

db_name=# \c other_db
...
other_db=#

データベースの削除

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

$ drop -i -e db_name

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

その他

データベースのディレクトリ初期化

 データベースのディレクトリ初期化は、データベースの領域となるディレクトリを指定するものです。この例では、デフォルト(環境変数PGDATA)のディレクトリに作成します。

シェルコマンド

$ initdb

 ディレクトリ指定をするには-Dオプションを用います。