MacPorts で PostgreSQL 導入

MySQL を入れたら、PostgreSQLが恋しくなった。
使う使わないは置いといて、使えるようにだけしておくことに。

先ずはパッケージを検索。

$ port search postgresql
postgresql7                    databases/postgresql7 7.4.21       The most advanced open-source database available anywhere
postgresql80                   databases/postgresql80 8.0.18       The most advanced open-source database available anywhere
postgresql80-doc               databases/postgresql80-doc 8.0.18       Documentation for the postgresql database
postgresql80-server            databases/postgresql80-server 8.0.18       run postgresql80 as server
postgresql81                   databases/postgresql81 8.1.14       The most advanced open-source database available anywhere
postgresql81-doc               databases/postgresql81-doc 8.1.14       Documentation for the postgresql database
postgresql81-server            databases/postgresql81-server 8.1.14       run postgresql81 as server
postgresql82                   databases/postgresql82 8.2.10       The most advanced open-source database available anywhere
postgresql82-doc               databases/postgresql82-doc 8.2.10       Documentation for the postgresql database
postgresql82-server            databases/postgresql82-server 8.2.10       run postgresql82 as server
postgresql83                   databases/postgresql83 8.3.4        The most advanced open-source database available anywhere.
postgresql83-doc               databases/postgresql83-doc 8.3.4        Documentation for the postgresql database
postgresql83-server            databases/postgresql83-server 8.3.4        run postgresql83 as server
postgresql_autodoc             databases/postgresql_autodoc 1.25         Automatic documentation generator for postgresql databases
caml-postgresql                devel/caml-postgresql 1.8.2        OCaml-interface to the PostgreSQL-database
postgresql-jdbc                java/postgresql-jdbc 8.0-311      PostgreSQL JDBC driver
py-postgresql-exception        python/py-postgresql-exception 0.2          exceptions for the py-postgresql modules
py-postgresql-greentrunk       python/py-postgresql-greentrunk 0.1          greentrunk interface to postgresql
py-postgresql-layout           python/py-postgresql-layout 0.3          layout for the py-postgresql modules
py-postgresql-pqueue           python/py-postgresql-pqueue 0.1          pure python implementation of the pq protocol
py-postgresql-proboscis        python/py-postgresql-proboscis 0.1          postgresql database connector in pure python

MySQLより多い…
postgresql83 を入れれば良い？？postgresql83-server を入れれば良い？？
詳細な情報は info で見れるらしいので確認。

port info postgresql83

$ port info postgresql83
postgresql83 8.3.4, databases/postgresql83 (Variants: universal, python, krb5, perl)
http://www.postgresql.org/

PostgreSQL is Object-Relational Database Management System. It supports almost all SQL constructs, including transactions, subselects, and user-defined types and functions. Write-ahead logging means increased performance while retaining data integrity.

Build Dependencies: bison
Library Dependencies: readline, openssl, zlib, libxml2, libxslt, ossp-uuid
Platforms: darwin
Maintainers: mww@macports.org jwa@macports.org

port info postgresql83-server

$ port info postgresql83-server
postgresql83-server 8.3.4, databases/postgresql83-server (Variants: universal)
http://www.postgresql.org/

run postgresql83 as server

Runtime Dependencies: postgresql83
Platforms: darwin
Maintainers: mww@macports.org jwa@macports.org

なるほど。

postgresql83はクライアント、postgresql83-server がデーモンででした。結局どっちもいれろと。
依存関係も確認。

$ port deps postgresql83
postgresql83 has build dependencies on:
	bison
postgresql83 has library dependencies on:
	readline
	openssl
	zlib
	libxml2
	libxslt
	ossp-uuid
$ port deps postgresql83-server
postgresql83-server has runtime dependencies on:
	postgresql83

クライアントには bison、readline、openssl、zlib、libxml2、libxslt、ossp-uuid が必要。
サーバはクライアントが入ってないと×のようで。

$ port installed | grep "bison\|readline\|openssl\|zlib\|libxml2\|libxslt\|ossp-uuid"
  bison @2.3_2 (active)
  libxml2 @2.7.1_0 (active)
  libxslt @1.1.23_1 (active)
  openssl @0.9.8g_0 (active)
  ossp-uuid @1.6.1_0 (active)
  py25-zlib @2.5.2_0 (active)
  readline @5.2.007_0+darwin_9 (active)
  zlib @1.2.3_1 (active)

幸い全部入ってましたーデフォルトなのかな？
インストールオプションも確認。

$ port variants postgresql83
postgresql83 has the variants:
	universal
	python: add support for python
	krb5: add support for Kerberos 5 authentication
	perl: add Perl support

