データベースの管理（一覧、作成、接続、削除）
データベースとは、実際にデータを収める複数のテーブルやビュー,トリガといったスクリプトを統括し管理するものです。
ここではデータベースの管理における、データベースの一覧、作成、削除を解説します。 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 db_name ... db_name=#
psql にログインした状態でデータベースを切り替えるには \c コマンドを使用します。
db_name=# \c other_db ... other_db=#
データベースの削除
データベースを削除するには、データベースの作成権限をもつユーザまたはデータベースオーナーで、以下のコマンドを実行します。
$ drop -i -e db_name
このコマンドには、削除の確認とバックエンドに送られるコマンドを表示するオプションが付いています。
その他
データベースのディレクトリ初期化
データベースのディレクトリ初期化は、データベースの領域となるディレクトリを指定するものです。この例では、デフォルト（環境変数PGDATA）のディレクトリに作成します。
シェルコマンド
$ initdb
ディレクトリ指定をするには-Dオプションを用います。