CentOS5.5へPostgreSQL9.0をyumでインストールする

特にチューニングとかはせずにUTF-8で起動するようにします。

# vi /etc/yum.repos.d/CentOS-Base.repo
# wget http://yum.pgsqlrpms.org/reporpms/9.0/pgdg-centos-9.0-2.noarch.rpm
--2010-09-22 00:37:49--  http://yum.pgsqlrpms.org/reporpms/9.0/pgdg-centos-9.0-2.noarch.rpm
yum.pgsqlrpms.org をDNSに問いあわせています... 69.55.231.170
yum.pgsqlrpms.org|69.55.231.170|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 4325 (4.2K) [application/x-redhat-package-manager]
`pgdg-centos-9.0-2.noarch.rpm' に保存中

100%[==============================================================================>] 4,325       --.-K/s 時間 0.1s    

2010-09-22 00:37:49 (34.6 KB/s) - `pgdg-centos-9.0-2.noarch.rpm' へ保存完了 [4325/4325]

# rpm -ivh ./pgdg-centos-9.0-2.noarch.rpm
警告: ./pgdg-centos-9.0-2.noarch.rpm: ヘッダ V3 DSA signature: NOKEY, key ID e8e345b8
準備中...                ########################################### [100%]
   1:pgdg-centos            ########################################### [100%]
# yum update
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * addons: ftp.nara.wide.ad.jp
 * base: ftp.nara.wide.ad.jp
 * extras: ftp.nara.wide.ad.jp
 * updates: centosa5-msync-dvd.centos.org
addons                                                                                           |  951 B     00:00     
base                                                                                             | 2.1 kB     00:00     
extras                                                                                           | 2.1 kB     00:00     
pgdg90                                                                                           | 1.9 kB     00:00     
pgdg90/primary_db                                                                                |  69 kB     00:00     
updates                                                                                          | 1.9 kB     00:00     
updates/primary_db                                                                               | 663 kB     00:13     
Excluding Packages from CentOS-5 - Base
Finished
Excluding Packages from CentOS-5 - Updates
Finished
Setting up Update Process
Resolving Dependencies
--> Running transaction check
---> Package bzip2.x86_64 0:1.0.3-6.el5_5 set to be updated
---> Package bzip2-devel.i386 0:1.0.3-6.el5_5 set to be updated
---> Package bzip2-devel.x86_64 0:1.0.3-6.el5_5 set to be updated
---> Package bzip2-libs.i386 0:1.0.3-6.el5_5 set to be updated
---> Package bzip2-libs.x86_64 0:1.0.3-6.el5_5 set to be updated
--> Processing Dependency: libpq.so.4()(64bit) for package: apr-util
---> Package postgresql-libs.x86_64 0:9.0.0-1PGDG.el5 set to be updated
--> Finished Dependency Resolution
apr-util-1.2.7-11.el5_5.1.x86_64 from installed has depsolving problems
  --> Missing Dependency: libpq.so.4()(64bit) is needed by package apr-util-1.2.7-11.el5_5.1.x86_64 (installed)
Error: Missing Dependency: libpq.so.4()(64bit) is needed by package apr-util-1.2.7-11.el5_5.1.x86_64 (installed)
 You could try using --skip-broken to work around the problem
 You could try running: package-cleanup --problems
                        package-cleanup --dupes
                        rpm -Va --nofiles --nodigest
The program package-cleanup is found in the yum-utils package.
 →Subversionが入っているとError: Missing Dependency: libpq.so.4()(64bit) is needed by package apr-utilが出ます。
# rpm -ev --allmatches subversion
# rpm -ev --allmatches apr-util
# rpm -ev --allmatches postgresql-libs
# yum update
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * addons: ftp.iij.ad.jp
 * base: ftp.iij.ad.jp
 * extras: ftp.iij.ad.jp
 * updates: centosz3-msync-dvd.centos.org
Excluding Packages from CentOS-5 - Base
Finished
Excluding Packages from CentOS-5 - Updates
Finished
Setting up Update Process
Resolving Dependencies
--> Running transaction check
---> Package bzip2.x86_64 0:1.0.3-6.el5_5 set to be updated
---> Package bzip2-devel.i386 0:1.0.3-6.el5_5 set to be updated
---> Package bzip2-devel.x86_64 0:1.0.3-6.el5_5 set to be updated
---> Package bzip2-libs.i386 0:1.0.3-6.el5_5 set to be updated
---> Package bzip2-libs.x86_64 0:1.0.3-6.el5_5 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================
 Package                       Arch                     Version                         Repository                 Size