perl と python は使いそう。krb5？認証方式はよくわからない…MacBookに他のマシンからつなぐ予定は無いからほっとく。
先ずはクライアントをインストール。

$ sudo port install postgresql83 +perl +python
Password:
--->  Fetching m4
--->  Attempting to fetch m4-1.4.11.tar.bz2 from http://ftp.gnu.org/gnu/m4
--->  Verifying checksum(s) for m4
--->  Extracting m4
--->  Configuring m4
--->  Building m4 with target all
--->  Staging m4 into destroot
--->  Installing m4 1.4.11_0
--->  Activating m4 1.4.11_0
--->  Cleaning m4
--->  Fetching bison
--->  Attempting to fetch bison-2.3.tar.bz2 from http://ftp.gnu.org/gnu/bison
--->  Verifying checksum(s) for bison
--->  Extracting bison
--->  Configuring bison
--->  Building bison with target all
--->  Staging bison into destroot
--->  Installing bison 2.3_2
--->  Activating bison 2.3_2
--->  Cleaning bison
--->  Fetching libxml2
--->  Attempting to fetch libxml2-2.7.1.tar.gz from ftp://fr.rpmfind.net/pub/libxml/
--->  Verifying checksum(s) for libxml2
--->  Extracting libxml2
--->  Configuring libxml2
--->  Building libxml2 with target all
--->  Staging libxml2 into destroot
--->  Installing libxml2 2.7.1_0
--->  Activating libxml2 2.7.1_0
--->  Cleaning libxml2
--->  Fetching p5-locale-gettext
--->  Attempting to fetch gettext-1.05.tar.gz from http://ftp.ucr.ac.cr/Unix/CPAN/modules/by-module/Locale
--->  Verifying checksum(s) for p5-locale-gettext
--->  Extracting p5-locale-gettext
--->  Configuring p5-locale-gettext
--->  Building p5-locale-gettext with target all
--->  Staging p5-locale-gettext into destroot
--->  Installing p5-locale-gettext 1.05_0
--->  Activating p5-locale-gettext 1.05_0
--->  Cleaning p5-locale-gettext
--->  Fetching help2man
--->  Attempting to fetch help2man-1.36.4.tar.gz from http://ftp.gnu.org/gnu/help2man
--->  Verifying checksum(s) for help2man
--->  Extracting help2man
--->  Applying patches to help2man
--->  Configuring help2man
--->  Building help2man with target all
--->  Staging help2man into destroot
--->  Installing help2man 1.36.4_1
--->  Activating help2man 1.36.4_1
--->  Cleaning help2man
--->  Fetching autoconf
--->  Attempting to fetch autoconf-2.62.tar.bz2 from http://ftp.gnu.org/gnu/autoconf
--->  Verifying checksum(s) for autoconf
--->  Extracting autoconf
--->  Configuring autoconf
--->  Building autoconf with target all
--->  Staging autoconf into destroot
--->  Installing autoconf 2.62_0
--->  Activating autoconf 2.62_0
--->  Cleaning autoconf
--->  Fetching automake
--->  Attempting to fetch automake-1.10.1.tar.bz2 from http://ftp.gnu.org/gnu/automake
--->  Verifying checksum(s) for automake
--->  Extracting automake
--->  Configuring automake
--->  Building automake with target all
--->  Staging automake into destroot
--->  Installing automake 1.10.1_0
--->  Activating automake 1.10.1_0
--->  Cleaning automake
--->  Fetching libtool
--->  Attempting to fetch libtool-1.5.26.tar.gz from http://ftp.gnu.org/gnu/libtool
--->  Verifying checksum(s) for libtool
--->  Extracting libtool
--->  Applying patches to libtool
--->  Configuring libtool
--->  Building libtool with target all
--->  Staging libtool into destroot
--->  Installing libtool 1.5.26_0
--->  Activating libtool 1.5.26_0
--->  Cleaning libtool
--->  Fetching libxslt
--->  Attempting to fetch libxslt-1.1.23.tar.gz from http://distfiles.macports.org/libxslt
--->  Verifying checksum(s) for libxslt
--->  Extracting libxslt
--->  Configuring libxslt
--->  Building libxslt with target all
--->  Staging libxslt into destroot
--->  Installing libxslt 1.1.23_1
--->  Activating libxslt 1.1.23_1
--->  Cleaning libxslt
--->  Fetching ossp-uuid
--->  Attempting to fetch uuid-1.6.1.tar.gz from ftp://ftp.ossp.org/pkg/lib/uuid/
--->  Verifying checksum(s) for ossp-uuid
--->  Extracting ossp-uuid
--->  Applying patches to ossp-uuid
--->  Configuring ossp-uuid
--->  Building ossp-uuid with target all
--->  Staging ossp-uuid into destroot
--->  Installing ossp-uuid 1.6.1_0
--->  Activating ossp-uuid 1.6.1_0
--->  Cleaning ossp-uuid
--->  Fetching postgresql83
--->  Attempting to fetch postgresql-8.3.4.tar.bz2 from http://ftp3.de.postgresql.org/pub/Mirrors/ftp.postgresql.org/source/v8.3.4/
--->  Verifying checksum(s) for postgresql83
--->  Extracting postgresql83
--->  Configuring postgresql83
--->  Building postgresql83
--->  Staging postgresql83 into destroot
--->  Installing postgresql83 8.3.4_0+perl+python

