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

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.

 

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

 

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:

Start the PostgreSQL server:

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:

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.

 

5. Set postgres Password

 

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:

 

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:

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();

 

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:

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

8. Create User and Database for PostgreSQL 9

 

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.

 

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.

 

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

Symlink 2: Symlink for the old data directory location of /var/lob/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.

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.

 

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:

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

Install Tomcat 7 on CentOS, RHEL, or Fedora

This post will cover installing and basic configuration of Tomcat 7 on CentOS 5.x. or CentOS 6.x

Tomcat 7 implements the JavaServer Pages 2.2 and Servlet 3.0 specifications and a number of new features. The Manager application also has a new look and finer-grain roles and access than 6.x

In this post, we’ll install Tomcat 7, the new JDK 7, configure Tomcat as a service, create a start/stop script, and (optionally) configure Tomcat to run under a non-root user.

We will also configure basic access to Tomcat Manager and take a quick look at memory management using JAVA_OPTS

Finally, we will look at running Tomcat on port 80 as well as some strategies for running Tomcat behind Apache.

To begin, we’ll need to install the Java Development Kit (JDK) 7

JDK 1.6 is the minimum JDK version for Tomcat 7.

Step 1: Install JDK 1.7

 

You can download the latest JDK here: http://www.oracle.com/technetwork/java/javase/downloads/index.html

We’ll install the latest JDK, which is JDK 7, Update 5. The JDK is specific to 32 and 64 bit versions.

My CentOS box is 64 bit, so I’ll need: jdk-7u5-linux-x64.tar.gz.

If you are on 32 bit, you’ll need: jdk-7u5-linux-i586.tar.gz

Start by creating a new directory /usr/java:

Change to the /usr/java directory we created

Download the appropriate JDK and save it to /usr/java directory we created above.

Unpack jdk-7u5-linux-x64.tar.gz in the /usr/java directory using tar -xzf:

This will create the directory /usr/java/jdk1.7.0_05. This will be our JAVA_HOME.

We can now set JAVA_HOME and put Java into the path of our users.

To set it for your current session, you can issue the following from the CLI:

To set the JAVA_HOME permanently, however, we need to add below to the ~/.bash_profile of the user (in this case, root).

We can also add it /etc/profile and then source it to give to all users.

Once you have added the above to ~/.bash_profile, you should log out, then log back in and check that the JAVA_HOME is set correctly.

Note: If you decided to use JDK 6 rather than 7 as we did above, simply save the JDK 6 bin file to /opt (or another location), then navigate to /usr/java and issue: ‘sh /opt/jdk-6u33-linux-x64.bin’. This will create a JAVA Home of /usr/java/jdk1.6.0.33

Step 2: Download and Unpack Tomcat 7.0.29 (or latest)

 

We will install Tomcat 7 under /usr/share.

Switch to the /usr/share directory:

Download apache-tomcat-7.0.29.tar.gz (or the latest version) here

and save it to /usr/share

Once downloaded, you should verify the MD5 Checksum for your Tomcat download using the md5sum command.

Compare the output above to the MD5 Checksum provided next to the download link and you used above and check that it matches.

unpack the file using tar -xzf:

This will create the directory /usr/share/apache-tomcat-7.0.29

Step 3: Configure Tomcat to Run as a Service.

We will now see how to run Tomcat as a service and create a simple Start/Stop/Restart script, as well as to start Tomcat at boot.

Change to the /etc/init.d directory and create a script called ‘tomcat’ as shown below.

And here is the script we will use.

The above script is simple and contains all of the basic elements you will need to get going.

As you can see, we are simply calling the startup.sh and shutdown.sh scripts located in the Tomcat bin directory (/usr/share/apache-tomcat-7.0.29/bin).

You can adjust your script according to your needs and, in subsequent posts, we’ll look at additional examples.

CATALINA_HOME is the Tomcat home directory (/usr/share/apache-tomcat-7.0.29)

Now, set the permissions for your script to make it executable:

We now use the chkconfig utility to have Tomcat start at boot time. In my script above, I am using chkconfig: 234 20 80. 2345 are the run levels and 20 and 80 are the stop and start priorities respectively. You can adjust as needed.

Verify it:

Now, let’s test our script.

Start Tomcat:

Stop Tomcat:

