Comment installer PostgreSQL sur Ubuntu et gérer efficacement vos bases de données

PostgreSQL est un système de gestion de base de données relationnelle (SGBDR) open source très populaire, connu pour son évolutivité et son efficacité dans le traitement de grandes quantités de données.

Grâce à ses performances élevées, PostgreSQL est idéal pour les sites web ou les applications des grandes entreprises. Il prend également en charge divers types de données et langages de programmation, notamment PHP et Python.

Dans ce tutoriel, nous vous expliquerons comment installer PostgreSQL sur Ubuntu 22.04 et versions ultérieures. Nous aborderons également le processus d’installation de pgAdmin et les solutions aux problèmes courants liés à PostgreSQL.

Comment installer PostgreSQL sur Ubuntu

Avant de continuer, assurez-vous que votre serveur privé virtuel (VPS) fonctionne sous Ubuntu 22.04 ou une version ultérieure. Sinon, suivez notre guide pour changer le système d’exploitation dans le VPS de Hostinger.

1. Installer PostgreSQL

L’installation de PostgreSQL implique l’exécution de commandes Linux sur votre machine. Pour un système local, exécutez-les directement via Terminal, l’invite de commande Linux.

Pour une machine distante comme un VPS, connectez-vous à l’aide d’une application SSH ou du Terminal. Les utilisateurs Hostinger peuvent trouver leurs identifiants de connexion à la machine distante en accédant au menu Aperçu VPS dans hPanel.

Vous pouvez installer les paquets Ubuntu PostgreSQL à partir du référentiel local ou à l’aide du gestionnaire de paquets APT. Nous vous recommandons cette dernière option pour vous assurer d’obtenir la dernière version.

Notre VPS prend également en charge d’autres bases de données. Par exemple, vous pouvez installer MongoDB si vous préférez un SGBD NoSQL.

Option 1 – Installation à partir du référentiel APT

La méthode la plus courante pour installer PostgreSQL sur Ubuntu consiste à utiliser le référentiel officiel via le gestionnaire de paquets APT. Voici les étapes à suivre :

1. Exécutez ces commandes pour importer la clé du référentiel officiel de PostgreSQL vers le gestionnaire de paquets APT :

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg -

2. Actualisez APT pour synchroniser tous les référentiels à l’aide de cette commande :

sudo apt update

3. Installez la dernière version de PostgreSQL avec APT et les privilèges sudo :

sudo apt-get -y install postgresql

4. Pour confirmer l’installation, exécutez cette commande :

sudo -u postgres psql

5. Entrez l’instruction de requête de version PostgreSQL :

SELECT version();

Option 2 – Installation à l’aide du référentiel Ubuntu local

La méthode alternative d’installation de PostgreSQL utilise le référentiel Ubuntu local. Voici les étapes à suivre :

1. Exécutez cette commande pour actualiser le référentiel Ubuntu :

sudo apt-get update

2. Vérifiez le référentiel apt PostgreSQL disponible à l’aide de cette commande :

sudo apt show postgresql

3. Exécutez cette commande pour installer la dernière version disponible.

sudo apt install postgresql postgresql-contrib

4. Pour vérifier si l’installation de PostgreSQL a réussi, exécutez la commande suivante :

sudo -u postgres psql

5. Ensuite, exécutez cette commande pour vérifier la version actuelle de PostgreSQL :

SELECT version();

La ligne de commande devrait indiquer que la version 14.11 du serveur PostgreSQL est correctement installée, comme ceci :

2. Vérifier l’état de PostgreSQL

Après avoir installé PostgreSQL, vérifiez qu’il est bien activé et opérationnel. Utilisez l’utilitaire de commande systemctl ou service pour vérifier l’état du service :

sudo service postgresql status

sudo systemctl status postgresql

Si PostgreSQL est activé, votre ligne de commande affichera « active » et « loaded ».