To use the postgresql server, install the postgresql83-server port
--->  Activating postgresql83 8.3.4_0+perl+python
--->  Cleaning postgresql83

長いな…サーバを使いたければ postgresql83-server も入れてねってことでサーバもインストール。

$ port variants postgresql83-server
postgresql83-server has the variants:
	universal

オプションは特に無い様子なのでそのままインストール。

$ sudo port install postgresql83-server
Password:
--->  Fetching postgresql83-server
--->  Verifying checksum(s) for postgresql83-server
--->  Extracting postgresql83-server
--->  Configuring postgresql83-server
--->  Building postgresql83-server with target all
--->  Staging postgresql83-server into destroot
--->  Creating launchd control script
###########################################################
# A startup item has been generated that will aid in
# starting postgresql83-server with launchd. It is disabled
# by default. Execute the following command to start it,
# and to cause it to launch at startup:
#
# sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql83-server.plist
###########################################################
--->  Installing postgresql83-server 8.3.4_0

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

To tweak your DBMS, consider increasing kern.sysv.shmmax by adding an increased kern.sysv.shmmax .. to /etc/sysctl.conf
--->  Activating postgresql83-server 8.3.4_0
--->  Cleaning postgresql83-server

スタートアップに登録したい場合は
sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql83-server.plist
を実行せよ、とのこと。
使うときだけ起動する予定なのでココはスキップ。

サーバのインストールが終わったら以下でDBインスタンスを作ってねーとのこと。

 sudo mkdir -p /opt/local/var/db/postgresql83/defaultdb
 sudo chown postgres:postgres /opt/local/var/db/postgresql83/defaultdb
 sudo su postgres -c '/opt/local/lib/postgresql83/bin/initdb -D /opt/local/var/db/postgresql83/defaultdb'

起動までの手順はMySQLに軍配かな…コマンド叩くの嫌いじゃないけど。
この通りやると「defaultdb」ってデータベースが出来上がる訳ですね。まぁいいや、このまま実行。

$ sudo mkdir -p /opt/local/var/db/postgresql83/defaultdb
$ sudo chown postgres:postgres /opt/local/var/db/postgresql83/defaultdb
$ sudo su postgres -c '/opt/local/lib/postgresql83/bin/initdb -D /opt/local/var/db/postgresql83/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/postgresql83/defaultdb ... ok
creating subdirectories ... ok
selecting default max_connections ... 20
selecting default shared_buffers/max_fsm_pages ... 2400kB/20000
creating configuration files ... ok
creating template1 database in /opt/local/var/db/postgresql83/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
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/postgresql83/bin/postgres -D /opt/local/var/db/postgresql83/defaultdb
or
    /opt/local/lib/postgresql83/bin/pg_ctl -D /opt/local/var/db/postgresql83/defaultdb -l logfile start

そういやフツーに chown postgres:postgres ってやってるけど、いつの間にユーザ追加されているのか？
そもそも Mac ってどこでユーザ管理してるんだろ？後で調べよう。

さて、initdb が完了。最後に出力されたコマンドでデーモンを起動できるよ、とのこと。

コマンド長い！

えー、環境変数とか登録してくれて構わないのに…
/opt/local/lib/postgresql83/bin にもパスが通っていない。
ちなみに MySQL は /opt/local/lib/mysql5/bin からパスの通ってる /opt/local/bin に一つずつシンボリックリンクが貼られてました。
そのうち改善されるのかな…とおもったら、クライアント(psql)だけは貼られてた。
サーバはわかる人しか使わないからパスを通さないんですね、わかります。と勝手に解釈して、起動と停止に使う postmaster*1とpg_ctl だけシンボリックリンクを貼っておくことに。

$ sudo ln -s /opt/local/lib/postgresql83/bin/postgres /opt/local/bin/
$ sudo ln -s /opt/local/lib/postgresql83/bin/pg_ctl /opt/local/bin/

ところで推奨の起動方法が２個表示するのってどうなのorz
まずは pg_ctl を思い出しておこう。ヘルプの表示が非常に懐かしい…

