While xTuple does publish a full Installer which provides PostgreSQL, the preferred method for implementing a production PostgreSQL server is to build PostgreSQL from source. Using the Linux distribution provided packages (apt, yum, rpm, etc) has the potential to introduce incompatibilities and curtail the migration options. The easiest way to support the customer is to keep things simple - and building PostgreSQL from source is simple. In this tutorial it is noted where the installation differs between RedHat/CentOS, Ubuntu and OSX.

The overall goal of this document is to allow someone with little or no Linux/OSX experience to install and configure PostgreSQL with confidence. A Guru once told me that “A 'Guru' is someone with five minutes more knowledge than you.” This document will start you on your journey to becoming a Guru.

Setting up the build environment

The first step is to make sure you have an appropriate environment to build PostgreSQL. Once completed, the framework for building and installing a vast majority of software from source will be enabled. Our requirements for building PostgreSQL are fairly simple. ( Taken from INSTALL located in the source)

• GNU make version 3.76.1 or later

• An ISO/ANSI C compiler

• The GNU Readline library

• The zlib compression library

• Kerberos, OpenSSL, OpenLDAP, and/or PAM, if you want to support authentication / encryption.

First, make sure the necessary packages for development on your platform are installed.

For RedHat, do:

yum install -y yum-fastestmirror
yum install -y bison-devel
yum install -y readline-devel
yum install -y zlib-devel
yum install -y openssl-devel wget
yum groupinstall -y 'Development Tools'

For Ubuntu, do:

Using the synaptic manager, install the developer tools.

For OSX, do:

Install the Xcode Tools, available on your OSX install CD 1, or download it from:  http://developer.apple.com/technology/Xcode.html

Now that we have installed the necessary tools on our platform, make a directory to contain the source files.  This directory may already exist. This is where the source for PostgreSQL will reside. It is recommended to keep it here, as it will be easier to reconfigure the compiled in options on the server if ever needed.

mkdir -p /usr/local/src

Change into the source directory.

cd /usr/local/src

Downloading , Extracting and building PostgreSQL

On all platforms, the ftp program should exist by default.  Download PostgreSQL using ftp like so...

:src root# ftp ftp.postgresql.org
Connected to ftp.postgresql.org.
220-
220- Welcome to the PostgreSQL FTP Server
220- ====================================
Name (ftp.postgresql.org:xtuple): anonymous
331 Please specify the password.
Password: name@company.com
ftp> cd pub
ftp> cd source
ftp> cd v8.3.7
ftp> bin
ftp> hash
ftp> get postgresql-8.3.7.tar.gz
ftp> ######################## While postgres downloads...
ftp> exit

 You could also obtain PostgreSQL with wget, curl, a web browser, etc. Here's a wget example.

wget http://wwwmaster.postgresql.org/redir/198/h/source/v8.3.7/postgresql-8.3.7.tar.gz -O /usr/local/src/

 While still in the /usr/local/src directory, extract and uncompress the source code using the 'tar' command:

tar zxvf postgresql-8.3.7.tar.gz

 Change into the postgres source directory

cd postgresql-8.3.7

The most important configure option is to build postgres with OpenSSL support. Execute `./configure -?` to read more about available options. From the command line below, we're specifying to install postgresql with OpenSSL and integer datetime support and to use default values for everything else. The default path for the application is /usr/local/pgsql. The `pgsql` directory is created by the install process later. The command `configure` configures the application for building, sets up the Makefiles with defaults and paths to other dependencies.

./configure –with-openssl --enable-integer-datetimes

 Once configure has completed without error, run `make`. This builds the application. On a reasonably new computer, this takes less than five minutes.

make

 Run `make install`. This installs the binaries and supporting pieces to the appropriate path, in this case, the default path of /usr/local/pgsql.

make install

 Next, we change into the contrib directory to make additional items. The “contribs” are contributed by various entities – people, corporations, etc. There are several contribs that we are interested in installing later on, so we need to build them.

cd /usr/local/src/postgresql-8.3.7/contrib/

 Run make all.

make all

 Run make install. These items end up in /usr/local/pgsql/share/contrib

make install

This is a momentous occasion! PostgreSQL is built and installed. Pat yourself on the back. Alas, there is still more work to do before we can turn it on. Take a look in /usr/local/pgsql and do an `ls` command.  Poke around, but don't get lost. You're about half way done.

