PostgreSQL | A Magic Web

PostgreSQL データベース・テーブルの作成

2009 年 5 月 27 日水曜日

PostgreSQL は、複数のデータベースを持つことができます。また、各データベースはその中に複数のテーブルを持つことができます。同じデータベース内では、テーブル同士のアクセスができますが、データベースを跨いだアクセスはできません。

データベースの作成

データベースの作成には、createdb 権限をもつ postgreSQL のロールが必要です。エンコードオプションを指定しない場合、デフォルトのエンコード（下の例では UTF8 → もしかしたらシステムのエンコード）になるようです。

データベースの作成

以下のコマンドは、psql でなにかしらのデータベースに接続した状態で実行します。

postgres=> CREATE DATABASE sampledb;
CREATE DATABASE

データベースを一覧で表示

作成したデータベースを確認するときなど。

postgres=> l

テーブルの作成

テーブルを作成するデータベースに接続してコマンドを実行します。書式は以下の通り

create table テーブル名(列名データ型, 列名データ型);

sampledb=> create table test(num int, name varchar(50));
CREATE TABLE

主キーを設定してテーブルを作成

列名データ型につづけて primary key を追加します。

test-> create table test
test-> (num int primary key, name varchar(50));

自動採番する列を作成

自動採番する列を含むテーブルを作成するには、自動採番したい列のデータ型を serial に設定します。

test-> create table test
test-> (num serial, name varchar(50));

デフォルト値を設定

デフォルト値を設定すると、レコードの該当列に値を入れなくてもデフォルト値が自動的に入ります。書式は以下のとおりです。

create table テーブル名 (列名データ型 default デフォルト値, 列名データ型);

test-> create table test
test-> (num int, name varchar(50) default 'ナメック星人');

その他オプション

主キーの設定と同じように、データ型につづけてオプションを追記します。

オプション	説明
not null	NULL （値なし）が許可されない制約
unique	値の重複が許可されない制約

作成したテーブルの確認

テーブルの一覧を表示

test=> \d

テーブル内の列情報を表示する方法。unique などの各種制約オプションも表示されます。

test=> \d users
                                 Table "public.users"
 Column  |         Type          |                      Modifiers
---------+-----------------------+-----------------------------------------------------
 num     | integer               | not null default nextval('users_num_seq'::regclass)
 id      | character varying(20) | not null
 pw      | character varying(20) | not null
 name    | character varying(50) | not null
 authlev | integer               | not null default 0
 env1    | character varying(50) |
 env2    | character varying(50) |
Indexes:
    "users_id_key" UNIQUE, btree (id)
    "users_pw_key" UNIQUE, btree (pw)

カテゴリー: PostgreSQL | コメントはまだありません »

PostgreSQL 主キーの設定方法

2009 年 5 月 27 日水曜日

テーブル作成時に主キーを設定

列名、データ型につづけて、PRIMARY KEY を加えます。

test=> create table test2
test-> (num int primary key, name varchar(50));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test2_pkey" for table "test2"
CREATE TABLE

作成済みのテーブルに主キーを設定

作成済みのテーブルに対して、主キーを設定する方法です。

test=> alter table test add primary key (num);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "test_pkey" for table "test"
ALTER TABLE

カテゴリー: PostgreSQL | コメントはまだありません »

PostgreSQL データ型

2009 年 5 月 27 日水曜日

よく目にするものだけまとめてみようと思ったのですが、すでにまとまっているページがあったので引用しました。より詳細な情報は、PostgreSQL 8.3.7文書 : 第 8章データ型に載っています。

型 内容

CHAR(長さ) カッコ内に指定した長さの固定長の文字列

VARCHAR(長さ) カッコ内に指定した長さ以下の可変長の文字列

TEXT 任意の長さの可変長の文字列

INTEGER 通常使用する整数

FLOAT 実数

NUMERIC (整数部分桁数、小数部分桁数) 任意精度の固定小数

DATE 日付

TIME 時刻

TIMESTAMP 日付と時刻

BOOLEAN 論理値。真（’t'）または偽（’f'）

http://www.itmedia.co.jp/enterprise/0307/18/epn20_5.html

参考にしたサイト

