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
documentation.
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
postgresql-7.4.6.tar.bz2. The base/docs/opt/test
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
around in /usr instead. The details don't matter much, as
long as you know where to find the psql
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
system-specific questions.)
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
postgres
or 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 initdb.
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 PGDATA
directory. If you installed from source, you can use pg_ctl
instead, or one of the scripts from contrib/start-scripts.
By default, initdb sets the default database character
encoding to 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
encoding (e.g. UNICODE) can be set while creating a
database, but cannot be changed thereafter.
After an initdb, the 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
point: set tcpip_socket=true to make Postgres accept TCP/IP
connections, and adjust the log_* settings to suit you. (In
particular, 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
the new listen_addresses variable.)
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
strategy.
That's it.
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 databases,
and anyone who has permission to do so can access
the database.
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
or
/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:
CREATE USER
-- Set up my database:
$ createdb --owner=ams --encoding=UNICODE ams
CREATE DATABASE
$ logout
As myself (user ams):
$ psql
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
ams=>
Please read the chapter on server
administration in the Postgres manual.