Installation rapide de PostgreSQL 9.6 - Ubuntu 18.04

Logo

Introduction

Dans cet article une instance PostgreSQL 9.6 est installée, normalisée et personnalisée (port, répertoires des fichiers de configuration et de bases de données…) sur un serveur Ubuntu 18.04.

Schéma PostgreSQL

Préparation de l’environnement

User postgres

En tant que root, le compte postgres est créé (groupe dba, id 10005)

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

Les binaires seront installés dans /opt/postgres avec les droits nécessaires :

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

La base de données sera installée dans le répertoire /sqlpac/postgres/srvpg1 : 

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

Installation de la distribution Postgresql 9.6

Télécharger l’archive PostgreSQL 9.6 pour Linux 64 bits depuis le site EnterpriseDB. La distribution (version 9.6.15 ici) est installée dans le répertoire /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

Un lien pgsql-9.6 est créé pour faciliter la gestion des versions mineures.

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

Pour l’utilisateur postgres, les variables d’environnement ci-dessous sont initialisées :

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

# Repertoire de la base de donnees
export PGDATA=/sqlpac/postgres/srvpg1

export PGBINHOME=/opt/postgres/pgsql-9.6

Initialisation de la base de données

Avec les variables d’environnement précédemment définies ($PATH, $PGLIB…), lancer l’utilitaire pg_ctl avec l’option intidb depuis le répertoire où la base de données va être installée ($PGDATA : /sqlpac/postgres/srvpg1). Aucune option particulière n’est ici donnée à l’installation (collations, timezones, …), les valeurs par défaut seront appliquées.

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

La structure de l’instance est initialisée.

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

Normalisation

Pour cette nouvelle instance srvpg1 : 2 variables d’environnement sont créées avec les répertoires correspondants.

$CFG /opt/postgres/dba/srvpg1/cfg Fichiers de configuration (postgresql.conf…)
$LOG /opt/postgres/dba/srvpg1/log Fichiers de log

Dans la norme adoptée, les fichiers de configuration postgresql.conf, pg_ident.conf et pg_hba.conf sont déplacés du répertoire de la base de données $PGDATA vers le répertoire $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

Les fichiers de configuration sont délocalisés. Pourquoi ce choix ? Un erreur humaine peut se produire à tout moment (rm -rf *, chown…), installer les fichiers de configuration dans un répertoire personnalisé en dehors du répertoire des fichiers de bases de données est plus sûre. Ce n’est pas le cas pour le fichier recovery.conf (PostgreSQL <= version 11) et les fichiers standby.signal et recovery.signal (PostgreSQL >= version 12) : ces fichiers doivent être installés dans le répertoire racine de la base de données $PGDATA. Le fichier postgresql.auto.conf doit également résider oblgatoirement dans le répertoire $PGDATA, mais ce fichier ne doit pas être édité manuellement et être géré uniquement à travers la commande ALTER SYSTEM.

Le fichier postgresql.conf est modifié afin de refléter la personnalisation : répertoires d’installation de la base de données et des fichiers de configuration, fichiers de log, port, archivage des fichiers WAL …

$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'

La base est alors démarrée avec pg_ctl :

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

Pour stopper, redémarrer la base avec pg_ctl : 

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

Par défaut le compte local postgres est autorisé à se connecter (définition dans le fichier pg_hba.conf):

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

Utiliser alors le binaire psql pour se connecter à l’instance créée. Lorsque le port est différent de celui par défaut, ajouter l’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=#

Sourcer un environnement PostgreSQL

Dans un environnement PostgreSQL mutualisé sur une machine : multiples instances et versions … un script bash peut être développé pour sourcer l’environnement d’une instance PostgreSQL.

Le profil par défaut pour le compte postgres est le suivant : 

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

Pour chaque instance PostgreSQL, un fichier de configuration $ETC/servername.pgq est créé, il initialise toutes les variables d’environnement utiles. Exemple avec srvpg1 / PostgreSQL 9.6 : 

source srvpg1.pgq
$PATH PATH=/opt/postgres/pgsql-9.6/bin:$PATH $LD_LIBRARY_PATH LD_LIBRARY_PATH=/opt/postgres/pgsql-9.6/lib :$LD_LIBRARY_PATH
$PGSRVNAME srvpg1 Nom de l’instance $PGVERSION 9.6 Version $PGPORT 30001 Port $PGBINHOME /opt/postgres/pgsql-9.6 Répertoire de la distribution PostgreSQL $PGLIB /opt/postgres/pgsql-9.6/lib Librairies PostgreSQL $PGDATA /sqlpac/postgres/srvpg1 Répertoire des fichiers de bases de données $PGWALDIR /sqlpac/postgres/srvpg1/pg_xlog Répertoire des fichiers WAL $PGPASSFILE /opt/postgres/dba/srvpg1/.srvpg1.pass Fichier Password $CFG /opt/postgres/dba/srvpg1/cfg Répertoire des fichiers de configuration (postgresql.conf…) $LOG /opt/postgres/dba/srvpg1/log Répertoire des fichiers de log $DMP /backup/postgres/srvpg1 Répertoire des backups $ARC /backup/postgres/log_archive/srvpg1 Répertoire des archives WAL

Certaines des variables d’environnement définies sont des variables systèmes PostgreSQL : $PGLIB, $PGDATA, $PGPORT, $PGPASSFILE. Pour la liste complète des variables d’environnement reconnues par PostgreSQL : Variables d’environnement | Chapter 33. libpq - C Library

Seule la version $PGVERSION est définie dans le fichier srvpg1.pgq, toutes les autres variables d’environnement sont "calculées" par le script bash $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

Pour les sessions interactives XTerm, des alias utiles peuvent être définis dans le script .bash_profile, notamment l’alias p qui ouvre une session PSQL sans prompt de mot de passe : 

$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

Si les méthodes pour les connexions locales ne sont pas définies à trust dans le fichier de configuration pg_hba.conf à cause de la mutualisation avec d’autres users sur la machine, vérifier que la permission 600 est donnée au fichier des mots de passe ($PGPASSFILE), sinon psql lève un avertissement et le mot de passe est demandé.

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

La syntaxe du fichier de mots de passe est très simple : 

HOST:PORT:DB_NAME:USER:PASSWORD

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

Il n’y a pas d’obfuscation ou d’encryption des mots de passe dans le fichier (pas encore), comme c’est le cas avec --login-path et mysql_config_editor pour MySQL, mais sécuriser ce fichier avec la permission 600 est largement suffisante.