PostgreSQL (pgsql for short) is the most advanced open source relational database server available. The number and type of options available can be daunting to the newbie as well as experienced database developer. This article will show you how to build and install pgsql from its source distribution available from http://www.postgresql.org. The current version of pgsql is 7.0.2 and comes packaged in a file named, surprisingly enough, postgresql-7.0.2.tar.gz.
For this article I used two systems to build pgsql, which are representative of the range of boxes used for development (though, probably not production) purposes. Their configurations are listed below:
System A: Dell Pentium 75, 16M, IDE, RedHat 6.2
System B: Dell Pentium III – 400, 64M, SCSI, FreeBSD 4.1
Both of these systems were base installs and no special steps were made to optimize performance.
Because pgsql runs on so many different systems, I chose to focus on two: GNU/Linux and FreeBSD. You will find that the process of installation is almost the same on both systems, except for a few steps at the end of the installation process.
Why Build Your Own?
GNU/Linux distributions all come with some sort of package management utility. The same goes for FreeBSD with its ports collection. If installing pgsql is as simple as using RPM or changing directory into a Ports directory, why on earth would you choose to compile pgsql yourself?
As with everything in life, there are tradeoffs. Package managers trade in configuration options for ease of use. If you get the base pgsql RPM from a RedHat disc, what compile time options were specified? What directory is RPM going to install pgsql to? Where is the data going to be housed? You don’t rightly know.
By building the sources yourself, you get complete control over how pgsql is compiled, where the data is going to be kept, and where the binaries are going to be installed.. You decide which compile time options are set as well as which support elements are included with the install.
It may be a good thing to rely on package managers for simpler apps, but when you’re talking about server-level systems you want all the control you can muster. Especially when working in a 24/7 production environment.
Requirements: System & Software
When it comes to installation hardware, pgsql is a feature-packed but lean relational database system. Just to see if it could be done, I once compiled and installed an earlier version of pgsql on a RedHat 6.0 box with 1 gig of disk and 8 megs of RAM. Pgsql compiled and ran without complaint on that anemic little machine.
To quickly sum up hardware requirements, it’s generally a safe bet to assume if you can install a modern GNU/Linux or FreeBSD distribution on a machine you should also be able to run pgsql. Deciding on a hardware configuration for a given workload is a black art and won’t be discussed in this article short of the standard database server equation: As Much Ram as Possible + As Much Disk as Possible + As Much CPU as Possible.
To build pgsql you are required to have a base level of software installed on the machine. If you have a modern distribution of GNU/Linux or FreeBSD you can rest assured that you have everything needed to build pgsql. You may be missing a piece here and there depending on what options you choose when installing your distribution but everything needed to build pgsql will most likely reside on your chosen distribution’s CD.
Unpacking the Source
Logged in as yourself (you need no special system privileges to build pgsql) and assuming you source tarball in your home directory type the following command to uncompress the file:
gunzip d postgresql*.gz ; tar xvf postgresql-7.0.2.tar
Once the command is completed you will have a directory named postgresql-7.0.2 within your home directory.
Change to the SRC directory (cd ~/postgresql-7.0.2/src) under the postgresql-7.0.2 directory created when you unpacked the source. To start the configure, process simply type ./configure at the prompt. You will see several screens worth of text scroll by as the configure script examines your system.
Configure does a lot of checking and may take a while to run through to completion. On System A the configure process took 177 seconds to run, while System B churned through in 33 seconds.
There are a number of command line options that can be given to the configure script which will change defaults and other options used to build pgsql. A few important arguments are listed below:
- –prefix=<directory> — Determines where the resulting binaries will be installed. The default prefix is /usr/local/pgsql.
- –with-pgport=<port number> — Tells pgsql which TCP/IP port it should use when chatting with client applications. The default port is 5432.
- –with-maxbackends=<number of backends> — The default number of back ends is set to 32 in file include/config.h. If you’re going to have a large user population, you’ll want to increase this value upwards. A good place to start is to multiply the number of users by two.. If you find this is still too low, rebuild pgsql with a higher number of back ends. As you look at include/config.h, you will see a comment discussing the affects of setting this parameter to a higher value.
There are hosts of other configure options that may pertain to your production installation, but for the time being stick to the defaults. We want to get pgsql up and running quickly.
If configure finds something with your system that will prevent pgsql from compiling, it will alert you and abort. For instance, configure will look for a C++ compiler on your system and abort if it cannot find one. If configure aborts, read the message, fix the problem, and rerun configure.
At this point we have the build system configured to compile pgsql. While it’s still in the SRC directory type make (or gmake), go grab a cup of coffee–depending on the type of machine you have this, step can take quite a while.
On System A the compile took almost 46 minutes, while System B performed the build in just over 5 minutes.
As with the configure script there are a number of command line options you can specify to modify how the pgsql source is compiled. For the purposes of getting pgsql up and running with the least amount of pain, we will skip over these options.
If the compile process runs into a situation it can’t handle it will abort with error messages. If this occurs, you can try to fix the problem yourself or go search the mailing list archives at the PostgreSQL web site. If you don’t find a solution searching the archives, it’s time to subscribe to one of the mailing lists and post a question asking for help.
After a good build we are now ready to install pgsql on your system. Up to this point we have been working under your account on the machine, since we didn’t need any special rights to unpack and build pgsql. From this point on you will need to su to root to perform the installation.
So su to root, and read on.
The install process is straight forward and is comprised of six steps: 1) Creating the postgres user; 2) Moving binaries; 3) Setting up share libraries; 4) Creating base templates; 5) Assigning ownership; and 6) Starting pgsql.
Pgsql is just another task running on your machine and needs to run under a non-privileged ID in order to operate safely. You will not want to run pgsql as the root user because this creates a security risk that opens your system up to attack.
Create a new user on your box named postgres. This ID will be used to run pgsql on your machine. Consult the man pages on your system to determine how to create this user ID. There is nothing sacred about naming this user postgreit’s just a convention I follow. You should also assign a password to this account, as the postgres user will have special rights when running the pgsql command line tools.
While still in the SRC directory, type make install. You will see messages scroll across the screen as the install process creates directories and moves the pgsql binaries to /usr/local/pgsql (or whatever directory you specified as the PREFIX during the configure script).
Once pgsql has been moved, change to /usr/local/pgsql and take a look around a bit. You will find directories named bin, data, doc, include, lib, etc. At this point in the install process the binaries are in place as is the directory structure. We now need to create the base templates used by pgsql when creating new databases. Unless they’re elsewhere, assume that we are still in the /usr/local/pgsql directory when executing the following commands.
Before creating the templates, we need to take care of a few shared libraries created during the compile. These libraries now reside in /usr/local/pgsql/lib and the system needs to be configured to recognize them. Under RedHat Linux you would edit a file named /etc/ld.so.conf, go to the end of the file, and add an entry denoting the full path to the pgsql lib directory (which is /usr/local/pgsql/lib). Save the file and run ldconfig to have the system reparse the ld.so.conf file.
Under FreeBSD, I edited the file /etc/defaults/rc.conf and added /usr/local/pgsql/lib to the ldconfig_paths variable. While I had to reboot the FreeBSD machine, I’m sure there is a ldconfig variant that would have done the job without a reboot (this should not be considered a weakness of FreeBSD by any stretch of the imagination I’m just new to how FreeBSD does its thing).
At this point we have the libraries registered and binaries in place.
Now we need to create the base template used by pgsql. The command to create the this is called initdb. We will need to specify two parameters to initdb so the process can locate the pgsql lib and data directory. Assuming you used defaults during the configure process, the full initdb command will look like this (it’s executed from /usr/local/pgsql):
bin/initdb D /usr/loca/pgsql/data L /usr/local/pgsql/lib
The above command will create the template databases in /usr/local/pgsql/data.
At this point, assuming everything went well, we now have pgsql completely installed. But we aren’t ready to roll just yet. We need to take care of assigning ownership and starting pgsql on boot.
You should still be in /usr/local/pgsql. Change to /usr/local and type the following command:
chown R postgres.postgres /usr/local/pgsql
NOTE: With FreeBSD 4.1 you will want to specify postgres:postgres.
This command will change the ownership of all files under the /usr/local/pgsql directory to the postgres user we created a few moments ago.
We have reached the last step of the installation process: getting pgsql to load at boot time. There are two different ways of accomplishing this, and which one you use depends on if you are running FreeBSD or GNU/Linux.
If you are running GNU/Linux there is a startup script already created for you. Perform the following commands:
cp postgresql-7.0.2/contrib/linux/postgres.init.sh /etc/rc.d/init.d/postgres
ln -s /etc/rc.d/init.d/postgres /etc/rc.d/rc3.d/S98postgres
To start pgsql type the following command:
Now, whenever the GNU/Linux machine boots into runlevel 3 it will start pgsql automatically. Repeat the ln command for rc5.d if the machine boots straight into X.
For FreeBSD the process is a bit different. As documented in postgresql-7.0.2/INSTALL, you will have to create a script manually.
Create a file named /usr/local/etc/rc.d/pgsql.sh with the following contents:
su l postgres c exec /usr/local/pgsql/bin/postmaster-D /usr/local/pgsql/data-S o F > /usr/local/pgsql/errlog' & echo n pgsql'
To test pgsql, su to the postgres user we created earlier and enter /usr/local/pgsql/bin/psql t template1. Once psql loads you will be presented with a prompt. Enter the following statement:
You should see a message like the following appear on the console:
PostgreSQL 7.0.2 on i386-unknown-freebsdelf4.1, compiled by gcc 2.95.2
If you run this on a GNU/Linux machine the message will be a bit different, but if you do see a message like the above, then congratulations! You have installed pgsql!
Other Things to Do
We now have a copy of pgsql running on the machine. The only user who can access pgsql is the postgres user we created earlier in the process. On top of this we only have one database in the system (template), and we don’t want to access this while we are learning about pgsql.
To remedy this, we will quickly create an account for you to use. You can su to the postgres user again, type /usr/local/pgsql/bin/createuse,r and follow the prompts. The first thing you will be asked is to name this user: Enter your login name. You will be asked if this new user you are creating is a super user. Answer no to this. When asked if this new user is allowed to create databases answer yes.
Finally, we will need to create a new database for the user you just created. Do this with the /usr/local/pgsql/bin/createdb command. Run this command using the username you entered above as a command line parameter.
To make sure the above two commands worked, type /usr/local/pgsql/bin/psql and you should be presented with the PostgreSQL Interactive Terminal. From here on out you can do whatever you’d like to the database we just created as well as create new databases.
- www.postgresql.org — the home of PostgreSQL. Online documentation, mailing list archives, and links to other informational sites–. All kinds of good stuff.
- Postgresql-7.0.2/INSTALL very good file discussing how to get pgsql running. Refer back to this file if you get lost during the build process.
Damond Walker works at Black Oak Computer Services Inc, of Newark, Del , as a Lotus Notes/Domino and SQL Server consultant. When not pushing bytes for his employer, he pushes strollers with his wife and their twin daughters.