Update, 2009-05-10 This page is meant for PostgreSQL 7.4.x, which
is now ancient (8.3 being the current version, with 8.4 in beta). These
instructions may still help you get an idea of what is needed, but some
details will certainly vary.
This web page is meant to help you to set up PostgreSQL for the first time.
It tries to focus on important steps in the process, to complement the
level of detail in the Postgres
The latest stable Postgres release at the time of writing (2005-01-10)
is v7.4.6, and the 8.0 release is expected in a few days. I'll assume
that you're trying to set up 7.4.x or later; and, because it's all I'm
familiar with, that you're using some form of Unix.
Every system has different preferences when it comes to getting things
installed in the right place, so I won't say much about it here. If a
binary package is available for your system, it may be convenient to
use it. If not (in my case, for example, vendor RPMs for 7.4.6 were
not yet available), you can always compile
and install from source. (You need to download
files are just the big package split into bite-sized chunks.)
No matter how it's done, your PostgreSQL installation will end up with
a fairly predictable layout. While installing from source, it's common
to put everything under, say,
/usr/local/pgsql. If you're
using a vendor package, it's likely that the same files will be spread
/usr instead. The details don't matter much, as
long as you know where to find the
program and the documentation.
I shall assume that you either found a binary package, or followed the
instructions for installing
from source in the Postgres manual. (The FAQs may help with
There are three important things to know about your installation:
- The superuser
During installation, a new user is created to serve as the Postgres
superuser. The server runs as this user, and the account is used to
perform administrative tasks. It's usually named
pgsql (and it usually doesn't have a valid password,
since it is only used by root via "su postgres").
- The data directory
Before you can start Postgres, the superuser must initialise a "database
cluster" (which everyone except the documentation refers to as the
data directory, or
$PGDATA) by running
Binary packages often arrange to run
initdb somewhere in
/var before starting the server for the first time
/var/lib/pgsql/data on my system).
- Starting and stopping the server
Vendor packages usually install a script to start
and stop the server (
/etc/init.d/postgresql on my
system); it should tell you the superuser name and the
directory. If you installed from source, you can use
instead, or one of the scripts from
initdb sets the default database character
SQL_ASCII (which is inappropriate if you
ever intend to store non-ASCII data), and derives locale
settings from the environment (
LANG=en_US.UTF-8 on my
system). Since some locale settings cannot be changed afterwards; and
because they determine, among other things, how strings are sorted, you
should choose a locale to suit the data you plan to store. The character
UNICODE) can be set while creating a
database, but cannot be changed thereafter.
PGDATA directory contains
an initial set of server configuration files, as well as the
template databases. You can start the server immediately, but you
may want to have a look at
postgresql.conf (general server configuration) and
pg_hba.conf (client authentication controls) first.
There are two reasons why you might want to edit postgresql.conf at this
tcpip_socket=true to make Postgres accept TCP/IP
connections, and adjust the
log_* settings to suit you. (In
log_statement=true causes the server to log all
SQL statements). (The configuration in 8.0 is a little different. For
example, TCP/IP connections are accepted by default, and controlled by
You may want to edit
pg_hba.conf to restrict access to
selected clients. By default, local users are trusted implicitly. If you
want to connect via TCP/IP, you'll need to add a host entry, as
explained by the comments in the file.
One very important — and often overlooked — subject is the
need to periodically VACUUM the database to avoid potential data loss
due to transaction ID wraparound, to reclaim the disk space used by
deleted rows, and to update planner statistics. The section on routine
maintenance in the manual has more details.
This is also the right time to give some consideration to a backup
The superuser can now run
pg_ctl start to start the postmaster
(or root can run the system's init script: something like
/etc/init.d/postgresql start); then create users
and anyone who has permission to do so can access
Here's how I got started:
As user postgres:
$ export PGDATA=/var/lib/pgsql/data
$ initdb --locale=en_US.UTF-8 --encoding=UNICODE
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 en_US.UTF-8.
-- (Lots of initdb output snipped.)
Success. You can now start the database server using:
/home/ams/inst/bin/postmaster -D /var/lib/pgsql/data
/home/ams/inst/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start
-- (I trust hosts on my internal network.)
$ echo "tcpip_socket=true" >> $PGDATA/postgresql.conf
$ echo "host ams ams 10.0.0.0/8 md5" >> $PGDATA/pg_hba.conf
$ pg_ctl -l logfile start
postmaster successfully started
-- Set up an account for myself:
$ createuser --no-adduser --no-createdb --pwprompt --encrypted ams
Enter password for new user:
Enter it again:
-- Set up my database:
$ createdb --owner=ams --encoding=UNICODE ams
As myself (user ams):
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
Please read the chapter on server
administration in the Postgres manual.