PostgreSQL 9.6 - Quick setup and installation on Ubuntu 18.04

Introduction

In this article, a PostgreSQL 9.6 instance is installed and customized (port, database and configuration files…) on Ubuntu 18.04.

Preparing the environment

User postgres

As user root, create the user postgres (group dba, id 10005)

root@vpsfrsqlpac1% useradd postgres -g dba -d /opt/postgres -m -s /bin/bash -u 10005

Binaries are installed in the directory /opt/postgres with the appropriate owner and rights :

root@vpsfrsqlpac1% mkdir /opt/postgres
root@vpsfrsqlpac1% chown postgres:dba /opt/postgres

The database will be installed in the directory /sqlpac/postgres/srvpg1 :

root@vpsfrsqlpac1% mkdir /sqlpac/postgres
root@vpsfrsqlpac1% chown postgres:dba /sqlpac/postgres
postgres@vpsfrsqlpac1% mkdir /sqlpac/postgres/srvpg1

Postgresql 9.6 distribution installation

Download the archive file PostgreSQL 9.6 for Linux 64 bits from EnterpriseDB. The distribution (version 9.6.15) is installed in the directory /opt/postgres/pgsql-9.6.15 :

postgres@vpsfrsqlpac1:/opt/postgres% gunzip postgresql-9.6.15-1-linux-x64-binaries.tar.gz

postgres@vpsfrsqlpac1:/opt/postgres% mkdir pgsql-9.6.15
postgres@vpsfrsqlpac1:/opt/postgres% tar xvf postgresql-9.6.15-2-linux-x64-binaries.tar \
                                              -C /opt/postgres/pgsql-9.6.15 \
                                              --strip-components=1

A symbolic link pgsql-9.6 is created for an ease of use managing minor versions changes.

postgres@vpsfrsqlpac1:/opt/postgres% ln -fs  pgsql-9.6.15 pgsql-9.6

For the user postgres, the environment variables below are initialized :

postgres@vpsfrsqlpac1:/opt/postgres%
          
export PATH=/opt/postgres/pgsql-9.6/bin
export LD_LIBRARY_PATH=/opt/postgres/pgsql-9.6/lib
export PGLIB=/opt/postgres/pgsql-9.6/lib

# Database directory
export PGDATA=/sqlpac/postgres/srvpg1

export PGBINHOME=/opt/postgres/pgsql-9.6

Database initialization

With the environment variables previously defined ($PATH, $PGLIB…), run the utility pg_ctl with the option intidb from the directory where the database will be installed ($PGDATA : /sqlpac/postgres/srvpg1). No extra options are specified here (collations, timezones…), default ones will be applied.

postgres@vpsfrsqlpac1:/sqlpac/postgres/srvpg1% pg_ctl initdb

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /sqlpac/postgres/srvpg1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Europe/Paris
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /opt/postgres/pgsql-9.6.15/bin/pg_ctl -D /sqlpac/postgres/srvpg1 -l logfile start

The instance structure is initialized.

postgres@vpsfrsqlpac1:/sqlpac/postgres/srvpg1% ls

base     pg_commit_ts  pg_ident.conf  pg_notify    pg_snapshots  pg_subtrans  PG_VERSION            postgresql.conf
global   pg_dynshmem   pg_logical     pg_replslot  pg_stat       pg_tblspc    pg_xlog
pg_clog  pg_hba.conf   pg_multixact   pg_serial    pg_stat_tmp   pg_twophase  postgresql.auto.conf

Normalization

For this new instance srvpg1 : 2 environment variables are created as well as the associated directories.

In the standard adopted, the configuration files postgresql.conf, pg_ident.conf and pg_hba.conf are moved from the database directory $PGDATA to the directory $CFG.

