PostgreSQLの導入(Debian wheezy)

# apt-get install postgresql
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
  libldap-2.4-2 libpq5 libsasl2-2 libsasl2-modules lsb-release openssl postgresql-9.1 postgresql-client-9.1
  postgresql-client-common postgresql-common ssl-cert
Suggested packages:
  libsasl2-modules-otp libsasl2-modules-ldap libsasl2-modules-sql libsasl2-modules-gssapi-mit
  libsasl2-modules-gssapi-heimdal lsb ca-certificates oidentd ident-server locales-all postgresql-doc-9.1
  openssl-blacklist
The following NEW packages will be installed:
  libldap-2.4-2 libpq5 libsasl2-2 libsasl2-modules lsb-release openssl postgresql postgresql-9.1
  postgresql-client-9.1 postgresql-client-common postgresql-common ssl-cert
0 upgraded, 12 newly installed, 0 to remove and 4 not upgraded.
Need to get 6999 kB of archives.
After this operation, 26.1 MB of additional disk space will be used.
Do you want to continue [Y/n]? y
Get:1 http://http.debian.net/debian/ wheezy/main libsasl2-2 amd64 2.1.25.dfsg1-6+deb7u1 [120 kB]
Get:2 http://http.debian.net/debian/ wheezy/main libldap-2.4-2 amd64 2.4.31-1+nmu2 [243 kB]
Get:3 http://http.debian.net/debian/ wheezy/main libpq5 amd64 9.1.9-1 [527 kB]
Get:4 http://http.debian.net/debian/ wheezy/main libsasl2-modules amd64 2.1.25.dfsg1-6+deb7u1 [116 kB]
Get:5 http://http.debian.net/debian/ wheezy/main lsb-release all 4.1+Debian8+deb7u1 [27.2 kB]
Get:6 http://http.debian.net/debian/ wheezy/main openssl amd64 1.0.1e-2 [699 kB]
Get:7 http://http.debian.net/debian/ wheezy/main postgresql-client-common all 134wheezy3 [62.8 kB]
Get:8 http://http.debian.net/debian/ wheezy/main postgresql-client-9.1 amd64 9.1.9-1 [1385 kB]
Get:9 http://http.debian.net/debian/ wheezy/main ssl-cert all 1.0.32 [19.5 kB]
Get:10 http://http.debian.net/debian/ wheezy/main postgresql-common all 134wheezy3 [138 kB]
Get:11 http://http.debian.net/debian/ wheezy/main postgresql-9.1 amd64 9.1.9-1 [3620 kB]
Get:12 http://http.debian.net/debian/ wheezy/main postgresql all 9.1+134wheezy3 [42.4 kB]
Fetched 6999 kB in 12s (561 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libsasl2-2:amd64.
(Reading database ... 21443 files and directories currently installed.)
Unpacking libsasl2-2:amd64 (from .../libsasl2-2_2.1.25.dfsg1-6+deb7u1_amd64.deb) ...
Selecting previously unselected package libldap-2.4-2:amd64.
Unpacking libldap-2.4-2:amd64 (from .../libldap-2.4-2_2.4.31-1+nmu2_amd64.deb) ...
Selecting previously unselected package libpq5.
Unpacking libpq5 (from .../libpq5_9.1.9-1_amd64.deb) ...
Selecting previously unselected package libsasl2-modules:amd64.
Unpacking libsasl2-modules:amd64 (from .../libsasl2-modules_2.1.25.dfsg1-6+deb7u1_amd64.deb) ...
Selecting previously unselected package lsb-release.
Unpacking lsb-release (from .../lsb-release_4.1+Debian8+deb7u1_all.deb) ...
Selecting previously unselected package openssl.
Unpacking openssl (from .../openssl_1.0.1e-2_amd64.deb) ...
Selecting previously unselected package postgresql-client-common.
Unpacking postgresql-client-common (from .../postgresql-client-common_134wheezy3_all.deb) ...
Selecting previously unselected package postgresql-client-9.1.
Unpacking postgresql-client-9.1 (from .../postgresql-client-9.1_9.1.9-1_amd64.deb) ...
Selecting previously unselected package ssl-cert.
Unpacking ssl-cert (from .../ssl-cert_1.0.32_all.deb) ...
Selecting previously unselected package postgresql-common.
Unpacking postgresql-common (from .../postgresql-common_134wheezy3_all.deb) ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Selecting previously unselected package postgresql-9.1.
Unpacking postgresql-9.1 (from .../postgresql-9.1_9.1.9-1_amd64.deb) ...
Selecting previously unselected package postgresql.
Unpacking postgresql (from .../postgresql_9.1+134wheezy3_all.deb) ...
Processing triggers for man-db ...
Setting up libsasl2-2:amd64 (2.1.25.dfsg1-6+deb7u1) ...
Setting up libldap-2.4-2:amd64 (2.4.31-1+nmu2) ...
Setting up libpq5 (9.1.9-1) ...
Setting up libsasl2-modules:amd64 (2.1.25.dfsg1-6+deb7u1) ...
Setting up lsb-release (4.1+Debian8+deb7u1) ...
Setting up openssl (1.0.1e-2) ...
Setting up postgresql-client-common (134wheezy3) ...
Setting up postgresql-client-9.1 (9.1.9-1) ...
update-alternatives: using /usr/share/postgresql/9.1/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up ssl-cert (1.0.32) ...
Setting up postgresql-common (134wheezy3) ...
supported_versions: WARNING: Unknown Debian release: 7.1
Adding user postgres to group ssl-cert
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Setting up postgresql-9.1 (9.1.9-1) ...
Creating new cluster (configuration: /etc/postgresql/9.1/main, data: /var/lib/postgresql/9.1/main)...
Moving configuration file /var/lib/postgresql/9.1/main/postgresql.conf to /etc/postgresql/9.1/main...
Moving configuration file /var/lib/postgresql/9.1/main/pg_hba.conf to /etc/postgresql/9.1/main...
Moving configuration file /var/lib/postgresql/9.1/main/pg_ident.conf to /etc/postgresql/9.1/main...
Configuring postgresql.conf to use port 5432...
update-alternatives: using /usr/share/postgresql/9.1/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
[ ok ] Starting PostgreSQL 9.1 database server: main.
Setting up postgresql (9.1+134wheezy3) ...

PATHの設定

.bashrcを作成する。

# su postgres
$ cd
$ nano .bashrc

下記を記述する。

#!/bin/bash

PATH="$PATH":/usr/lib/postgresql/9.1/bin
export POSTGRES_HOME=/usr/lib/postgresql/9.1
export PGLIB=$POSTGRES_HOME/lib
export PGDATA=/var/lib/postgresql/9.1/main

.bashrcを反映させる。

$ source ~/.bashrc

インストール時に作成されたDBクラスタを移動する

インストール時に作成されたDBクラスタはエンコードがSQL_ASCIIになっている。
UTF8を使用したいので、ディレクトリ名を変更する。

postgresqlを停止する。

$ exit ←postgresユーザからログアウトしてrootに戻る
# /etc/init.d/postgresql stop

mainディレクトリの名前を変更する

$ su postgres
$ cd /var/lib/postgresql/9.1
$ mv main main-org

空のディレクトリを作成する。

$ mkdir main
$ chmod 700 main

データベースの初期化

$ initdb --encoding=UTF8 --locale=ja_JP.UTF-8
initdb: invalid locale name "ja_JP.UTF-8"
initdb: invalid locale name "ja_JP.UTF-8"
initdb: invalid locale name "ja_JP.UTF-8"
initdb: invalid locale name "ja_JP.UTF-8"
initdb: invalid locale name "ja_JP.UTF-8"
initdb: invalid locale name "ja_JP.UTF-8"
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 C.
The default text search configuration will be set to "english".

fixing permissions on existing directory /var/lib/postgresql/9.1/main ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 24MB
creating configuration files ... ok
creating template1 database in /var/lib/postgresql/9.1/main/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... 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:

    postgres -D /var/lib/postgresql/9.1/main
or
    pg_ctl -D /var/lib/postgresql/9.1/main -l logfile start

postgresユーザからログアウトしてrootに戻る。

$ exit

PostgreSQL(Server)の起動

# /etc/init.d/postgresql start
[....] Starting PostgreSQL 9.1 database server: main[....] The PostgreSQL server failed to start. Please check the log output: 2013-09-21 08:38:45 UTC FATAL: could not load server certificate file "server.crt": No such file or dir[FAILy ... failed!

ここで、上記エラーが出る。
インストール時に作成された、server.keyとserver.crtをmainディレクトリに移動する。

# su postgres
$ cd /var/lib/postgresql/9.1/main-org
$ mv server.* ../main/
$ exit

再度、起動する。

# /etc/init.d/postgresql start
[ ok ] Starting PostgreSQL 9.1 database server: main.