Initialiser et utiliser une base de données PostgreSQL 10/11

Mise à jour avec la version PostgreSQL 11: 28/06/2019-16h57

Bonjour et bienvenue dans ce nouvel article consacré à la prise en main de PostgreSQL. Si vous n’avez pas encore effectué l’installation de PostgreSQL, je vous recommande l’article Installer et configurer PostgreSQL 10.

Niveau: Débutant

Par où commencer

Pour ceux qui n’ont pas suivi le tutoriel sur l’installation de PostgreSQL 10, je vous invite à lire le tutoriel. sur ce sujet;

Nous allons d’abord vérifier que PostgreSQL est bien installé. Pour cela, exécutez la commande suivante:

sudo systemctl status postgresql-10

ou

sudo systemctl status postgresql-11

vous obtiendrez comme statut Active : inactive (dead) qui veut tout simplement dire que PostgreSQL est installé mais non démarré.

Initialiser et démarrer la base

Pour initialiser la base PostgreSQL, il faut exécuter la commande (comme root):

/usr/pgsql-10/bin/postgresql-10-setup 
initdb sudo systemctl enable postgresql-10 
systemctl start postgresql-10  

ou 

/usr/pgsql-10/bin/postgresql-11-setup
initdb sudo systemctl enable postgresql-11
systemctl start postgresql-11

Le première commande initialise la base de donnée en créant l’ensemble des fichiers de configuration. La 2° commande crée le service. Résultat attendu : Created symlink from …. to /usr/lib/system/system/postgresql-10.service. En suite.

Avec la dernière commande, vous avez démarré votre base.

Vous pouvez même vérifier que votre base est bien démarrée et en cours d’exécution. Utilisez alors la commande :

systemctl status postgresql-10

ou

systemctl status postgresql-11

Maintenant connectez vous en ligne de commande à psql pour modifier le mot de passe par défaut de l’utilisateur postgres.

su postgres
psql
\password
postgres postgres
\q
exit

Configurez le fichier pg_hba.conf

sudo vi /var/lib/pgsql/10/data/pg_hba.conf

ou

sudo vi /var/lib/pgsql/11/data/pg_hba.conf

Identifiez les lignes ipv4 et ipv6 et remplacez idem par md5. Puis enregistrez et quittez. En suite, faites

sudo vi /var/lib/pgsql/10/data/postgresql.conf
ou
sudo vi /var/lib/pgsql/11/data/postgresql.conf

Identifiez la ligne listen _adress = ‘localhost’ par listen _adress = ‘*’ puis enregistrez et quittez.

redémarrer votre base avec la commande sudo systemctl restart postgresql-10 puis,

Testez l’accès avec psql -U postgres

Utilisez le mot de passe que vous avez configuré précédemment. Si vous parvenez à vous connecter, cela veut dire que vous avez proprement effectuer la configuration requise. Faites vous plaisir avec votre base de données. Ci dessous je vous donnes quelques commandes pour commencer.

Les commandes de base

Maintenant que vous avez installé votre base de données. Il va falloir créer des utilisateurs, au moins une base de données et y ajouter des schémas.
Une fois connecté sur votre base en psql, les commandes de base ( méta-commande du programme psql) sont :

\l liste toutes les bases disponibles. C’est l’équivalent de la requête SELECT datname FROM pg_database; Par défaut vous allez voir 3 bases. La base postgres est créée à l’initialisation de votre système de base de données lorsque vous avez exécuté initdb. template0 et template1 sont des squelettes de base qui permettent de créer des base standards à partir de la commande CREATE DATABASE. template1 et template0 n’ont pas de statut particulier en dehors du fait que template1 est la base de données source par défaut pour la commande CREATE DATABASE. Par exemple, on pourrait supprimer template1 et la recréer à partir de template0 sans effet secondaire gênant. La base de données postgres est une simple copie de template1 et peut être supprimée et re-créée si nécessaire.
\dn liste les schémas disponibles
\c NOM_DATABASE vous permet de sélectionner une base de données.
\du permet d’afficher les rôles et les utilisateurs de la base de données. Pour déterminer l’ensemble des rôles, vous pouvez aussi utiliser la commande SELECT rolname FROM pg_roles;
\db liste les tablespace ou l’instruction sql SELECT spcname FROM pg_tablespace;
2 tablespaces sont crées à l’initialisation de la base.
pg_globale pour stocker les objets systèmes pg-default qui est l’espace logique des DB de template0 et template1.
Les tablespaces dans PostgreSQL permettent aux administrateurs de bases de données de définir l’emplacement dans le système de fichiers où seront stockés les fichiers représentant les objets de la base de données.

Autres commandes utiles

CREATE ROLE nom_utilisateur;
createuser nom_utilisateur;
GRANT admin TO nom_utilisateur;
dropuser nom_utilisateur;
CREATE ROLE nom LOGIN;
CREATE USER nom;
CREATE USER est équivalent à CREATE ROLE sauf que CREATE USER utilise LOGIN par défaut alors que CREATE ROLE ne le fait pas.
CREATE ROLE nom_utilisateur CREATEDB.

CREATE DATABASE nom;
CREATE DATABASE nom_base OWNER nom_role;
CREATE DATABASE nom_base TEMPLATE template0;
DROP DATABASE nom;

Quelques exemples pratiques

CREATE DATABASE tutoriels;
Create user dieudo with encrypted password ‘dieudo’;
grant all privileges on database tutoriels to dieudo;

CREATE TABLE formation (
code char(5) PRIMARY KEY,
titre varchar(40) NOT NULL,
date_dispo date,
format varchar(10),
duree interval hour to minute
);

• 

Pour sortir utilisez \q ou encore ctrl + D.

Résolution des problèmes

Si vous changez le port par défaut, vous allez avoir sûrement un problème à vous connecter en utilisant psql. L’erreur vous dit que le service n’est pas en exécution sur le port 5432. Pour résoudre le problème, ajoutez la variable d’environnement PGPORT. Pour faire simple, vi /etc/environment
PGPORT=__NOUVEAUPORT__ export PGPORT
Vérifiez la nouvelle valeur avec printenv PGPORT

Ce tutoriel est maintenant terminé, merci de l’avoir suivi.

Pour rester toujours informé, abonnez vous à notre newsletter.

Vous êtes Décideur, Dirigeant d’entreprise ou Architecte ?, Bref vous avez le pouvoir de décision ? Vous êtes dans une démarche de transformation profonde de votre SI, j’ai écrit un livre sur les questions à se poser pour choisir une solution ESB «  » 12 questions pour Choisir un ESB ». Vous pouvez demander votre copie ici.

A propos de moi

@Dieudonné MIAFFO – A la base je suis ingénieur informaticien de formation, avec une solide culture générale de l’informatique et un goût très prononcé pour les évolutions des systèmes d’information.
Je suis passionné par l’évolution technologique et c’est tout naturellement que j’exerce mon métier d’Architecte Expert ESB.
Je suis diplômé de SUPELEC et depuis 2012 j’interviens sur différents projets de transformation digitale. Nous accompagnons les entreprises en les aidant à tirer le meilleur de leur système d’information.

Mes sujets d’intérêt concernent principalement les ESB | API et la cybersécurité.