Saturday, October 30, 2010

Install PostgreSQL 9 on CentOS


PLEASE NOTE: I have created an updated and much expanded installation guide for PostgreSQL 9.1 here:

PLEASE USE THE ABOVE GUIDE.


This post will cover installing PostgreSQL 9 on CentOS.
PostgreSQL 9 is the first major release from PostgreSQL in some time and the directory structure has changed. If you are using Webmin, we will also show how to configure Webmin to manage PostgreSQL 9. PLEASE NOTE: If you are looking to install PostgreSQL 9 on cPanel, please see my step-by-step guide here: http://www.davidghedini.com/pg/entry/installing_postgresql_9_on_cpanel: We'll use the simplest method to install, which is the postrgres 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....
[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* 
Now, let's use 'yum list' to check the packages that are now available.
[root@server1 yum.repos.d]# yum list postgres*
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * addons: mirrors.seas.harvard.edu
 * base: centos.mirror.choopa.net
 * extras: centos.mirror.nac.net
 * updates: mirror.net.cen.ct.gov
pgdg90                                                   | 2.8 kB     00:00
pgdg90/primary_db                                        |  57 kB     00:00
Excluding Packages from CentOS-5 - Base
Finished
Excluding Packages from CentOS-5 - Updates
Finished
Installed Packages
postgresql-libs.x86_64                    8.1.18-2.el5_4.1             installed
Available Packages
postgresql-jdbc90.x86_64                  9.0.801-1PGDG.rhel5          pgdg90
postgresql-jdbc90-debuginfo.x86_64        9.0.801-1PGDG.rhel5          pgdg90
postgresql90.x86_64                       9.0.4-1PGDG.rhel5            pgdg90
postgresql90-contrib.x86_64               9.0.4-1PGDG.rhel5            pgdg90
postgresql90-debuginfo.x86_64             9.0.4-1PGDG.rhel5            pgdg90
postgresql90-devel.x86_64                 9.0.4-1PGDG.rhel5            pgdg90
postgresql90-docs.x86_64                  9.0.4-1PGDG.rhel5            pgdg90
postgresql90-jdbc.x86_64                  9.0.801-1PGDG.rhel5          pgdg90
postgresql90-jdbc-debuginfo.x86_64        9.0.801-1PGDG.rhel5          pgdg90
postgresql90-libs.i386                    9.0.4-1PGDG.rhel5            pgdg90
postgresql90-libs.x86_64                  9.0.4-1PGDG.rhel5            pgdg90
postgresql90-odbc.x86_64                  09.00.0200-1PGDG.rhel5       pgdg90
postgresql90-odbc-debuginfo.x86_64        09.00.0200-1PGDG.rhel5       pgdg90
postgresql90-plperl.x86_64                9.0.4-1PGDG.rhel5            pgdg90
postgresql90-plpython.x86_64              9.0.4-1PGDG.rhel5            pgdg90
postgresql90-pltcl.x86_64                 9.0.4-1PGDG.rhel5            pgdg90
postgresql90-python.x86_64                4.0-2PGDG.rhel5              pgdg90
postgresql90-python-debuginfo.x86_64      4.0-2PGDG.rhel5              pgdg90
postgresql90-server.x86_64                9.0.4-1PGDG.rhel5            pgdg90
postgresql90-test.x86_64                  9.0.4-1PGDG.rhel5            pgdg90
postgresql_autodoc.noarch                 1.40-1.rhel5                 pgdg90
[root@server1 yum.repos.d]#
We can now install PostgreSQL 9 using yum: yum install postgresql90 postgresql90-devel postgresql90-server postgresql90-libs
[root@server1 ~]# yum install postgresql90 postgresql90-devel postgresql90-server postgresql90-libs
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * addons: mirrors.seas.harvard.edu
 * base: centos.mirror.choopa.net
 * extras: centos.mirror.nac.net
 * updates: mirror.net.cen.ct.gov
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.x86_64 0:9.0.4-1PGDG.rhel5 set to be updated
--> Processing Dependency: libxslt.so.1()(64bit) for package: postgresql90
---> Package postgresql90-devel.x86_64 0:9.0.4-1PGDG.rhel5 set to be updated
---> Package postgresql90-libs.i386 0:9.0.4-1PGDG.rhel5 set to be updated
--> Processing Dependency: libldap_r-2.3.so.0 for package: postgresql90-libs
---> Package postgresql90-libs.x86_64 0:9.0.4-1PGDG.rhel5 set to be updated
---> Package postgresql90-server.x86_64 0:9.0.4-1PGDG.rhel5 set to be updated
--> Running transaction check
---> Package libxslt.x86_64 0:1.1.17-2.el5_2.2 set to be updated
---> Package openldap.i386 0:2.3.43-12.el5_6.7 set to be updated
--> Processing Dependency: libsasl2.so.2 for package: openldap
---> Package openldap.x86_64 0:2.3.43-12.el5_6.7 set to be updated
--> Running transaction check
---> Package cyrus-sasl-lib.i386 0:2.1.22-5.el5_4.3 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package                  Arch        Version                Repository    Size
================================================================================
Installing:
 postgresql90             x86_64      9.0.4-1PGDG.rhel5      pgdg90       1.4 M
 postgresql90-devel       x86_64      9.0.4-1PGDG.rhel5      pgdg90       1.6 M
 postgresql90-libs        i386        9.0.4-1PGDG.rhel5      pgdg90       220 k
 postgresql90-libs        x86_64      9.0.4-1PGDG.rhel5      pgdg90       218 k
 postgresql90-server      x86_64      9.0.4-1PGDG.rhel5      pgdg90       4.8 M
Installing for dependencies:
 cyrus-sasl-lib           i386        2.1.22-5.el5_4.3       base         127 k
 libxslt                  x86_64      1.1.17-2.el5_2.2       base         488 k
 openldap                 i386        2.3.43-12.el5_6.7      updates      296 k
Updating for dependencies:
 openldap                 x86_64      2.3.43-12.el5_6.7      updates      304 k

Transaction Summary
================================================================================
Install      8 Package(s)
Update       1 Package(s)
Remove       0 Package(s)

Total download size: 9.4 M
Is this ok [y/N]: y
Downloading Packages:
(1/9): cyrus-sasl-lib-2.1.22-5.el5_4.3.i386.rpm          | 127 kB     00:00
(2/9): postgresql90-libs-9.0.4-1PGDG.rhel5.x86_64.rpm    | 218 kB     00:00
(3/9): postgresql90-libs-9.0.4-1PGDG.rhel5.i386.rpm      | 220 kB     00:00
(4/9): openldap-2.3.43-12.el5_6.7.i386.rpm               | 296 kB     00:00
(5/9): openldap-2.3.43-12.el5_6.7.x86_64.rpm             | 304 kB     00:00
(6/9): libxslt-1.1.17-2.el5_2.2.x86_64.rpm               | 488 kB     00:00
(7/9): postgresql90-9.0.4-1PGDG.rhel5.x86_64.rpm         | 1.4 MB     00:01
(8/9): postgresql90-devel-9.0.4-1PGDG.rhel5.x86_64.rpm   | 1.6 MB     00:01
(9/9): postgresql90-server-9.0.4-1PGDG.rhel5.x86_64.rpm  | 4.8 MB     00:03
--------------------------------------------------------------------------------
Total                                           978 kB/s | 9.4 MB     00:09
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Updating       : openldap                                                1/10
  Installing     : postgresql90-libs                                       2/10
  Installing     : libxslt                                                 3/10
  Installing     : postgresql90                                            4/10
  Installing     : cyrus-sasl-lib                                          5/10
  Installing     : postgresql90-server                                     6/10
  Installing     : postgresql90-devel                                      7/10
  Installing     : openldap                                                8/10
  Installing     : postgresql90-libs                                       9/10
  Cleanup        : openldap                                               10/10

Installed:
  postgresql90.x86_64 0:9.0.4-1PGDG.rhel5
  postgresql90-devel.x86_64 0:9.0.4-1PGDG.rhel5
  postgresql90-libs.i386 0:9.0.4-1PGDG.rhel5
  postgresql90-libs.x86_64 0:9.0.4-1PGDG.rhel5
  postgresql90-server.x86_64 0:9.0.4-1PGDG.rhel5

Dependency Installed:
  cyrus-sasl-lib.i386 0:2.1.22-5.el5_4.3    libxslt.x86_64 0:1.1.17-2.el5_2.2
  openldap.i386 0:2.3.43-12.el5_6.7

Dependency Updated:
  openldap.x86_64 0:2.3.43-12.el5_6.7

Complete!
[root@server1 ~]#
We can now initialize Postgre 
*note: when using Webmin, please see 'Configuring Webmin to Manage PostgreSQL9 below):
[root@server1 ~]# service postgresql-9.0 initdb
Start the Postgre server:
[root@server1 ~]# 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 alreay initialized the database, do so now by clicking the initialize database button.
PostgreSQL 9 Hosting PostGIS Hosting 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/

15 comments:

Paul said...

I've got PostgreSQL 8.1.22 running on my CentOS 5.5 box. I'm keen to upgrade to Postgre 9 - should I remove 8.1.22 first (with yum?) or should I try an upgrade? Cheers, Paul.

David Ghedini said...

Hi Paul -

Thanks for your post.

I'm not really qualified (by experience or training) to offer useful advice on migrating across five major releases.

I'm not even sure if you can jump this far without some intermediate step (maybe you can, I just don't know).

