Before I go much further, let's talk about installing PostgreSQL. Chapters 21, "PostgreSQL Administration," and 23, "Security," discuss PostgreSQL installation in detail, but I'll show you a typical installation procedure here.

When you install PostgreSQL, you can start with prebuilt binaries or you can compile PostgreSQL from source code. In this chapter, I'll show you how to install PostgreSQL on a Linux host starting from prebuilt binaries. If you decide to install PostgreSQL from source code, many of the steps are the same. I'll show you how to build PostgreSQL from source code in Chapter 21.

In older versions of PostgreSQL, you could run the PostgreSQL server on a Windows host but you had to install a Unix-like infrastructure (Cygwin) first: PostgreSQL wasn't a native Windows application. Starting with PostgreSQL version 8.0, the PostgreSQL server has been ported to the Windows environment as a native-Windows application. Installing PostgreSQL on a Windows server is very simple; simply download and run the installer program. You do have a few choices to make, and we cover the entire procedure in Chapter 21.

Installing PostgreSQL Using an RPM

The easiest way to install PostgreSQL is to use a prebuilt RPM package. RPM is the Red Hat Package Manager. It's a software package designed to install (and manage) other software packages. If you choose to install using some method other than RPM, consult the documentation that comes with the distribution you are using.

PostgreSQL is distributed as a collection of RPM packagesyou don't have to install all the packages to use PostgreSQL. Table 1.1 lists the RPM packages available as of release 7.4.5.

Table 1.1. PostgreSQL RPM Packages as of Release 7.4.5




Clients, libraries, and documentation


Programs (and data files) required to run a server


Files required to create new client applications


JDBC driver for PostgreSQL


Tcl client and PL/Tcl


PostgreSQL's Python library


Regression test suite for PostgreSQL


Shared libraries for client applications


Extra documentation not included in the postgresql base package


Contributed software

Don't worry if you don't know which of these you need; I'll explain most of the packages in later chapters. You can start working with PostgreSQL by downloading the postgresql, postgresql-libs, and postgresql-server packages. The actual files (at the website) have names that include a version number: postgresql-7.4.5-2PGDG.i686.rpm, for example.

I strongly recommend creating an empty directory, and then downloading the PostgreSQL packages into that directory. That way you can install all the PostgreSQL packages with a single command.

After you have downloaded the desired packages, use the rpm command to perform the installation procedure. You must have superuser privileges to install PostgreSQL.

To install the PostgreSQL packages, cd into the directory that contains the package files and issue the following command:

# rpm -ihv *.rpm

The rpm command installs all the packages in your current directory. You should see results similar to what is shown in Figure 1.3.

Figure 1.3. Using the rpm command to install PostgreSQL.

The RPM installer should have created a new user (named postgres) for your system. This user ID exists so that all database files accessed by PostgreSQL can be owned by a single user.

Each RPM package is composed of many files. You can view the list of files installed for a given package using the rpm -ql command:

# rpm -ql postgresql-server
# rpm -ql postgresql-libs

At this point (assuming that everything worked), you have installed PostgreSQL on your system. Now it's time to create a database to play, er, work in.

While you have superuser privileges, issue the following commands:

# su - postgres
bash-2.04$ echo $PGDATA
bash-2.04$ initdb

The first command (su - postgres) changes your identity from the OS superuser (root) to the PostgreSQL superuser (postgres). The second command (echo $PGDATA) shows you where the PostgreSQL data files will be created. The final command creates the two prototype databases (template0 and template1).

You should get output that looks like that shown in Figure 1.4.

Figure 1.4. Creating the prototype databases using initdb.

You now have two empty databases named template0 and template1. You really should not create new tables in either of these databasesa template database contains all the data required to create other databases. In other words, template0 and template1 act as prototypes for creating other databases. Instead, let's create a database that you can play in. First, start the postmaster process. The postmaster is a program that listens for connection requests coming from client applications. When a connection request arrives, the postmaster starts a new server process. You can't do anything in PostgreSQL without a postmaster. Figure 1.5 shows you how to get the postmaster started.

Figure 1.5. Creating a new database with createdb.

After starting the postmaster, use the createdb command to create the movies database (this is also shown in Figure 1.5). Most of the examples in this book take place in the movies database.

Notice that I used the pg_ctl command to start the postmaster[1].

[1] You can also arrange for the postmaster to start whenever you boot your computer, but the exact instructions vary depending on which operating system you are using. See the section titled "Arranging for PostgreSQL Startup and Shutdown" in Chapter 21

The pg_ctl program makes it easy to start and stop the postmaster. To see a full description of the pg_ctl command, enter the command pg_ctl --help. You will get the output shown in Figure 1.6.

Figure 1.6. pg_ctl options.

If you use a recent RPM file to install PostgreSQL, the two previous steps (initdb and pg_ctl start) can be automated. If you find a file named postgresql in the /etc/rc.d/init.d directory, you can use that shell script to initialize the database and start the postmaster. The /etc/rc.d/init.d/postgresql script can be invoked with any of the command-line options shown in Table 1.2.

Table 1.2. /etc/rc.d/init.d/postgresql Options




Start the postmaster


Stop the postmaster


Display the process ID of the postmaster if it is running


Stop and then start the postmaster


Force the postmaster to reread its configuration files without performing a full restart

At this point, you should use the createuser command to tell PostgreSQL which users are allowed to access your database. Let's allow the user 'bruce' into our system (see Figure 1.7).

Figure 1.7. Creating a new PostgreSQL user.

That's it! You now have a PostgreSQL database up and running.

Part I: General PostgreSQL Use

Introduction to PostgreSQL and SQL

Working with Data in PostgreSQL

PostgreSQL SQL Syntax and Use


Part II: Programming with PostgreSQL

Introduction to PostgreSQL Programming

Extending PostgreSQL


The PostgreSQL C APIlibpq

A Simpler C APIlibpgeasy

The New PostgreSQL C++ APIlibpqxx

Embedding SQL Commands in C Programsecpg

Using PostgreSQL from an ODBC Client Application

Using PostgreSQL from a Java Client Application

Using PostgreSQL with Perl

Using PostgreSQL with PHP

Using PostgreSQL with Tcl and Tcl/Tk

Using PostgreSQL with Python

Npgsql: The .NET Data Provider

Other Useful Programming Tools

Part III: PostgreSQL Administration

Introduction to PostgreSQL Administration

PostgreSQL Administration

Internationalization and Localization


Replicating PostgreSQL Data with Slony

Contributed Modules


show all menu

PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
ISBN: 735712573
Year: 2004
Pages: 261 © 2008-2017.
If you may any questions please contact us: