PostgreSQL Install FAQ

Purpose: To help a new user of PostgreSQL to advance succesfully through a fresh install of PostgreSQL from start to finish. This document includes a blow by blow account of two separate installs of PostgreSQL. One on a fully updated Red Hat 5.1 box and one on a freshly installed and partially updated Red Hat 6.0 box.

Preface: PostgreSQL is a fully Open Source implementation of an SQL server.

For the sessions which follow, PostgreSQL had already been installed via RPM.

1. POSTGRESQL ERRORS ON SETUP (a transcription of a live session)

The /etc/passwd file shows this for user PostgreSQL:

postgres:x:100:233:PostgreSQL Server:/var/lib/pgsql:/bin/bash

[root@ns /home]# su - postgres

[postgres@ns pgsql]$ pwd
/var/lib/pgsql

[postgres@ns pgsql]$ initdb <-- Note: DO NOT RUN THIS AS ROOT! BAD THINGS HAPPEN! initdb does not find the file
'/usr/lib/pgsql/local1_template1.bki.source/local1_template1.bki.source'.
This means you have identified an invalid PGLIB directory.
You specify a PGLIB directory with a --pglib invocation
option, a PGLIB environment variable, or a postconfig program.

[postgres@ns pgsql]$ vi .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then

. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin
PATH=$PATH:/usr/lib/pgsql/bin
MANPATH=$MANPATH:/usr/lib/pgsql/man
PGLIB=/usr/lib/pgsql/
PGDATA=/usr/lib/pgsql/data
ENV=$HOME/.bashrc
USERNAME=""

export USERNAME ENV PATH MANPATH PGLIB PGDATA

[SHIFT]ZZ

[postgres@ns pgsql]$ exit
logout
[root@ns /home]# su - postgres
[postgres@ns pgsql]$ initdb
initdb: using /usr/lib/pgsql//local1_template1.bki.source as input to create the
template database.
initdb: using /usr/lib/pgsql//global1.bki.source as input to create the global
classes.
initdb: using /usr/lib/pgsql//pg_hba.conf.sample as the host-based
authentication control file.

We are initializing the database system with username postgres (uid=100). This user will own all the files and must also own the server process.

Creating Postgres database system directory /usr/local/pgsql/data

mkdir: cannot make directory `/usr/local/pgsql/data': No such file or directory [postgres@ns pgsql]$ exit
logout
[root@ns /home]# chown -Rf postgres.postgres /usr/lib/pgsql/
[root@ns /home]# su - postgres
[postgres@ns pgsql]$ initdb
initdb: using /usr/lib/pgsql//local1_template1.bki.source as input to create the
template database.
initdb: using /usr/lib/pgsql//global1.bki.source as input to create the global
classes.
initdb: using /usr/lib/pgsql//pg_hba.conf.sample as the host-based
authentication control file.

We are initializing the database system with username postgres (uid=100).
This user will own all the files and must also own the server process.

Creating Postgres database system directory /usr/lib/pgsql/data

Creating Postgres database system directory /usr/lib/pgsql/data/base

initdb: creating template database in /usr/lib/pgsql/data/base/template1
Running: postgres -boot -C -F -D/usr/lib/pgsql/data -Q template1

Creating global classes in /base
Running: postgres -boot -C -F -D/usr/lib/pgsql/data -Q template1

Adding template1 database to pg_database...
Running: postgres -boot -C -F -D/usr/lib/pgsql/data -Q template1 <
/tmp/create.1994

vacuuming template1
creating public pg_user view
loading pg_description

^^^^^^^^^ SUCCESS!

2. SECOND ITERATION

The /etc/passwd file shows this for user postgres:

postgres:x:101:234:PostgreSQL Server:/var/lib/pgsql:/bin/bash

[root@ntbox /root]# su - postgres
[postgres@ntbox pgsql]$ pwd
/var/lib/pgsql
[postgres@ntbox pgsql]$ ls -al
total 3

drwxr-xr-x   2 postgres postgres     1024 Sep 19 01:06 .
drwxr-xr-x 11 root root 1024 Sep 6 23:59 ..
-rw------- 1 postgres postgres 20 Sep 19 01:06 .bash_history

[postgres@ntbox pgsql]$ initdb
initdb does not know where to find the files that make up
Postgres (the PGLIB directory). You must identify the PGLIB
directory either with a --pglib invocation option, or by
setting the PGLIB environment variable, or by having a program
called 'postconfig' in your search path that outputs an asignment
for PGLIB.
[postgres@ntbox pgsql]$ cp /etc/skel/.bash_profile .
[postgres@ntbox pgsql]$ cp /etc/skel/.bashrc .
[postgres@ntbox pgsql]$ vi .bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then

. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin
PATH=$PATH:/usr/lib/pgsql/bin
MANPATH=$MANPATH:/usr/lib/pgsql/man
PGLIB=/usr/lib/pgsql/
PGDATA=/usr/lib/pgsql/data
ENV=$HOME/.bashrc
USERNAME=""

export USERNAME ENV PATH MANPATH PGLIB PGDATA

[SHIFT] ZZ

[postgres@ntbox pgsql]$ initdb

We are initializing the database system with username postgres (uid=101).
This user will own all the files and must also own the server process.

Creating Postgres database system directory /usr/lib/pgsql/data

mkdir: cannot make directory `/usr/lib/pgsql/data': Permission denied
[postgres@ntbox pgsql]$ exit
logout
[root@ntbox /root]# chown -Rf postgres.postgres /usr/lib/pgsql/
[root@ntbox /root]# su - postgres
[postgres@ntbox pgsql]$ initdb