If you haven't visited already, I would start with below.

http://www.postgresql.org/docs/current/static/migration.html

neki chan said...

I already Install PostgreSQL 9. But how do i initial the postgres password? Or how to access db postgres ?

David Ghedini said...

Hi Neki -

Thanks for your post.

I'm not sure if I've understood your question properly...

From the command line, you could simply su - postgres and then launch psql:

[root@server1 ~]# su - postgres
-bash-3.2$ psql
psql (9.0.1)
Type "help" for help.

postgres=#

Authentication by default is restrictive, but you can edit your pg_hba.conf file to create an appropriate authentication scenario for your needs.

If you meant something else, such as how to access from a remote host, access from Tomcat, etc.., please let me know.

Jim Salter said...

Thanks for the how-to, David... I'm a Debian guy, so figuring out how to do this was frustrating the heck out of me. =)

David Ghedini said...

Thanks, Jim. I'm glad you found it useful.

Patrick Mast said...

Hi David,

Thanks for your guide.

I get the error message:

Transaction Check Error:
installing package postgresql90-server-9.0.3-1PGDG.rhel5.x86_64 needs 16KB on the / filesystem
installing package postgresql90-9.0.3-1PGDG.rhel5.x86_64 needs 16KB on the / filesystem
installing package postgresql90-libs-9.0.3-1PGDG.rhel5.x86_64 needs 20KB on the / filesystem