Restarting Tomcat (Must be started first):

We should review the Catalina.out log located at /usr/share/apache-tomcat-7.0.29/logs/catalina.out and check for any errors.

We can now access the Tomcat Manager page at:

http://yourdomain.com:8080 or http://yourIPaddress:8080 and we should see the Tomcat home page.

Step 4: Configuring Tomcat Manager Access.

Tomcat 7 contains a number of changes that offer finer-grain roles.

For security reasons, no users or passwords are created for the Tomcat manager roles by default. In a production deployment, it is always best to remove the Manager

application.

To set roles, user name(s) and password(s), we need to configure the tomcat-users.xml file located at $CATALINA_HOME/conf/tomcat-users.xml.

In the case of our installation, $CATALINA_HOME is located at /usr/share/apache-tomcat-7.0.29.

By default the Tomcat 7 tomcat-users.xml file will have the elements between the and tags commented-out. .

New roles for Tomcat 7 offer finer-grained access and The following roles are now available:

manager-gui
manager-status
manager-jmx
manager-script
admin-gu
admin-script.

We can set the manager-gui role, for example as below

Caution should be exercised in granting multiple roles so as not to under-mind security.

Step 5 (Oprtional): Manage Memory Usage Using JAVA_OPTS.

Getting the right heap memory settings for your installation will depend on a number of factors.

For simplicity, we will set our inital heap size, Xms, and our maximum heap size, Xmx, to the same value of 128 Mb

Simliarly, there are several approaches you can take as to where and how you set your JAVA_OPTS

Again, for simplicity, we will add our JAVA_OPTS memory parameters in our Catalina.sh file.

So, open the Catalina.sh file located under /usr/share/apache-tomcat-7.0.29/bin with a text editor or vi.

Since we are using 128 Mb for both initial and maximum heap size, add the following line to Catalina.sh

I usually just add this in the second line of the file so it looks as so:

Step 6 (Optional): How to Run Tomcat using Minimally Privileged (non-root) User.

In our Tomcat configuration above, we are running Tomcat as Root.

For security reasons, it is always best to run services with the only those privileges that are necessary.

There are some who make a strong case that this is not required, but it’s always best to err on the side of caution.

To run Tomcat as non-root user, we need to do the following:

1. Create the group ‘tomcat’:

2. Create the user ‘tomcat’ and add this user to the tomcat group we created above.

The above will create a home directory for the user tomcat in the default user home as /home/tomcat

If we want the home directory to be elsewhere, we simply specify so using the -d switch.

The above will create the user tomcat’s home directory as /usr/share/apache-tomcat-7.0.29/tomcat

3. Change ownership of the tomcat files to the user tomcat we created above:

Note: it is possible to enhance our security still further by making certain files and directories read-only. This will not be covered in this post and care should be used when setting such permissions.

4. Adjust the start/stop service script we created above. In our new script, we need to su to the user tomcat:

 

Step 7 (Optional): How to Run Tomcat on Port 80 as Non-Root User.

Note: the following applies when you are running Tomcat in “stand alone” mode with Tomcat running under the minimally privileged user Tomcat we created in the previous step.

To run services below port 1024 as a user other than root, you can add the following to your IP tables:

Be sure to save and restart your IP Tables.

Step 8 (Optional): Running Tomcat behind Apache

As an alternative to running Tomcat on port 80, if you have Apache in front of Tomcat, you can use mod_proxy as well as ajp connector to map your domain to your Tomcat application(s) using an Apache vhost as shown below.

While Tomcat has improved it’s ‘standalone performance’, I still prefer to have Apace in front of it for a number of reasons.

In your Apache config, be sure to set KeepAlive to ‘on’. Apache tuning, of course, is a whole subject in itself…

Example 1: VHOST with mod_proxy:

Example 2: VHOST with ajp connector and mod_proxy:

In both vhost examples above, we are “mapping” the domain to Tomcat’s ROOT directory.

If we wish to map to an application such as yourdomain.com/myapp, we can add some rewrite as shown below.

This will rewrite all requests for yourdomain.com to yourdomain.com/myapp.

Example 3: VHOST with rewrite:

Related Tomcat Posts

Learn More About Apache Tomcat 7

Apache Tomcat Foundation
Tomcat 7