Creating and running a database with PostgreSQL

Setup the Run-time Environment

On unix, the database files should be owned by a single user, and the database running under that user.

In our setup, we created the user postgres, which will run and own the database files. It is convenient to add the PostgreSQL binary path to the PATH environment variable for this user.

Choose a directory for your data. We chose /home/postgres/data

For convenience, we set the environment variable PGDATA to /home/postgres/data. Otherwise it should be specified with the -D option.

We setup the environment for this user using the following .profile script

export PATH=$PATH:/usr/local/pgsql/bin
export PGDATA=/home/postgres/data

If you are using Cygwin, then you will not need to set your PATH, but you will need to choose a PGDATA. You will also need to run the Inter-Process Communication daemon before starting postgres.

ipc-daemon2 &
export PGDATA=/cygdrive/d/jurn/postgresdb/data

Create a database cluster

A cluster may contain many databases. It is not a cluster of servers.

Initialise the instance.

If the environment variables are already set, you can run initdb to initialise a database.

bash-2.05a$ initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale C.

creating directory /home/postgres/data... ok
creating directory /home/postgres/data/base... ok
creating directory /home/postgres/data/global... ok
creating directory /home/postgres/data/pg_xlog... ok
creating directory /home/postgres/data/pg_clog... ok
selecting default max_connections... 100
selecting default shared_buffers... 1000
creating configuration files... ok
creating template1 database in /home/postgres/data/base/1... ok
initializing pg_shadow... ok
enabling unlimited row size for system tables... ok
initializing pg_depend... ok
creating system views... ok
loading pg_description... ok
creating conversions... ok
setting privileges on built-in objects... ok
creating information schema... ok
vacuuming database template1... ok
copying template1 to template0... ok

Success. You can now start the database server using:

    /usr/local/pgsql/bin/postmaster -D /home/postgres/data
or
    /usr/local/pgsql/bin/pg_ctl -D /home/postgres/data -l logfile start

Starting and Stopping the cluster

Before actions such as creating a database can be used, you must start the database.

Starting PostgreSQL

A basic start command:

bash-2.05a$ pg_ctl start -l /home/postgres/logfile
postmaster successfully started

However, this will only allow local connections through unix domain sockets. If you needed to share this instance over the network, you must add the -i option to enable TCP connections. If you do this, it will by default bind to all addresses. Make sure your PostgreSQL installation is not directly exposed to the internet by binding the ports to the localhost or lan address by using the -h option. If your PostgreSQL instance must be accessible from the internet, then utilise ssh to create a secure tunnel to the host and forward the appropriate ports. The PostgreSQL Documentation explains this in detail.

The example below listens on the LAN address 192.168.1.1:

bash-2.05a$ pg_ctl start -l /home/postgres/logfile -o "-i -h 192.168.1.1"
postmaster successfully started

The example below listens on localhost or 127.0.0.1:

bash-2.05a$ pg_ctl start -l /home/postgres/logfile -o "-i -h localhost"
postmaster successfully started

Stopping PostgreSQL

bash-2.05a$ pg_ctl stop
waiting for postmaster to shut down.....done
postmaster successfully shut down

Sample init.d script

init.d

#!/bin/sh
#
# postgresql
#
# variables
PGDTA=/home/postgres/data
LOGFILE=/home/postgres/logfile
OPTIONS="-i -h localhost"
PG_CTL=/usr/local/pgsql/bin/pg_ctl

# See how we were called.
case "$1" in
  start)
        # Start daemon.
        su - postgres -c "$PG_CTL start -D $PGDTA -l $LOGFILE -o \"$OPTIONS\""
        ;;
  stop)
        su - postgres -c "$PG_CTL stop -D $PGDTA"
        ;;
  status)
        su - postgres -c "$PG_CTL status -D $PGDTA"
        ;;
  reload)
        su - postgres -c "$PG_CTL reload -D $PGDTA"
        ;;
  *)
        echo "Usage: $0 {start|stop|status|reload}"
        exit 1
esac

exit 0

Further Setup

See User and Authentication for more information on how to setup users.