OS X Mountaion Lion で MacPorts を使って開発環境を整える(postgres編)

まずは、

$ sudo port install postgresql90

とすると終わり際に、

To use the postgresql server, install the postgresql90-server port

と出るので、引き続き、

$ sudo port install postgresql90-server

を実行。終わり際に、

###########################################################
# A startup item has been generated that will aid in
# starting postgresql90-server with launchd. It is disabled
# by default. Execute the following command to start it,
# and to cause it to launch at startup:
#
# sudo port load postgresql90-server
###########################################################

と、

To create a database instance, after install do
 sudo mkdir -p /opt/local/var/db/postgresql90/defaultdb
 sudo chown postgres:postgres /opt/local/var/db/postgresql90/defaultdb
 sudo su postgres -c '/opt/local/lib/postgresql90/bin/initdb -D /opt/local/var/db/postgresql90/defaultdb'

To tweak your DBMS, consider increasing kern.sysv.shmmax by adding an increased kern.sysv.shmmax .. to /etc/sysctl.conf

が出る。
順番にやっていく。

$ sudo port load postgresql90-server
$ sudo mkdir -p /opt/local/var/db/postgresql90/defaultdb
$ sudo chown postgres:postgres /opt/local/var/db/postgresql90/defaultdb
$ sudo su postgres -c '/opt/local/lib/postgresql90/bin/initdb -D /opt/local/var/db/postgresql90/defaultdb'

ここで、だーっと出る。

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale ja_JP.UTF-8.
The default database encoding has accordingly been set to UTF8.
initdb: could not find suitable text search configuration for locale ja_JP.UTF-8
The default text search configuration will be set to "simple".

fixing permissions on existing directory /opt/local/var/db/postgresql90/defaultdb ... ok
creating subdirectories ... ok
selecting default max_connections ... 20
selecting default shared_buffers ... 2400kB
creating configuration files ... ok
creating template1 database in /opt/local/var/db/postgresql90/defaultdb/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

/opt/local/lib/postgresql90/bin/postgres -D /opt/local/var/db/postgresql90/defaultdb
or
/opt/local/lib/postgresql90/bin/pg_ctl -D /opt/local/var/db/postgresql90/defaultdb -l logfile start

最後のやつをやってみる。

$ /opt/local/lib/postgresql90/bin/postgres -D /opt/local/var/db/postgresql90/defaultdb

エラーになる。

postgres cannot access the server configuration file "/opt/local/var/db/postgresql90/defaultdb/postgresql.conf": Permission denied

パーミッションの問題か。
sudo su postgres して再トライ。

$ sudo su postgres
$ /opt/local/lib/postgresql90/bin/postgres -D /opt/local/var/db/postgresql90/defaultdb
LOG:  database system was shut down at 2012-08-11 04:10:00 JST
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections

動いたっぽい。

$ /opt/local/lib/postgresql90/bin/psql -l -U postgres                              List of databases
   Name    |  Owner   | Encoding | Collation | Ctype |   Access privileges  
-----------+----------+----------+-----------+-------+-----------------------
 postgres  | postgres | UTF8     | C         | C     |
 template0 | postgres | UTF8     | C         | C     | =c/postgres          +
           |          |          |           |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C         | C     | =c/postgres          +
           |          |          |           |       | postgres=CTc/postgres
(3 rows)

よさそうだ。
パスを通しておく。

$ vim .profile

export PATH=/opt/local/lib/postgresql90/bin:$PATH

テスト用DBを作っておく。

$ createdb sample_db -U postgres -E UTF-8

psqlして、

$ psql sample_db -U postgres

テーブルを作る。

sample_db=# CREATE TABLE users (
sample_db(# id SERIAL PRIMARY KEY,
sample_db(# name varchar(255)
sample_db(# );
NOTICE:  CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
CREATE TABLE

確認。

sample_db=# \d
              List of relations
 Schema |     Name     |   Type   |  Owner  
--------+--------------+----------+----------
 public | users        | table    | postgres
 public | users_id_seq | sequence | postgres
(2 rows)

sample_db=# \d users
                                 Table "public.users"
 Column |          Type          |                     Modifiers                     
--------+------------------------+----------------------------------------------------
 id     | integer                | not null default nextval('users_id_seq'::regclass)
 name   | character varying(255) |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

データを入れておく。

sample_db=# INSERT INTO users (name) VALUES ('yamada');
INSERT 0 1

セレクトして確認。

sample_db=# select * from users;
 id |  name 
----+--------
  1 | yamada
(1 row)

再びportでインストール

$ sudo port install php5-postgresql

アパッチ再起動

$ sudo /opt/local/apache2/bin/apachectl restart

infophp()で確認。

簡単なphpを書いて接続テスト。

<?php
$conn = pg_pconnect("dbname=sample_db user=postgres");
if (!$conn) {
  echo "An error occured.\n";
  exit;
}

$result = pg_query($conn, "SELECT * FROM users");
if (!$result) {
  echo "An error occured.\n";
  exit;
}

while ($row = pg_fetch_row($result)) {
  echo "Id: $row[0]  Name: $row[1]";
  echo "<br />\n";
}

表示される。

Id: 1 Name: yamada

よさそうだ。