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.
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
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.
 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.
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
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
Internationalization and Localization
Replicating PostgreSQL Data with Slony