========================================================================================================================
Updating:
 bzip2                         x86_64                   1.0.3-6.el5_5                   updates                    50 k
 bzip2-devel                   i386                     1.0.3-6.el5_5                   updates                    39 k
 bzip2-devel                   x86_64                   1.0.3-6.el5_5                   updates                    38 k
 bzip2-libs                    i386                     1.0.3-6.el5_5                   updates                    37 k
 bzip2-libs                    x86_64                   1.0.3-6.el5_5                   updates                    35 k

Transaction Summary
========================================================================================================================
Install       0 Package(s)
Upgrade       5 Package(s)

Total download size: 199 k
Is this ok [y/N]: y
Downloading Packages:
(1/5): bzip2-libs-1.0.3-6.el5_5.x86_64.rpm                                                       |  35 kB     00:00     
(2/5): bzip2-libs-1.0.3-6.el5_5.i386.rpm                                                         |  37 kB     00:00     
(3/5): bzip2-devel-1.0.3-6.el5_5.x86_64.rpm                                                      |  38 kB     00:00     
(4/5): bzip2-devel-1.0.3-6.el5_5.i386.rpm                                                        |  39 kB     00:00     
(5/5): bzip2-1.0.3-6.el5_5.x86_64.rpm                                                            |  50 kB     00:00     
------------------------------------------------------------------------------------------------------------------------
Total                                                                                    33 kB/s | 199 kB     00:06     
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Updating       : bzip2-libs                                                                                      1/10 
  Updating       : bzip2                                                                                           2/10 
  Updating       : bzip2-libs                                                                                      3/10 
  Updating       : bzip2-devel                                                                                     4/10 
  Updating       : bzip2-devel                                                                                     5/10 
  Cleanup        : bzip2                                                                                           6/10 
  Cleanup        : bzip2-devel                                                                                     7/10 
  Cleanup        : bzip2-devel                                                                                     8/10 
  Cleanup        : bzip2-libs                                                                                      9/10 
  Cleanup        : bzip2-libs                                                                                     10/10 

Updated:
  bzip2.x86_64 0:1.0.3-6.el5_5          bzip2-devel.i386 0:1.0.3-6.el5_5        bzip2-devel.x86_64 0:1.0.3-6.el5_5      
  bzip2-libs.i386 0:1.0.3-6.el5_5       bzip2-libs.x86_64 0:1.0.3-6.el5_5      

Complete!
# yum install postgresql-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * addons: ftp.iij.ad.jp
 * base: ftp.iij.ad.jp
 * extras: ftp.iij.ad.jp
 * updates: centosz3-msync-dvd.centos.org
Excluding Packages from CentOS-5 - Base
Finished
Excluding Packages from CentOS-5 - Updates
Finished
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql-server.x86_64 0:9.0.0-1PGDG.el5 set to be updated
--> Processing Dependency: postgresql = 9.0.0-1PGDG.el5 for package: postgresql-server
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-server
--> Running transaction check
---> Package postgresql.x86_64 0:9.0.0-1PGDG.el5 set to be updated
---> Package postgresql-libs.x86_64 0:9.0.0-1PGDG.el5 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================
 Package                           Arch                   Version                          Repository              Size
========================================================================================================================
Installing:
 postgresql-server                 x86_64                 9.0.0-1PGDG.el5                  pgdg90                 4.6 M
Installing for dependencies:
 postgresql                        x86_64                 9.0.0-1PGDG.el5                  pgdg90                 1.3 M
 postgresql-libs                   x86_64                 9.0.0-1PGDG.el5                  pgdg90                 207 k

Transaction Summary
========================================================================================================================
Install       3 Package(s)
Upgrade       0 Package(s)

Total download size: 6.1 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): postgresql-libs-9.0.0-1PGDG.el5.x86_64.rpm                                                | 207 kB     00:00     
(2/3): postgresql-9.0.0-1PGDG.el5.x86_64.rpm                                                     | 1.3 MB     00:04     
(3/3): postgresql-server-9.0.0-1PGDG.el5.x86_64.rpm                                              | 4.6 MB     00:25     
------------------------------------------------------------------------------------------------------------------------
Total                                                                                   195 kB/s | 6.1 MB     00:31     
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : postgresql-libs                                                                                  1/3 
  Installing     : postgresql                                                                                       2/3 
  Installing     : postgresql-server                                                                                3/3 

Installed:
  postgresql-server.x86_64 0:9.0.0-1PGDG.el5                                                                            

Dependency Installed:
  postgresql.x86_64 0:9.0.0-1PGDG.el5                      postgresql-libs.x86_64 0:9.0.0-1PGDG.el5                     

Complete!

