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
# yum update
On continue ensuite en installant simplement les paquets suivants :
# yum install postgresql postgresql-server
Vous devez créer un nouveau cluster de base de données PostgreSQL avant de pouvoir commencer à créer des tables et à les charger avec des données. Un cluster de bases de données est un ensemble de bases de données gérées par une seule instance de serveur. La création d'un cluster de base de données consiste à créer les répertoires dans lesquels les données de la base de données seront placées, à générer les tables de catalogue partagées et à créer le template1 et les bases de données postgres.
La base de données template1 est une sorte de modèle utilisé pour créer de nouvelles bases de données ; tout ce qui est stocké dans template1, même les objets que vous ajoutez vous-même seront placés dans de nouvelles bases de données lors de leur création.
La base de données postgres est une base de données par défaut conçue pour être utilisée par les utilisateurs, les services publics et les applications tierces.
Le paquet Postgres que nous avons installé à l'étape précédente est accompagné d'un script pratique appelé postgresql-setup qui aide à l'administration des clusters de bases de données de bas niveau.
Pour créer un cluster de base de données, exécutez le script en utilisant l'option --initdb :
# postgresql-setup --initdb
Vous verrez le résultat suivant :
* Initializing database in '/var/lib/pgsql/data' * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
Démarrez maintenant le service PostgreSQL en utilisant systemctl :
# systemctl start postgresql
Ensuite, utilisez une fois de plus systemctl pour permettre au service de démarrer à chaque fois que le serveur démarre :
# systemctl enable postgresql
Cela donnera le résultat suivant :
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
Maintenant que PostgreSQL est opérationnel, nous verrons en revue l'utilisation des rôles pour apprendre comment fonctionne Postgres et en quoi il est différent des systèmes de gestion de bases de données similaires que vous avez pu utiliser par le passé.
La base de donnée PostgreSQL se gère par le service systemd <state> postgresql.
# systemctl status postgresql ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Tue 2020-04-14 14:56:54 CEST; 2min 39s ago Main PID: 24650 (code=exited, status=0/SUCCESS) Tasks: 0 (limit: 4701) Memory: 0B CGroup: /system.slice/postgresql.service avril 14 14:56:54 debian systemd[1]: Starting PostgreSQL RDBMS... avril 14 14:56:54 debian systemd[1]: Started PostgreSQL RDBMS
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@srv:~# su - postgres postgres@srv:~$ psql -c "SELECT version() ;" version ------------------------------------------------------------------------------------------------------------- PostgreSQL 10.14 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit (1 ligne)
Nous allons définir un mot de passe pour le compte administrateur “postgres”.
$ sudo su - postgres postgres@srv:~$ psql -c "alter user postgres with password 'StrongPassword'" ALTER ROLE
Les fichiers qui nous permettront de configurer le moteur de base de donnée se trouveront dans l'arborescence /var/lib/pgsql/data/
Le fichier principal de postgresql est /var/lib/pgsql/data/postgresql.conf
# ls -l /var/lib/pgsql/data/ total 64 drwx------. 21 postgres postgres 4096 26 oct. 21:09 . drwx------. 4 postgres postgres 125 23 oct. 16:54 .. drwx------. 5 postgres postgres 41 23 oct. 14:18 base -rw-------. 1 postgres postgres 30 26 oct. 21:09 current_logfiles drwx------. 2 postgres postgres 4096 26 oct. 21:49 global drwx------. 2 postgres postgres 84 26 oct. 21:09 log drwx------. 2 postgres postgres 6 23 oct. 14:18 pg_commit_ts drwx------. 2 postgres postgres 6 23 oct. 14:18 pg_dynshmem -rw-------. 1 postgres postgres 4411 23 oct. 16:36 pg_hba.conf -rw-------. 1 postgres postgres 1636 23 oct. 14:18 pg_ident.conf drwx------. 2 postgres postgres 6 23 oct. 14:18 pg_log drwx------. 4 postgres postgres 68 26 oct. 21:09 pg_logical drwx------. 4 postgres postgres 36 23 oct. 14:18 pg_multixact drwx------. 2 postgres postgres 18 26 oct. 21:09 pg_notify drwx------. 2 postgres postgres 6 23 oct. 14:18 pg_replslot drwx------. 2 postgres postgres 6 23 oct. 14:18 pg_serial drwx------. 2 postgres postgres 6 23 oct. 14:18 pg_snapshots drwx------. 2 postgres postgres 6 23 oct. 16:55 pg_stat drwx------. 2 postgres postgres 63 26 oct. 21:52 pg_stat_tmp drwx------. 2 postgres postgres 18 23 oct. 14:18 pg_subtrans drwx------. 2 postgres postgres 6 23 oct. 14:18 pg_tblspc drwx------. 2 postgres postgres 6 23 oct. 14:18 pg_twophase -rw-------. 1 postgres postgres 3 23 oct. 14:18 PG_VERSION drwx------. 3 postgres postgres 60 23 oct. 14:18 pg_wal drwx------. 2 postgres postgres 18 23 oct. 14:18 pg_xact -rw-------. 1 postgres postgres 88 23 oct. 14:18 postgresql.auto.conf -rw-------. 1 postgres postgres 22988 23 oct. 17:10 postgresql.conf -rw-------. 1 postgres postgres 45 26 oct. 21:09 postmaster.opts -rw-------. 1 postgres postgres 104 26 oct. 21:09 postmaster.pid
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@srv:~# su - postgres
On définit un mot de passe depuis le client SQL “psql” :
postgres@srv:~$ psql -c "ALTER USER postgres WITH password 'monpassword'" ALTER ROLE
Editez le fichier “/var/lib/pgsql/data/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 : “/var/lib/pgsql/data/pg_hba.conf”
... # Accept from anywhere host all all 0.0.0.0/0 md5 # Accept from trusted subnet host all all 192.168.1.0/24 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 Postgres 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é, Postgres 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.