$ pg_ctl --help
pg_ctl is a utility to start, stop, restart, reload configuration files,
report the status of a PostgreSQL server, or signal a PostgreSQL process.

Usage:
  pg_ctl start   [-w] [-t SECS] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"]
  pg_ctl stop    [-W] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
  pg_ctl restart [-w] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
                 [-o "OPTIONS"]
  pg_ctl reload  [-D DATADIR] [-s]
  pg_ctl status  [-D DATADIR]
  pg_ctl kill    SIGNALNAME PID

Common options:
  -D, --pgdata DATADIR   location of the database storage area
  -s, --silent           only print errors, no informational messages
  -t SECS                seconds to wait when using -w option
  -w                     wait until operation completes
  -W                     do not wait until operation completes
  --help                 show this help, then exit
  --version              output version information, then exit
(The default is to wait for shutdown, but not for start or restart.)

If the -D option is omitted, the environment variable PGDATA is used.

Options for start or restart:
  -l, --log FILENAME     write (or append) server log to FILENAME
  -o OPTIONS             command line options to pass to postgres
                         (PostgreSQL server executable)
  -p PATH-TO-POSTGRES    normally not necessary
  -c, --core-files       allow postgres to produce core files

Options for stop or restart:
  -m SHUTDOWN-MODE   can be "smart", "fast", or "immediate"

Shutdown modes are:
  smart       quit after all clients have disconnected
  fast        quit directly, with proper shutdown
  immediate   quit without complete shutdown; will lead to recovery on restart

Allowed signal names for kill:
  HUP INT QUIT ABRT TERM USR1 USR2

Report bugs to <pgsql-bugs@postgresql.org>.

さぁ、デーモンの起動だ。

$ pg_ctl -D /opt/local/var/db/postgresql83/defaultdb -l logfile start
pg_ctl: could not open PID file "/opt/local/var/db/postgresql83/defaultdb/postmaster.pid": Permission denied

$ sudo pg_ctl -D /opt/local/var/db/postgresql83/defaultdb start
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.

もちろんわざとです。
root 権限では立ち上げられないんだった…

$ sudo ls -l /opt/local/var/db/postgresql83/defaultdb
total 64
-rw-------   1 postgres  postgres      4  1 16 21:41 PG_VERSION
drwx------   5 postgres  postgres    170  1 16 21:41 base
drwx------  30 postgres  postgres   1020  1 16 21:41 global
drwx------   3 postgres  postgres    102  1 16 21:41 pg_clog
-rw-------   1 postgres  postgres   3429  1 16 21:41 pg_hba.conf
-rw-------   1 postgres  postgres   1460  1 16 21:41 pg_ident.conf
drwx------   4 postgres  postgres    136  1 16 21:41 pg_multixact
drwx------   3 postgres  postgres    102  1 16 21:41 pg_subtrans
drwx------   2 postgres  postgres     68  1 16 21:41 pg_tblspc
drwx------   2 postgres  postgres     68  1 16 21:41 pg_twophase
drwx------   4 postgres  postgres    136  1 16 21:41 pg_xlog
-rw-------   1 postgres  postgres  16591  1 16 21:41 postgresql.conf

postgresユーザで作ったから postgres ユーザでデーモンも起動すべきかと思ったけど、パスワード設定してないよね。
というか postgresユーザってどこに居るの？Mac にログインできちゃうの？？
小生、この辺りめっさ弱いな…Linuxならアカウント作っちまえ！ってなっちゃうけど、Darwin はなんか違うのかな？

苦肉の策というかこうするしか無い？su の -c オプションで実行したいシェルを渡す。

$ sudo su postgres -c 'pg_ctl -D /opt/local/var/db/postgresql83/defaultdb start'
server starting
LOG:  database system was shut down at 2009-01-16 21:41:13 JST
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections

動いた。接続してみよう。

$ psql83 -U postgres 
Welcome to psql83 8.3.4, 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 table test(id char(1));
CREATE TABLE
postgres=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | test | table | postgres
(1 row)

postgres=# \d test
        Table "public.test"
 Column |     Type     | Modifiers 
--------+--------------+-----------
 id     | character(1) | 

postgres=# drop table test;
DROP TABLE
postgres=# \d
No relations found.

postgres=# \q

そうだ、この感じだ！！ポスグレかわいいよポスグレ。
CPU喰うと奥様に怒られるのでちゃんと止めておく亭主関白。

$ sudo su postgres -c 'pg_ctl -D /opt/local/var/db/postgresql83/defaultdb stop'
waiting for server to shut down....LOG:  received smart shutdown request
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  database system is shut down
 done
server stopped

コレにて完了。しかしこのコマンドの長さをなんとかしたい…

*1:postgresコマンド