PostgreSQL 10.0 Compile/Install HOWTO

31 Oct 2017

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 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/v10.0/postgresql-10.0.tar.bz2
# wget https://ftp.postgresql.org/pub/source/v10.0/postgresql-10.0.tar.bz2.sha256
# sha256sum postgresql-10.0.tar.bz2 > actual.sha256
# diff actual.sha256 postgresql-10.0.tar.bz2.sha256
# rm actual.sha256 postgresql-10.0.tar.bz2.sha256
# tar -xjvf postgresql-10.0.tar.bz2
# cd postgresql-10.0

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/postgresql-10.0 \
    --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

# vi runcontrib.sh

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-10.0/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

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/postgresql-10.0

Initialize the database:

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

Configure the database, and assume a laptop with 32 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/postgresql-10.0/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 = 128MB
shared_buffers = 8GB

#temp_buffers = 8MB
temp_buffers = 80MB

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

#maintenance_work_mem = 64MB
maintenance_work_mem = 2GB  # 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 = -1
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, though with PostgreSQL 9.6,
# that setting disappears and becomes max_wal_size, which http://www.postgresql.org/docs/9.6/static/release-9.6.html
# says to set to max_wal_size = (3 * checkpoint_segments) * 16MB
# In fact (2016 Jul 03) according to https://blog.pgaddict.com/posts/postgresql-performance-on-ext4-and-xfs,
# "In production we usually shoot for much longer checkpoints, ideally triggered by timeout - increasing the checkpoint
# segments to 256 or 1024 is not really uncommon, these days."

# checkpoint_segments = XXX  # no longer exists as of pg 9.6

#max_wal_size = 1GB
max_wal_size = 3GB

# 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 = 4GB
effective_cache_size = 16GB

# settings for logging:

log_destination = 'stderr'
logging_collector = on
log_directory = '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 %m [%p] '

# 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

# New to PostgreSQL 9.6! If a session leaves an uncommitted,unrolled back transaction 
# for longer than 10 seconds, timeout the session.
idle_in_transaction_session_timeout = 10000

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 10.0
# 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/postgresql-10.0

# 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/postgresql-10.0/data/postmaster.pid

# Command used to start PostgreSQL
ExecStart= /usr/local/postgresql-10.0/bin/pg_ctl -s -D ${PGROOT}/data start -w -t 120
# Command used to reload PostgreSQL
ExecReload=/usr/local/postgresql-10.0/bin/pg_ctl -s -D ${PGROOT}/data reload
# Command used to stop PostgreSQL
ExecStop=  /usr/local/postgresql-10.0/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
# systemctl enable postgresql

Now start postgresql:

# systemctl start postgresql

Let's enable all of our contrib modules!

# su - postgres

$ /usr/local/postgresql-10.0/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/postgresql-10.0/bin/psql -f /home/postgres/enable-extensions.sql -d template1

Make a database user (optional):

$ /usr/local/postgresql-10.0/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/postgresql-10.0/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