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.

Part I: General PostgreSQL Use

Introduction to PostgreSQL and SQL

Working with Data in PostgreSQL

PostgreSQL SQL Syntax and Use

Performance

Part II: Programming with PostgreSQL

Introduction to PostgreSQL Programming

Extending PostgreSQL

PL/pgSQL

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

Security

Replicating PostgreSQL Data with Slony

Contributed Modules

Index





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
EAN: N/A
Year: 2004
Pages: 261
Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net