I've had to do this a few times and each time I have to dig around to get this just right so this time I decided to install and document what I did.  I'm doing this install on Centos 5.2 64bit that is running in a VM (VMware Fusion) on my Mac Book Pro.  This Centos install is also running a version of ColdFusion and Apache.  I have two reasons for doing this install.  One is so I can practice the upgrade from 8.1 to 8.3.  There are some SQL functions in 8.3 I want to get into that lets you return ranks with your query.  See this. And the next was so I could document this process so I don't have to search the net to find how to do it and then figure out how I need to do it.  Since I maintain my own servers I tend to have to research this sort of thing a few times a year.  I'm also planning to purchase a new to me server with much more power to run my production PostgreSQL DB and I figure I should have this part sorted out by the time I decide to spend the money on the machine.

So step one after figuring out the VM stuff (running VMware is new to me) is to install PostgreSQL.  When I did my first PostgreSQL install ages ago I downloaded the source and ran a bunch of commands something along the lines of .configure --some-options; make; make install; and some other stuff.  Now that I'm using Centos I figured why the heck do ALL of that when there is YUM!  So I did yum:

view plain print about
1$ yum install postgresql postgresql-server postgresql-pl postgresql-libs postgresql-devel

Within a few minutes all the packages were downloaded and installed.  That was the painless part.  Next is doing the initial configuration and first database setup.  That normally includes creating a postgres user account and creating the data folder but it looks like yum did that for me. I was left doing a few simple commands:

view plain print about
1$ /etc/init.d/postgresql start
3$ su - postgres
5$ /usr/bin/createdb test
7$ psql test

These commands just started PostgreSQL server, change to the postgres user, created a database called test and it just logged me into the test DB using the psql text based client.  We're good!