ITmediaエンタープライズ : 第1回 PostgreSQLで学ぶSQLデータベースの操作 (5/15)

カテゴリー: PostgreSQL | コメントはまだありません »

PostgreSQL で自動採番される列を作る

2009 年 5 月 20 日水曜日

自動採番される列を含むテーブルを作る

やり方自体は簡単で、自動採番させたい列のデータ型に serial を指定するだけ。下の例では、システム側と Postgre 側にユーザー centos を設定済みで、データベース sampledb を作成済みです。

自動採番にはシーケンスを使うようですが（←よくわかっていない）、自動的に “テーブル名_列名_seq” の名前のシーケンスが作成されているようです。

[root@centos data]# su - centos
[centos@centos ~]$ psql sampledb
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

sampledb=> CREATE TABLE table1
sampledb-> (id serial, nam varchar(20), pre varchar(10), ag text);
NOTICE:  CREATE TABLE will create implicit sequence "table1_id_seq" for serial column "table1.id"
CREATE TABLE

データ型（type）に serial を指定してテーブルを作りましたが、"serial" というデータ型があるわけではないようです。

sampledb=> \d table1
                                Table "public.table1"
 Column |         Type          |                      Modifiers
--------+-----------------------+-----------------------------------------------------
 id     | integer               | not null default nextval('table1_id_seq'::regclass)
 nam    | character varying(20) |
 pre    | character varying(10) |
 ag     | text                  |

自動採番される列以外の列にデータを追加して、動作を確認してみます。

sampledb=> INSERT INTO table1 (nam, pre, ag) VALUES ('ピッコロ', 'ナメック星', 30);
INSERT 0 1
sampledb=> INERT INTO table1 (nam, pre, ag) VALUES ('ベジータ', '惑星べじーた', 28);
INSERT 0 1
sampledb=> select * from table1;
 id |   nam    |     pre      | ag
----+----------+--------------+----
  1 | ピッコロ | ナメック星   | 30
  2 | ベジータ | 惑星べじーた | 28
(2 rows)

id にはデータを追加していないのにもかかわらず、自動的に番号が振られました。

カテゴリー: PostgreSQL | コメントはまだありません »

PostgreSQL のファイルの構成

2009 年 5 月 20 日水曜日

yum でインストールしたままの状態の、PostgreSQL のファイルの配置です。環境は以下のとおり。

CentOS 5.3
PostgreSQL 8.3

データベースクラスタ

デフォルト状態では、設定ファイルもデータベースのデータファイルもすべて1箇所にまとめられているようです。PostgreSQL では、設定ファイル、データベースシステムの全ファイルが保存される領域を、データベースクラスタと呼ぶようです。

var/lib/pgsql/data/

PG_VERSION	PostgreSQL のバージョンファイル
pg_hba.conf	ホスト認証ファイル
pg_ident.conf	ident による認証ファイル
postgresql.conf	実行時パラメータ設定ファイル
postmaster.opts	起動オプション記録
base/	データベースのデータが保存される
global/	コントロールファイルやパスワードファイルなど、共通オブジェクトの保存ディレクトリ
pg_clog/	コミットログが置かれる（コミットログはすべてのトランザクションのコミット状態を記録する
pg_xlog/	WAL ログ（トランザクションログ）が保存される
pg_subtrans/	サブトランザクションの状態を記録する
pg_tblspc/	テーブルスペースへのシンボリックリンクを記録する
pg_twophase/	準備されたトランザクション（二層コミット）の状態を記録する
pg_multixact/	マルチトランザクションの状態を記録する。共有行ロックで使用

参考にしたもの

「PostgreSQL完全機能リファレンス」鈴木啓修(著)

カテゴリー: PostgreSQL | コメントはまだありません »

PostgreSQL と PHP の接続

2009 年 5 月 17 日日曜日

やっとこさ、うまくいったので、PHP から PostgreSQL にアクセスする際の条件などをまとめてみる。使っているのは、CentOS 5.3, PostgreSQL 8.3, PHP 5.1.6 の環境。

PHP から PostgreSQL にアクセスする際の条件

PostgreSQL と PHP (Apacheも)すでにインストールされているとして、その他に必要な設定など。

