PostgreSQL: Premiers pas

Comment débuter avec PostgreSQL

👋 Bienvenue sur la documentation de Stackhero !

Stackhero propose une solution PostgreSQL cloud prête à l'emploi qui offre de nombreux avantages, notamment :

  • Connexions et transferts de données illimités.
  • Interface web PgAdmin incluse.
  • De nombreux modules inclus comme PostGIS, TimescaleDB et PgVector.
  • Mises à jour simplifiées en un clic.
  • Performance optimale et sécurité renforcée grâce à une VM privée et dédiée.

Gagnez du temps et simplifiez-vous la vie : il suffit de 5 minutes pour essayer la solution PostgreSQL cloud hosting de Stackhero !

Utiliser le CLI psql de PostgreSQL

Vous pouvez gérer PostgreSQL avec son CLI officiel psql. Il est disponible à l'installation sur votre ordinateur, ce qui vous permet d'administrer votre service PostgreSQL à distance. Vous pouvez également l'exécuter via Docker. Cette méthode est souvent privilégiée car elle évite d'installer psql directement sur votre machine et facilite le changement de version.

Pour accéder à psql avec Docker, exécutez la commande suivante (remplacez 18 par la version majeure de votre service PostgreSQL) :

docker run -v $(pwd):/mnt -it postgres:18-alpine /bin/bash

Connectez-vous ensuite à votre service PostgreSQL avec :

cd /mnt
psql \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=admin

Créer un utilisateur et une base de données dans PostgreSQL

Par défaut, un utilisateur admin est créé avec les droits d'administration. Il est recommandé de créer un utilisateur et une base de données dédiés pour chaque projet que vous souhaitez héberger.

Utiliser l'interface web PgAdmin

Pour utiliser l'interface web PgAdmin, ouvrez votre domaine PostgreSQL en HTTPS (par exemple, https://<XXXXXX>.stackhero-network.com). Connectez-vous avec le nom d'utilisateur admin et le mot de passe que vous avez défini dans la configuration de votre service (visible sur votre tableau de bord Stackhero).

Créer un utilisateur

  1. Allez dans Servers / PostgreSQL, faites un clic droit sur Login/Group Roles et sélectionnez Create / Login/Group Role :

    Créer un utilisateur dans PostgreSQL avec PgAdminCréer un utilisateur dans PostgreSQL avec PgAdmin

  2. Définissez le nom de connexion :

    Définir le login utilisateurDéfinir le login utilisateur

  3. Définissez un mot de passe sécurisé pour éviter les attaques par force brute :

    Définir le mot de passe utilisateurDéfinir le mot de passe utilisateur

  4. Enfin, assurez-vous que seul le privilège "Can login" est sélectionné :

    Définir les droits utilisateurDéfinir les droits utilisateur

Cliquez sur le bouton "Save" pour créer votre utilisateur.

Créer une base de données

  1. Allez dans Servers / postgresql, faites un clic droit sur Databases et sélectionnez Create / Database... :

    Créer une base de données avec PgAdminCréer une base de données avec PgAdmin

