David Ghedini

Linux, Java, Oracle, and PostgreSQL


David Ghedini

Tuesday Mar 01, 2011

Install PostgreSQL 9 on CentOS

This post will cover installing and basic configuration of PostgreSQL 9.x on CentOS.

We will install PostgreSQL 9 using the PostgreSQL repository and yum.

The same procedure can be used to install PostgreSQL 9 on Red Hat and Fedora using the appropriate rpm.

Optionally, we'll also see how to install PostGIS.

As the directory structure of PostgreSQL has changed with the release of PostgreSQL 9, we will also look a look at how we can create symlinks to make life easier when installing software or modules that still expect the old directory structure.

Finally, for Webmin users, we will see how to configuring Webmin to manage PostgreSQL 9.

I am using CentOS 6, but the same procedure works for CentOS 5. Finally, if you are using Webmin, we will also show how to configure Webmin to manage PostgreSQL 9.

With the release of PostgreSQL 9, the directory structure of PostgreSQL has changed.

We will also creating symlinks (if needed) from the new PostgreSQL 9 file locations to the previous PostgreSQL 8 file locations.

If you are looking trying to install PostgreSQL 9 on cPanel, please see my post here.

We'll use the simplest method to install, which is the postrgres repo rpms.


1. Download and Install the PostgreSQL Repository



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

The repo rpms are 32 and 64 bit specific.

Since I am installing on CentOS 6 x64, I will need:

http://yum.pgrpms.org/9.1/redhat/rhel-5-x86_64/pgdg-centos91-9.1-4.noarch.rpm

So, using wget:

wget http://yum.pgrpms.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm

[root@server1 ~]# wget http://yum.pgrpms.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm
--2011-11-01 00:11:50--  http://yum.pgrpms.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.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: 5124 (5.0K) [application/x-redhat-package-manager]
Saving to: pgdg-centos91-9.1-4.noarch.rpm

100%[======================================>] 5,124       --.-K/s   in 0s

2011-11-01 00:11:51 (310 MB/s) - pgdg-centos91-9.1-4.noarch.rpm

[root@server1 ~]# 


Now, install the repo....

[root@server1 ~]# rpm -i pgdg-centos91-9.1-4.noarch.rpm


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

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


[root@server1 yum.repos.d]# vi CentOS-Base.repo
# 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-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*


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
base                                                     | 3.7 kB     00:00
base/primary_db                                          | 4.2 MB     00:09
extras                                                   | 3.0 kB     00:00
extras/primary_db                                        | 1.9 kB     00:00
pgdg91                                                   | 2.8 kB     00:00
pgdg91/primary_db                                        |  79 kB     00:00
updates                                                  | 3.5 kB     00:00
updates/primary_db                                       | 3.3 MB     00:00
vz-base                                                  |  951 B     00:00
vz-base/primary                                          | 1.3 kB     00:00
vz-base                                                                     3/3
vz-updates                                               |  951 B     00:00
vz-updates/primary                                       |  157 B     00:00
Available Packages
postgresql91.x86_64                         9.1.1-1PGDG.rhel6             pgdg91
postgresql91-contrib.x86_64                 9.1.1-1PGDG.rhel6             pgdg91
postgresql91-debuginfo.x86_64               9.1.1-1PGDG.rhel6             pgdg91
postgresql91-devel.i686                     9.1.1-1PGDG.rhel6             pgdg91
postgresql91-devel.x86_64                   9.1.1-1PGDG.rhel6             pgdg91
postgresql91-docs.x86_64                    9.1.1-1PGDG.rhel6             pgdg91
postgresql91-jdbc.x86_64                    9.1.901-1PGDG.rhel6           pgdg91
postgresql91-jdbc-debuginfo.x86_64          9.1.901-1PGDG.rhel6           pgdg91
postgresql91-libs.i686                      9.1.1-1PGDG.rhel6             pgdg91
postgresql91-libs.x86_64                    9.1.1-1PGDG.rhel6             pgdg91
postgresql91-odbc.x86_64                    09.00.0200-1PGDG.rhel6        pgdg91
postgresql91-odbc-debuginfo.x86_64          09.00.0200-1PGDG.rhel6        pgdg91
postgresql91-plperl.x86_64                  9.1.1-1PGDG.rhel6             pgdg91
postgresql91-plpython.x86_64                9.1.1-1PGDG.rhel6             pgdg91
postgresql91-pltcl.x86_64                   9.1.1-1PGDG.rhel6             pgdg91
postgresql91-python.x86_64                  4.0-2PGDG.rhel6               pgdg91
postgresql91-python-debuginfo.x86_64        4.0-2PGDG.rhel6               pgdg91
postgresql91-server.x86_64                  9.1.1-1PGDG.rhel6             pgdg91
postgresql91-tcl.x86_64                     1.9.0-1.rhel6                 pgdg91
postgresql91-tcl-debuginfo.x86_64           1.9.0-1.rhel6                 pgdg91
postgresql91-test.x86_64                    9.1.1-1PGDG.rhel6             pgdg91
postgresql_autodoc.noarch                   1.40-1.rhel6                  pgdg91
[root@server1 yum.repos.d]#