php-pgsql のインストール
postgresql.conf の設定
pg_hba.conf の設定

php-pgsql

yum で普通にインストールすればよい。特に設定も必要なし。httpd サービスの再起動は必要かも。

[root@localhost ~]# yum -y install php-pgsql

インストール後に、phpinfo() を出力して、動作していることを確認。

[root@centos ~]# vi /var/www/html/test.php
<?php
  phpinfo();
?>

postgresql.conf の設定

すべてのクライアントからのアクセスを許可する設定です。PostgreSQL 8.3.7文書 : 第 18章サーバの構成で詳しく説明されています。

[root@centos ~]# vi /var/lib/pgsql/data/postgresql.conf
(かなり省略)
listen_addresses = '*'

pg_hba.conf の設定

PostgreSQL 8.3.7文書 : 第 21章クライアント認証に詳しく説明されています。

[root@centos data]# vi /var/lib/pgsql/data/pg_hba.conf
(かなり省略)
host    all         all         192.168.0.*/32       md5

php.ini でエラー表示を On

この設定は、PHP から PostgreSQL にアクセスする際の条件ではないです。上記の設定を含めて、php から postgreSQL にアクセスできないとき、php.ini のエラー表示を有効にすると、表示されるエラーメッセージから原因を探ることができるので便利。

[root@centos ~]# vi /etc/php.ini
(省略)
display_errors = On

ここがよくわからない

接続認証での、postgresql.conf とpg_hba.confの関係はどうなっているんだろう？→宿題

カテゴリー: PostgreSQL | コメントはまだありません »

PostgreSQL のエンコード

2009 年 5 月 17 日日曜日

現在、CentOS5.3 と PostgreSQL 8.3 で検証中です。

デフォルトのエンコードはUTF8 ?

PostgreSQL のデータベースを初期化する際に、エンコード指定のオプションをつけなかった場合、データベースのエンコードは UTF8 になるようです。

ただし、これが PostgreSQL のデフォルトなのか、Linux システムのエンコードを継承しているのかは未確認。今回使用している CentOS のシステムの文字コードは UTF8 です。

[root@centos ~]# cat /etc/sysconfig/i18n
LANG="ja_JP.UTF-8"

また、システム自体のエンコードとは違う文字コードの設定はできないようです。

postgres=> CREATE DATABASE sampledb2 WITH ENCODING = 'EUC_JP';
ERROR:  encoding EUC_JP does not match server's locale ja_JP.UTF-8
DETAIL:  The server's LC_CTYPE setting requires encoding UTF8.

データベースのエンコードの確認

データベースのエンコードの確認は、該当のデータベースに接続して、\encoding で行うことができます。

[root@centos ~]# su - centos
[centos@centos ~]$ psql postgres
(省略)
postgres=> \encoding
UTF8

また、存在するデータベースをリスト化する \l でもエンコードを確認することができます。こっちのほうが早いですね。

postgres=> \l
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+----------
 postgres  | postgres | UTF8
 template0 | postgres | UTF8
 template1 | postgres | UTF8
(3 rows)

カテゴリー: PostgreSQL | コメントはまだありません »

PostgreSQL データベースロール

2009 年 5 月 17 日日曜日

PostgreSQL 独自のユーザー、グループの両方を兼ねた “ロール” という概念があるようです。これは PostgreSQL 8.1 からできたもので、それ以前は普通にユーザーとグループだったようです。

データベースロールの属性

データベースロールにはいくつかの属性を設定できるようです。

権限	デフォルト	説明
スーパーユーザー権限	false	PostgreSQL の権限検査を受けない権限、全権限をもったユーザ権限。*1
ログイン権限	false	データベースにログインするための権限
ロール作成	false	他のロールを作成する権限
データベース作成	false	データベース作成に必要な権限
ロールの継承	true
パスワード	なし	データベース接続時の認証に使われるパスワード
パスワードの有効期限	無制限	パスワードの有効期限
最大接続数	無制限	該当のロールで同時にデータベースにアクセスできる最大数

データベースロールとシステムのユーザーについて

PostgreSQL のデータベースロールと、Linux 側のユーザーは全く別のもの。ただし、以下にあるように、同名にしておくと便利。

