Posted on

How to Install PostgreSQL 9.2 on CentOS 6.3

This quick post shows the basic steps to install PostgreSQL Database Server (PostgreSQL 9.2) on CentOS 6.3. This steps has been tested using CentOS 6.3. However, It may works on other version CentOS or Redhat as well. PostgreSQL is a Sophisticated open-source Object-Relational DBMS supporting almost all SQL constructs, including subselects, transactions, and user-defined types. PostgreSQL is a powerful, open source object-relational database system that available for many platforms including Linux, FreeBSD, Solaris, Microsoft Windows and Mac OS X.

PostgreSQL Global Development Group (PGDG) builds RPMs for various Linux distributions. At the time of this writing, there are available RPMs and SRPMs for the following platforms :

  • PostgreSQL 9.2

Follow these steps to install PostgreSQL 9.2 on CentOS 6.3 :

1. Download the latest production release for your distro here: http://yum.pgrpms.org/repopackages.php

[root@centos63 ~]# wget http://yum.pgrpms.org/9.2/redhat/rhel-6-i386/pgdg-centos92-9.2-5.noarch.rpm
--2012-09-25 21:50:05--  http://yum.pgrpms.org/9.2/redhat/rhel-6-i386/pgdg-centos92-9.2-5.noarch.rpm
Resolving yum.pgrpms.org... 98.129.198.114
Connecting to yum.pgrpms.org|98.129.198.114|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5220 (5.1K) [application/x-redhat-package-manager]
Saving to: âpgdg-centos92-9.2-5.noarch.rpmâ

100%[==========================================================>] 5,220       4.76K/s   in 1.1s

2012-09-25 21:50:06 (4.76 KB/s) - âpgdg-centos92-9.2-5.noarch.rpmâ

2. Install the repo :

[root@centos63 ~]# rpm -ivh pgdg-centos92-9.2-5.noarch.rpm
Preparing...                ########################################### [100%]
   1:pgdg-centos92          ########################################### [100%]

3. Edit the CentOS-Base.repo to exclude postgreql.

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

Add ‘exclude=postgresql*’ to the [base] and [updates] sections:

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

4. Use ‘yum list’ to check the packages that are now available.

[root@centos63 ~]# yum list postgres*
Loaded plugins: fastestmirror, presto, priorities
Loading mirror speeds from cached hostfile
 * base: centos.ipserverone.com
 * extras: centos.ipserverone.com
 * updates: centos.ipserverone.com
Available Packages
postgresql.i686                              8.4.11-1.el6_2                     CentOS6.3-Repository
postgresql-contrib.i686                      8.4.11-1.el6_2                     CentOS6.3-Repository
postgresql-devel.i686                        8.4.11-1.el6_2                     CentOS6.3-Repository
postgresql-docs.i686                         8.4.11-1.el6_2                     CentOS6.3-Repository
postgresql-jdbc.i686                         8.4.701-3.el6                      CentOS6.3-Repository
postgresql-jdbc.noarch                       8.4.701-8.el6                      CentOS6.3-Repository
postgresql-libs.i686                         8.4.11-1.el6_2                     CentOS6.3-Repository
postgresql-odbc.i686                         08.04.0200-1.el6                   CentOS6.3-Repository
postgresql-plperl.i686                       8.4.11-1.el6_2                     CentOS6.3-Repository
postgresql-plpython.i686                     8.4.11-1.el6_2                     CentOS6.3-Repository
postgresql-pltcl.i686                        8.4.11-1.el6_2                     CentOS6.3-Repository
postgresql-server.i686                       8.4.11-1.el6_2                     CentOS6.3-Repository
postgresql-test.i686                         8.4.11-1.el6_2                     CentOS6.3-Repository
postgresql92.i686                            9.2.1-1PGDG.rhel6                  pgdg92
postgresql92-contrib.i686                    9.2.1-1PGDG.rhel6                  pgdg92
postgresql92-debuginfo.i686                  9.2.1-1PGDG.rhel6                  pgdg92
postgresql92-devel.i686                      9.2.1-1PGDG.rhel6                  pgdg92
postgresql92-docs.i686                       9.2.1-1PGDG.rhel6                  pgdg92
postgresql92-libs.i686                       9.2.1-1PGDG.rhel6                  pgdg92
postgresql92-odbc.i686                       09.01.0200-1PGDG.rhel6             pgdg92
postgresql92-odbc-debuginfo.i686             09.01.0200-1PGDG.rhel6             pgdg92
postgresql92-plperl.i686                     9.2.1-1PGDG.rhel6                  pgdg92
postgresql92-plpython.i686                   9.2.1-1PGDG.rhel6                  pgdg92
postgresql92-pltcl.i686                      9.2.1-1PGDG.rhel6                  pgdg92
postgresql92-server.i686                     9.2.1-1PGDG.rhel6                  pgdg92
postgresql92-tcl.i686                        2.0.0-1.rhel6                      pgdg92
postgresql92-tcl-debuginfo.i686              2.0.0-1.rhel6                      pgdg92
postgresql92-test.i686                       9.2.1-1PGDG.rhel6                  pgdg92