# chkconfig --list | grep post
postgresql-9.0 	0:off	1:off	2:off	3:off	4:off	5:off	6:off
# chkconfig --level 345 postgresql-9.0 on
# chkconfig --list | grep post
postgresql-9.0 	0:off	1:off	2:off	3:on	4:on	5:on	6:off
# su - postgres
-bash-3.2$ initdb --encoding=UTF8 --no-locale
データベースシステム内のファイルの所有者は"postgres"ユーザでした。
このユーザがサーバプロセスを所有しなければなりません。

データベースクラスタはロケールCで初期化されます。
デフォルトのテキスト検索設定はenglishに設定されました。

ディレクトリ/var/lib/pgsql/9.0/dataの権限を設定しています ... ok
サブディレクトリを作成しています ... ok
デフォルトのmax_connectionsを選択しています ... 100
デフォルトの shared_buffers を選択しています ... 32MB
設定ファイルを作成しています ... ok
/var/lib/pgsql/9.0/data/base/1にtemplate1データベースを作成しています ... ok
pg_authidを初期化しています ... ok
依存関係を初期化しています ... ok
システムビューを作成しています ... ok
システムオブジェクトの定義をロードしています ... ok
変換を作成しています ... ok
ディレクトリを作成しています ... ok
組み込みオブジェクトに権限を設定しています ... ok
情報スキーマを作成しています ... ok
PL/pgSQL サーバサイド言語をロードしています ...ok
template1データベースをバキュームしています ... ok
template1からtemplate0へコピーしています ... ok
template1からpostgresへコピーしています ... ok

警告: ローカル接続向けに"trust"認証が有効です。
pg_hba.confを編集する、もしくは、次回initdbを実行する時に-Aオプショ
ンを使用することで変更することができます。

成功しました。以下を使用してデータベースサーバを起動することができます。

    postmaster -D /var/lib/pgsql/9.0/data
または
    pg_ctl -D /var/lib/pgsql/9.0/data -l logfile start
$ exit
# vi /var/lib/pgsql/9.0/data/pg_hba.conf
# vi /var/lib/pgsql/9.0/data/postgresql.conf
# /etc/init.d/postgresql-9.0 start
postgresql-9.0 サービスを開始中:                           [  OK  ]
# su - postgres
$ psql postgres
psql (9.0.0)
"help" でヘルプを表示します.

postgres=# help
PostgreSQL へのコマンドライン・インターフェース、psql へようこそ。
       \copyright とタイプすると、配布条件を表示します。
       \h とタイプすると、SQL コマンドのヘルプを表示します。
       \? とタイプすると、psql コマンドのヘルプを表示します。
       \g と打つかセミコロンで閉じると、クエリーを実行します。
       \q で終了します。
postgres=# select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig 
----------+----------+-------------+----------+-----------+----------+----------+-----------
 postgres |       10 | t           | t        | t         | ******** |          | 
(1 行)

postgres=# \q
$ exit
#

/etc/yum.repos.d/CentOS-Base.repo

# CentOS-Base.repo
#
# The mirror system uses the connecting IP address of the client and the
# update status of each mirror to pick mirrors that are updated to and
# geographically close to the client.  You should use this for CentOS updates
# unless you are manually picking other mirrors.
#
# If the mirrorlist= does not work for you, as a fall back you can try the 
# remarked out baseurl= line instead.
#
#

[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

# add for PostgreSQL9.0
exclude=postgresql*
 →この2行を追加

#released updates 
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

# add for PostgreSQL9.0
exclude=postgresql*
 →この2行を追加

#packages used/produced in the build but not released
[addons]
name=CentOS-$releasever - Addons
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=addons
#baseurl=http://mirror.centos.org/centos/$releasever/addons/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

#additional packages that may be useful
[extras]
name=CentOS-$releasever - Extras
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=extras
#baseurl=http://mirror.centos.org/centos/$releasever/extras/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

#additional packages that extend functionality of existing packages
[centosplus]
name=CentOS-$releasever - Plus
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=centosplus
#baseurl=http://mirror.centos.org/centos/$releasever/centosplus/$basearch/
gpgcheck=1
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

#contrib - packages by Centos Users
[contrib]
name=CentOS-$releasever - Contrib
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=contrib
#baseurl=http://mirror.centos.org/centos/$releasever/contrib/$basearch/
gpgcheck=1
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

/var/lib/pgsql/9.0/data/postgresql.conf

#listen_addresses = 'localhost'
↓
listen_addresses = '*'

/var/lib/pgsql/9.0/data/pg_hba.conf

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
#host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
#host    all             all             ::1/128                 trust
host     all             all             0.0.0.0/0               md5