PostgreSQL 9.3 Compile/Install HOWTO

updated 20 Aug 2017

I'm going to assume this install takes place on a stock install of Ubuntu 13.04 without the PostgreSQL packages installed.. This particular example shows a custom-compile of PostgreSQL 9.3, with perl enabled so that the perl procedural language can be used.

Add the postgres user

Please note that the postgres user will already exist if you had previously installed PostgreSQL in your system. Note we do this as root:

root@mycomputer:/etc/init.d# adduser postgres

Adding user `postgres' ...
Adding new group `postgres' (1001) ...
Adding new user `postgres' (1001) with group `postgres' ...
Creating home directory `/home/postgres' ...
Copying files from `/etc/skel' ...
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully
Changing the user information for postgres
Enter the new value, or press ENTER for the default
	Full Name []: PostgreSQL
	Room Number []: none
	Work Phone []: none
	Home Phone []: none
	Other []: none
Is the information correct? [Y/n] 

Install prerequisites

To compile, PostgreSQL requires a few packages to be installed. Note we are doing everything as root:

root@mycomputer:/usr/local/src/postgresql-9.3.0# apt-get install flex
root@mycomputer:/usr/local/src/postgresql-9.3.0# apt-get install bison build-essential
root@mycomputer:/usr/local/src/postgresql-9.3.0# apt-get install libreadline6-dev
root@mycomputer:/usr/local/src/postgresql-9.3.0# apt-get install zlib1g-dev
root@mycomputer:/usr/local/src/postgresql-9.3.0# apt-get install libossp-uuid-dev

Get the PostgreSQL source code

This can be found at www.postgresql.org. In our example here, we will assume we have downloaded the source code into /usr/home/myuser/Downloads. Note we are doing everything as root:

root@mycomputer:/usr/local/src# cp /home/myuser/Downloads/postgresql-9.3.0.tar.gz .
root@mycomputer:/usr/local/src# tar -xzvf postgresql-9.3.0.tar.gz 
root@mycomputer:/usr/local/src# cd postgresql-9.3.0/

Create the following wrapper script for configure. It just makes life easier:

root@mycomputer:/usr/local/src/postgresql-9.3.0# vi runconfigure.sh

Write a config wrapper script

This just helps document what configuration settings you used when you configured the build. The contents of runconfigure.sh are:

#!/bin/bash

./configure \
    --prefix=/usr/local/pgsql-9.3 \
    --with-ossp-uuid

Build

root@mycomputer:/usr/local/src/postgresql-9.3.0# chmod +x runconfigure.sh
root@mycomputer:/usr/local/src/postgresql-9.3.0# ./runconfigure.sh 
root@mycomputer:/usr/local/src/postgresql-9.3.0# make
root@mycomputer:/usr/local/src/postgresql-9.3.0# make install
root@mycomputer:/usr/local/src/postgresql-9.3.0# cd ./contrib/uuid-ossp
root@mycomputer:/usr/local/src/postgresql-9.3.0/contrib/uuid-ossp# make
root@mycomputer:/usr/local/src/postgresql-9.3.0/contrib/uuid-ossp# make install
root@mycomputer:/usr/local/src/postgresql-9.3.0/contrib/uuid-ossp# chown -R postgres:postgres /usr/local/pgsql-9.3

Configure

Let's create a start/stop script for PostgreSQL:

root@mycomputer:/usr/local/src/postgresql-9.3.0# cd /etc/init.d/
root@mycomputer:/etc/init.d# vi postgresql

The contents of postgresql are:

#!/bin/bash

# chkconfig: 2345 98 02
# description: PostgreSQL RDBMS

# where to find commands like su, echo, etc...
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

DB_ENCODING=UTF8

DB_LOCALE=C

PG_INSTALL_DIR=/usr/local/pgsql-9.3

PG_DATA_DIR="${PG_INSTALL_DIR}/data"

PG_SERVER_LOG="$PG_DATA_DIR/serverlog"

PG_UNIX_USER=postgres

POSTGRES="$PG_INSTALL_DIR/bin/postgres"

PG_CTL="$PG_INSTALL_DIR/bin/pg_ctl"

INITDB="$PG_INSTALL_DIR/bin/initdb"

# die on first failure; do not keep trucking
set -e

