Data Management

Set up a PostgreSQL database server on Linux

Use this tutorial to get a PostgreSQL database server up and running on Linux.

In a previous article, I discussed the use of the popular MySQL database server on Linux. In this article, we will look at how to set up an alternative open source database called PostgreSQL. While MySQL is a fast, powerful, and relatively simple RDBMS, PostgreSQL is more complex and trades some speed for that complexity.

If you've gotten used to using referential integrity, views, and triggers, PostgreSQL is the database for you—although version 4 of MySQL will have some of these features as well. For the purposes of showing you how to set up PostgreSQL in this tutorial, I have used the following software versions:
  • Red Hat Linux 7.1
  • PostgreSQL 7.1.3

Getting ready for the installation
Before you begin, you need to get the software. You can locate and download the latest release here. (In this article, we'll use version 7.1.3, but it's best to use the latest production version available.) Once you have downloaded this source distribution of PostgreSQL, you should put it into the /usr/local directory on your Linux server. If you put it somewhere else, please make note of it and modify the proceeding instructions accordingly.

Author's note
If you decide to download the latest development release of PostgreSQL, you may also need to download and install flex (>=version 2.5.4) and bison (>=version 1.28) in order to properly build PostgreSQL.

PostgreSQL will not run as root. This was a security design decision by the developers. Therefore, you need to have a user account set up to run the PostgreSQL processes. We’ll use the account postgres in this tutorial, and we’ll also create a group named postgres.

Of course, in order to create these accounts, you will need to be logged in as a superuser (usually root). To create the user group postgres on my Linux server, I will type groupadd postgres at the command line. Next, to create the user and put it into this group, I will issue the command useradd postgres –g postgres.

We need to do a couple of additional things before we actually begin installing PostgreSQL. First, we will manually create the directories we want to use and assign the postgres user as their owner. This will result in fewer permissions headaches down the line.

First, create two directories:
mkdir /usr/local/pgsql
mkdir /usr/local/postgresql-7.1.3

Then, assign ownership of these two directories to the postgres user we created earlier:
chown postgres.postgres /usr/local/pgsql
chown postgres.postgres /usr/local/postgresql-7.1.3

Now let’s expand the distribution so that we can install it. First, issue a su postgres command to change to the postgres user. Next, switch to the /usr/local directory with the command cd /usr/local and issue the following command to expand the file you downloaded:
gunzip –dc postgresql-7.1.3.tar.gz | tar xvf

When the list of files being extracted stops, and you are back at a command prompt, change to the distribution directory with the command cd /usr/local/postgres-7.1.3.

If you build it, they will come
As with any open source distribution, the first step in the installation process is to configure the installation routine with options that are specific to your operating environment, including the desired program location and the enabling/disabling of specific features.

For the purposes of our installation, we are interested in making sure that the software is installed to /usr/local/pgsql, the directory we created earlier. To accomplish this, we will issue the command:
./configure --prefix=/usr/local/pgsql

The next step is to compile the source with the options that were provided in the previous step. Do this with the command make. Once this is complete, you are ready to install the binaries by typing the command make install.

But wait, there's more
PostgreSQL also has some environment variables you should set before you start up the database. For example, you should let PostgreSQL know where to find its data files, its shared libraries, its applications files, and its man pages. On my RedHat Linux 7.1 server, I like to do that by modifying the .bash_profile in /home/postgres. Since this file is read at login, the appropriate variables are added to the environment for the postgres user. Use your favorite text editor to follow the steps here (mine is pico).

Open .bash_profile for editing with the command:
pico /home/postgres/.bash_profile

Then, add the following lines to the file before the unset username line:
PGLIB=/usr/local/pgsql/lib
PGDATA=/usr/local/pgsql/data
PATH=$PATH:/usr/local/pgsql/bin
MANPATH=$MANPATH:/usr/local/pgsql/man
export PGLIB PGDATA PATH MANPATH

Save the file and create the directory /usr/local/pgsql/pgdata with the command:
mkdir /usr/local/pgsql/data

You do not need to become the root user to do this since the postgres user owns the entire /usr/local/pgsql directory tree. This directory will house all of our database files.

Start your (database) engines
Before you can actually start the database engine, you need to initialize the various PostgreSQL databases with an initialization command. To perform this initialization, issue the command cd /usr/local/pgsql/bin and then type ./initdb and press [Enter]. You will get a number of status messages and then be returned to a prompt.

At this point, you can manually start the Postgres postmaster with the command:
/usr/local/pgsql/bin/postmaster –i -D /usr/local/pgsql/data &

This will start PostgreSQL using the databases located in the /usr/local/pgsql/data directory, allow connections from the Internet (the –i parameter), and run it in the background (the & parameter). Your database engine has now been started and is ready for some data.

Let's create a database
In this example, we’ll create a database called mydb and a table called mytable just to make sure that everything is working. First, type createdb mydb at the command prompt. PostgreSQL will respond with CREATED DATABASE to let you know it got the command. Now, let’s use the psql utility to work with this database. Type psql mydb and press [Enter]. This will bring you into the interactive PostgreSQL utility.

To create the mytable table inside the mydb database, do the following. Keep in mind that this is not intended to be a real table, but rather just a sample:
CREATE TABLE mytable (
id varchar(20),
name varchar(30));

Once you have done this, you will get a CREATED message indicating that the table was created. Now let’s insert a record into the table, view the contents, and then exit the psql utility:
INSERT INTO mytable values ('Author', 'Scott Lowe');

Now psql will respond with INSERT 18732 1. To view the contents of this table, type:
SELECT “* FROM MYTABLE;”

You will be acknowledged with the following:
   id   |    name
--------+------------
 Author | Scott Lowe
(1 row)

To exit the psql utility, type \q and press [Enter].

End sum
This wraps our look at a basic installation of the PostgreSQL 7.1.3 database on RedHat Linux. I think you will find that PostgreSQL is a powerful database capable of running successfully in many environments with its advanced capabilities.

If you have any problems installing PostgreSQL, I recommend that you peruse the searchable documents available at the PostgreSQL Web site.

What do you think about PostgreSQL?
We look forward to getting your input and hearing about your experiences regarding this topic. Post a comment or a question about this article.

 
0 comments

Editor's Picks