2. Install PostgreSQL 9.1 Using Yum



We can now install PostgreSQL 9 using yum:

yum install postgresql91 postgresql91-devel postgresql91-server postgresql91-libs postgresql91-contrib
[root@server1 yum.repos.d]# yum install postgresql91 postgresql91-devel postgresql91-server postgresql91-libs postgresql91-contrib
Loaded plugins: fastestmirror
Determining fastest mirrors
 * base: mirror.us.leaseweb.net
 * extras: mirror.lug.udel.edu
 * updates: centos.mirror.choopa.net
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql91.x86_64 0:9.1.1-1PGDG.rhel6 set to be updated
---> Package postgresql91-devel.x86_64 0:9.1.1-1PGDG.rhel6 set to be updated
---> Package postgresql91-libs.x86_64 0:9.1.1-1PGDG.rhel6 set to be updated
---> Package postgresql91-server.x86_64 0:9.1.1-1PGDG.rhel6 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package                  Arch        Version                 Repository   Size
================================================================================
Installing:
 postgresql91             x86_64      9.1.1-1PGDG.rhel6       pgdg91      939 k
 postgresql91-devel       x86_64      9.1.1-1PGDG.rhel6       pgdg91      1.4 M
 postgresql91-libs        x86_64      9.1.1-1PGDG.rhel6       pgdg91      186 k
 postgresql91-server      x86_64      9.1.1-1PGDG.rhel6       pgdg91      3.4 M

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

Total download size: 5.9 M
Installed size: 25 M
Is this ok [y/N]: y
Downloading Packages:
(1/4): postgresql91-9.1.1-1PGDG.rhel6.x86_64.rpm         | 939 kB     00:02
(2/4): postgresql91-devel-9.1.1-1PGDG.rhel6.x86_64.rpm   | 1.4 MB     00:01
(3/4): postgresql91-libs-9.1.1-1PGDG.rhel6.x86_64.rpm    | 186 kB     00:00
(4/4): postgresql91-server-9.1.1-1PGDG.rhel6.x86_64.rpm  | 3.4 MB     00:02
--------------------------------------------------------------------------------
Total                                           800 kB/s | 5.9 MB     00:07
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : postgresql91-libs-9.1.1-1PGDG.rhel6.x86_64               1/4
  Installing     : postgresql91-9.1.1-1PGDG.rhel6.x86_64                    2/4
  Installing     : postgresql91-server-9.1.1-1PGDG.rhel6.x86_64             3/4
  Installing     : postgresql91-devel-9.1.1-1PGDG.rhel6.x86_64              4/4

Installed:
  postgresql91.x86_64 0:9.1.1-1PGDG.rhel6
  postgresql91-devel.x86_64 0:9.1.1-1PGDG.rhel6
  postgresql91-libs.x86_64 0:9.1.1-1PGDG.rhel6
  postgresql91-server.x86_64 0:9.1.1-1PGDG.rhel6

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





3. Initialize and Start PostgreSQL 9.1



We can now initialize and Start PostgreSQL

NOTE: when using Webmin, please see 'Configuring Webmin to Manage PostgreSQL9 below:

[root@server1 yum.repos.d]# service postgresql-9.1 initdb
Initializing database:                                     [  OK  ]
[root@server1 yum.repos.d]#


Start the PostgreSQL server:

[root@server1 yum.repos.d]# service postgresql-9.1 start
Starting postgresql-9.1 service:                           [  OK  ]
[root@server1 yum.repos.d]#


If you encounter startup errors, check under /var/lib/pgsql/9.1/data/pg_log for clues.


4. Set PostgreSQL 9 Environment



