PostgreSQL est disponible sur l’AppStream par défaut de CentOS 8 et il existe plusieurs versions que vous pouvez installer.

Vous pouvez choisir entre ces versions en activant la collection appropriée de paquets et de dépendances qui s'alignent sur la version que vous voulez installer, chaque collection étant appelée module stream.

Dans un premier temps, on s'assure que le système est à jour

$ sudo apt update

On continue ensuite en installant simplement les paquets suivants :

$ sudo apt install postgresql postgresql-server

La base de donnée PostgreSQL se gère par le service systemd <state> postgresql.

postgres@pgsql:~$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Thu 2021-12-09 14:48:06 UTC; 1h 37min ago
    Process: 6547 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 6547 (code=exited, status=0/SUCCESS)

Pour vérifier l'installation, nous nous connecterons au serveur de base de données PostgreSQL à l'aide de l'utilitaire psql et vérifions la version du serveur :

root@pgsql:~# su - postgres
postgres@pgsql:~$ psql -c "SELECT version() ;"
                                                               version                                                               
-------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)

In order to install PostgreSQL on our system we need to update our repository and for that execute the below command:

sudo -u postgres psql -c “SHOW config_file;”

Output:

             config_file

—————————————– /etc/postgresql/13/main/postgresql.conf (1 row)

You need to change the listening address in the postgresql.conf configuration file showing in the command output. Also, you need to edit “pg_hba.conf” in the same directory to allow remote access.

Les fichiers qui nous permettront de configurer le moteur de base de donnée se trouveront dans l'arborescence /etc/postgresql/12/main/

Le fichier principal de postgresql est /etc/postgresql/12/main/postgresql.conf

postgres@pgsql:~$ ls -al /etc/postgresql/12/main/
total 64
drwxr-xr-x 3 postgres postgres  4096 Dec  9 14:47 .
drwxr-xr-x 3 postgres postgres  4096 Dec  9 13:42 ..
drwxr-xr-x 2 postgres postgres  4096 Dec  9 13:42 conf.d
-rw-r--r-- 1 postgres postgres   315 Dec  9 13:42 environment
-rw-r--r-- 1 postgres postgres   143 Dec  9 13:42 pg_ctl.conf
-rw-r----- 1 postgres postgres  4930 Dec  9 14:47 pg_hba.conf
-rw-r----- 1 postgres postgres  1636 Dec  9 13:42 pg_ident.conf
-rw-r--r-- 1 postgres postgres 26917 Dec  9 14:46 postgresql.conf
-rw-r--r-- 1 postgres postgres   317 Dec  9 13:42 start.conf

Par défaut, lorsque nous installons le moteur de base de donnée PostgreSQL, il n'y a pas de mot de passe pour l'administrateur de définie.

On se connecte (depuis root) à cet utilisateur (postgres) :

root@pgsql:~# su - postgres

On définit un mot de passe depuis le client SQL “psql” :

postgres@pgsql:~$ psql -c "ALTER USER postgres WITH password 'monpassword'"
ALTER ROLE

Éditez le fichier “/etc/postgresql/12/main/postgresql.conf” et définissez l'adresse d'écoute sur l'adresse IP de votre serveur ou “*” pour toutes les interfaces.

listen_addresses = '192.168.1.200'. 

Configurer également PostgreSQL pour qu'il accepte les connexions à distance en éditant le fichier : “/etc/postgresql/12/main/pg_hba.conf”

...
# Accept from anywhere
host all all 0.0.0.0/0 md5

Redémarrer le service “postgresql”

# systemctl restart postgresql

PostgreSQL utilise un concept appelé “rôles” pour gérer l'authentification et l'autorisation des clients.

Ceux-ci sont en quelque sorte similaires aux comptes réguliers de type Unix, mais PostgreSQL ne fait pas de distinction entre les utilisateurs et les groupes et préfère plutôt le terme plus souple de “rôle”.