Pour les utilisateurs d’Hostinger, vous pouvez également vérifier si PostgreSQL fonctionne en demandant à l’assistant IA Kodee. Il suffit de saisir « Vérifiez l’état du service postgresql dans mon VPS » et Kodee affichera l’état du service.

En plus de l’état du service, vérifiez si PostgreSQL est prêt à accepter une connexion à l’aide de cette commande :

sudo pg_isready

3. Se connecter à PostgreSQL

Lors de l’installation, PostgreSQL crée automatiquement le rôle postgres par défaut pour se connecter à la base de données. Pour passer à cet utilisateur, exécutez la commande suivante :

sudo su - postgres

Votre ligne de commande devrait maintenant commencer par postgres. Pour vous connecter à la base de données PostgreSQL, utilisez le shell PostgreSQL à l’aide de la commande suivante :

sudo psql

La commande psql créera une session dans la base de données par défaut. Si la commande est introuvable, installez manuellement le client PostgreSQL à l’aide de cette commande :

sudo apt-get install postgresql-client

Pour vérifier l’état de la connexion, exécutez cette commande à l’aide de l’invite PostgreSQL :

\conninfo

Pour quitter l’invite PostgreSQL et revenir à l’utilisateur système normal, utilisez la commande exit. Utilisez quant à vous \q pour vous déconnecter de la session interactive de la base de données.

4. Modifier le mot de passe utilisateur

Il est essentiel de modifier le mot de passe par défaut du rôle postgres pour améliorer la sécurité de la base de données. Voici comment procéder :

1. Activez l’invite PostgreSQL à l’aide de cette commande :

sudo -u postgres psql

2. Exécutez cette instruction pour modifier le mot de passe du compte postgres. N’oubliez pas de remplacer NewPassword par votre mot de passe réel :

ALTER USER postgres PASSWORD 'NewPassword';

3. Vérifiez si la modification a été appliquée avec succès à l’aide de la commande suivante :

\p

4. Redémarrez le service PostgreSQL à l’aide de cette commande :

sudo service postgresql restart

5. Créer un utilisateur sur PostgreSQL

Pour gérer l’accès à la base de données et garantir une organisation correcte des données, vous aurez besoin de plusieurs utilisateurs PostgreSQL. Le moyen le plus simple d’en créer un est d’utiliser le mode interactif. Voici les étapes à suivre :

1. Passez à l’utilisateur postgres :

sudo su - postgres

2. Exécutez cette commande pour créer un nouvel utilisateur :

createuser --interactive

3. Entrez le nom du nouveau compte PostgresQL. Pour ce tutoriel, il s’agit de TheNewUser.
4. Pour valider le nouveau rôle, connectez-vous à PostgreSQL avec le compte postgres :

sudo -u postgres psql

5. Vérifiez tous les utilisateurs à l’aide de la commande suivante :

\du

Comme indiqué dans la sortie, la base de données crée correctement TheNewUser.

6. Créer une base de données sur PostgreSQL

PostgreSQL suppose que le rôle et la base de données auront le même nom. Par conséquent, si vous créez un rôle TheNewUser, PostgreSQL accédera à une base de données portant le même nom.

Voici les étapes de configuration de la base de données PostgreSQL :

1. Exécutez la commande suivante pour passer à l’utilisateur postgres et créer une nouvelle base de données :

sudo -u postgres createdb TheNewUser

2. Pour vous connecter à la nouvelle base de données, créez un compte utilisateur Linux portant le même nom. Comme le nom d’utilisateur contient des majuscules, ajoutez l’option –force-badname.

sudo adduser TheNewUser --force-badname

3. Accédez à l’invite interactive PostgreSQL à l’aide du nouveau compte utilisateur :

sudo -u TheNewUser psql

4. Confirmez la connexion à l’aide de cette commande :

\conninfo

L’utilisateur devrait désormais être connecté à la nouvelle base de données et pouvoir créer une nouvelle table. Par exemple, entrez la commande suivante pour créer une table de données acheteurs :

