Prerequisites

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

Package	Description
postgresql	Clients, libraries, and documentation
postgresql-server	Programs (and data files) required to run a server
postgresql-devel	Files required to create new client applications
postgresql-jdbc	JDBC driver for PostgreSQL
postgresql-tcl	Tcl client and PL/Tcl
postgresql-python	PostgreSQL's Python library
postgresql-test	Regression test suite for PostgreSQL
postgresql-libs	Shared libraries for client applications
postgresql-docs	Extra documentation not included in the postgresql base package
postgresql-contrib	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 www.postgresql.org 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
/etc/rc.d/init.d/postgresql
/usr/bin/initdb
/usr/bin/initlocation
...
/var/lib/pgsql/data
# rpm -ql postgresql-libs
/usr/lib/libecpg.so.3
/usr/lib/libecpg.so.3.2.0
/usr/lib/libpgeasy.so.2
...
/usr/lib/libpq.so.2.1

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
/var/lib/pgsql/data
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

Option	Description
start	Start the postmaster
stop	Stop the postmaster
status	Display the process ID of the postmaster if it is running
restart	Stop and then start the postmaster
reload	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.