The deault home directory for the user postgres is at /var/lib/pgsql

The bash_profile for the user postgres will look like this:

[ -f /etc/profile ] && source /etc/profile
PGDATA=/var/lib/pgsql/9.1/data
export PGDATA


This contains a path for the data directory, but no path for the executable/binary directory. To ammend this, add the path as below:

[ -f /etc/profile ] && source /etc/profile
PGDATA=/var/lib/pgsql/9.1/data
export PGDATA
PATH=$PATH:$HOME/bin:/usr/pgsql-9.1/bin
export PATH


Placing the binary directory in the path for postgres will allow you to invoke pg_ctl and other commands from the shell.


5. Set postgres Password



The superuser postgres has no password set by default.

To set the password, switch to postgres user:

[root@server1 yum.repos.d]# su - postgres


Connect as postgres to the postgres database and set the password for user postgres using alter user as below:

-bash-4.1$ psql postgres postgres
psql (9.1.1)
Type "help" for help.

postgres=# alter user postgres with password 'postgres';
ALTER ROLE
postgres=#



6. Configure PostgreSQL 9 pg_hba.conf File



Locate your pg_hba.conf file under /var/lib/pgsql/9.1/data

On installation, your pg_hba.conf file will look like this:

[root@server1 yum.repos.d]# vi /var/lib/pgsql/9.1/data/pg_hba.conf
# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.



# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident


Change the METHOD to md5 as shown below:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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


In order for the change to take effect, reload the pg_hba.conf file.

As with any command, there are several ways you can reload the pg_hba.conf file.

Method 1: From the shell using pg_ctl reload:

[root@server1 yum.repos.d]# su - postgres
-bash-4.1$ pg_ctl reload
server signaled
-bash-4.1$


Method 2: From psql using pg_reload_conf();

-bash-4.1$ psql postgres postgres
psql (9.1.1)
Type "help" for help.

postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#

Method 3: From the shell using -c switch to run select pg_reload_conf();
-bash-4.1$ psql postgres postgres -c "select pg_reload_conf();"
Password for user postgres:
 pg_reload_conf
----------------
 t
(1 row)

-bash-4.1$



7. Configure Remote Access for PostgreSQL 9



Locate the postgresql.conf file under /var/lib/pgsql/9.1/data.

Look for CONNECTIONS AND AUTHENTICATION. It will look as below:



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

# - Connection Settings -

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


By default, access is limited to local machine (localhost).

To enable remote connections, uncomment listen_addresses and change to '*' as shown below.

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

# - Connection Settings -

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



You can also set the listen_address limit to a specific IP (or IPs using a comma separated list).

Note: For security, it is also a good idea to change the default port. To do this, uncomment port and set to a new port value.

If you change the port, you will need to restart the service.

Restart the postgresql service:
service postgresql-9.1 restart
Stopping postgresql-9.1 service:                           [  OK  ]
Starting postgresql-9.1 service:                           [  OK  ]
[root@server1 yum.repos.d]#


If you encounter startup errors, check under /var/lib/pgsql/9.1/data/pg_log for clues.

Verify the changes to listen_address and port (if changed):

-bash-4.1$ psql
Password:
psql (9.1.1)
Type "help" for help.

postgres=# show listen_addresses;
 listen_addresses
------------------
 *
(1 row)


postgres=# show port;
 port
------
 5432
(1 row)

postgres=#



8. Create User and Database for PostgreSQL 9



To check Check functionality, connect to postgres db as user postgres.

[root@server1 yum.repos.d]# psql postgres postgres
Password for user postgres:
psql (9.1.1)
Type "help" for help.

postgres=#


Create a user:

postgres=# create user myuser with password 'secret';
CREATE ROLE


Create a database and give ownership to the new user:

postgres=# create database mytestdb owner=myuser;
CREATE DATABASE


Connect to the database as user:

postgres=# \c mytestdb myuser
Password for user myuser:
You are now connected to database "mytestdb" as user "myuser".


Create a table and insert row(s):

mytestdb=> create table testtable (col1 varchar);
CREATE TABLE
mytestdb=> insert into testtable values('hello');
INSERT 0 1


Select on the table you created:

mytestdb=> select * from testtable;
 col1
-------
 hello
(1 row)

mytestdb=>


Describe table:

mytestdb=> \dt
          List of relations
 Schema |   Name    | Type  | Owner
--------+-----------+-------+--------
 public | testtable | table | myuser