Une fois installé, PostgreSQL est configuré pour utiliser l'authentification ident, ce qui signifie qu'il associe les rôles Postgres à un compte système Unix/Linux correspondant. Si un rôle existe au sein de Postgres, un nom d'utilisateur Unix/Linux portant le même nom peut se connecter à ce rôle.

La procédure d'installation a créé un compte utilisateur nommé postgres qui est associé au rôle Postgres par défaut.

Pour utiliser PostgreSQL, vous pouvez vous connecter à ce compte.

Il existe plusieurs façons d'utiliser ce compte pour accéder à l'invite PostgreSQL.

Basculez sur le compte postgres sur votre serveur en tapant :

# su -u postgres

Vous pouvez maintenant accéder immédiatement à une invite Postgres en tapant :

[postgres@pgsql ~]$ psql
psql (10.14)
Saisissez « help » pour l'aide.
 
postgres=#

Cela vous permettra de vous connecter à l'invite PostgreSQL, et de là, vous serez libre d'interagir immédiatement avec le système de gestion de la base de données.

Sortez de l'invite PostgreSQL en tapant :

postgres=# \q

Vous pouvez également exécuter des commandes avec le compte postgres directement en utilisant sudo. 

Par exemple, dans l'exemple précédent, on vous a demandé d'accéder à l'invite de Postgres en passant d'abord à l'écran postgres user et ensuite de lancer psql pour ouvrir l'invite de Postgres. Vous pouvez faire cela en une seule étape en exécutant la commande unique psql en tant qu'utilisateur postgres avec privilèges sudo, comme ceci :

$ sudo -u postgres psql

Cela vous permettra de vous connecter directement à Postgres sans passer par le bash shell intermédiaire.

Là encore, vous pouvez quitter la session Postgres interactive en tapant :

postgres=# \q

Dans cette étape, vous avez utilisé le compte postgres pour accéder à l'invite psql. Mais de nombreux cas d'utilisation nécessitent plusieurs rôles Postgres.

Actuellement, le rôle postgres est le seul à être configuré dans la base de données. Vous pouvez créer de nouveaux rôles à partir de la ligne de commande avec la commande createrole. L'indicateur --interactive vous invitera à indiquer le nom du nouveau rôle et vous demandera également si celui-ci doit disposer des autorisations de superutilisateur.

Si vous êtes connecté en tant que compte postgres, vous pouvez créer un nouvel utilisateur en tapant :

[postgres@srv ~]$ createuser --interactive

Si, au contraire, vous préférez utiliser sudo pour chaque commande sans quitter votre compte normal, tapez :

$ sudo -u postgres createuser --interactive

Le script vous proposera quelques choix et, en fonction de vos réponses, exécutera les commandes Postgres nécessaires pour créer un utilisateur selon vos spécifications. Pour ce tutoriel, créez un rôle nommé charles et donnez-lui des privilèges de super-utilisateur en entrant y lorsque cela est nécessaire :

Enter name of role to add: charles
Shall the new role be a superuser? (y/n) y

Vous pouvez obtenir plus de contrôle en passant des indicateurs supplémentaires. Consultez les options en consultant la page de manuel de create user: 

$ man createuser

Votre installation de Postgres a maintenant un nouveau rôle, mais vous n'avez encore ajouté aucune base de données. La section suivante décrit ce processus.

Une autre hypothèse posée par défaut par le système d'authentification Postgres est que tout rôle utilisé pour se connecter disposera d'une base de données du même nom à laquelle il pourra accéder.

Cela signifie que si l'utilisateur que vous avez créé dans la dernière section est appelé “charles”, ce rôle tentera de se connecter à une base de données qui est également appelée “charles” par défaut.

Vous pouvez créer une telle base de données avec la commande createdb.

Si vous êtes connecté en tant que compte postgres, vous devez taper quelque chose comme :

[postgres@srv ~]$ createdb charles