if [ $# -ne 1 ]; then
    echo "please enter start/stop/restart etc..." 1>&2
    exit 1
fi

# Only start if we can find postgres and pg_ctl.
if [ ! -x $PG_CTL ]; then
    echo "$PG_CTL not found" 1>&2
    exit 1
fi

if [ ! -x $POSTGRES ]; then
    echo "$POSTGRES not found" 1>%amp;2
    exit 1
fi

case $1 in
  init)
	su - $PG_UNIX_USER -c "$INITDB --pgdata='$PG_DATA_DIR' --encoding=$DB_ENCODING --locale=$DB_LOCALE"
	;;
  start)
	echo -n "Starting PostgreSQL: "
	su - $PG_UNIX_USER -c "$PG_CTL start -D '$PG_DATA_DIR' -l $PG_SERVER_LOG &"
	echo "ok"
	;;
  stop)
	echo -n "Stopping PostgreSQL: "
	su - $PG_UNIX_USER -c "$PG_CTL stop -D '$PG_DATA_DIR' -s -m fast"
	echo "ok"
	;;
  restart)
	echo -n "Restarting PostgreSQL: "
	su - $PG_UNIX_USER -c "$PG_CTL stop -D '$PG_DATA_DIR' -s -m fast -w"
	su - $PG_UNIX_USER -c "$PG_CTL start -D '$PG_DATA_DIR' -l $PG_SERVER_LOG &"
	echo "ok"
	;;
  reload)
        echo -n "Reload PostgreSQL: "
        su - $PG_UNIX_USER -c "$PG_CTL reload -D '$PG_DATA_DIR' -s"
        echo "ok"
        ;;
  status)
	su - $PG_UNIX_USER -c "$PG_CTL status -D '$PG_DATA_DIR'"
	;;
  *)
	# Print help
	echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2
	exit 1
	;;
esac

exit 0

Get the script ready to run, but do not run it yet:

root@mycomputer:/etc/init.d# chmod +x ./postgresql

Configure the kernel params to allow postgres to use as much memory as it needs:

Note!I copied this from my PostgreSQL 9.1 install, but I think as of PostgreSQL 9.2, it is no longer necessary to configure your system's shared memory parameters. I'll re-visit this later.

root@mycomputer:/etc/init.d# cd
root@mycomputer:~# vi postgresql-kernel-params.sh

The contents of postgresql-kernel-params.sh are:

#!/bin/bash

SYSCTL=/sbin/sysctl

echo "# add the output of this script to /etc/sysctl.conf,"
echo "# and then, as root, run"
echo
echo "# sysctl -p /etc/sysctl.conf"
echo
echo "# to load change the kernel settings for these parameters."
echo

PAGE_SIZE=`getconf PAGE_SIZE`

echo "# page size is: $PAGE_SIZE"

NUM_PHYS_PAGES=`getconf _PHYS_PAGES`

echo "# number of physical pages on this box: $NUM_PHYS_PAGES"

CURR_SHMALL=`$SYSCTL -n kernel.shmall`
PREF_SHMALL=`expr $NUM_PHYS_PAGES / 2`

echo "# kernel.shmall should be half of the number of pages. Current kernel.shmall, in pages, is: $CURR_SHMALL"
echo "# kernel.shmall should be:"
echo
echo "kernel.shmall = $PREF_SHMALL"
echo

CURR_SHMMAX=`$SYSCTL -n kernel.shmmax`
PREF_SHMMAX=`expr $PREF_SHMALL \* $PAGE_SIZE`

echo "# kernel.shmmax should be half of available RAM, in kB. Current kernel.shmmax, in kB, is: $CURR_SHMMAX"
echo "# kernel.shmmax should be:"
echo
echo "kernel.shmmax = $PREF_SHMMAX"
echo

# CURR_SHMMIN=`$SYSCTL -n kernel.shmmin`  # XXX: does not exist on linux
# CURR_SHMSEG=`$SYSCTL -n kernel.shmseg`  # XXX: does not exist on linux

CURR_SHMMNI=`$SYSCTL -n kernel.shmmni`

echo "# kernel.shmmni is usually set to a sane amount on Linux. Currently, it is: $CURR_SHMMNI"

# CURR_SEMMNI=`$SYSCTL -n kernel.semmni`  # XXX: does not exist on linux
# CURR_SHMMNI=`$SYSCTL -n kernel.semmns`  # XXX: does not exist on linux
# CURR_SHMMSL=`$SYSCTL -n kernel.semmsl`  # XXX: does not exist on linux
# CURR_SHMMSL=`$SYSCTL -n kernel.semmap`  # XXX: does not exist on linux
# CURR_SHMMSL=`$SYSCTL -n kernel.semmvx`  # XXX: does not exist on linux

CURR_SEM=`$SYSCTL -n kernel.sem`

echo "# kernel.sem usually has sane defauls. They are currently: $CURR_SEM"

And so now I run it:

root@mycomputer:~# chmod +x ./postgresql-kernel-params.sh
root@mycomputer:~# ./postgresql-kernel-params.sh 

And the output is:

# add the output of this script to /etc/sysctl.conf,
# and then, as root, run

# sysctl -p /etc/sysctl.conf

# to load change the kernel settings for these parameters.

# page size is: 4096
# number of physical pages on this box: 1782938
# kernel.shmall should be half of the number of pages. Current kernel.shmall, in pages, is: 2097152
# kernel.shmall should be:

kernel.shmall = 891469

# kernel.shmmax should be half of available RAM, in kB. Current kernel.shmmax, in kB, is: 33554432
# kernel.shmmax should be:

