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
#!/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.