OpenBSD: PostgreSQL 導入と設定

1. PostgreSQL パッケージのインストール.

$ su
password:
# pkg_add postgresql-server
quirks-2.9 signed on 2014-08-02T11:06:13Z
postgresql-server-9.3.4p0:postgresql-client-9.3.4p0: ok
useradd: Warning: home directory `/var/postgresql' doesn't exist, and -m was not specified
postgresql-server-9.3.4p0: ok
The following new rcscripts were installed: /etc/rc.d/postgresql
See rc.d(8) for details.
Look in /usr/local/share/doc/pkg-readmes for extra documentation.
#

2. ロケールを設定し PostgreSQL の新しいデータベースクラスタを作成する.

# su - _postgresql
$ export LC_CTYPE=ja_JP.UTF-8
$ initdb -D /var/postgresql/data -U postgres -E UTF-8 -A md5 -W
The files belonging to this database system will be owned by user "_postgresql".
This user must also own the server process.

The database cluster will be initialized with locales
  COLLATE:  C
  CTYPE:    ja_JP.UTF-8
  MESSAGES: C
  MONETARY: C
  NUMERIC:  C
  TIME:     C
initdb: could not find suitable text search configuration for locale "ja_JP.UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

creating directory /var/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 40
selecting default shared_buffers ... 128MB
creating configuration files ... ok
creating template1 database in /var/postgresql/data/base/1 ... ok
initializing pg_authid ... ok
Enter new superuser password:
Enter it again:
setting password ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... not supported on this platform
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
syncing data to disk ... ok

Success. You can now start the database server using:

    postgres -D /var/postgresql/data
or
    pg_ctl -D /var/postgresql/data -l logfile start

$ exit

3. PostgreSQL の起動スクリプトと停止スクリプト

# ed /etc/rc.local
/etc/rc.local: No such file or directory
a
if [ -x /usr/local/bin/pg_ctl ]; then
        echo -n ' postgresql'
        su -l _postgresql -c "nohup /usr/local/bin/pg_ctl start \
            -D /var/postgresql/data -l /var/postgresql/logfile \
            -o '-D /var/postgresql/data' >/dev/null"
fi
.
w
227
q
# ed /etc/rc.shutdown
/etc/rc.shutdown: No such file or directory
a
if [ -f /var/postgresql/data/postmaster.pid ]; then
        su -l _postgresql -c "/usr/local/bin/pg_ctl stop -m fast \
            -D /var/postgresql/data"
        rm -f /var/postgresql/data/postmaster.pid
fi
.
w
188
q
#

4. 再起動後, postgresql のロケールを確認

# reboot
(snip)
$ su - _postgresql
Password:
Sorry
$ psql -U postgres -l
Password for user postgres:
                                List of databases
   Name    |  Owner   | Encoding | Collate |    Ctype    |   Access privileges
-----------+----------+----------+---------+-------------+-----------------------
 postgres  | postgres | UTF8     | C       | ja_JP.UTF-8 |
 template0 | postgres | UTF8     | C       | ja_JP.UTF-8 | =c/postgres          +
           |          |          |         |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | ja_JP.UTF-8 | =c/postgres          +
           |          |          |         |             | postgres=CTc/postgres
(3 rows)

$