Category: PostgreSQL

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.

 

 

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

Now, install the repo….

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:

Now, let’s use ‘yum list’ to check the packages that are now available.

 

 

We can now install PostgreSQL 9 using yum:

yum install postgresql91 postgresql91-devel postgresql91-server postgresql91-libs postgresql91-contrib

 

 

We can now initialize and Start PostgreSQL

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

Start the PostgreSQL server:

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

 

 

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

The bash_profile for the user postgres will look like this:

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

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

 

 

The superuser postgres has no password set by default.

To set the password, switch to postgres user:

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

 

 

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

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

Change the METHOD to md5 as shown below:

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:

</br />
Method 2: From psql using pg_reload_conf();

Method 3: From the shell using -c switch to run select pg_reload_conf();

 

 

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

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

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

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

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:

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):

 

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

Create a user:

Create a database and give ownership to the new user:

Connect to the database as user:

Create a table and insert row(s):

Select on the table you created:

Describe table:

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

 

 

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.

 

 

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

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

 

 

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.

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

Create a database.

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

 

 

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:

Change to:

2. Command to start PostgreSQL
Original:

Change to:

3. Command to stop PostgreSQL

Original:

Change to:

4. Command to initialize PostgreSQL
Original:

Change to:

5. Path to postmaster PID file
Original:

Change to:

 

6. Paths to host access config file
Original:

Change to:

 

7. Default backup repository directory
Original:

Change to:

 

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