Post Build Setup (You didn't think you could just build this thing and use it did you?)

Configuring the Start Scripts

Copy the startup script to the proper location. This is an important file, as it contains the hints to starting postgres in the correct path. This file needs to be edited if postgresql is installed in a non-default path. Linux is a handled differently than OSX.

 For RedHat and Ubuntu, do:

cp /usr/local/src/postgresql-8.3.7/contrib/start-scripts/linux /etc/init.d/postgresql

Then make the startup script executable.

chmod 775 /etc/init.d/postgresql

For RedHat, add the script to the Server's startup routine (init) with chkconfig like this:

chkconfig --add /etc/init.d/postgresql

 For Ubuntu, add the script to the Server's startup routine (init) with update-rc.d like so:

update-rc.d /etc/init.d/postgresql defaults

For OSX, there are differing methods to start postgres on server start-up depending on your version of OSX.

If you're running 10.3 and possibly 10.4, use the SystemStarter methodology. Read through the source code too.

cd /usr/local/src/postgresql-8.3.7/contrib/start-scripts/osx

Then execute the install.sh script located in there. It may do you some good to read through the script and see what it's doing.

/bin/sh ./install.sh

If you're running 10.5 and 10.6, the new and improved method for starting apps in 10.5 and 10.6 is to use a “launchd” script. Cut, paste and save the contents below as/Library/LaunchDaemons/org.postgresql.postgres.plist

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>Label</key>
<string>PostgreSQL</string>
<key>UserName</key>
<string>postgres</string>
<key>RunAtLoad</key>
<true/>
<key>EnvironmentVariables</key>
<dict>
<key>PGDATA</key>
<string>/usr/local/pgsql/data</string>
</dict>
<key>ProgramArguments</key>
<array>
<string>/usr/local/pgsql/bin/postgres</string>
</array>
<key>ServiceDescription</key>
<string>PostgreSQL Server</string>
</dict>
</plist>

For OSX, change the plist's permissions:

chown root /Library/LaunchDaemons/org.postgresql.postgres.plist

 And on OSX, to start/stop it, issue: (Don't do this step yet - here for reference)

sudo launchctl load /Library/LaunchDaemons/org.postgresql.postgres.plist
sudo launchctl unload /Library/LaunchDaemons/org.postgresql.postgres.plist

Add A Postgres User (service account)

Now, we need to add a postgres user.  This user runs the postgresql server.  Postgres will not run as root.

For RedHat and Ubuntu, do use the 'adduser' program.  This will fail harmlessly if the user exists.

adduser postgres -d /usr/local/pgsql

For OSX users first check to see if the user 'postgres' exists, if so, you're done.

dscl . -list /Users | grep postgres

 If no result returned (user does not exist), find the next available UID

dscl . -list /Users UniqueID | awk '{print $2}' | sort -n | tail -1 | awk '{print $1+1}'

 Use that number returned as NEWUID, it's the next UID. Below, replace NEWUID with the number.

dscl . create /Users/postgres uid NEWUID

 Create the postgres users' environment:

dscl . -create /Users/postgres UserShell /bin/bash
dscl . -create /Users/postgres NFSHomeDirectory /usr/local/pgsql
dscl . create /Groups/postgres gid NEWUID <----Enter the number from above
dscl . merge /Groups/postgres users "postgres"

*See: http://www.postgresql.org/docs/8.3/interactive/postgres-user.html for alternate OSX specific user creation options.

Add Paths to Binaries and Man Pages

Add the path information to the shell, so that postgres commands and manual pages can be more readily accessed.

For RedHat and Ubuntu, do:

echo 'PATH=$PATH:/usr/local/pgsql/bin; export PATH' > /etc/profile.d/postgresql.sh
echo 'MANPATH=$MANPATH:/usr/local/pgsql/man; export MANPATH >> /etc/profile.d/pgmanual.sh

chmod 775 /etc/profile.d/postgresql.sh
chmod 775 /etc/profile.d/pgmanual.sh

For OSX, do:

echo '/usr/local/pgsql/bin' > /etc/paths.d/postgresql
echo '/usr/local/pgsql/man' > /etc/manpaths.d/postgresql

Create a Logging Directory

Besides chewing up the available drive space, a log file provides useful debugging information and error collecting, and is highly configurable. The most popular way (and easier way) to configure logging is to use the stderr interface provided. You could also use the syslog facility. The options for controlling the logging are in the postgresql.conf file.

Make a logging directory and make sure postgres can read/write to it.

mkdir -p /var/log/pgsql
chown -R postgres:postgres /var/log/pgsql/

Create the PostgreSQL Database Cluster

This is the real heart and soul of the postgres database. It IS the postgres database. In high performance applications, this directory can exist on a separate array from the OS drives. For sake of simplicity, create the data directory under the pgsql directory, this can be named anything, but naming it 'data' is most common.  This is your postgres database cluster. This directory contains the main configuration files for your database – postgresql.conf and pg_hba.conf. Until the `initdb` command runs, those files do not exist and you cannot start the server until they do.

Make a directory to contain the databases.

mkdir /usr/local/pgsql/data

Change ownership of the data directory to postgres. PostgreSQL runs as the user 'postgres', not as root. This is important, as postgresql won't start if the data directory isn't owned by the postgres user.

chown -R postgres:postgres /usr/local/pgsql/data

 Execute the initdb script.  This prepares the cluster for use. This command is the same on all platforms. Our application uses UTF8.  SQL-ASCII may be encountered in the field from older OpenMFG installations. If so, please contact us, it is not advisable to change the encoding without sufficient testing (some db data can be dropped if converted incorrectly). Postgresql runs as a non-privledged user 'postgres' in this case. PostgreSQL does not run as the root user.

Lets log in as the postgres user. As root, issue:

su – postgres

And create the db cluster with `initdb`

/usr/local/pgsql/bin/initdb -U postgres -E=UTF8 /usr/local/pgsql/data

Editing the Configuration Files

Using the 'nano' editor, (or vi), modify the postgresql.conf to allow the installation to listen for remote connections. Also, while we're in here let's configure the logging to create the log file in /var/log/pgsql/. The main cause of not being able to connect to a PostgreSQL database is because of a misconfiguration in this file.

listen_addresses = '*'
port = 5432
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pgsql/'
log_filename = 'postgresql-%Y-%m-%d'
log_line_prefix = ' %t %d %u '

Now, edit the pg_hba.conf and configure some network rules. Add the line in red to match your LAN address range. Set access from other computers to use md5 authentication. You can also set the other methods to md5, (and others) but for managability, leave the local connections set to 'trust' for now. The order of rules in this file matters.

# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 192.168.0.0/24 md5
# IPv6 local connections:
host all all ::1/128 trust

Finally... Start PostgreSQL

Add postgres to the server's startup routine and start it. (as the 'root' user)

For RedHat and Ubuntu to start postgres, call the init script directly. (as root)

/etc/init.d/postgresql start

For OSX, use the 'launchctl' program, start postgresql. (as root)

launchctl load /Library/LaunchDaemons/org.postgresql.postgres.plist

Check to see if postgresql is running with the command `ps aux | grep postgres`, you should get output similar to the following:

xTupleAppleServer:~ admin$ ps aux | grep postgres
postgres 21486 0.0 0.0 80404 372 ?? Ss 4:59PM 0:00.19 postgres: stats collector process
postgres 21485 0.0 0.0 84340 588 ?? Ss 4:59PM 0:00.17 postgres: autovacuum launcher process
postgres 21484 0.0 0.0 84292 428 ?? Ss 4:59PM 0:00.52 postgres: wal writer process
postgres 21483 0.0 0.0 84292 500 ?? Ss 4:59PM 0:00.69 postgres: writer process
postgres 21481 0.0 0.0 80356 280 ?? Ss 4:59PM 0:00.16 postgres: logger process
postgres 21480 0.0 0.0 84292 1788 ?? Ss 4:59PM 0:00.20 /usr/local/pgsql/bin/postgres
admin 22200 0.0 0.0 590472 204 s000 R+ 6:50PM 0:00.00 grep postgres

Load Additional Functions

Now that we actually have a live running PostgreSQL server, let's take this opportunity to actually work with it. Let's start by loading some additional functions into template1 of the postgres install. Template1, template0 and postgres are databases that are created by default and can be used (basically copied) to ensure that new databases contain the same functions and structures (it's a template!).

PGCrypto is required for xTuple to function properly, so that should be loaded first.

/usr/local/pgsql/bin/psql -U postgres -p 5432 template1 < /usr/local/pgsql/share/contrib/pgcrypto.sql
-- The Functions below are optional --
/usr/local/pgsql/bin/psql -U postgres -p 5432 template1 < /usr/local/pgsql/share/contrib/adminpack.sql
/usr/local/pgsql/bin/psql -U postgres -p 5432 template1 < /usr/local/pgsql/share/contrib/pgstattuple.sql
/usr/local/pgsql/bin/psql -U postgres -p 5432 template1 < /usr/local/pgsql/share/contrib/sslinfo.sql

Connect to the Database

Now, we can connect to our freshly installed postgres database. The `psql` command is the postgres shell. Enter the following at the prompt:

psql -U postgres

 

Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=#

If you get the above output, you've successfully connected and are ready to install the xTuple databases, but first let's install the postgresql procedural language - PL/pgSQL

postgres=#CREATE LANGUAGE plpgsql;

Take a minute to admire your work... You've build postgresql from source, initialized a database cluster and connected to it, and all via the command line! Really, it's an accomplishment!  Now you can install the xTuple Databases.   Follow the steps located here: http://www.xtuple.org/InstallingTheDatabase

Here's an outline of what you've done (OSX Version)...

Last login: Tue Aug 25 20:19:43 2009 from 192.168.0.86
:~ xtuple$ sudo su -
:~ root# mkdir -p /usr/local/src
:~ root# cd /usr/local/src
:src root# ftp ftp.postgresql.org
:src root# tar zxvf postgresql-8.3.7.tar.gz
:src root# cd postgresql-8.3.7
:postgresql-8.3.7 root# ./configure --with-openssl --with-bonjour --with-gssapi && make && make install
:postgresql-8.3.7 root# cd contrib
:contrib root# make all && make install
:contrib root# nano /Library/LaunchDaemons/org.postgresql.postgres.plist
root# dscl . -list /Users | grep postgres
root# dscl . -list /Users UniqueID | awk '{print $2}' | sort -n | tail -1 | awk '{print $1+1}'
root# dscl . create /Users/postgres uid 509;
root# dscl . -create /Users/postgres UserShell /bin/bash;
root# dscl . -create /Users/postgres NFSHomeDirectory /usr/local/pgsql;
root# dscl . create /Groups/postgres gid postgres;
root# dscl . merge /Groups/postgres users "postgres";
:contrib root# echo '/usr/local/pgsql/bin' > /etc/paths.d/postgresql
:contrib root# echo '/usr/local/pgsql/man' > /etc/manpaths.d/postgresql
:contrib root# mkdir -p /var/log/pgsql
:contrib root# chown -R postgres:postgres /var/log/pgsql/
:contrib root# mkdir /usr/local/pgsql/data
:contrib root# chown -R postgres:postgres /usr/local/pgsql/data
:contrib root# su - postgres
:~ postgres$ /usr/local/pgsql/bin/initdb -U postgres -E=UTF8 /usr/local/pgsql/data
:~ postgres$ nano /usr/local/pgsql/data/postgresql.conf
:~ postgres$ nano /usr/local/pgsql/data/pg_hba.conf
     host all all 192.168.0.0/24 md5
:~ postgres$ exit
:contrib root# launchctl load /Library/LaunchDaemons/org.postgresql.postgres.plist <
:contrib root# ps aux | grep postgres
:contrib root# /usr/local/pgsql/bin/psql -U postgres -p 5432 template1 < /usr/local/pgsql/share/contrib/pgcrypto.sql
:contrib root# /usr/local/pgsql/bin/psql -U postgres -p 5432 template1 < /usr/local/pgsql/share/contrib/adminpack.sql
:contrib root# /usr/local/pgsql/bin/psql -U postgres -p 5432 template1 < /usr/local/pgsql/share/contrib/pgstattuple.sql
:contrib root# /usr/local/pgsql/bin/psql -U postgres -p 5432 template1 < /usr/local/pgsql/share/contrib/sslinfo.sql
:contrib root# psql -U postgres -h localhost -p 5432
postgres=# CREATE LANGUAGE plpgsql;
postgres=# \q
xtuples-mac-mini:contrib root# exit
xtuples-mac-mini:~ xtuple$ exit

Appendix

Interactive bash script to create postgres user on OSX from the shell.

#!/bin/bash
# This is an interactive shell script to create the postgresql user on OSX.
# As root, save the contents to xtpguser.sh and execute with `sh ./xtpguser.sh`
echo "HIT ENTER TO JUST ACCEPT THE DEFAULTS"
echo "What is the path to postgres install? default is [/usr/local/pgsql]:"
read NFSHOME
if [ "$NFSHOME" = "" ]; then
NFSHOME="/usr/local/pgsql"
fi
 
echo "What name do you want to call the PostgreSQL service user account, default is [postgres]:"
read USER
 
if [ "$USER" = "" ]; then
USER="postgres"
fi
 
# Eval if the user exists.
if dscl . -list /Users | grep ${USER} > /dev/null ; then
echo "${USER} exists already, exiting."
exit 0;
fi;
 
# User doesn't exist, so create them
USER_ID=`dscl . -list /Users UniqueID | awk '{print $2}' | sort -n | tail -1 | awk '{print $1+1}'`
echo "User does not exist, creating...";
dscl . create /Users/${USER} uid ${USER_ID};
dscl . -create /Users/${USER} UserShell /bin/bash;
dscl . -create /Users/${USER} NFSHomeDirectory ${NFSHOME};
dscl . create /Groups/${USER} gid ${USER_ID};
dscl . merge /Groups/${USER} users "${USER}";
 
echo Successfully created user ${USER} with gid of ${USER_ID}.;
exit 0;

Other Sources:

http://www.postgresql.org
http://www.xtuple.org/InstallingTheDatabase
http://www.xtuple.org/replication-how-to
http://www.xtuple.org/docs/admin-and-setup
http://www.xtuple.org/BackingUpTheDatabase
After the install, docs are in: /usr/local/pgsql/doc/postgresql/html/index.html
#postgresql on IRC