Table 32 PostgreSQL Files and Directories

Directory

Contents

/usr/internet/pgsql/man

Location of PostgreSQL reference pages.

/usr/internet/pgsql/doc

Location of the PostgreSQL documentation.

/usr/internet/pgsql/bin/

Location of the PostgreSQL commands.

/usr/internet/pgsql/

Home directory of the PostgreSQL account where all files

 

and directories are installed.

/usr/internet/pgsql/.profile

Contains a set of environment variable definitions for

 

running most PostgreSQL commands.

/usr/internet/pgsql/bin/pg_wrap

Command for defining the environment and running any

 

PostgreSQL commands.

/usr/internet/pgsql/data/pg_hba.conf

File for configuring access control to a PostgreSQL

 

database.

/usr/internet/pgsql/jdbc

Contains the pre-compiled jdbc drivers.

 

 

Running the Postmaster Startup Script

The postmaster daemon manages the communication between frontend and backend processes. It automatically runs as a background process when you install PostgreSQL and when you reboot. (Postmaster does not interact with the user.) Only one postmaster should be running at a time in a given PostgreSQL installation. An installation means a database directory and postmaster port number. You can run more than one postmaster on a machine only if each one has a separate directory and port number.

Internet Express provides a startup script for postmaster in /sbin/init.d/postgres and a symbolic link in /sbin/rc3.d/S70postgres, which ensures that the postmaster daemon always runs.

Using Existing PostgreSQL Accounts

The Internet Express installation procedure creates a default PostgreSQL account (postgres). If you already have an existing account, possibly in another directory, use the pg_wrap command in /usr/internet/pgsql/bin/pg_wrap as a prefix to any other PostgreSQL command you run. This command resets the environment variables to the existing account.

Setting up a Crontab Entry for Vacuuming Databases

As part of routine database maintenance and to ensure that PostgreSQL runs efficiently, periodically run a vacuum on your database.

Vacuuming a database allows you to:

•Recover disk space occupied by updated or deleted rows in the database.

Because data affected by update or delete procedures are not immediately removed by PostgreSQL, you can remove the data by periodically vacuuming the database. When you run the vacuum on your database, disk space is opened for reuse and disk space requirement are kept low.

•Protect against loss of data due to transaction ID wrap-around.

PostgreSQL assigns each transaction a unique ID, which determines the chronology of transactions. The ID has a fixed length of 32 bits. When the server has been running for a long time (more than 4 billion transactions), the transaction ID will eventually wrap to zero. This can cause old transactions to reappear, resulting in catastrophic data loss. Periodically vacuuming the database prevents this type of transaction ID wrap-around.

252 PostgreSQL Database and MySQL Administration