5. Start install PostgreSQL 9 using yum:

[root@centos63 ~]# yum install postgresql92 postgresql92-devel postgresql92-server postgresql92-libs postgresql92-contrib -y

6. Verify, Initialize and Start PostgreSQL service :

[root@centos63 ~]# service postgresql-9.2 status
 is stopped
[root@centos63 ~]# service postgresql-9.2 initdb
Initializing database:                                     [  OK  ]
[root@centos63 ~]# service postgresql-9.2 start
Starting postgresql-9.2 service:                           [  OK  ]
Posted on

How to Install phpPgAdmin for Managing PostgreSQL Databases on CentOS 6.2

phpPgAdmin is a web application, written in PHP and web-based administration utility for managing PostgreSQL databases. It handles all the basic functionality as well as some advanced features such as triggers, views and functions. It can create databases, create tables, alter tables and query the data. In this post, i will show how to install phpPgAdmin for PostgreSQL Databases on linux CentOS 6.2 server. Assumed that you have already configured EPEL repository.

[root@centos62 ~]# yum install phpPgAdmin -y
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.averse.net
 * epel: ftp.riken.jp
 * extras: mirror.averse.net
 * updates: mirror.issp.co.th
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package phpPgAdmin.noarch 0:5.0.3-1.el6 will be installed
--> Processing Dependency: php-pgsql >= 4.2 for package: phpPgAdmin-5.0.3-1.el6.noarch
--> Running transaction check
---> Package php-pgsql.i686 0:5.3.3-3.el6_2.6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================
 Package                Arch               Version                        Repository           Size
====================================================================================================
Installing:
 phpPgAdmin             noarch             5.0.3-1.el6                    epel                909 k
Installing for dependencies:
 php-pgsql              i686               5.3.3-3.el6_2.6                updates              67 k

Transaction Summary
====================================================================================================
Install       2 Package(s)

Total download size: 976 k
Installed size: 4.9 M
Downloading Packages:
(1/2): php-pgsql-5.3.3-3.el6_2.6.i686.rpm                                    |  67 kB     00:00
(2/2): phpPgAdmin-5.0.3-1.el6.noarch.rpm                                     | 909 kB     00:10
----------------------------------------------------------------------------------------------------
Total                                                                83 kB/s | 976 kB     00:11
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : php-pgsql-5.3.3-3.el6_2.6.i686                                                   1/2
  Installing : phpPgAdmin-5.0.3-1.el6.noarch                                                    2/2
Non-fatal POSTIN scriptlet failure in rpm package phpPgAdmin-5.0.3-1.el6.noarch
warning: %post(phpPgAdmin-5.0.3-1.el6.noarch) scriptlet failed, exit status 7

Installed:
  phpPgAdmin.noarch 0:5.0.3-1.el6

Dependency Installed:
  php-pgsql.i686 0:5.3.3-3.el6_2.6

Complete!
Posted on

How to Change PostgreSQL Log Format on CentOS 6.2