多くのアプリケーション（createuserおよびpsqlを含む）では、オペレーティングシステムの現在のユーザ名をデフォルトと仮定します。したがって、ロールとオペレーティングシステムのユーザの組み合わせ間で名前を一致させておくと便利です。

第 18章データベースロールと権限

データベースロールの作成 CREATE ROLE

データベースロールを作成する前に、同名のシステムユーザーを作成しておきます。

[root@centos ~]# useradd centos
[root@centos ~]# passwd centos

データベースロールの作成には Create Role コマンドを使います。Create Role は SQL コマンドなので、クライアントプログラムである psql でデータベースに接続して行います。

[root@centos ~]# su - postgres
-bash-3.2$ psql
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# create role centos with login password 'パスワード';
CREATE ROLE
postgres=# \du
                               List of roles
 Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+-----------
 centos    | no        | no          | no        | no limit    | {}
 postgres  | yes       | yes         | yes       | no limit    | {}
(2 rows)

データベースロールの属性の変更 ALTER ROLE

ロールの属性変更には、基本的にスーパーユーザー権限かロール作成権限を持ったユーザーが行う必要がある。*2

下の例では、作成した centos ロールに createdb 権限を付与した。付与する権限はスペースで区切ることで、複数付与することもできる。

postgres=# alter role centos createdb;
ALTER ROLE
postgres=# \du
                               List of roles
 Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+-----------
 centos    | no        | no          | yes       | no limit    | {}
 postgres  | yes       | yes         | yes       | no limit    | {}
(2 rows)

*1

データベースの初期化を行ったユーザー（ロール）が最初のスーパーユーザーとなる。デフォルトでは postgres ユーザー（ロール）

*2

一般ユーザーは、権限の削除と自分のパスワード変更のみ可能（権限の付与ができない）

参考にしたサイト

19.1. データベースロール

カテゴリー: PostgreSQL | コメントはまだありません »

PostgreSQL のインストール

2009 年 5 月 17 日日曜日

PostgreSQL のインストールはソースからコンパイルして行うと、依存関係ではまりやすいので、サクッと済ませるには yum を使うのがよさそう。PostgreSQL を yum でインストールする方法について、PostgreSQL RPM Building Project – Yum Repository Howto に書かれている。そのあたりのまとめ。

yum リポジトリの登録

yum でインストールといっても、標準のリポジトリでは、置かれている rpm パッケージのバージョンが古かったりする。そこで、PostgreSQL RPM Building Project が管理しているリポジトリを追加で登録する。

yum リポジトリを追加する際、通常であれば、/etc/yum.repos.d/ にある、.repo ファイルを編集するか、新たな .repo ファイルを作成して同じディレクトリに置く必要がある。

ところが、PostgreSQL RPM Building Project では、、/etc/yum.repos.d/ におくための .repo ファイルを rpm パッケージで配布している。つまり、rpm で .repos ファイルを自動設置できるということ。これは便利！

yum リポジトリインストール用の rpm は、Linux のディストリビューションごと、Postgres のバージョンごとになっている。Packages beginning with letter "P".から、インストールしたい PostgreSQL のバージョンの rpm を見つける。

見つけた URL を wget !

[root@centos ~]# wget http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-centos-8.3-6.noarch.rpm

すかさず、yum リポジトリをインストール。

[root@centos ~]# rpm -ivh pgdg-centos-8.3-6.noarch.rpm

/etc/yum.repos.d/ を確認すると、pgdg-81-cetos.repo ができている。

[root@centos ~]# ls /etc/yum.repos.d/
CentOS-Base.repo  CentOS-Media.repo  pgdg-83-centos.repo

ちなみに、pgdg-83-centos.repo の中身。

[root@centos ~]# cat /etc/yum.repos.d/pgdg-83-centos.repo
[pgdg83]
name=PostgreSQL 8.3 $releasever - $basearch
baseurl=http://yum.pgsqlrpms.org/8.3/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

[pgdg83-id]
name=PostgreSQL 8.3 $releasever - $basearch with integer datetimes.
baseurl=http://yum.pgsqlrpms.org/8.3/redhat/rhel-$releasever-$basearch-id
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