There is no room on '/'. However, there is plenty of room on '/usr' and '/var'. Can I change the location of the installation?

David Ghedini said...

Postgres files are installed under /usr and /var by default.

What is the output of df -h?

dave connors said...

david,
thanks for this resource. as an absolute beginner with linux this post was a godsend!!

what would be my default username and password for the postgresql server now installed as above?

David Ghedini said...

Glad you found it useful. There is no default password. The rpm creates a postgres linux user as well as the postgres db role. As root, you should be able to su-postgres and then connect. If you want to set passwords for these users, you can do so as you would any linux or postgres user. (i.e. postgres passwd for linux and ALTER USER for postgres). Be careful as you would need to update your pg_hba.conf accordingly.

Beta said...

I want to install postresql on my CentOS, thanks already to guide how to install.

Raj said...

Hello David/guys,

I didn't understand why we exclude the postgres entries from /etc/yum.repos.d/CentOS-Base.repo ? If you exclude it, then how will you get it?

After excluding, when I issue

yum list postgres*

It says

[root@prod yum.repos.d]# yum list postgres*
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: centosk.centos.org
* extras: centosy3.centos.org
* updates: centosq4.centos.org
Error: No matching Packages to list

Please advise.

raj

Raj said...

Hi David/guys,

Why do we need to exclude postgres from the yum config files?

I excluded and this is what I get when I run

yum list postgres*

[root@prod yum.repos.d]# yum list postgres*
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: centosk.centos.org
* extras: centosy3.centos.org
* updates: centosq4.centos.org
Error: No matching Packages to list

David Ghedini said...

Hi Raj -

The 'exclude=postgresql*'is added to the CentOS-Base.repo in both the Base and Updates sections.

Also, did you actually install the Postgresql repository?

I would also recommend viewing my updated post at http://www.davidghedini.com/pg/entry/install_postgresql_9_on_centos

David

Unknown said...

I had the same issue as Raj following the newer 9.1 guide. properly added the exclude lines, and even loggedout/loggedin but still get the "Error: No matching Packages to list" message.

I was, however, able to install the postgres pacakges. So it seems only an issue with the "list" command. Not sure why.