PostgreSQL 9.4 Compile/Install HOWTO

PostgreSQL 9.4.5 Build

12 Sep 2015

Create the postgresql user and group.

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

$ su -
# addgroup --gid 1001 postgres
# adduser --home /home/postgres --uid 1001 --gid 1001 --disabled-password postgres

Install prerequisites

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

# apt-get install build-essential flex bison libreadline6-dev zlib1g-dev libossp-uuid-dev uuid

Get the PostgreSQL source code

This can be found at www.postgresql.org. Note we are doing everything as root:

# cd /usr/local/src
# wget https://ftp.postgresql.org/pub/source/v9.4.5/postgresql-9.4.5.tar.bz2
# wget https://ftp.postgresql.org/pub/source/v9.4.5/postgresql-9.4.5.tar.bz2.sha256
# sha256sum postgresql-9.4.5.tar.bz2 > actual.sha256
# diff actual.sha256 postgresql-9.4.5.tar.bz2.sha256
# rm actual.sha256 postgresql-9.4.5.tar.bz2.sha256
# tar -xjvf postgresql-9.4.5.tar.bz2
# cd postgresql-9.4.5

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

# 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

set -e
set -u
set -o pipefail

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

Make and install the base PostgreSQL system:

# chmod +x runconfigure.sh
# ./runconfigure.sh
# make
# make install

Write a contrib module builder script

This just helps document what contrib modules and binaries you built. The contents of runcontrib.sh are:

#!/bin/bash

set -u
set -e
set -o pipefail

export BASEDIR=/usr/local/src/postgresql-9.4.5/contrib

build_contrib() {
    cd $BASEDIR/$1
    make
    make install
}

# contrib modules

build_contrib uuid-ossp
build_contrib pageinspect
build_contrib pg_buffercache
build_contrib pg_freespacemap
build_contrib pg_prewarm
build_contrib pgrowlocks
build_contrib pg_stat_statements
build_contrib pgstattuple

# contrib binaries 

build_contrib oid2name
build_contrib pgbench
build_contrib pg_xlogdump

Make and install your contrib modules and binaries:

# chmod +x runcontrib.sh
# ./runcontrib.sh

Ensure our new postgres user/group owns our freshly-installed PostgreSQL cluster:

# chown -R postgres:postgres /usr/local/pgsql-9.4.5

Initialize the database:

# su - postgres
$ cd /usr/local/pgsql-9.4.5/bin
$ ./initdb --pgdata=/usr/local/pgsql-9.4.5/data --encoding=UTF8 --no-locale

Configure the database, and assume a laptop with 16 GB of RAM that will pretend it is a dedicated PostgreSQL server (that is, pretend PostgreSQL will be the only major piece of software running on the machine and will therefore have near-exclusive access to system resources, as practice for setting up on a real server):

$ cd /usr/local/pgsql-9.4.5/data
$ vi postgresql.conf

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 = 4GB

temp_buffers = 8MB  ==>  temp_buffers = 80MB

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

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

# Load the shared library required by pg_stat_statements
#shared_preload_libraries = ''  ==> shared_preload_libraries = 'pg_stat_statements'

# 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.
# My laptop has an SSD, so I'm setting random_page_cost to 1.0, because a random access is the same
# cost as a sequential access.

random_page_cost = 4.0  ==>  random_page_cost = 1.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 = 8GB

# 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.

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

Make a systemd Postgres control script. Exit being the postgres user and go back to being root.

$ exit
# cd /etc/systemd/system
# vi postgresql.service

[Unit]
Description=PostgreSQL 9.4.5
# This unit can only run after the network is up and running
# (that is, the network target has run)
After=network.target

[Service]
# PostgreSQL is a traditional UNIX daemon that forks a child, 
# and the initial process exits
Type=forking
# Wait 120 seconds on startup and shutdown to consider the process
# correctly started up or shut down.
TimeoutSec=120
# The UNIX user and group to execute PostgreSQL as
User=postgres
Group=postgres

# Set the PGROOT environmental variable for PostgreSQL
Environment=PGROOT=/usr/local/pgsql-9.4.5

# If StandardOutput= or StandardError= are set to syslog, journal or kmsg,
# prefix log lines with "postgres"
SyslogIdentifier=postgres

# Let systemd know where PostgreSQL keeps its pid file
PIDFile=/usr/local/pgsql-9.4.5/data/postmaster.pid

# Command used to start PostgreSQL
ExecStart= /usr/local/pgsql-9.4.5/bin/pg_ctl -s -D ${PGROOT}/data start -w -t 120
# Command used to reload PostgreSQL
ExecReload=/usr/local/pgsql-9.4.5/bin/pg_ctl -s -D ${PGROOT}/data reload
# Command used to stop PostgreSQL
ExecStop=  /usr/local/pgsql-9.4.5/bin/pg_ctl -s -D ${PGROOT}/data stop -m fast

# Use the lowest allowable setting for the OOM killer; this should 
# actually disable the OOM killer for PostgreSQL
OOMScoreAdjust=-1000

[Install]
# This unit is part of target multi-user
WantedBy=multi-user.target

# systemctl daemon-reload

Now start postgresql:

# systemctl start postgresql

Let's enable all of our contrib modules!

# su - postgres

$ /usr/local/pgsql-9.4.5/bin/psql -d template1

If we enable the contrib modules in here, then all other databases inherit those contrib modules.

Let's get a list of our available extensions, and then use that list to create our extensions (note that plpgsql is already created).

template1=# select name from pg_available_extensions;
        name        
--------------------
 pg_buffercache
 plpgsql
 pg_stat_statements
 pgrowlocks
 pgstattuple
 pg_freespacemap
 pg_prewarm
 pageinspect
 uuid-ossp
(9 rows)

template1=# \q

Create the following file /home/postgres/enable-extensions.sql:

create extension "uuid-ossp";
create extension pg_buffercache;
create extension pg_stat_statements;
create extension pgrowlocks;
create extension pgstattuple;
create extension pg_freespacemap;
create extension pg_prewarm;
create extension pageinspect;

Now use that file to enable all the extensions in template1, to be picked up by other databases we create:

$ /usr/local/pgsql-9.4.5/bin/psql -f /home/postgres/enable-extensions.sql -d template1

Make a database user (optional):

$ /usr/local/pgsql-9.4.5/bin/psql -d template1
template1=# create user myuser superuser createdb createrole password 'mypassword';
template1=# \q

Create a database owned by that user (optional):

$ /usr/local/pgsql-9.4.5/bin/psql -d template1
template1=# create database mydb with owner myuser;
template1=# \q

Optional system settings to consider:

memory.oom_control = 1
vm.overcommit_memory = 2