Il est conseillé d'utiliser le même nom pour la base de données et l'utilisateur. Par exemple, si votre projet s'appelle "superWebsite", créez un utilisateur "superWebsite" et une base de données "superWebsite".

  1. Indiquez le nom de la base de données et choisissez le propriétaire (l'utilisateur que vous venez de créer) :

    Définir le nom et le propriétaire de la baseDéfinir le nom et le propriétaire de la base

Votre base de données est maintenant créée.

Utiliser le CLI psql

Créer un utilisateur

Pour créer un utilisateur sur PostgreSQL avec le CLI psql, exécutez la requête SQL suivante :

CREATE ROLE "myProject" WITH
  LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOINHERIT
  NOREPLICATION
  CONNECTION LIMIT -1
  PASSWORD 'secretPassword';

N'oubliez pas de remplacer myProject par le nom de votre projet et secretPassword par un mot de passe sécurisé. Il est également recommandé d'utiliser le nom de votre projet comme login et nom de base de données. Par exemple, si votre projet s'appelle "superWebsite", créez un utilisateur "superWebsite" et une base de données "superWebsite".

Vous pouvez générer un mot de passe sécurisé avec cette commande : openssl rand -base64 24 | tr -d '\n' | cut -c1-32

Créer une base de données

Pour créer une base de données sur PostgreSQL avec le CLI psql, exécutez la requête SQL suivante :

CREATE DATABASE "myProject"
  WITH
  OWNER = "myProject"
  ENCODING = 'UTF8'
  CONNECTION LIMIT = -1
  IS_TEMPLATE = false;

Il est conseillé d'utiliser le même nom pour la base de données et l'utilisateur. Par exemple, si votre projet s'appelle "superWebsite", créez un utilisateur "superWebsite" et une base de données "superWebsite".

Importer des données depuis votre ordinateur vers PostgreSQL

L'une des méthodes les plus simples pour importer des données depuis votre ordinateur vers votre instance PostgreSQL est d'utiliser le CLI PostgreSQL. Sur votre ordinateur, exécutez la commande suivante (remplacez <DB_NAME> par le nom de votre base et data.pgsql par le nom de votre fichier SQL) :

psql \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=<DB_NAME> \
  < data.pgsql

Exporter des données de PostgreSQL vers votre ordinateur

L'export de données depuis votre instance PostgreSQL vers votre ordinateur est tout aussi simple avec le CLI PostgreSQL.

  1. Pour exporter l'intégralité de la base (remplacez <DB_NAME> par le nom de votre base) :

    pg_dump \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=<DB_NAME> \
  > data.pgsql

  2. Pour exporter une seule table (remplacez <DB_NAME> par le nom de votre base et <TABLE_NAME> par le nom de votre table) :

    pg_dump \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=<DB_NAME> \
  --table=<TABLE_NAME> \
  > data.pgsql

Utiliser PostgreSQL avec Node.js

Pour connecter PostgreSQL avec Node.js et le package pg (aussi appelé node-postgres), vous pouvez vous inspirer de l'exemple suivant. Notez l'argument ssl, important pour garantir le chiffrement TLS :

const { Client } = require('pg');

(async () => {
  const pg = new Client({
    host: '<XXXXXX>.stackhero-network.com',
    port: <PORT>,
    user: '<USERNAME>',
    password: '<PASSWORD>',
    database: '<DATABASE>',
    ssl: {}
  });

  await pg.connect();

  const result = await pg.query('SELECT 1');

  await pg.end();
})().catch(error => {
  console.error('');
  console.error('🐞 Une erreur est survenue !');
  console.error(error);
  process.exit(1);
});

Un exemple complet d'utilisation de PostgreSQL avec Node.js et la librairie pg (avec async/await) est disponible sur ce dépôt Git : https://github.com/stackhero-io/postgresqlGettingStarted.

Utiliser PostgreSQL avec Node.js/TypeORM

Pour se connecter à PostgreSQL depuis Node.js avec TypeORM, activez le flag ssl à true comme dans l'exemple ci-dessous :

createConnection({
  type: 'postgres',
  host: '<XXXXXX>.stackhero-network.com',
  port: <PORT>,
  username: '<USERNAME>',
  password: '<PASSWORD>',
  database: '<DATABASE>',
  extra: {
    ssl: true
  }
});

Utiliser PostgreSQL avec Django

Si ce n'est pas déjà fait, installez le module psycopg qui sera utilisé pour se connecter à PostgreSQL :

pip install psycopg

Dans cette première étape, vous allez stocker le mot de passe directement dans le fichier settings.py. Cette méthode est uniquement destinée aux tests car elle n'est pas sécurisée. Plus loin dans cette documentation, vous trouverez un exemple de bonne pratique.

Ouvrez le fichier settings.py et ajoutez la configuration suivante :

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'HOST': '<XXXXXX>.stackhero-network.com',
    'PORT': <PORT>,
    'OPTIONS': {
      'sslmode': 'require',
    },
    'NAME': 'admin',
    'USER': 'admin',
    'PASSWORD': '<ADMIN_PASSWORD>'
  }
}

Attention : cet exemple n'est pas recommandé pour la production et doit être utilisé uniquement pour des tests !

Une fois la connexion fonctionnelle, vous pouvez adopter une méthode plus sécurisée pour stocker les identifiants. L'exemple suivant utilise django-environ et stocke les identifiants dans un fichier .env.

  1. Installez django-environ :

    pip install django-environ

  2. Ouvrez le fichier settings.py et modifiez-le comme suit :

    import environ