In this post, i will show the quick step to change log format for PostgreSQL on linux CentOS 6.2 server. PostgreSQL is a Sophisticated open-source Object-Relational DBMS supporting almost all SQL constructs, including subselects, transactions, and user-defined types. PostgreSQL is a powerful, open source object-relational database system that available for many platforms including Linux, FreeBSD, Solaris, Microsoft Windows and Mac OS X. Assumed that you have installed PostgreSQL database server on your CentOS 6.2 server

1. Open the PostgreSQL configuration file :

[root@centos62 ~]# vi /var/lib/pgsql/data/postgresql.conf

2. Go to line 334 and change log format as below :

log_line_prefix = '%t %u %d' 

#log_hostname = off
log_line_prefix = '%t %u %d'            # special values:
                                        #   %u = user name

3. Restart the PostgreSQL database server :

[root@centos62 ~]# /etc/rc.d/init.d/postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]
Posted on

How to Configure PostgreSQL to Listen from Any IP Address on CentOS 6.2

PostgreSQL is a Sophisticated open-source Object-Relational DBMS supporting almost all SQL constructs, including subselects, transactions, and user-defined types. PostgreSQL is a powerful, open source object-relational database system that available for many platforms including Linux, FreeBSD, Solaris, Microsoft Windows and Mac OS X. In this post, i will show the quick steps on how to enable PostgreSQL to listen to all ip address on linux CentOS 6.2 server.

1. Open the PostgreSQL configuration file :

[root@centos62 ~]# vi /var/lib/pgsql/data/postgresql.conf

2. Go to line 59 and uncomment the line. Enable listen from any ip address as below. “*” is for all ip addresses :


#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'                 # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all

3. Restart the PostgreSQL database server :

[root@centos62 ~]# /etc/rc.d/init.d/postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]
Posted on

How to Start, Stop, Restart PostgreSQL Database Server on Linux CentOS 6.2 Server

PostgreSQL is a Sophisticated open-source Object-Relational DBMS supporting almost all SQL constructs, including subselects, transactions, and user-defined types. PostgreSQL is a powerful, open source object-relational database system that available for many platforms including Linux, FreeBSD, Solaris, Microsoft Windows and Mac OS X. In this post, i will show the quick steps on how to start, stop, restart and check the status of PostgreSQL database server on linux CentOS 6.2 server. This steps may working on other version such as CentOS 5.1, CentOS 5.2, CentOS 5.3, CentOS 5.4, CentOS 5.5, CentOS 5.6, CentOS 5.7, CentOS 6.0 and CentOS 6.1.

Start PostgreSQL server :

[root@centos62 ~]# /etc/rc.d/init.d/postgresql start
Starting postgresql service:                               [  OK  ]

Stop PostgreSQL server :

[root@centos62 ~]# /etc/rc.d/init.d/postgresql stop
Stopping postgresql service:                               [  OK  ]

Restart PostgreSQL server :

[root@centos62 ~]# /etc/rc.d/init.d/postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

Check PostgreSQL server status :

[root@centos62 ~]# /etc/rc.d/init.d/postgresql status
postmaster (pid  6129) is running...
Posted on

How to Fix “/var/lib/pgsql/data is missing. Use “service postgresql initdb” to initialize the cluster first” Error On Linux CentOS 6.2 Server

Question :
When i typing service postgresql start as root on linux CentOS 6.2 server, i got the following error message :

[root@centos62 ~]# service postgresql start

/var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first.
                                                           [FAILED]

Answer :

PostgreSQL is a Sophisticated open-source Object-Relational DBMS supporting almost all SQL constructs, including subselects, transactions, and user-defined types. PostgreSQL is a powerful, open source object-relational database system that available for many platforms including Linux, FreeBSD, Solaris, Microsoft Windows and Mac OS X. On Linux CentOS 6.2, you have to type “service postgresql initdb” first before starting postgresql server. Please type the following command :

[root@centos62 ~]# service postgresql initdb
Initializing database:                                     [  OK  ]
Posted on

How to Install PostgreSQL Database Server on Linux CentOS 6.2 Server