CREATE TABLE buyers(usr_id INT PRIMARY KEY, usr_name VARCHAR(240) NOT NULL, usr_location VARCHAR(240) NOT NULL);

Lors de la création d’une table de base de données, tenez compte des six contraintes PostgreSQL :

• PRIMARY KEY – indique qu’une colonne peut être l’identifiant unique d’une ligne.
• FOREIGN KEY – spécifie que les valeurs d’une colonne doivent être identiques à celles des lignes d’une autre table.
• NOT NULL – garantit que la valeur d’une colonne n’est pas vide.
• CHECK – définit la valeur d’une colonne de manière à ce qu’elle satisfasse une expression booléenne, qui est soit « vraie », soit « fausse ».
• UNIQUE – garantit que les valeurs d’une colonne sont uniques parmi toutes les lignes.
• EXCLUSION – empêche les valeurs de deux lignes de se chevaucher lorsqu’elles sont comparées.

Pour ajouter des valeurs à votre table, utilisez l’instruction suivante. N’oubliez pas de remplacer les espaces réservés par la valeur appropriée et d’ajuster le nombre de lignes en conséquence :

INSERT INTO nomtableau (column1, column2, column3) VALUES (row1, row2, row3);

Pour afficher le tableau, entrez l’instruction suivante :

SELECT * FROM nomtableau

7. Configurer le serveur PostgreSQL

Configurez votre serveur PostgreSQL pour permettre à d’autres systèmes d’accéder à la base de données à distance. Pour ce faire, configurez le serveur pour qu’il écoute toutes les adresses IP en modifiant le fichier de configuration.

Avant de continuer, utilisez la commande exit pour quitter le shell PostgreSQL et revenir en tant qu’utilisateur système normal. Ensuite, procédez comme suit :

1. Ouvrez le fichier de configuration à l’aide d’un éditeur de texte. Remplacez vim si vous utilisez un autre éditeur et modifiez le répertoire en conséquence :

vim /etc/postgresql/14/main/postgresql.conf

2. Recherchez la ligne listen_addresses.
3. Supprimez le symbole # et remplacez la valeur par un astérisque. Voici à quoi cela devrait ressembler :

listen_addresses = '*'

4. Utilisez la commande :wq pour enregistrer les modifications et quitter vim.

5. Définissez la politique d’accès pour authentifier la connexion client à votre serveur PostgreSQL. Pour ce faire, ouvrez le fichier pg_hba.conf dans le même répertoire à l’aide de vim. La commande doit ressembler à ceci :

vim /etc/postgresql/14/main/pg_hba.conf

6. Ajoutez une nouvelle politique au bas du fichier en utilisant cette syntaxe :

connection database user ip_address encryption

7. Par exemple, nous allons activer toutes les connexions TCP/IP de tous les utilisateurs à toutes les bases de données PostgreSQL. Ces utilisateurs peuvent utiliser n’importe quelle adresse IP avec un mot de passe crypté MD5. La règle se présente comme suit :

host all all 0.0.0.0/0 md5

8. Redémarrez le service PostgreSQL pour appliquer les modifications.

8. Configurer une connexion à une base de données distante

Après avoir configuré la base de données, configurez l’accès à distance à PostgreSQL à l’aide de la commande psql. Pour ce faire, utilisez cette syntaxe de commande sur la machine cliente :

psql -h ip_address -p port -d database -U username

Par exemple, exécutez cette commande pour vous connecter à la base de données TheNewUser hébergée dans un VPS avec l’adresse IP 185.185.185.185 à l’aide du compte TheNewUser :

psql -h 185.185.185.185 -p 5432 -d TheNewUser -U TheNewUser

N’oubliez pas que le port PostgreSQL par défaut est 5423. Pour vérifier si la connexion est établie, exécutez cette commande :

\conninfo

Une fois connecté, vous pouvez envoyer des requêtes à la base de données pour récupérer des données. Pour activer l’accès à distance à PostgreSQL dans votre application, installez un pilote de base de données et importez le module dans votre code.