env = environ.Env()
environ.Env.read_env()

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'HOST': env('STACKHERO_POSTGRESQL_HOST'),
    'PORT': <PORT>,
    'OPTIONS': {
      'sslmode': 'require',
    },
    'NAME': 'admin',
    'USER': 'admin',
    'PASSWORD': env('STACKHERO_POSTGRESQL_ADMIN_PASSWORD')
  }
}

  3. Ouvrez ou créez le fichier .env dans le même dossier que settings.py et ajoutez :

    STACKHERO_POSTGRESQL_HOST=<XXXXXX>.stackhero-network.com
STACKHERO_POSTGRESQL_ADMIN_PASSWORD=<ADMIN_PASSWORD>

  4. Enfin, ajoutez .env à votre fichier .gitignore pour éviter que vos identifiants ne soient stockés dans votre dépôt Git :

echo ".env" >> .gitignore

Activer l'extension PostgreSQL PostGIS

L'extension PostGIS est incluse avec notre service PostgreSQL. Vous devez l'activer sur chaque base où vous souhaitez l'utiliser.

Pour activer l'extension, connectez-vous à votre base et exécutez la requête suivante :

CREATE EXTENSION postgis;

Vous pouvez vérifier que PostGIS fonctionne en consultant sa version :

SELECT PostGIS_Full_Version();

Vous pouvez aussi obtenir la liste de toutes les extensions PostGIS installées :

SELECT * FROM pg_extension WHERE extname LIKE 'postgis%';

Si besoin, vous pouvez ajouter d'autres extensions. Cependant, il est fortement conseillé de n'activer que les extensions dont vous avez réellement besoin :

-- Activer PostGIS
CREATE EXTENSION postgis;

-- Activer le support raster
CREATE EXTENSION postgis_raster;

-- Activer la topologie
CREATE EXTENSION postgis_topology;

-- Correspondance floue nécessaire pour Tiger
CREATE EXTENSION fuzzystrmatch;

-- Standardiseur basé sur des règles
CREATE EXTENSION address_standardizer;

-- Jeu de données d'exemple pour le standardiseur
CREATE EXTENSION address_standardizer_data_us;

-- Activer le geocodeur US Tiger
CREATE EXTENSION postgis_tiger_geocoder;

Attention : n'activez pas PostGIS sur la base postgres !

Désactiver l'extension PostgreSQL PostGIS

Pour retirer PostGIS d'une base, connectez-vous à la base concernée et exécutez la requête suivante :

DROP EXTENSION postgis;

Activer l'extension PgVector

Activer l'extension PgVector sur Stackhero est très simple. Exécutez la requête suivante :

CREATE EXTENSION vector;

Activer l'extension TimescaleDB

Activer l'extension TimescaleDB sur Stackhero est très simple. Exécutez la requête suivante :

CREATE EXTENSION timescaledb;

Suivre les statistiques de planification et d'exécution SQL (pg_stat_statements)

Pour activer le support de pg_stat_statements, commencez par l'activer dans la configuration PostgreSQL via le tableau de bord Stackhero. Ensuite, activez l'extension pour votre base courante en exécutant :

CREATE EXTENSION pg_stat_statements;

Pour plus d'informations, consultez la documentation officielle PostgreSQL.

Configurer PostgreSQL avec Skyvia

Utiliser Skyvia avec Stackhero for PostgreSQL est très simple. Pour garantir une connexion sécurisée, configurez les paramètres suivants :

  1. Définissez Protocol sur SSL
  2. Indiquez le Port de votre service PostgreSQL
  3. Forcez le chiffrement en mettant SSL Mode sur Require
  4. Sélectionnez un SSL TLS Protocol supérieur ou égal à 1.2

Pour plus de détails sur la configuration de Skyvia, consultez la documentation officielle Skyvia.

Exemple de configuration Skyvia avec Stackhero for PostgreSQLExemple de configuration Skyvia avec Stackhero for PostgreSQL

Autres articles pour PostgreSQL

  1. Alternative à l'arrêt d'ElephantSQL
    Ne vous inquiétez pas de l'arrêt d'ElephantSQL : Stackhero est là !