This start script automatically does some of what I would normally have had to do manually.  I also would have had to modify my environment paths and a few other things.  Can this get easier? (Well for you it doesn't, it took me a few IPA beers to re-learn this.  The old and hard way is burned into my memory and is getting in the way of this newer simpler method.)

In dev environments you may have everything on one computer, but in production ColdFusion is usually on a different server than your database.  For security reasons, PostgreSQL does not listen on all available IP addresses on the server machine initially.  In order to access the server over the network, you need to enable listening on the network address first.  This can be done by editing the postgresql.conf file located in your data folder.  By default (with the steps we took) this is located at /var/lib/pgsql/data/postgresql.conf.  

You'll want to locate this line in the configuration file and change it to allow listening on your desired IP address.

view plain print about
1#listen_addresses = 'localhost'
3listen_addresses = '*'


view plain print about
1listen_addresses = ''

and change this line

view plain print about

The * means it will listen on all the IP addresses on the server.  By specifying an IP address PostgreSQL will only listen for requests on the specified IP address.  Also note that I removed the # at the start of the line.  If your not familiar with Linux config files you may not know that the # is used to comment out a line.

Next you will have to define who has access to your PostgreSQL server.  This is done in the pg_hga.conf file in the same data folder.  By default PostgreSQL only allows localhost access.  You want to add the IP address of the remote ColdFusion server and the IP address(s) of the workstations that will have client access.  You can also specify IP address ranges to make this a little easier.  So edit the pg_hba.conf file. 

You will need to add lines at the bottom of the file to include additional IP addresses.  I added this line since this is for a dev environment everything is on the same internal IP network.

view plain print about
1host all all trust

*This configuration step is the same for all PostgreSQL 7.3 and up installations and the PostgreSQL manual covers this very well.  Your file will be located at /var/lib/pgsql/data/pg_hba.conf if you followed this install.

Next since your configuring for remote access you have to tell Linux to let in connections on port 5432.  By default your Linux firewall will block access on this port.  Allowing access is done by editing your iptables.  This is easily done by editing /etc/sysconfig/iptables.  You will need to add this line.

view plain print about
1-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

Then you will need to restart the iptables service.

view plain print about
1$ service iptables restart

Now you can access your PostgreSQL 8.1 server remotely.

By default the only user is postgres and you will have to login using this user first and then create any additional users you need.

If you need a GUI client to access and manage your databases then I would suggest (and I also use) pgAdmin III.  pgAdmin comes in Mac, Linux and Windows flavors so everyone can be happy with using a PostgreSQL server no matter what desktop OS they are running.

So now I have PostgreSQL 8.1 64 bit installed and running.  So far YUM is our friend and it will continue to be our friend. The Centos5 packages are only upto PostgreSQL 8.1.11 and I want 8.3.x. and thus YUM can not install anything newer yet.  Or can it?  You need to use another yum source to get the 8.3 packages. That source is http://yum.pgsqlrpms.org/.

Before I even begin the describe the upgrade, BACKUP your databases.  I know we just installed this server, but my point of this was to install a replicated environment of my production server and to learn how to do the upgrade.  So between this paragraph and the last one I migrated one of my databases to this new install.  The pgAdmin tool made this super fast and easy.  First I used pgAdmin to recreate the users I have on production on the new server.  Then I created a new database with the same name as the one I use for this blog.  Then I connect to my production server and used pgAdmin to make a backup of that database and save the backup on my desktop.  Then I reconnected pgAdmin to the new server and ran a restore using the backup file I just created. Easy!

Now we upgrade to 8.3.  First we remove what we just installed.  Yum is still our friend. Did you remember to backup your databases?  If not STOP and do the backup NOW.  This is your last chance. 

view plain print about
1$ /etc/init.d/postgresql stop
3$ yum remove postgresql postgresql-server postgresql-pl postgresql-libs postgresql-devel
5$ rm -rf /var/lib/pgsql

Note that the rm -rf command just wiped out the data folder for the old PostreSQL installation.  This means all the data that was in there is gone.  You backup your databases like I said right?

Now we need to get the new version.

First I went to the pgsqlrpms.org website and found the version I wanted to download.  Then I ran this command from the Centos server.

view plain print about
1$ wget 'http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-centos-8.3-5.noarch.rpm'
3$ rpm -Uvh pgdg-centos-8.3-5.noarch.rpm

This just setup yum to use a new package repository for installing PostgreSQL 8.3.

I just ran yum search postgresql to see what I had for packages.  And it's what I suspected. I had way too many option for PostgreSQL because yum was finding packages from both Centos and the pgsqlrpms.org website.  So we have to block the Centos update location. To do this do the following:

view plain print about
1$ cd /etc/yum.repos.d

Edit the CentOS-Base.repo file.  Add the following line to the [base] and [updates] section inside the file.


Now yum search postgresql returns better results. Now do the install

view plain print about
1$ yum install postgresql postgresql-server postgresql-pl postgresql-libs postgresql-devel pgadmin3 postgresql-contrib

Before saying yes to the install check the version number reported to make sure it's what we wanted.  Mine reports 8.3.6.  Also you may note I slipped in the packages pgadmin3 and postgresql-contrib.  I did this because while researching for this upgrade I found that after installing the pgadmin package I could run a command to install pgAdmin pack. This lets pgAdmin have more functionality and even edit your postgreSQL config files remotely.  Cool stuff.

First we need to get our upgraded install running.

view plain print about
1$ service postgresql initdb
3$ service postgresql start
5$ su - postgres
7$ /usr/bin/createdb test

This is just like when we installed 8.1.  We need to initialize the postgreSQL server, start the server, change to the postgres user account and create the test database.

Now we install the pgAdmin pack. We do this while still running as postgres user.

view plain print about
1$ psql -d postgres -f /usr/share/pgsql/contrib/adminpack.sql

You'll see that many CREATE FUNCTION commands have run.  Now we have to enable remote access just like before.

Now we nee to enable remote access.  This is the same as before. You need to edit your postgresql.conf and pg_hba.conf files located in /var/lib/pgsql/data/ if you did the default install like we did here.

After editing restart postgreSQL.

view plain print about
1$ service postgresql restart

If all went well you have PostgreSQL 8.3 running on your Centos 5.2 64 bit server.

Now lets connect from pgAdmin on your workstation.  Now when connecting I am asked for a password for the postgres user.  By default this is postgres.  For production servers you will want to change this.  For my testing, I'm leaving it be for now.  But I am going to create my other user accounts that I have on my production server.  I used pgAdmin to create these accounts.

Time to restore my database to the new server.  I'll use pgAdmin to do this since it is so easy to do.  First create a new database with pgAdmin that is the same name as the one your going to restore. Then right click on that database and choose restore.  This worked without flaws for me and I now have my database migrated to a new version of PostgreSQL.

Now I am going to test to see if my ColdFusion installs can find the server and database.   To do this fire up your ColdFusion admin and add a new datasource.  Again this worked just fine and my ColdFusion 8 dev install on my Mac Book can access the databases running on my VMWare Centos server with PostgreSQL.  Success.  Now I and hopefully you know how to install and upgrade PostgreSQL database server on Centos.