[pgdg83-source]
name=PostgreSQL 8.3 $releasever - $basearch - Source
failovermethod=priority
baseurl=http://yum.pgsqlrpms.org/srpms/8.3/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

[pgdg83-id-source]
name=PostgreSQL 8.3 $releasever - $basearch - integer-datetimes Source
failovermethod=priority
baseurl=http://yum.pgsqlrpms.org/srpms/8.3/redhat/rhel-$releasever-$basearch-id
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

次の手順は、PostgreSQL RPM Building Project – Yum Repository Howto にかいてある。

In order to use PGDG repository properly, you may need to exclude postgresql packages from the repository of the distro. Here are the steps:

As root, cd /etc/yum.repos.d

Edit distro’s .repo file:

On Fedora, edit fedora.repo and fedora-updates.repo, [fedora] sections

On CentOS, edit CentOS-Base.repo, [base] and [updates] sections.

On Red Hat, edit edit /etc/yum/pluginconf.d/rhnplugin.conf [main] section.

Add

exclude=postgresql*

to the bottom of the section.

That is enough for excluding distro packages.

exclude は、yum のインストールやアップデートから、指定したパッケージを除外するオプション。CentOS-Base.repo に exclude=postgresql* と指定することで、postgresql から始まるパッケージを CentOS 標準のリポジトリから除外する。

[root@centos ~]# vi /etc/yum.repos.d/CentOS-Base.repo
（省略）
[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

exclude=postgresql*

#released updates
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

exclude=postgresql*

これで、PostgreSQL の yum リポジトリの登録が完了。長くなってしまった。

リポジトリの追加がうまくいったかどうかは、yum install postgresql を実行して（Is this ok [y/N]:N で中止する）インストールされる postgresql のバージョンで確認することがでる。

PstgreSQL のインストール

あとは簡単。yum install でサクッといける。yum install を実行すると、インストールされる PostgreSQL のバージョンを確認することができる。

[root@centos ~]# yum install postgresql-server
(省略)
==========================================================================================
 Package                   Arch         Version                      Repository      Size
==========================================================================================
Installing:
 postgresql-server         i386         8.3.7-1PGDG.rhel5            pgdg83         4.6 M

PostgreSQL の初期設定

データベースの初期化

データベースの初期化は、8.3 あたりから initdb オプションで行うようです。service postgresql start ではエラーになりました。

[root@centos ~]# service postgresql start

/var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first.
                                                           [失敗]
[root@centos ~]# service postgresql initdb
データベースを初期化中:                                    [  OK  ]

サービスの開始と自動起動の登録

[root@centos ~]# service postgresql start
postgresql サービスを開始中:                               [  OK  ]
[root@centos ~]# chkconfig postgresql --list
postgresql      0:off   1:off   2:off   3:off   4:off   5:off   6:off
[root@centos ~]# chkconfig postgresql on
[root@centos ~]# chkconfig postgresql --list
postgresql      0:off   1:off   2:on    3:on    4:on    5:on    6:off

システム側の postgres ユーザーにパスワードを設定

システム側の postgres ユーザーは postgresql-server インストール時に自動で作成されます。

[root@centos ~]# passwd postgres
Changing password for user postgres.
New UNIX password:
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

PostgreSQL の postgres ユーザーにパスワードを設定

この postgres ユーザー（ロール）は PostgreSQL のスーパーユーザー権限を持っています。

root@centos ~]# su - postgres
-bash-3.2$ psql template1
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# alter role postgres with password 'パスワード';
ALTER ROLE

ここまでの作業で、とりあえず使える状態になりました。

カテゴリー: PostgreSQL | コメントはまだありません »

型	内容
CHAR(長さ)	カッコ内に指定した長さの固定長の文字列
VARCHAR(長さ)	カッコ内に指定した長さ以下の可変長の文字列
TEXT	任意の長さの可変長の文字列
INTEGER	通常使用する整数
FLOAT	実数
NUMERIC (整数部分桁数、小数部分桁数)	任意精度の固定小数
DATE	日付
TIME	時刻
TIMESTAMP	日付と時刻
BOOLEAN	論理値。真（’t'）または偽（’f'）