(1 row)



Note that by default the schema used is Public. You should create a specific schema for your users.


9. Configure PostgreSQL 9 Service to Start at Boot



By default, the service postgresql-9.1 is added to chkconifg, but all run levels are set to off.

Add for run levels 2,3, and 4 for the postgresql-9.1 service.

[root@server1 yum.repos.d]# chkconfig --level 234 postgresql-9.1 on





10. Create Symlinks for Backward Compatibility from PostgreSQL 9 to PostgreSQL 8



Many, if not most, third party software and modules are still be set to look for PoistgreSQL's conf file and data directory under their old (pre-version 9) locations.

You can address this, and make life easier for yourself, by creating a few symlinks from the new locations to the old.

Symlink 1: Symlink for the binary directory. This is particularly useful as this is the location of the pg_config file

root@server1 [~]# ln -s /usr/pgsql-9.1/bin/pg_config /usr/bin


Symlink 2: Symlink for the old data directory location of /var/lob/pgsql

root@server1 [~]# ln -s /var/lib/pgsql/9.1/data /var/lib/pgsql  
root@server1 [~]# ln -s /var/lib/pgsql/9.1/backups /var/lib/pgsql  



11. Install PostGIS on PostgreSQL 9



Using the postgresql repo, we can easily install PostGIS if we wish to.

The installtion will also install Proj4 and Geos and required perl modules.

[root@server1 yum.repos.d]# yum install postgis91 postgis91-utils
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.us.leaseweb.net
 * extras: mirror.lug.udel.edu
 * updates: centos.mirror.choopa.net
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgis91.x86_64 0:1.5.3-2.rhel6 set to be updated
--> Processing Dependency: proj for package: postgis91-1.5.3-2.rhel6.x86_64
--> Processing Dependency: geos for package: postgis91-1.5.3-2.rhel6.x86_64
--> Processing Dependency: libgeos_c.so.1()(64bit) for package: postgis91-1.5.3-2.rhel6.x86_64
--> Processing Dependency: libproj.so.0()(64bit) for package: postgis91-1.5.3-2.rhel6.x86_64
---> Package postgis91-utils.x86_64 0:1.5.3-2.rhel6 set to be updated
--> Processing Dependency: perl-DBD-Pg for package: postgis91-utils-1.5.3-2.rhel6.x86_64
--> Running transaction check
---> Package geos.x86_64 0:3.3.0-1.rhel6 set to be updated
---> Package perl-DBD-Pg.x86_64 0:2.15.1-3.el6 set to be updated
--> Processing Dependency: perl(DBI) for package: perl-DBD-Pg-2.15.1-3.el6.x86_64
---> Package proj.x86_64 0:4.7.0-1.rhel6 set to be updated
--> Running transaction check
---> Package perl-DBI.x86_64 0:1.609-4.el6 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package                Arch          Version               Repository     Size
================================================================================
Installing:
 postgis91              x86_64        1.5.3-2.rhel6         pgdg91        1.3 M
 postgis91-utils        x86_64        1.5.3-2.rhel6         pgdg91         21 k
Installing for dependencies:
 geos                   x86_64        3.3.0-1.rhel6         pgdg91        502 k
 perl-DBD-Pg            x86_64        2.15.1-3.el6          base          197 k
 perl-DBI               x86_64        1.609-4.el6           base          705 k
 proj                   x86_64        4.7.0-1.rhel6         pgdg91        157 k

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

Total download size: 2.9 M
Installed size: 11 M
Is this ok [y/N]: y
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : proj-4.7.0-1.rhel6.x86_64                                1/6
  Installing     : perl-DBI-1.609-4.el6.x86_64                              2/6
  Installing     : perl-DBD-Pg-2.15.1-3.el6.x86_64                          3/6
  Installing     : geos-3.3.0-1.rhel6.x86_64                                4/6
  Installing     : postgis91-1.5.3-2.rhel6.x86_64                           5/6
  Installing     : postgis91-utils-1.5.3-2.rhel6.x86_64                     6/6

Installed:
  postgis91.x86_64 0:1.5.3-2.rhel6    postgis91-utils.x86_64 0:1.5.3-2.rhel6

Dependency Installed:
  geos.x86_64 0:3.3.0-1.rhel6          perl-DBD-Pg.x86_64 0:2.15.1-3.el6
  perl-DBI.x86_64 0:1.609-4.el6        proj.x86_64 0:4.7.0-1.rhel6

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