postgres@vpsfrsqlpac1:/sqlpac/postgres/srvpg1% mv $PGDATA/postgresql.conf $CFG
postgres@vpsfrsqlpac1:/sqlpac/postgres/srvpg1% mv $PGDATA/pg_ident.conf $CFG
postgres@vpsfrsqlpac1:/sqlpac/postgres/srvpg1% mv $PGDATA/pg_hba.conf $CFG

Relocating the configuration files. Why this choice ? A human error can occur at any time (rm -rf *, chown…), installing the configuration files in a custom path outside the database data files directory is safier. This is not the case for the file recovery.conf (PostgreSQL <= version 11) and the files standby.signal and recovery.signal (PostgreSQL >= version 12) : these files must be installed in the root database directory $PGDATA. The file postgresql.auto.conf must also reside in the directory $PGDATA, but this file should not be edited manually and only managed through the command ALTER SYSTEM.

The postgresql.conf file is modified in order to apply the customization : database directory, configuration and log files directories, port, WAL files archiving… .

$CFG/postgresql.conf

data_directory = '/sqlpac/postgres/srvpg1'                        
hba_file = '/opt/postgres/dba/srvpg1/cfg/pg_hba.conf'        
ident_file = '/opt/postgres/dba/srvpg1/cfg/pg_ident.conf'

listen_addresses = '*'
port = 30001

max_connections = 100

# Archiving

archive_mode = on
archive_command = 'test ! -f /backup/postgres/log_archive/srvpg1/%f && cp %p /backup/postgres/log_archive/srvpg1/%f'

# Log
log_destination = 'stderr, csvlog'                        
logging_collector = on          
log_directory = '/opt/postgres/dba/srvpg1/log'               
log_filename = 'srvpg1.log'
log_min_messages = info
log_line_prefix = '%m '    #   %m = timestamp with milliseconds
log_statement = 'ddl'
log_timezone = 'Europe/Paris'

datestyle = 'iso, mdy'
timezone = 'Europe/Paris'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'

Use pg_ctl to start the database:

postgres@vpsfrsqlpac1% pg_ctl start -D $CFG 

LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

To stop, restart the database with pg_ctl :

postgres@vpsfrsqlpac1% pg_ctl stop -D $CFG -m fast
postgres@vpsfrsqlpac1% pg_ctl restart -D $CFG -m fast

By default, the local user postgres is allowed to connect (definition in the file pg_hba.conf):

pg_hba.conf

# "local" is for Unix domain socket connections only
local   all             all                                     trust

Use psql to connect. When the port is not the default one (5432), add the option -p <port>

postgres@vpsfrsqlpac1% psql

postgres@vpsfrsqlpac1% psql -p 30001

psql.bin (9.6.15)
Type "help" for help.

Cannot read termcap database;
using dumb terminal settings.
postgres=#

Sourcing a PostgreSQL environment

In a mutualized PostgreSQL environment on a host : multiple instances and versions… One can setup a bash script sourcing the environment for a PostgreSQL instance.

The default profile for the user postgres is the following :

$HOME/.profile

## Custom profile
export DBA=$HOME/dba
export ETC=$DBA/etc
export PATH=$DBA/bin:$PATH

For each PostgreSQL instance, a configuration file $ETC/servername.pgq is created, it will initialize all the useful environment variables. Example for srvpg1 / PostgreSQL 9.6 :

source srvpg1.pgq

Some of the environment variables defined are system PostgreSQL variables : $PGLIB, $PGDATA, $PGPORT, $PGPASSFILE. For the complete liste of PostgreSQL environment variables : Environment Variables | Chapter 33. libpq - C Library

Only the version $PGVERSION is defined in the file srvpg1.pgq, all the other environment variables are computed in the bash script $DBA/etc/defaults_srv.bash.

$ETC/srvpg1.pgq

# PostgreSQL Server Instance Name
[ -z "$PGSRVNAME" ] && export PGSRVNAME=$(basename ${BASH_SOURCE[0]%.*})
# PostgreSQL  version
export PGVERSION=9.6

source $DBA/etc/defaults_srv.bash

