PostgreSQL debian

Fiche express
Distribution	Debian
Version PostgreSQL (exemple)	10
Connection pooler	pgbouncer
Modèle	multi-instances, un compte OS par instance
Voir aussi	PostgreSQL install · PostgreSQL · Psql

Cas concret d'exploitation PostgreSQL sous Debian avec un modèle multi-instances : chaque instance a son propre compte système, son propre PGDATA, et un pgbouncer dédié devant elle pour le pooling de connexions. Ce modèle diffère de l'installation simple décrite dans PostgreSQL install (une seule instance système, paquets RHEL) — utile pour héberger plusieurs bases/versions indépendantes sur le même serveur.

Paquets et binaires

dpkg --get-selections | grep pos

postgresql-10                                   install
postgresql-client-10                            install
postgresql-client-common                        install
postgresql-common                               install

Binaires sous /usr/lib/postgresql/10/ :

/usr/lib/postgresql/10/bin/pg_ctl --version
# pg_ctl (PostgreSQL) 10.5 (Debian 10.5-1.pgdg90+1)

Organisation multi-instances

Chaque instance tourne sous un compte OS dédié (exemple : pg10test), avec ses propres variables d'environnement PG* définies dans un fichier de profil (~/.pgsrc), ce qui permet de faire cohabiter plusieurs versions/instances sur le même serveur sans conflit :

pg10test@pgtest10:~$ env | grep PG

PGVERSION=10
PGVERSIONMAJ=10.5
PGENGINE=/usr/lib/postgresql/10/bin
PGDATA=/pgsdb/pg10test/data
PGDATABASE=pg10test
PGHOST=/pgsdb/pg10test
PGPORT=5432

PGHOST pointe ici vers un chemin Unix (répertoire), pas une adresse réseau : la connexion se fait via socket Unix (.s.PGSQL.5432 dans ce répertoire), pas en TCP.

psql -l

Connexion via le compte postgres générique (à réserver à l'administration, pas à l'usage applicatif courant) :

psql -h pgtest10 -p 5432 -l

Démarrage / arrêt

Sur ce type d'environnement, un script d'init maison (LSB, /etc/init.d/) orchestre généralement postgres + pgbouncer ensemble pour l'instance, plutôt que de piloter le service postgresql standard directement :

systemctl list-units --type=service | grep pos

pg-instance.service        loaded active running LSB: start pgbouncer & PostgreSQL
postgresql.service         loaded active exited  PostgreSQL RDBMS

Des alias sont en général définis pour l'exploitant de l'instance (chargés depuis son profil) : status_db, start_db, stop_db, restart_db.

Authentification

Fichier pg_hba.conf de l'instance. Par défaut dans ce type de déploiement :

• connexions locales (socket) : peer (utilisateur Unix = rôle PostgreSQL) ;
• connexions par port (TCP) : md5 (mot de passe).

Après modification, recharger sans couper les connexions en cours :

pg_ctl reload

pgbouncer

• Fichier de configuration : /pgsdb/<instance>/pgbouncer/ini
• Log : /pgsdb/<instance>/traces
• listen_addr = 0.0.0.0
• listen_port = 6432
• auth_file = /pgsdb/<instance>/pgbouncer/ini/userlist.txt

Point de vigilance sécurité : le fichier userlist.txt contient les identifiants d'authentification pgbouncer — s'il est en lecture pour tous (-rw-r--r--), c'est une fuite d'identifiants potentielle. Il doit être restreint au compte de l'instance uniquement (chmod 600).

Notions et commandes usuelles

Comptes utilisateurs

En ligne de commande (compte de l'instance ou postgres) :

createuser --createdb USER

Équivalent SQL :

CREATE ROLE db1_owner NOSUPERUSER CREATEDB NOCREATEROLE INHERIT LOGIN;

Consultation (\du) :

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 admin     | Create role, Create DB                                     | {}
 db1_owner | Create DB                                                  | {}
 pg10test  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 postgres  |                                                            | {}

Tablespaces

Un tablespace correspond à un espace disque dédié ; les tables (et leurs données) peuvent y être localisées.

mkdir <chemin_tablespace>

CREATE TABLESPACE db1_data OWNER db1_owner LOCATION '<chemin_tablespace>';

Consultation (\db) :

             List of tablespaces
    Name    |  Owner   |         Location
------------+----------+--------------------------
 db1_data   | pg10test | /pgsdb/pg10test/db1_data
 pg_default | pg10test |
 pg_global  | pg10test |

Bases de données

createdb --tablespace=TABLESPACE --owner=OWNER <base>
psql -l

Équivalent SQL :

CREATE DATABASE <base> OWNER <owner> TABLESPACE <tablespace>;

Consultation (\l) :

                             List of databases
   Name    |   Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+-----------+----------+---------+-------+-----------------------
 db1       | db1_owner | UTF8     | C       | C     |
 pg10test  | pg10test  | UTF8     | C       | C     |
 postgres  | pg10test  | UTF8     | C       | C     |
 template0 | pg10test  | UTF8     | C       | C     | =c/pg10test          +
           |           |          |         |       | pg10test=CTc/pg10test
 template1 | pg10test  | UTF8     | C       | C     | =c/pg10test          +
           |           |          |         |       | pg10test=CTc/pg10test

Pour autoriser une connexion sans mot de passe en socket local à une base précise (usage applicatif interne, sur un hôte de confiance) :

local   <base>   all   trust

Fichier de service (.pg_service.conf)

Un fichier de service regroupe les paramètres de connexion à une base sous un nom court, utilisé côté client — évite de répéter host/user à chaque commande :

[db1_owner]
host=/pgsdb/pgtest10
user=db1_owner

psql service=db1_owner

Architecture recommandée

Modèle appliqué systématiquement pour chaque nouvelle base sur ce type d'environnement :

• créer un utilisateur dédié <base>_owner (pas de partage de rôle entre bases) ;
• créer des filesystems dédiés par base (données, index), appartenant au compte de l'instance ;
• créer des tablespaces dédiés par base (données, index), appartenant à <base>_owner ;
• créer la base de données avec son tablespace ;
• restreindre les autorisations de connexion (local, éventuellement trust pour un

 accès applicatif interne de confiance uniquement — jamais en écoute réseau ouverte).

Voir aussi

• PostgreSQL install — installation simple RHEL/CentOS, à comparer avec cette approche
• PostgreSQL — panorama général
• Psql — méta-commandes (\l, \du, \db...)
• Pg dump — sauvegarde de ces instances