kernel.shmmax = 3651457024

# kernel.shmmni is usually set to a sane amount on Linux. Currently, it is: 4096
# kernel.sem usually has sane defauls. They are currently: 250	32000	32	128

And so I follow the advice and edit sysctl.conf:

root@mycomputer:~# vi /etc/sysctl.conf 

And so I follow the advice and edit sysctl.conf:

and at the bottom of sysct.conf, I add:

kernel.shmall = 891469
kernel.shmmax = 3651457024

And now I make the changes take effect:

root@mycomputer:~# sysctl -p /etc/sysctl.conf

Initialise the database

root@mycomputer:~# cd /usr/local/

root@mycomputer:/usr/local# chown -R postgres:postgres pgsql-9.3  # this should have already been done earlier

root@mycomputer:/usr/local# su - postgres

postgres@mycomputer:~$ cd /usr/local/pgsql-9.3/bin
postgres@mycomputer:/usr/local/pgsql-9.3/bin$ ./initdb --pgdata=/usr/local/pgsql-9.3/data --encoding=UTF8 --no-locale
postgres@mycomputer:/usr/local/pgsql-9.3/bin$ cd ../data/
postgres@mycomputer:/usr/local/pgsql-9.3/data$ vi postgresql.conf

Configure the database

Now that we have run initdb, we have a data directory, and inside that data directory, we have a postgresql.conf file that needs to be edited.

If you run a postgresql database with few connections but large jobs, here are some recommended settings for postgrsql.conf. Assume a sever with 75GB of RAM.

listen_address='localhost'  ==>  listen_address='*'

# according to http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html,
# shared_buffers should be 25% of RAM, but no higher than 8GB.
# However, see http://www.keithf4.com/a-small-database-does-not-mean-small-shared_buffers/
# for a point of view on when shared_buffers might legitimately be set above 8GB

shared_buffers = 32MB  ==>  shared_buffers = 8GB

temp_buffers = 8MB  ==>  temp_buffers = 80MB

work_mem = 1MB  ==>  work_mem = 1GB  # set higher on a per-session basis

maintenance_work_mem = 16MB  ==>  maintenance_work_mem = 20GB  # set quite high; used to build indexes and do data loads

# according to http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html,
# wal_buffers should be 3% of shared_buffers up to a maximum of 16MB, the size of a single WAL segment.

wal_buffers = 64kB  ==>  wal_buffers = 16MB

# according to https://www.packtpub.com/article/server-configuration-tuning-postgresql,
# checkpoint segments should be much higher than the default of 3:

checkpoint_segments = 3  ==>  checkpoint_segments = 64

# if you are on RAID 10, the cost of accessing a random page should be much closer to the cost
# of sequential IO. (In fact, if you are on SSDs, seq_page_cost and random_page_cost should equal each other.)

random_page_cost = 4.0  ==>  random_page_cost = 2.0

# according to http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server, 
# "Setting effective_cache_size to 1/2 of total memory would be a normal conservative setting, 
# and 3/4 of memory is a more aggressive but still reasonable amount."

effective_cache_size = 128MB  ==>  effective_cache_size = 37GB

# settings for logging:
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_min_messages = debug1  # obviously rather verbose; drop to warning if your logs fill up
log_line_prefix = '%t'

# log queries that run for more than 2000ms (2 seconds)
log_min_duration_statement = 2000

# According to http://www.databasesoup.com/2014/05/why-you-should-always-set-tempfilelimit.html,
# you should set temp_file_limit if you are concerned that any process (or set of processes)
# could start over-expanding all of the temporary files that get used for sorts, hashes, and similar operations.
# If you wet temp_file_limit, when a user goes over the limit, their query gets cancelled and they see an error.

# According to 
# https://blog.2ndquadrant.com/autovacuum-tuning-basics/
# and
# https://blog.2ndquadrant.com/when-autovacuum-does-not-vacuum/
# the autovacuumer does not do enough work before backing off again,
# and heavily updated systems can easily get behind and never catch up.
# The default is an anemic 200, whereas the max allowable is 10000.
autovacuum_vacuum_cost_limit = 2000

Next, edit pg_hba.conf if you need to, though the defaults allow logins to all databases from all users from the local machine.

Start PostreSQL

Now become root again:

postgres@mycomputer:/usr/local/pgsql-9.3/data$ exit

And start PostgreSQL!

root@mycomputer:/etc/init.d# ./postgresql start

Make a database user

Now become postgres user again

root@mycomputer:/etc/init.d# su - postgres

make a database user to use:

postgres@mycomputer:~$ /usr/local/pgsql-9.3/bin/psql template1

template1=# create user myuser superuser createdb createrole password 'mypassword';
template1=# \q

Make a database

now go create your first database

postgres@mycomputer:~$ /usr/local/pgsql-9.3/bin/psql -U myuser template1

template1=# create database mydb with owner myuser;
template1=# \q