$ETC/defaults_srv.bash

#!/bin/bash
#

# PGBINHOME
PGBINHOME=/opt/postgres/pgsql-${PGVERSION}
export PGBINHOME

#
# Setting PATH and LD_LIBRARY_PATH Environment
LD_LIBRARY_PATH=$PGBINHOME/lib:$LD_LIBRARY_PATH
PATH=$PGBINHOME/bin:$PATH
export PATH LD_LIBRARY_PATH

export CFG=$DBA/$PGSRVNAME/cfg
export LOG=$DBA/$PGSRVNAME/log
export DMP=`readlink $DBA/$PGSRVNAME/dmp`
export ARC=`readlink $DBA/$PGSRVNAME/arc`

# Checking postgresql.conf file
[ ! -f ${CFG}/postgresql.conf ] && echo "Could not find postgresql.conf in $CFG, exiting..." && exit 3

#
# PG Variables
PGLIB=$PGBINHOME/lib

PGDATA=`cat $CFG/postgresql.conf | grep 'data_directory' | sed "s/ *= */=/g" | tr -d "'" | awk '{print $1}' | awk -F"=" '{print $2}'`

PGPORT=$(cat $CFG/postgresql.conf | grep -i '^port' | sed "s/ *= */=/g" | awk '{print $1}' | awk -F"=" '{print $2}')

[ -z "$PGPORT" ] && PGPORT=5432

PGPASSFILE=$DBA/$PGSRVNAME/.$PGSRVNAME.pass

# WAL DIR
# Automatic detection depending on the PostgreSQL version
PGWALDIR=$PGDATA/pg_wal   # By default pg_wal, PGSQL > 9
MAJOR_VERSION=`echo ${PGVERSION} | awk -F"." '{print $1}'`
[ $MAJOR_VERSION -lt 10 ] && PGWALDIR=$PGDATA/pg_xlog

export PGLIB PGDATA PGPORT PGWALDIR PGPASSFILE

For interactive Xterm sessions, useful aliases can be defined in the script .bash_profile, especially the alias p which opens a PSQL session without prompting a password :

$HOME/.bash_profile

if [ ! -z "$PGSRVNAME" ]; then
        PS1="\e[0;33m\u\e[m@\e[0;33m\h\e[m [\e[0;31m$PGSRVNAME\e[m] :\w > "

        # Title : 0 minimized, 1 maximized
        PROMPT_COMMAND='echo -ne "\033]0;${PGSRVNAME}@${HOSTNAME} ${PWD}\007"'

        # Alias
        
        alias p="psql"
        
        alias godat="cd $PGDATA"
        alias gowal="cd $PGWALDIR"
        alias godmp="cd $DMP"

        # Alias config file
        alias gocfg="cd $CFG"
        alias mcfg="vi ${CFG}/postgresql.conf"

        # Alias log files
        alias golog="cd ${LOG}"
        alias tlog="tail -50 ${LOG}/${PGSRVNAME}.log"
        alias flog="tail -f ${LOG}/${PGSRVNAME}.log"
        alias vlog="view ${LOG}/${PGSRVNAME}.log"


fi

If local connections methods are not set to trust in the configuration file pg_hba.conf due to the mutualization with other users on the host, check the secured permission 600 is given to the password file ($PGPASSFILE), otherwise psql raises a warning and the password is prompted.

WARNING: password file "/opt/postgres/dba/srvpg1/.srvpg1.pass" has group or world access;
permissions should be u=rw (0600) or less
Password:

postgres@vpsfrsqlpac1% chmod 600 /opt/postgres/dba/srvpg1/.srvpg1.pass

The syntax in the password file is very easy :

HOST:PORT:DB_NAME:USER:PASSWORD

localhost:30001:postgres:postgres:**************

There is no encryption or obfuscation of passwords in the password file (not yet), as is the case with --login-path and mysql_config_editor for MySQL, but securing this file with the permission 600 is largely enough.