PostgreSQLの使い方メモ
LastUpdate : 12/08/05
Update : 08/04/12
CentOS5.1(64Bit版)にPostgreSQLをインストールし、DBサーバとして使うための、基本的な使い方までのメモです。
メモといいつつ、ただの、作業履歴です（ぉ
yumを使ってインストールを行います
| [root@postgres_server ~]# yum install postgresql-server |
ついでに、立ち上がったときにpostgresが自動的に起動するようにしておきます。
| [root@postgres_server ~]# chkconfig --list postgresql postgresql 0:off 1:off 2:off 3:off 4:off 5:off 6:off [root@postgres_server ~]# chkconfig --level 3 postgresql on [root@postgres_server ~]# chkconfig --list postgresql postgresql 0:off 1:off 2:off 3:on 4:off 5:off 6:off [root@postgres_server ~]# |
デフォルトではオフになってるので、ONにしておきます（私の場合、ランレベルは常に３なので、３のみONにしています。ランレベル５（自動でXが立ち上がる場合）の場合、５をONにしておかないと自動的に立ち上がりません。
yum でインストールを行うと、postgresを実行するユーザアカウント「postgres」が自動で追加されます。
念のためパスワードを設定しておきます(linuxのpasswdコマンド)
日本PostgreSQLユーザ会 → http://www.postgresql.jp/
↑の中にある、オンラインマニュアル（日本語） → http://www.postgresql.jp/document/
オンラインマニュアルは丁寧に書かれているので、大方これでなんとかなりそうな感じです。
というか、これを読めば一通り大丈夫だと思います（＾＾；
インストール直後、行わなければならないことは、データベースのデータを格納する場所の初期化です。
「 initdb -D /var/lib/pgsql/data 」とかで初期化しなければならなかったような気がしますが、どうも、yum なんかでインストールすると、違う感じ？
マニュアル（「 PostgreSQL 8.1.1文書 の １６．２データベースクラスタの作成 」 [ http://www.postgresql.jp/document/pg811doc/html/creating-cluster.html ]）には
＞ 初期化が終わると、データベースクラスタにはpostgresという名前のデータベースが含まれています。
だとか
＞ データディレクトリが既に初期化されているように見える場合は、initdbは実行を拒否します。
とかマニュアルにあります。
で、データベース一覧を表示させてみると・・・
| [root@postgres_server pgsql]# su - postgres -bash-3.1$ psql -l List of databases Name | Owner | Encoding -----------+----------+---------- postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 (3 rows) -bash-3.1$ |
とか、表示されています（postgres というデータベースが存在している）。
初期化が完了していると、判断していいのでしょうか？（涙
まぁ、問題があれば、やり直せばいいだけだし・・・ね？ｔｔ
なんというか、たぶん、初回起動時に、postgresというインスタンスがつくられているよーな気がします。
データベースクラスタのパスを変更したい場合とかの場合は、initdbで初期化しなおす必要がある気がします（ ← 変更したいと思ったことがないため、やったことがありませんｔｔ
※12/08/05 追記
CentOS6.3では上記手順ではうまくいかなかった。
yum install postgresql でインストール後以下のコマンドを実行
service postgresql initdb
その後、service postgresql startでPostgreSQLの起動を行う。
自分用のデータベースを作成します。
postgresが立ち上がっている必要がありますので、注意してください（インストール直後は起動していないため、service postgresql start などのコマンドで起動させてください。）。
コマンドラインより以下のプログラムを呼び出し、データベースを作成してもらいます。
テスト用に「 testdb 」というデータベースを作成してみます。
| [root@postgres_server ~]# su - postgres -bash-3.1$ createdb testdb ← testdbというデータベースを作成 CREATE DATABASE -bash-3.1$ |
このコマンドが完了したならば、今度はデータベースの一覧を表示させます
| -bash-3.1$ psql -l ← データベースの一覧を表示させる List of databases Name | Owner | Encoding -----------+----------+---------- postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 testdb | postgres | UTF8 ← 先ほど作成したデータベースが追加されている (4 rows) -bash-3.1$ |
今、作成したデータベース、testdbを削除する。
| -bash-3.1$ dropdb testdb ←データベースを削除する DROP DATABASE -bash-3.1$ psql -l ←データベースの一覧を表示させる List of databases Name | Owner | Encoding -----------+----------+---------- postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 (3 rows) -bash-3.1$ |
「 testdb 」という名前のデータベースを作成し、テーブルを挿入し、挿入したレコードを表示させています。
| -bash-3.1$ createdb testdb ←testdbというデータベースの作成 CREATE DATABASE -bash-3.1$ psql -d testdb ←psqlでtestdbへ接続（-dで接続するデータベースを指定する） Welcome to psql 8.1.11, 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 testdb=# \d ←テーブルの一覧を表示させる No relations found. ←データベースを作成した直後のため何もテーブルは存在しない testdb=# create table TicketTBL( ←テーブルの作成 testdb(# TicketNo integer primary key, testdb(# comment text, testdb(# updated timestamp); ←psqlにおいてコマンドの実行は「\g」ですが「;」でもOKみたいです NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tickettbl_pkey" for table "tickettbl" CREATE TABLE testdb=# insert into TicketTBL(TicketNo,comment,updated) ←レコードの挿入 testdb-# VALUES( 1 , 'こめんとですよーーーーーｗｗｗ' , NOW() ); INSERT 0 1 testdb=# select * from TicketTBL ←先ほど挿入したレコードを表示 testdb-# ; ticketno | comment | updated ----------+--------------------------------+---------------------------- 1 | こめんとですよーーーーーｗｗｗ | 2008-04-12 23:24:38.268346 (1 row) testdb=# \d TicketTBL ←先ほど作成したテーブルの情報を表示させてみる Table "public.tickettbl" Column | Type | Modifiers ----------+-----------------------------+----------- ticketno | integer | not null comment | text | updated | timestamp without time zone | Indexes: "tickettbl_pkey" PRIMARY KEY, btree (ticketno) testdb=# |
インストールした状態だと、外部から接続できないようになっています。
外部から接続できるようにするため、設定ファイルを編集します。
postgresql.conf ファイルを編集します（ /var/lib/pgsql/data/postgresql.conf ）
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
|
となっているところがあるかと思います（vi なら/listen_addressesと打ち込んで検索すれば、２回目ぐらいで発見できます）
これを、コメントにもあるように、「 listen_address = '*' 」に変更しますし、保存します。
| listen_address = '*' |
次に、pg_hba.conf ファイルを編集します（ /var/lib/pgsql/data/pg_hba.conf ）
# TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: host all all 127.0.0.1/32 ident sameuser # IPv6 local connections: host all all ::1/128 ident sameuser #ALC_GROUP Lan access host all all 192.168.118.0/24 md5 |
長いコメントの最後の数行にアクセス設定が書いてあります。最後の２行が追加したところです。
パスワードで認証をさせ、なおかつ、192.168.118.0のアドレス以下からしかアクセスさせない、という設定になっています。
PostgreSQLの中で、postgresというユーザが存在します（linuxのプロセスを実行させるためのユーザーではない）。
このpostgresにパスワードを未設定のため、設定を行います。
| [root@postgres_server ~]# su - postgres -bash-3.1$ psql -d testdb Welcome to psql 8.1.11, 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 testdb=# alter user postgres with password 'nyannyan'; ←postgres(ユーザ名)にnyannyanというパスワードを設定 ALTER ROLE testdb=# \q -bash-3.1$ |
設定ファイルの編集が終わったら、保存し、PostgreSQLを再起動させる。
とりあえず、外部からの接続ということで、Windows機から接続をしてみました（Windows機に、たまたまPostgreSQLがはいっていたため、psqlから問い合わせをしてみました）
| C:\Program Files\PostgreSQL\8.3\bin>psql -d testdb -h 192.168.118.115
-p 5432 -U postgres ←PostgreSQLを動かしているサーバに接続（コマンドの意味は下参照） Password for user postgres: ←ここで、先ほど決めたパスワードを入力 Welcome to psql 8.3.0 (server 8.1.11), 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 WARNING: You are connected to a server with major version 8.1, but your psql client is major version 8.3. Some backslash commands, such as \d, might not work properly. testdb=# select * from tickettbl ← 適当なテーブルから、データをselectしてみました testdb-# ; ticketno | comment | updated ----------+--------------------------------+---------------------------- 1 | こめんとですよーーーーーｗｗｗ | 2008-04-12 23:24:38.268346 (1 row) testdb=# |
サーバーとクライアントで使っているPostgreSQLのバージョンが異なるため、ワーニングがでていますが、とりあえず、使うことができました。
サーバーの設定においては、ファイヤーオールの設定、そして、上にも書きましたが設定ファイルの編集は忘れないでください。
※psqlのオプション
-d データベース名前
-h リモートホストアドレス
-p 使用するポート番号
-U 使用するユーザー名
そして、以下のように使用します。
psql -d testDB -h 192.168.118.115 -p 5432 -U postgres
以下の内容はほんとーに、これでよいのか、考え中ですので、注意してください。
PostgreSQLのコマンドに、「 initdb 」というものがあります。これを使いインスタンスの追加を行います。
（１）インスタンス管理者用としてアカウントを作成
インスタンスごとに、管理者ユーザを作る必要があるのかどうかは不明ですが、とりあえず作ってみます。
とりあえず、普通にここでは、「 adduser postgres2 」とし、postgres2というユーザを追加しました（念のため、パスワードも設定しておきました）。
（２）データベースを格納するディレクトリを作成
[root@postgres_server ~]# mkdir -p /var/postgresdb/test [root@postgres_server ~]# chown postgres2:postgres2 /var/postgresdb/test [root@postgres_server ~]# chmod 775 /var/postgresdb/test |
こんな感じで、ディレクトリを作成し、さらに、所有権・パーミッションも変更しておきます。
（３）インスタンスを作成
initdbを行う際、ロケールを指定することができますが、この点はなんだか揉めてるみたい（？）。
まぁ、現状ではとりあえず、
[postgres2@postgres_server ~]$ initdb --no-locale --encoding=EUC_JP --username=postgres2 --pwprompt --pgdata /var/postgresdb/test |
こんな感じで指定します。LIKEでの検索などに問題がでてくるらしいのでロケールの設定には「 --no-locale 」を必ず指定します。
--encodingにて、エンコーディングの指定をし、
--pgdataにて、データベースのデータをどこに配置するか（（２）で作成したディレクトリを指定）を指定します。
その他、オプションもありますので、マニュアルを参照してください。
（４）インスタンスの起動
initdbコマンドが正常に終了すると「 postmaster -D /var/postgresdb/test 」でインスタンスを起動してね、みたいなメッセージが表示されます。
すでに、インスタンスが起動している場合、同じポートで、インスタンスが複数待ちうけというのは、おかしな話なので、ポート番号を変更し、インスタンスを起動させます。
データベースの保存ディレクトリとして（３）にて、/var/postgresdb/test を指定しました。
ここに、設定ファイルなども、作成されるので、その中の「 postgresql.conf 」ファイルの編集を行います。
| postgresql.conf |
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
port = 50000
|
大体、５０行目あたりに、外部からの接続を受け入れるかどうか、や、ポート番号の設定の項目があります。
#でコメントアウトされていたら、#をはずじ、ポート番号などを設定します（ここでは、50000に設定）
そして、ファイヤーオールの設定ももちろん行います（50000のポートを外部から接続受け入れ）
そして、インスタンスの起動を行います。
[postgres2@postgres_server ~]$ pg_ctl -o "-p 50000" -D /var/postgresdb/test -w start |
pg_ctlというpostgreSQLのデータベースの起動・停止の管理用プログラムを使い、起動させてみます。 -wオプションをつけ、データベースが起動するまで、制御が帰ってこないようにしています（pg_ctlを実行しているのは、（１）で作成したpostgres2です。この辺の管理者ユーザが必要なのか、そしてそれの指定方法などいまいちわかってないので、あやしいです）。
・・・これで、たぶん、うまく動く・・・はず。