Install PostgreSQL 9 on CentOS

This post will cover installing PostgreSQL 9 on CentOS.

PostgreSQL 9 is the first major release from PostgreSQL in some time and the file structure has changed somewhat.

If you are using Webmin, we will also show how to configure Webmin to manage PostgreSQL 9 in light of the new file structure.

We’ll use the simplest method to install, which is the postrgre repo rpms.

Begin by locating the appropriate one here:

http://yum.pgrpms.org/reporpms/repoview/pgdg-centos.html

There is a 9.1.1 Alpha version available, but I am going to install 9.0.2

For CentOS I will need:

http://yum.pgrpms.org/reporpms/9.0/pgdg-centos-9.0-2.noarch.rpm

So, using wget:

wget http://yum.pgrpms.org/reporpms/9.0/pgdg-centos-9.0-2.noarch.rpm

[root@server1 ~]# wget http://yum.pgrpms.org/reporpms/9.0/pgdg-centos-9.0-2.noarch.rpm
--2010-10-29 15:38:15--  http://yum.pgrpms.org/reporpms/9.0/pgdg-centos-9.0-2.noarch.rpm
Resolving yum.pgrpms.org... 77.79.103.58
Connecting to yum.pgrpms.org|77.79.103.58|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 4623 (4.5K) [application/x-rpm]
Saving to: `pgdg-centos-9.0-2.noarch.rpm'

100%[======================================>] 4,623       --.-K/s   in 0s

2010-10-29 15:38:15 (259 MB/s) - `pgdg-centos-9.0-2.noarch.rpm' saved [4623/4623]

Now install the repo….

view plainprint?

[root@server1 ~]# rpm -i pgdg-centos-9.0-2.noarch.rpm

We now need to edit the CentOS-Base.repo to exclude postgre.

To do, so we simply edit CentOS-Base.repo and add ‘exclude=postgresql*’ to the [base] and [updates] sections:

[root@server1 ~]# cd /etc/yum.repos.d
[root@server1 yum.repos.d]# vi CentOS-Base.repo

[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
exclude=postgresql*

#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
exclude=postgresql*

We can now install PostgreSQL 9 using yum:

yum install postgresql90-server

[root@server1 yum.repos.d]# yum install postgresql90-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * addons: mirror.ash.fastserv.com
 * base: yum.singlehop.com
 * extras: mirrors.netdna.com
 * updates: mirror.cogentco.com
Excluding Packages from CentOS-5 - Base
Finished
Excluding Packages from CentOS-5 - Updates
Finished
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql90-server.x86_64 0:9.0.1-1PGDG.rhel5 set to be updated
--> Processing Dependency: postgresql90 = 9.0.1-1PGDG.rhel5 for package: postgresql90-server
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql90-server
--> Processing Dependency: libxslt.so.1()(64bit) for package: postgresql90-server
--> Running transaction check
---> Package libxslt.x86_64 0:1.1.17-2.el5_2.2 set to be updated
---> Package postgresql90.x86_64 0:9.0.1-1PGDG.rhel5 set to be updated
---> Package postgresql90-libs.x86_64 0:9.0.1-1PGDG.rhel5 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package                  Arch        Version                 Repository   Size
================================================================================
Installing:
 postgresql90-server      x86_64      9.0.1-1PGDG.rhel5       pgdg90      4.6 M
Installing for dependencies:
 libxslt                  x86_64      1.1.17-2.el5_2.2        base        488 k
 postgresql90             x86_64      9.0.1-1PGDG.rhel5       pgdg90      1.3 M
 postgresql90-libs        x86_64      9.0.1-1PGDG.rhel5       pgdg90      209 k

Transaction Summary
================================================================================
Install      4 Package(s)
Update       0 Package(s)
Remove       0 Package(s)

Total download size: 6.5 M
Is this ok [y/N]: y
Downloading Packages:
(1/4): postgresql90-libs-9.0.1-1PGDG.rhel5.x86_64.rpm    | 209 kB     00:01
(2/4): libxslt-1.1.17-2.el5_2.2.x86_64.rpm               | 488 kB     00:00
(3/4): postgresql90-9.0.1-1PGDG.rhel5.x86_64.rpm         | 1.3 MB     00:02
(4/4): postgresql90-server-9.0.1-1PGDG.rhel5.x86_64.rpm  | 4.6 MB     00:07
--------------------------------------------------------------------------------
Total                                           535 kB/s | 6.5 MB     00:12
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : postgresql90-libs                                        1/4
  Installing     : libxslt                                                  2/4
  Installing     : postgresql90                                             3/4
  Installing     : postgresql90-server                                      4/4

Installed:
  postgresql90-server.x86_64 0:9.0.1-1PGDG.rhel5

Dependency Installed:
  libxslt.x86_64 0:1.1.17-2.el5_2.2
  postgresql90.x86_64 0:9.0.1-1PGDG.rhel5
  postgresql90-libs.x86_64 0:9.0.1-1PGDG.rhel5

Complete!
[root@server1 yum.repos.d]#

We can now initialize Postgre

*note: when using Webmin, please see ‘Configuring Webmin to Manage PostgreSQL9 below):

[root@server1 yum.repos.d]# service postgresql-9.0 initdb

Start the Postgre server:

[root@server1 yum.repos.d]# service postgresql-9.0 start

Configuring Webmin to Manage PostegreSQL 9

Due to the directory structure of PostgreSQL 9, you will need to make a few changes to the Webmin management interface it let Webmin know where the Postgre files are located.

Under Servers>PostgreSQL Database Server

Click on Module Configuration.

Make the following substitutions in the System Configuration Section:

1. Path to psql command:

Original:  /usr/bin/psql
Change to: /usr/pgsql-9.0/bin/psql

2. Command to start PostgreSQL

Original: if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb start; else /etc/rc.d/init.d/postgresql start; fi

Change to:  if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb start; else /etc/rc.d/init.d/postgresql-9.0 start; fi

3. Command to stop PostgreSQL

Original: if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb stop; else /etc/rc.d/init.d/postgresql stop; fi
Change to:  if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb stop; else /etc/rc.d/init.d/postgresql-9.0 stop; fi

4. Command to initialize PostgreSQL

Original: if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb start; else /etc/rc.d/init.d/postgresql initdb ; /etc/rc.d/init.d/postgresql start; fi
Change to:  if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb start; else /etc/rc.d/init.d/postgresql-9.0 initdb ; /etc/rc.d/init.d/postgresql-9.0 start; fi

5. Path to postmaster PID file

Original: /var/run/postmaster.pid
Change to: /var/run/postmaster-9.0.pid

6. Paths to host access config file

Original: /var/lib/pgsql/data/pg_hba.conf
Change to: /var/lib/pgsql/9.0/data/pg_hba.conf

7. Default backup repository directory

Original: /home/db_repository
Change to: /var/lib/pgsql/9.0/backups

Save the configuration.

If you have not already initialized the database, do so now by clicking the initialize database button.
Additional information and references:

http://people.planetpostgresql.org/devrim/index.php?/archives/43-How-to-install-PostgreSQL-9.0-Beta-1-to-FedoraCentOSRHEL.html

http://www.postgresonline.com/journal/index.php?/archives/45-An-Almost-Idiots-Guide-to-PostgreSQL-YUM.html

http://www.postgresql.org/

D. Ghedini