Home > Articles > Data > SQL

PostgreSQL Compiling and Installation

  • Print
  • + Share This
PostgreSQL is destined to be one of the shining stars of the open-source arena. With PostgreSQL, you can create an open-source database that rivals many high-dollar commercial alternatives. New Riders author Barry Stinson gives those who are new to PostgreSQL, but familiar with database concepts in general, a quick-start guide to administrating a PostgreSQL database.
From the author of

The first step in getting to know PostgreSQL is to install and configure it for use. This differs, depending on whether it is being installed from source-code or from a package based distribution (such as RPM).

Source-Based Installation

The source files can be retrieved from the PostgreSQL FTP site (ftp.postgresql.org) or from numerous mirror sites around the world.

After the file is downloaded, it is most probably in a tarred-gzipped format. You must unpack it before you can compile it. To do so, move the file to a clean directory (such as /usr/src/postgres) and issue the following commands:

$tar xzf postgresql-7.2.tar.gz

After the code is unpacked, you can delete the original tar.gz file, if disk space is an issue. Otherwise, move the code to a safe location.

Next, review the INSTALL text file included in the directory created for installation notes. Then, complete the following:

  1. Create a user account to serve as the DBA's account (postgres is a popular choice). You can do this with userconf, useradd, or whatever tool your system provides for user management.

  2. Review the installation options for your system by typing ./configure --help.

  3. Configure the source code with the options that you selected (for example, configure --with-odbc).

  4. Type make (or gmake) to build binaries.

  5. If the make fails, examine the log files generated (usually in ./config.log) for reasons why the compile didn't work

  6. Type make install to install the binaries in the location specified (default is /usr/local/pgsql).

  7. Tell your machine where the libraries are located. You can do this by setting the LD_LIBRARY_PATH environmental variable to the <BASEDIR>/lib path or by editing the /etc/ld.so.conf file to include it.

  8. Include the <BASEDIR>/bin path in the users or systems search path (for example, /etc/profile).

  9. Create the directory to hold the databases, change the ownership to the DBA, and initialize the location. If a user named postgres exists, your code should look like the following:

    # mkdir /usr/local/pgsql/data
    # chown postgres /usr/local/pgsql/data
    # su - postgres
  10. Start the postmaster server (as the DBA account) in the background. Specify the data directory that you just created in step 9:

    $/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data &
  11. As the DBA, create the users that you need using the createuser command.

  12. Switch to the user you created and create the database(s) needed (for example, createdb).

Package-Based Installation

Essentially, package-based installations (such as RPM or DEB) automate the steps outlined in the source-install process. Nonetheless, it is still a good idea to study the process so that you understand what the package is doing to your system.

Depending on the package management system installed on your machine, the commands will be different. The following assumes you have RPM-based package management tools. However, the Debian package management system is very similar in concept.

  1. Download the list of RPM files that you require from ftp.postgresql.org/pub/binary.

  2. Install files using the appropriate command, such as rpm -Uvh *.rpm.

  3. Verify that a superuser for PostgreSQL was created by examining /etc/passwd (or the equivalent). Switch to the DBA user account (typically postgres), and create the users that you need (such as createuser web).

  4. Switch to that user and create the working database and tables (for example, createdb web site).

Creating Users

Before creating your database tables and schema, it is important to create the database users that you will need. In many cases (such as using a database for a web site), it is sufficient to have a single database user.

Database users are entities that are separate from regular operating system users, and PostgreSQL provides command-line tools for creating and administrating user and user-rights.

To create users, the easiest way is to utilize the command-line utility createuser. Alternatively, a user can be created from within a psql interactive session.

To create a user from the command line, type the following:

$createuser web
$Shall the new user be allowed to create databases (y/n)? N
$Shall the new user be allowed to create users (y/n)? N

Or, from a psql session, you can type the following:


Creating a user from a SQL session enables some additional options that are not available from the command-line utility. For instance, passwords, group membership, and account expiration all can be set from this method.


Groups are just an extension to the concept of users. PostgreSQL allows users to be collected into logical groups for easier permission management. To create a group, the following command should be entered in an SQL session:

psql=>CREATE GROUP webusers;

Then users can be added or removed from the group as follows:

psql=>ALTER GROUP webusers ADD USER bill, mary, amy, jane;
psql=>ALTER GROUP webusers DROP USER mary;
  • + Share This
  • 🔖 Save To Your Account