Dans ce tutoriel, nous vous montrerons comment procéder dans une application Python hébergée sur un VPS Ubuntu 22.04. Après vous être connecté via SSH, procédez comme suit :

1. Installez le gestionnaire de paquets pip à l’aide de cette commande. Si vous l’avez déjà, ignorez cette étape :

sudo apt-get install python-pip

2. Téléchargez et installez le pilote de base de données psycopg2 à l’aide de pip :

pip install psycopg2-binary

3. Ajoutez la ligne suivante à votre code d’application pour importer le module et établir une connexion avec la base de données PostgreSQL. N’oubliez pas de modifier la valeur en conséquence :

import psycopg2
# Connectez votre application à la base de données distante
conn = psycopg2.connect(host="182.182.182.182", port="5432", dbname="TestNewUser", user="TestNewUser", password="UserPassword")
# Ouvrez un curseur pour les opérations de base de données
cur = conn.cursor()
# Exécutez une requête. Remplacez table par votre propre table 
cur.execute("SELECT * FROM table")
# Récupérez les données
records = cur.fetchall()

9. Installer pgAdmin

Installez l’interface graphique web pgAdmin pour simplifier vos tâches de gestion de base de données PostgreSQL. Nous utiliserons le gestionnaire de paquets APT pour l’installer :

1. Exécutez ces commandes individuellement pour ajouter le référentiel officiel pgAdmin :

curl -fsSL https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/pgadmin.gpg

sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list'

2. Mettez à jour les listes de paquets pour synchroniser le référentiel.

sudo apt update

3. Exécutez la commande suivante pour installer pgAdmin :

sudo apt install pgadmin4

4. Une fois la configuration de l’interface graphique terminée, lancez la configuration principale de pgAdmin à l’aide de cette commande :

sudo /usr/pgadmin4/bin/setup-web.sh

5. Entrez votre adresse e-mail et votre mot de passe. Cela devrait ressembler à ceci :

Pour accéder à l’interface Web pgAdmin, saisissez l’adresse suivante dans la barre d’adresse de votre navigateur Web :

185.185.185.185/pgadmin4

Remplacez l’adresse IP par celle de votre VPS. Entrez ensuite votre adresse e-mail et votre mot de passe. Cliquez sur Connexion pour accéder au tableau de bord principal de pgAdmin.

Pour connecter PostgreSQL, sélectionnez Ajouter un nouveau serveur et poursuivez le processus de configuration.

Dépannage de l’installation de PostgreSQL

Dans cette section, nous allons examiner les problèmes courants liés à l’installation de PostgreSQL et leurs solutions.

1. Impossible de se connecter au serveur PostgreSQL

Tout d’abord, vous devez vérifier si le système client est correctement connecté à Internet. Si la connexion Internet n’est pas en cause, les autres causes courantes de cette erreur sont une base de données inactive ou des permissions de fichiers mal configurées.

Vérifiez l’état de PostgreSQL et assurez-vous qu’il est chargé et actif. Sinon, redémarrez-le à l’aide de la commande suivante :

sudo systemctl restart postgresql

Si cela ne fonctionne pas, vérifiez si les permissions du répertoire et des fichiers PostgreSQL sont définies sur 0700 et 0600. Pour ce faire, utilisez la commande suivante :

ls /var/lib/postgresql/main

Ensuite, utilisez la commande chmod pour modifier leurs permissions.

2. Problèmes de connexion PostgreSQL à partir d’un hôte distant

Une politique de connexion mal configurée peut entraîner des problèmes d’hôte distant qui vous empêchent d’accéder au serveur PostgreSQL. Pour résoudre ce problème, ouvrez votre fichier pg_hba.conf et ajoutez la politique d’accès à partir de l’adresse IP de chaque client :

host all all 0.0.0.0/0 md5