Si, au contraire, vous préférez utiliser sudo pour chaque commande sans quitter votre compte normal, vous devez taper :

$ sudo -u postgres createdb charles

Cette flexibilité offre de multiples façons de créer des bases de données au besoin.

Maintenant que vous avez créé une nouvelle base de données, vous allez vous y connecter avec votre nouveau rôle.

Pour vous connecter avec une authentification basée sur l'identité, vous aurez besoin d'un utilisateur Linux portant le même nom que votre rôle et votre base de données Postgres.

Si vous n'avez pas d'utilisateur Linux correspondant disponible, vous pouvez en créer un avec la commande adduser. Vous devrez le faire à partir de votre compte root ou non avec privilèges sudo (c'est-à-dire sans être connecté en tant qu'utilisateur postgres) :

# sudo adduser charles

Une fois ce nouveau compte disponible, vous pouvez basculer sur ce dernier et vous connecter à la base de données en tapant :

# sudo -i -u charles
# psql

Si vous souhaitez que votre utilisateur se connecte à une autre base de données, vous pouvez le faire en incluant le drapeau “-d” et en spécifiant la base de données, comme ceci : 

[charles@pgsql ~]$ psql -d postgres

Une fois connecté, vous pouvez vérifier vos informations de connexion actuelles en tapant :

charles=# \conninfo

Cela donnera le résultat suivant :

You are connected to database "charles" as user "charles" via socket in "/var/run/postgresql" at port "5432".

Cette commande est utile si vous vous connectez à des bases de données qui ne sont pas celles par défaut ou avec des utilisateurs qui ne sont pas ceux par défaut.

Après vous être connecté à votre base de données, vous pouvez maintenant essayer de créer et de supprimer des tables.

Tout d'abord, créez une table pour stocker certaines données. Par exemple, vous allez créer une table qui décrit certains équipements d'une aire de jeux.

créez une simple table comme celle-ci :

charles=# CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

Cette commande permet de créer une table d'inventaire des équipements de terrain de jeu. Elle commence par une identification de l'équipement, qui est de type série. Ce type de données est un entier auto-incrémenté. Vous avez également donné à cette colonne la contrainte de CLÉ PRIMAIRE ce qui signifie que les valeurs doivent être uniques et non nulles. 

Pour deux des colonnes (equip_id et install_date), la commande ne spécifie pas de longueur de champ. En effet, certains types de colonnes n'ont pas besoin d'une longueur fixe car la longueur est déduite du type.

Les deux lignes suivantes créent des colonnes pour l'équipement respectivement le type et color, chacune ne pouvant pas être vide. La ligne après ceux-ci crée une colonne location et une contrainte qui requiert que la valeur soit l'une des huit valeurs possibles. La dernière ligne crée une colonne de date qui enregistre la date à laquelle vous avez installé l'équipement.

Vous pouvez voir votre nouvelle table en tapant :

charles=# \d

Cela donnera le résultat suivant :

                  List of relations
 Schema |          Name           |   Type   | Owner
--------+-------------------------+----------+-------
 public | playground              | table    | charles
 public | playground_equip_id_seq | sequence | charles
(2 rows)

Votre table « playground » est là, mais il y a aussi un élément nommé playground_equip_id_seq qui est du type sequence. Il s'agit d'une représentation du type de série que vous avez donné à votre colonne equip_id. Ceci permet de garder une trace du prochain numéro de la séquence et est créé automatiquement pour les colonnes de ce type.

Si vous voulez voir uniquement la table sans la séquence, vous pouvez taper :

charles=# \dt

Il en résultera ce qui suit :

          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+-------
 public | playground | table | charles
(1 row)

Dans cette étape, vous avez créé un exemple de table.

Maintenant que l'on a une table, nous pouvons y insérer quelques données.

Par exemple, ajoutez une diapositive et une balançoire en appelant la table à laquelle vous voulez ajouter, en nommant les colonnes, puis en fournissant des données pour chaque colonne, comme ceci :