The required PostGIS sql files will be installed under /usr/pgsql-9.1/share/contrib/postgis-1.5

Create a database.

-bash-4.1$ createdb pgisdb
Password:
-bash-4.1$


Run the postgis.sql and spatial_ref_sys.sql files using below.

-bash-4.1$ psql -d pgisdb -f /usr/pgsql-9.1/share/contrib/postgis-1.5/postgis.sql


-bash-4.1$ psql -d pgisdb -f /usr/pgsql-9.1/share/contrib/postgis-1.5/spatial_ref_sys.sql



12. 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.1/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.1 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.1 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.1 initdb ; /etc/rc.d/init.d/postgresql-9.1 start; fi
5. Path to postmaster PID file Original:
/var/run/postmaster.pid

Change to:
/var/run/postmaster-9.1.pid

6. Paths to host access config file Original:
/var/lib/pgsql/data/pg_hba.conf

Change to:
 /var/lib/pgsql/9.1/data/pg_hba.conf

7. Default backup repository directory Original:

 /home/db_repository

Change to:

 /var/lib/pgsql/9.1/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:

Postgresql.Org/

PostgreSQL 9.1 Documentation

PostgreSQL 9 Hosting



PostGIS Hosting with PostgreSQL 9

Bookmark and Share



Comments:

This was *seriously* helpful, thank you for creating this.

I am brand new to postgres, still trying to figure out how to install modules, not very clear from the docs... can't find the pg_module command to generate the sql (and catalog)

Posted by Jasdeep on January 30, 2012 at 12:44 PM CST #

Hi Jasdeep -

Glad you found it useful.

Which module(s) are you having problems with?

David

Posted by David Scott Ghedini on January 30, 2012 at 01:39 PM CST #

This is likely the most useful set of installation instructions that I've ever seen anyone post. It is very well laid out, but also thoughtful and considerate in the level of detail. You've managed to make it useful for a wide range of programmers; kudos to you!

I followed these to install Postgresql v9.1.2 on my virtual dedicated server, provided by Godaddy. It is on Centos 5.6, but the pre-build comes with Postgresql v8.1.22. Starting from scratch for the 12th time in as many days, I just performed a "yum upgrade" and a Webmin install prior to following your procedure to install Postgresql.

I've learned that from a freshly-provisioned GoDaddy server, it is best to do nothing at all to the v8 Postgresql: don't start it, initialize it, or even try to remove it. If it is removed, it also takes away some of the required turbopanel packages, and will cripple Godaddy's Simple Control Panel. Just pretend it isn't there.

I started running into problems around step 7: restarting the server. The problems were likely just related to the existing v8 Postgresql. I just rebooted and skipped to using Webmin, where I could finish everything else off.

I like Webmin a lot as well, and was really impressed that you provided module configuration instructions for that as well.

Thank you very much for posting this. You will be top on my search for guidance in the future.

Cheers!

~Sean

Posted by Sean Harasymchuk on January 31, 2012 at 10:17 PM CST #

Thanx David, it is really helpful.

Best Regards from Indonesia
Dayat

Posted by Dayat on February 15, 2012 at 05:14 AM CST #

Hello David,
I have followed this manual step by step. But in step 7, I have modified the port with 5555.
Before restart the server continues in 5432.
When I write:
#service postgresql-9.1 start
┬┐What script execute? ┬┐Would it is set a PGPORT?
Thanks a lot in advance

Posted by Agus on March 04, 2012 at 03:13 AM CST #

Hello again. I have resolved the problem with:

# service postgresql-9.1 stop
# cd /etc/sysconfig/pgsql/
# vi postgresql-9.1
PGPORT=5555
export PGPORT
# service postgresql-9.1 start
You can see the result in:
/var/lib/pgsql/9.1/pgstartup.log
And test it with:
# psql -p 5555 postgres postgres
Thank you,

Posted by Agus on March 04, 2012 at 11:48 AM CST #

Hi, Your blog is vert useful. helped me a ot while installing apache,jboss and pgsql.Would you please help me to integrate openldap and pgsql.??

Posted by Aswathi on March 20, 2012 at 05:04 AM CDT #

Hi All, Thank you very much for the instructions, they proved very useful for my installation.
Being reasonably new to the installation process on Centos I'm curious to know why most examples first execute 'rpm -i', then 'yum install'?
As far as I understand 'yum install' is effectively the same 'rpm -i'.
I haven't been able to find any answer as to why both are usually executed in an installation when they are effectively the same process.