Vérifiez ensuite la valeur listen_address dans votre fichier de configuration postgres.conf. Assurez-vous qu’elle comporte un astérisque afin que PostgreSQL puisse écouter toutes les adresses IP.

Si cela ne fonctionne pas, vérifiez si votre pare-feu ferme le port d’écoute PostgreSQL. Pour le serveur Ubuntu, exécutez la commande ufw :

sudo ufw status verbose

Si la connexion depuis et vers le port 5432 est désactivée, ouvrez-la à l’aide de la commande suivante :

sudo ufw allow 5432/tcp

3. Problèmes de connexion à pgAdmin

Des problèmes de connexion à pgAdmin peuvent survenir en raison d’une erreur de connexion à la base de données. S’il s’agit d’un problème lié au réseau, essayez les solutions précédentes et réinstallez pgAdmin.

Si le problème est lié aux identifiants de connexion du compte utilisateur, entrez dans la session psql et réinitialisez le mot de passe à l’aide de cette instruction :

ALTER USER username WITH PASSWORD 'new_password';

Vous pouvez également rencontrer des problèmes de connexion si l’utilisateur ne dispose pas du privilège LOGIN. Pour l’accorder, utilisez l’instruction suivante :

ALTER USER username WITH LOGIN;

4. Conflit de versions PostgreSQL

Les conflits de version se produisent lorsque PostgreSQL est incompatible avec la base de données ou d’autres applications. La cause la plus courante est la non-mise à jour de PostgreSQL, ce qui entraîne une incompatibilité de version.

Pour résoudre ce problème, réinstallez ou mettez à jour PostgreSQL à partir du référentiel officiel APT. Mettez également à jour toutes les applications tierces, car elles peuvent être obsolètes.

Nous vous recommandons de tester la dernière version dans un environnement de développement afin d’éviter d’autres problèmes sur le serveur en production.

5. Impossible de créer un utilisateur ou une base de données PostgreSQL

Une syntaxe de commande incorrecte provoque souvent des erreurs lors de la création d’utilisateurs et de bases de données PostgreSQL. Pour y remédier, assurez-vous que votre commande se termine par un point-virgule (;).

Vous devez également exécuter la commande en utilisant l’utilisateur postgres et la ligne de commande psql. Pour ce faire, exécutez cette commande :

sudo -u postgres psql

Si votre terminal affiche ce qui suit, vous devriez pouvoir créer un utilisateur et une base de données.

postgres=#

Conclusion

PostgreSQL est un système de gestion de base de données open source développé par la communauté. Il utilise une structure logique pour stocker efficacement de grandes quantités de données et simplifier leur accès.

Dans ce tutoriel sur les systèmes de gestion de bases de données relationnelles, nous avons expliqué les étapes à suivre pour installer PostgreSQL sur Ubuntu 22.04 via l’invite de commande Linux. Voici un récapitulatif :

1. Installez PostgreSQL à partir du référentiel officiel à l’aide d’APT.
2. Exécutez systemctl status pour vérifier si Postgresql est actif.
3. Connectez-vous au compte PostgreSQL à l’aide de sudo su – postgres.
4. Modifiez le mot de passe du compte administrateur par défaut pour améliorer la sécurité.
5. Créez un nouvel utilisateur PostgreSQL pour gérer vos bases de données.
6. Créez une nouvelle base de données à l’aide de la commande createdb.
7. Configurez le serveur PostgreSQL en définissant sa politique de connexion.
8. Configurez une connexion à votre base de données PostgreSQL distante.
9. Installez pgAdmin pour fournir une interface utilisateur graphique.

Pour éviter tout problème, assurez-vous que les politiques de connexion et les identifiants de connexion pgAdmin sont correctement configurés. De plus, installez la dernière version du SGBDR via APT pour éviter tout problème d’incompatibilité.

Comment installer PostgreSQL sur Ubuntu – FAQ

Tout le contenu des tutoriels de ce site est soumis aux normes éditoriales et aux valeurs rigoureuses de Hostinger.