charles=# INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
charles=# INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

Vous devez faire attention lors de la saisie des données afin d'éviter quelques problèmes courants. Pour commencer, les noms des colonnes ne doivent pas être mis entre guillemets, mais les valeurs des colonnes que vous saisissez doivent l'être.

Une autre chose à garder à l'esprit est que vous n'entrez pas de valeur pour la colonne equip_id. En effet, il est généré automatiquement chaque fois qu'une nouvelle ligne est créée dans la table.

Récupérez les informations que vous avez ajoutées en tapant :

charles=# SELECT * FROM playground;

Vous verrez le résultat suivant :

 equip_id | type  | color  | location  | install_date
----------+-------+--------+-----------+--------------
        1 | slide | blue   | south     | 2017-04-28
        2 | swing | yellow | northwest | 2018-08-16
(2 rows)

Ici, vous pouvez voir que votre equip_id a bien été renseigné et que toutes vos autres données ont été organisées correctement.

Si le toboggan de l'aire de jeu se casse et que vous devez l'enlever, vous pouvez également enlever la ligne de votre table en tapant :

charles=# DELETE FROM playground WHERE type = 'slide';

Interrogez à nouveau la table :

charles=# SELECT * FROM playground;

Vous verrez ce qui suit :

 equip_id | type  | color  | location  | install_date
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2018-08-16
(1 row)

Remarquez que votre entrée ne fait plus partie de la table.

Maintenant que vous avez ajouté et supprimé des entrées dans votre table, vous pouvez essayer d'ajouter et de supprimer des colonnes.

Après avoir créé une table, vous pouvez la modifier pour ajouter ou supprimer des colonnes. Ajoutez une colonne pour indiquer la dernière visite de maintenance pour chaque équipement en tapant :

charles=# ALTER TABLE playground ADD last_maint date;

Si vous affichez à nouveau les informations de votre table, vous verrez que la nouvelle colonne a été ajoutée (mais qu'aucune donnée n'a été saisie) :

charles=# SELECT * FROM playground;

Vous verrez ce qui suit :

 equip_id | type  | color  | location  | install_date | last_maint
----------+-------+--------+-----------+--------------+------------
        2 | swing | yellow | northwest | 2018-08-16   |
(1 row)

La suppression d'une colonne est tout aussi simple. Si vous constatez que votre équipe de travail utilise un outil distinct pour suivre l'historique de la maintenance, vous pouvez supprimer la colonne en tapant :

charles=# ALTER TABLE playground DROP last_maint;

Cela supprime la colonne last_maint et toutes les valeurs qui s'y trouvent, mais laisse toutes les autres données intactes.

Vous pouvez mettre à jour les valeurs d'une entrée existante en interrogeant l'enregistrement que vous souhaitez et en paramétrant la colonne sur la valeur que vous souhaitez utiliser.

Vous pouvez interroger l'enregistrement swing (balançoire) ; cela correspondra à chaque balançoire de votre table et changera sa couleur en rouge :

charles=# UPDATE playground SET color = 'red' WHERE type = 'swing';

Vous pouvez vérifier que l'opération a réussi en effectuant une nouvelle requête :

charles=# SELECT * FROM playground;

Vous verrez ce qui suit :

 equip_id | type  | color | location  | install_date
----------+-------+-------+-----------+--------------
        2 | swing | red   | northwest | 2010-08-16
(1 row)

Comme vous pouvez le voir, votre balançoire est maintenant enregistrée comme étant rouge.

https://pgtune.leopard.in.ua/#/

https://github.com/le0pard/pgtune

https://community.jaguar-network.com/optimisation-dun-serveur-postgresql/

https://www.dsfc.net/logiciel-libre/postgresql/tuning-postgresql-configuration-memoire-linux/

https://www.digitalocean.com/community/tutorials/introduction-to-queries-postgresql