PostgreSQL is a Sophisticated open-source Object-Relational DBMS supporting almost all SQL constructs, including subselects, transactions, and user-defined types. PostgreSQL is a powerful, open source object-relational database system that available for many platforms including Linux, FreeBSD, Solaris, Microsoft Windows and Mac OS X. Follow these steps to install PostgreSQL database server on linux CentOS 6.2.

1. Run the following command to install PostgreSQL Server and it’s related package :

[root@centos62 ~]# yum install postgresql-server postgresql -y
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirrors.hostemo.com
 * epel: ftp.riken.jp
 * extras: mirrors.hostemo.com
 * updates: mirrors.hostemo.com
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql.i686 0:8.4.9-1.el6_1.1 will be installed
--> Processing Dependency: postgresql-libs(x86-32) = 8.4.9-1.el6_1.1 for package: postgresql-8.4.9-1.el6_1.1.i686
--> Processing Dependency: libpq.so.5 for package: postgresql-8.4.9-1.el6_1.1.i686
---> Package postgresql-server.i686 0:8.4.9-1.el6_1.1 will be installed
--> Running transaction check
---> Package postgresql-libs.i686 0:8.4.9-1.el6_1.1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================
 Package                   Arch         Version                  Repository                    Size
====================================================================================================
Installing:
 postgresql                i686         8.4.9-1.el6_1.1          CentOS6.2-Repository         2.7 M
 postgresql-server         i686         8.4.9-1.el6_1.1          CentOS6.2-Repository         3.3 M
Installing for dependencies:
 postgresql-libs           i686         8.4.9-1.el6_1.1          CentOS6.2-Repository         201 k

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

Total download size: 6.2 M
Installed size: 28 M
Downloading Packages:
----------------------------------------------------------------------------------------------------
Total                                                                50 MB/s | 6.2 MB     00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : postgresql-libs-8.4.9-1.el6_1.1.i686                                             1/3
  Installing : postgresql-8.4.9-1.el6_1.1.i686                                                  2/3
  Installing : postgresql-server-8.4.9-1.el6_1.1.i686                                           3/3

Installed:
  postgresql.i686 0:8.4.9-1.el6_1.1             postgresql-server.i686 0:8.4.9-1.el6_1.1

Dependency Installed:
  postgresql-libs.i686 0:8.4.9-1.el6_1.1

Complete!

2. Check PostgreSQL status :

[root@centos62 ~]# service postgresql status
postmaster is stopped

3. On Linux CentOS 6.2, you have to initialize PostgreSQL database first before starting postgresql server. Please type the following command :

[root@centos62 ~]# service postgresql initdb
Initializing database:                                     [  OK  ]

4. Start PostgreSQL server :

[root@centos62 ~]# service postgresql start
Starting postgresql service:                               [  OK  ]

Turn on and off whether PostgreSQL starts at boot :

[root@centos62 ~]# chkconfig postgresql on
Posted on

How to Fix “An old version of the database format was found” While Starting PostgreSQL

I would like to share with you on how to fix “An old version of the database format was found” While Starting PostgreSQL server. This error usually happen after we upgrade the postgresql from the lower version to higher version. As an example, we upgrade the postgresql version 8.2 to postgresql version 8.4.1 as below. You will get an error while you starting the postgresql.

Symptoms:
This error will appeared when starting the postgresql service

    [root@server ~]# service postgresql start

An old version of the database format was found.
You need to upgrade the data format before using PostgreSQL.
See /usr/share/doc/postgresql-8.4.1/README.rpm-dist for more information.

Solution:
1. Assume that you have successfully backup the postgresql database.
2. Run this command.

    [root@server ~]# cd /var/lib/pgsql/data
[root@server data]# ls
base    pg_clog      pg_ident.conf  pg_multixact  pg_tblspc    PG_VERSION  postgresql.conf
global  pg_hba.conf  pg_log         pg_subtrans   pg_twophase  pg_xlog     postmaster.opts
[root@server data]# rm -rf *

Reinitialize the database.

    [root@server data]# service postgresql initdb
Initializing database:                                     [  OK  ]

or you can proceed to directly start the database service.

    [root@server data]# service postgresql start
Starting postgresql service:                               [  OK  ]