PostgreSQL Installation Guide

By Abhijit Menon-Sen <ams@toroid.org>

2009-05-11

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.

Installation

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.

initdb

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.

The data directory

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.

Routine maintenance

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.

What now?

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.