Thanks

Posted by Steven on April 06, 2012 at 07:47 PM CDT #

excellent..... piece of information.
Usually i read from multiple places to achieve some installation like this. This is all in one

Posted by Umakanth Srinivasan on July 26, 2012 at 01:00 PM CDT #

thanks

Posted by vicky on August 10, 2012 at 03:02 AM CDT #

Hi,tutorial is excellent.
When I Create a User and Database for PostgreSQL 9, I got error in my console:
FATAL: Peer authentication failed for user"postgres"

Posted by Ashokkumar on August 23, 2012 at 06:41 AM CDT #

Thanks a lot four you tutorial !
It's a very good one.
I work on this installation since 2 days, and I succeed the installation with your site.
Thanks a lot.

Posted by Antoine on August 23, 2012 at 09:36 AM CDT #

Hi Ashokkumar,

Check your pg_hba.conf (step 6).

Be sure to reload the file as well after making the changes (also in step 6)

Thanks,
David

Posted by David Ghedini on August 23, 2012 at 09:17 PM CDT #

Thank you. Very helpful. Helped me past rpm and yum problems.

Posted by Scott on September 07, 2012 at 04:47 PM CDT #

Great Work by davidghedini.
Really very thankful to you for putting so nice tutorial and i have configured posgresql 9.1 on centos 6 without any single error.
Again regards.
Farrukh

Posted by farrukh on September 12, 2012 at 07:02 AM CDT #

This was such a timesaver. Thanks. Georg

Posted by Georg Zimmer on September 27, 2012 at 09:10 AM CDT #

Thank you very much this was so helpfull !!!

Posted by Sebastien on October 02, 2012 at 07:44 AM CDT #

Thanks a lot, clear and very helpfull.

Posted by ivan on November 13, 2012 at 01:15 PM CST #

Thanks so much... you have no idea of how helpful this has been.

Posted by gerri on December 31, 2012 at 02:36 PM CST #

Thank you very much. I have used this and the JBoss as well as Apache tutorial.

Best regards
Benjamin

Posted by Benjamin on March 22, 2013 at 10:41 AM CDT #

Thanks

Posted by Mauricio dos Santos Magnani Junior on April 23, 2013 at 09:35 PM CDT #

Thank you for the excellent tutorials on your website. Postgres installation went fine and I followed all the steps. However, when I reboot my machine and log in with non-root user account, postgres does not autostart even though I set the chkconfig levels in step 9. Is there anything I need to change to the startup service to work as non-root user? Thanks, Gianni

Posted by Gianni on June 23, 2013 at 03:39 AM CDT #

thank you for great instructions on virtualmin after upgrade to 9.2, when executing psql --version i get psql (PostgreSQL) 8.4.13 how can i remove or disable the 8.4.13?

Posted by david on June 24, 2013 at 11:06 AM CDT #

Thank you for the excellent manual.
I followed this to install postgresql-9.1, but I ran into some errors.
When I pg_ctl reload, it occurs:
pg_ctl: PID file "/var/lib/pgsql/9.1/data/postmaster.pid" does not exist
Is server running?

and
service postgresql-9.1 stop
Stopping postgresql-9.1 service: [FAILED]
I work on this for hours, but I can't resolve it. Please help me.

Posted by Icey on August 13, 2013 at 08:17 AM CDT #

Very very helpfull, but I have to change this: # IPv4 local connections: host all all 127.0.0.1/32 md5 to this: # IPv4 local connections: host all all 0.0.0.0/0 md5 to allow remote access. Thanks for this great tutorial

Posted by Godoy on September 02, 2013 at 07:27 PM CDT #

Hi- When I edit edit CentOS-Base.repo and add 'exclude=postgresql*' to the [base] and [updates] sections it doesn't take me back to the [root@server1 yum.repos.d]# prompt. What am I doing wrong here? I've tried multiple approaches and none seem to work. Thanks

Posted by Zac stanley on February 26, 2014 at 12:08 PM CST #

Post a Comment:
  • HTML Syntax: Allowed

Main Menu

Built With

Search

Pages

LinkedIn

Technorati Profile

Add Technorati Favorite

Tag Cloud

Enciva Solutions

Navigation

Visitors

Sponsors

Feeds

Tag Cloud

VPS Hosting: 1Gbps Network