データベースの管理（一覧、作成、接続、削除）
データベースとは、実際にデータを収める複数のテーブルやビュー,トリガといったスクリプトを統括し管理するものです。
ここではデータベースの管理における、データベースの一覧、作成、削除を解説します。 PostgreSQL におけるデータベースの管理は、システム上で実行できる psql シェルコマンド、psql にログインしての psql コマンド、SQL のクエリーから行います。
まずはデータベースを管理する上でのユーザーについてです。
データベースを管理する上での注意点
ユーザーについて
データベースの作成はスーパーユーザーもしくはデータベースを作成することが許可されたユーザーであることが必要です。
UbuntuでPostgreSQLを標準インストールするとスーパーユーザーはpostgres
です。
データベースの操作、特にデーターの追加、更新、削除といった操作をスーパーユーザーのみが行えるようにしておくのはセキュリティー上好ましくありません。
外部からデータベースに接続できるのがスーパーユーザーのみにしておくと、そのIDとパスワードでデータベースのすべてが改変できるためです。
データベースの作成や操作権限は別のユーザーで行えるようにしておいた方がよいでしょう。詳しくは以下の「オーナーを指定したデータベース作成」を参照してください。
はじめて PostgreSQL を使われる場合はスーパーユーザーはpostgres
のままでよいでしょう。外部からデータベースに接続するようになったらスーパーユーザー、ユーザーを検討した方がよいと思います。
データベース間の通信における注意点
データベースごとにデータは独立して管理され、外部キーなどでデータベースをまたいだテーブル間の参照ができません。
あるデータベースから他のデータベースを参照することはできません。インターフェイスなどでデータベースの外から参照することはできます。
以降は、まずは作成済みまたは既存のデータベースを確認することから始めています。
データベースの一覧
PostgreSQLで、既に作成されたのデーターベースの一覧を確認します。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
psql にログインし、コマンドプロンプト \l
でデータベースの一覧を表示します。
$ psql ... =# \l
その他のコマンドプロンプトに関しては、\?
でコマンドの一覧が表示されます。
SQLコマンド
psql にログインしたら、以下のような SQL コマンドも実行することができます。SQL コマンドは PostgreSQL の外部からの問い合わせでも使用します。
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
システム上からシェルコマンドを利用してデータベースを作成する方法です。
オーナーを指定したデータベース作成
オーナーとして任意のユーザーを指定してデータベースを作成することもできます。
スーパーユーザーだけがデータベースを操作できることは危険ですので、他のユーザーでデータベースが操作できるようにした方がよいでしょう。
方法としては、データベースを作成するときに任意のユーザーをオーナーとして設定します。普段はこのオーナーユーザーでデータベースと接続します。
ここでオーナーにするユーザーは事前に追加しておかなければなりません。
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オプションを用います。