We are initializing the database system with username postgres (uid=101).
This user will own all the files and must also own the server process.

Creating Postgres database system directory /usr/lib/pgsql/data

Creating Postgres database system directory /usr/lib/pgsql/data/base

Creating template database in /usr/lib/pgsql/data/base/template1

Creating global classes in /usr/lib/pgsql/data/base

Adding template1 database to pg_database...

Vacuuming template1
Creating public pg_user view
Creating view pg_rules
Creating view pg_views
Creating view pg_tables
Creating view pg_indexes
Loading pg_description

^^^^^^^^^ SUCCESS!

3. MORE ERRORS

[root@ntbox template1]# /etc/rc.d/init.d/postgresql start
Starting postgresql service: /usr/bin/postmaster does not find the database
system. Expected to find it in the PGDATA directory
"/usr/lib/pgsql/data/base/template1", but unable to open file with pathname
"/usr/lib/pgsql/data/base/template1/base/template1/pg_class".

No data directory -- can't proceed.
postmaster []

vi /etc/rc.d/init.d/postgresql

Find this line:

su -l postgres -c '/usr/bin/postmaster -S -D/usr/lib/pgsql'

and change it to this:

su -l postgres -c '/usr/bin/postmaster -S -D/usr/lib/pgsql/data'

[root@ntbox template1]# /etc/rc.d/init.d/postgresql start
Starting postgresql service: postmaster [1964]

As you can see postgre starts and runs now.

4. MORE CONFIGURATION

The next thing we need to do is configure the "access" file for PostgreSQL so that
people can use it.

cd /usr/lib/pgsql/data

vi pg_hba.conf

Set up permissions to access the database system. Do this by editing file
/usr/lib/pgsql/data/pg_hba.conf. The instructions are included in the file.
(If your database is not located in the default location, i.e. if PGDATA is set
to point elsewhere, then the location of this file will change accordingly.)
This file should be made read only again once you are finished. If you are
upgrading from PostgreSQL v6.0 or later you can copy file pg_hba.conf from your
old database on top of the one in your new database, rather than redoing the
file from scratch.

Now that you have PostgreSQL running lets test that it's operational. Make sure
you are logged in as user postgres or you will get this:

[root@ntbox data]# createdb
Connection to database 'template1' failed.
FATAL 1: SetUserId: user 'root' is not in 'pg_shadow'

createdb: database creation failed on root.

So if this happens then do this:

[root@ntbox data]# su - postgres

Now that you're logged in properly do this:

Create a database by typing:

[postgres@ntbox pgsql]$ createdb

Connect to the new database:

[postgres@ntbox pgsql]$ psql

Now you will see this:

Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL

type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: postgres

You type this: SELECT datetime 'now'; at the postgres=> prompt.

postgres=> SELECT datetime 'now'; Sun Sep 19 01:41:43 1999 EDT
(1 row)

...and you should see the above (with the proper date for your system, of course.

Now exit psql by typing this: \q

postgres=> \q

and now you're back to the command line prompt.

[postgres@ntbox pgsql]$

Your PostgreSQL system is now running and ready for customization.

For additional reference on installing PostgreSQL see the following: Installation Procedure using Source Code