PostgreSQL setup primer

Introduction

This document attempts to provide a clear and consise setup procedure for PostgreSQL installations. It is geared to a Linux setup and we assume that the user is installing PostgreSQL in the default location (/usr/local/pgsql). We also assume that the user is using Bash. One can easily use the information provided to use another shell, installation location or OS.

Pre-installation

These are things that must be done before installation

  • Create a new group named postgres
  • Create a new user named postgres with a home directory of /usr/local/pgsql and add this user to the postgres group created above.

    Do installation

    This entails grabbing the source, compiling it and installing it.

  • Get the source for PostgreSQL 7.1.2 from http://www.postgresql.org
  • Extract tarball (cd /tmp ; tar xvzf postgresql-7.1.2.tar.gz)
  • cd /tmp/postgresql-7.1.2
  • Set some environment variables: 
    export PGLIB=/usr/local/pgsql/lib
export PATH=/usr/local/pgsql/bin:$PATH
export JAVA_HOME=/usr/java
export ANT_HOME=/usr/local/jakarta-ant-1.3
export PATH=$ANT_HOME/bin:$PATH
  • Run the configure script that comes with PostgreSQL and tell it you want to compile Tcl, Perl and Java support: 
    ./configure --with-tcl --with-java --with-perl
  • Run Gmake -- not any other make tool: 
    gmake
  • Assuming that the gmake was fine with no errors, install the software: 
     gmake install

    This installs the software into /usr/local/pgsql (by default). This usually needs to be done as root because usually normal users cannot create directories in /usr/local. Once the software has been installed, change ownership and group ownership of the /usr/local/pgsql directory structure to the postgres user and group you just created. Since this directory will be the home directory for postgres, that user must own everything in that directory: 

    cd /usr/local
chown -R postgres pgsql
chgrp -R postgres pgsql

    Create database

    This creates the data files associated with a PostgreSQL installation. The initdb only creates the initial set of files needed for the software. The createdb command is the command one would use to create a new logical database instance.

  • Login as postgres
  • Set the PGDATA environment variable to point to your data files. One can also use the -D switch to all PostgreSQL commands (psql, pg_ctl, etc) but setting PGDATA makes this unneccessary. Ideally, this should be set in the .bash_profile startup script in the home directory of the postgres user. 
     export PGDATA=/usr/local/pgsql/data
  • Create the initial set of data files needed for PostgreSQL to run: 
    initdb -D /usr/local/pgsql/data
  • Startup the PostgreSQL server. One can run the postmaster daemon directly but a convenience script exists called pg_ctl that provides the ability to name a log file for all server output and error messages. This script also makes it easy to restart or stop a postmaster daemon. 
    pg_ctl -l ~/server.log start
  • Create a database. The initdb command creates a default database named template1. It is best to create separate instances for whatever databases one would like to setup. 
    createdb webapps

    Setup user(s) along with remote connections

  • Use the command-line SQL interpretor for PostgreSQL to change the password of the postgres user: 
    [postgres@fw pgsql]$ psql test
Welcome to psql, 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

test=# alter user postgres with password 'password';
ALTER USER
test=# \q
[postgres@fw pgsql]$
  • Setup remote connections for PostgreSQL. Each user must be added in the database and in the password file in the $PGDATA directory by using the pg_passwd utility. The pg_passwd utility uses a password file to handle remote connections to the database. The first time it is run, it will create the password file. The file can be named anything but convention says that one should name it passwd to be consistant. An example of using the pg_passwd utility follows: 
    [postgres@fw pgsql]$ cd $PGDATA
[postgres@fw data]$ pg_passwd passwd
Username: postgres
New password: 
Re-enter new password: 
[postgres@fw data]$
  • Add remote connection info to the PostgreSQL HBA config file. The pg_hba.conf file contains access information for PostgreSQL. By default, upon installation anyone on a local machine can connect using psql. To setup remote connections, one must modify pg_hba.conf and run the postmaster daemon with an additional command-line option. To do this, edit pg_hba.conf and add the following line before any additional configuration lines in the file. The default config file has a lot of comments in the beginning and three config lines at the end of the file: 
     host  all  0.0.0.0 0.0.0.0 password passwd

    This tells the postmaster to use host-based authentication and allow all hosts to connect in this manner. It also tells it to use password-based challenges and to use the file named passwd in the $PGDATA directory (by default) to read users and passwords from. There are other authenication methods but this provides basic password-based authentication over non-secure links (although secure connections are possible as well).

  • Make sure postmaster runs with a -i so TCP connections can be made. This entails creating a file called $PGDATA/postmaster.opts.default that only contains the following line: 
    '-i'

    This line consists of a dash lowercase I with single forward quotes around it. This file defines command-line options that are added to the postmaster command line when it is run using the pg_ctl script. The -i switch tells the postmaster to accept remote connections.

    Conclusion

  • At this point, PostgreSQL should be setup and working properly. You can use PgAdmin along with the PostgreSQL ODBC driver to connect to a remote (or local) PostgreSQL installation to administer the instances on that machine. One can also use the PgAccess utility that isprovided with PostgreSQL to administer the database. PgAccess is a Tcl/Tk based tool while PgAdmin is a Windoze-only based tool with a few more features including some import capabilities.

    Links

    PostgreSQL.org
    Great Bridge - PostgreSQL.com - commercial support
    PgAdmin
    PostgreSQL ODBC drivers
    Created by Benny Morrison