Installer un serveur PostgreSQL [Diablotins]

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
<jumbotron>
====== Installer un serveur PostgreSQL ======

C'est un serveur de base de données dont vous aurez certainement besoin pour alimenter vos sites web et applications.
</jumbotron>
Préparez [[https://www.freshports.org/databases/postgresql15-server/|databases/postgresql15-server]] dans vos [[ports:poudriere|poudrières]].
Dans l'exemple qui suit <label type="primary">popeye</label> est l'hôte et <label type="success">pg</label> est la //jail//.

===== Préparez une Jail =====

Construisez une [[jails:zfs|jail]] nommée <label type="success">pg</label> dans ''/jails/pg'' pour accueillir votre serveur de bases de données.

Adaptez la configuration de la //jail// <label type="success">pg</label> :

<file bash pg/etc/rc.conf.local>
hostname="pg"
</file>

<file javascript /etc/jail.conf>
pg {
        host.hostname = pg;           
        ip4.addr = "lo1|192.168.0.10";# Adresse IP à attribuer.
        path ="/jails/pg";            # où est-elle ?
        mount.devfs;                  # postgresql a besoin de /dev/null
        # allow.sysvipc=1;              # preferez la ligne qui suit
        sysvshm=inherit;              # postgresql va monter des shm
        exec.start = "/bin/sh /etc/rc";
        exec.stop = "/bin/sh /etc/rc.shutdown";
}
</file>

Notez que //postgresql// réclame quelques réglages pour une //jail//:

  * ''mount.devfs'', en fait l'installation de //postgresql// n'a besoin que de ''/dev/null''. C'est le moyen le plus rapide de l'avoir.
  * ''sysvshm=inherit'' ou ''allow.sysvipc=1'', //postgresql// doit pouvoir allouer une [[https://www.freebsd.org/cgi/man.cgi?query=shmget&apropos=0&sektion=2&manpath=FreeBSD+13.1-RELEASE&arch=default&format=html|shared memory SYSV]] pour fonctionner, ''sysvipc'' va ouvrir d'autres //IPC// que //shm//.

<code bash>
:~ ifconfig lo1 create
:~ jail -c pg
:~ pkg -j pg install databases/postgresql15-server
</code>

Pour que l'interface réseau soit créée au démarrage:
<code bash>
:~ sysrc cloned_interfaces+="lo1"
</code>

Ainsi que la jail:
<code bash>
:~ sysrc jail_enable="YES"
:~ sysrc jail_list+="pg"
</code>
===== Préparation =====

Depuis la jail, initialisez //postgresql//:

<code bash>
:~ jexec pg
:~ /usr/local/etc/rc.d/postgresql oneinitdb
</code>

Vous allez probablement permettre à l'hôte voire d'autre jail de communiquer avec votre serveur, autorisez le à écouter tout le monde:

<file bash /var/db/postgres/data15/postgresql.conf>
listen_addresses = '*'
</file>

Affinez le contrôle en autorisant tout va transiter par l’hôte:
<file whitespace /var/db/postgres/data15/pg_hba.conf>
host    all             all             192.168.0.1/24         trust
</file>

<code bash>
:~ service postgresql onestart
2020-04-11 13:54:50.404 UTC [19008] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-04-11 13:54:50.421 UTC [19008] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-04-11 13:54:50.444 UTC [19008] LOG:  ending log output to stderr
2020-04-11 13:54:50.444 UTC [19008] HINT:  Future log output will go to log destination "syslog".
</code>

changez le mot de passe administrateur du serveur de base de données:

<code postgresql>
:~ jexec pg 
:~ su - postgres 
$ psql
psql (13.8, server 15.0)
WARNING: psql major version 13, server major version 15.
         Some psql features might not work.
Type "help" for help.
postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(3 rows)

postgres=# \password postgres
Enter new password: 
Enter it again: 
postgres=# \q
</code>


Voire de le lancer au démarrage de la jail:

<code bash>
:~ sysrc -j pg postgresql_enable="YES"
</code>

Vous pouvez maintenant démarrer le serveur de base de données:
<code bash>
:~ service -j pg  postgresql start
</code>

==== Extensions ====

Selon vos besoins, installez le port [[https://www.freshports.org/databases/postgresql15-contrib/|postgresql-contrib]].
Pour, par exemple, charger l'extension [[https://www.postgresql.org/docs/15/pgstatstatements.html|pg_stat_statements]]:

<code>
shared_preload_libraries = 'pg_stat_statements'
#pg_stat_statements.track = all
#pg_stat_statements.max = 10000
</code>
===== Tests =====

Est-ce que le serveur est à l'écoute ?

<code bash>
root@popeye# jexec pg sockstat -l
USER     COMMAND    PID   FD PROTO  LOCAL ADDRESS         FOREIGN ADDRESS      
postgres postgres   36299 3  tcp4   192.168.0.10:5432     *:*
postgres postgres   36299 5  stream /tmp/.s.PGSQL.5432
root     syslogd    99367 5  dgram  /var/run/log
root     syslogd    99367 6  dgram  /var/run/logpriv
</code>

Essayons, depuis l'hôte:

<code bash>
root@popeye# telnet 192.168.0.10 5432
Trying 192.168.0.10...
Connected to 192.168.0.10.
Escape character is '^]'.
</code>

A l'aide du client [[https://www.freshports.org/databases/postgresql15-client/|databases/postgresql15-client]]
<code bash>
root@popeye# pg_isready --host=192.168.0.10
192.168.0.10:5432 - accepting connections
</code>

<code bash>
root@popeye# psql -U postgres --host=192.168.0.10 -W
Password: 
psql (13.8, server 15.0)
WARNING: psql major version 13, server major version 15.
         Some psql features might not work.
Type "help" for help.
postgres-# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(3 rows)

postgres-# \q
</code>


===== Socket unix =====

Vous avez noté la ligne de sockstat(1):

<code bash>
postgres postgres   36299 5  stream /tmp/.s.PGSQL.5432
</code>

Cela signifie que vous pouvez communiquer avec la serveur via une socket(2) unix.
\\ \\ Vous n'avez même pas besoin de configuration réseau pour communiquer avec cette //jail//:

<code bash>
root@popeye:# psql -U postgres --host=/jails/pg/tmp/ -W
Password: 
psql (13.8, server 15.0)
WARNING: psql major version 13, server major version 15.
         Some psql features might not work.
Type "help" for help.

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(3 rows)

postgres=# \q

</code>

<callout type="primary" icon="true">
Dans ce cas, vous pouvez attribuer la valeur ''disable'' à ''ip4'' et ''ip6'' de votre jail et l'isoler ainsi complètement du réseau.
</callout>

===== ZFS ======

Si vous utilisez une [[jails:zfs|jail sous ZFS]] vous pouvez améliorer les performances en paramétrant le //dataset// au mieux.

<code bash>
zfs get atime,compression,primarycache,recordsize,dedup,reservation zroot/JAILS/master/pg15
NAME                     PROPERTY      VALUE           SOURCE
zroot/JAILS/master/pg15  atime         off             inherited from zroot
zroot/JAILS/master/pg15  compression   lz4             inherited from zroot
zroot/JAILS/master/pg15  primarycache  metadata        local
zroot/JAILS/master/pg15  recordsize    128K            default
zroot/JAILS/master/pg15  dedup         off             inherited from zroot/JAILS/master
zroot/JAILS/master/pg15  reservation   1G              local
</code>

===== Mettre à jour =====

Stoppez le service:
<code bash>
service postgresql stop
</code>

Sauvegarder les répertoires  ''/usr/local/bin'' dans ''/usr/local/pgold''.
Connectez vous en tant qu'utilisateur ''postgre'':

<code bash>
su - postgres
</code>

Installez la nouvelle version puis préparer la nouvelle base de données comme indiqué dans l'article.
Demandez la migration:
<code bash>
su - postgres
pg_update -b /usr/local/pgold -B /usr/local/bin/ -d ./data11 -D ./data15
</code>

Une fois la migration terminée, démarrer le service et demandez un ''VACUUM ANALYZE;'' sur chaque base.
===== En savoir plus =====

<alert type="info">
  * [[https://pgconfigurator.cybertec.at/|Configurator]]
</alert>