PostgreSQL: Premiers pas

Comment débuter avec PostgreSQL

👋 Bienvenue sur la documentation de Stackhero !

Stackhero propose une solution cloud PostgreSQL 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 sans effort en un clic.
  • Performance optimale et sécurité robuste grâce à une VM privée et dédiée.

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

Utilisation de la CLI psql de PostgreSQL

Vous pouvez gérer PostgreSQL avec sa CLI officielle psql. Elle est disponible pour installation sur votre ordinateur, vous permettant de gérer votre service PostgreSQL à distance. Alternativement, vous pouvez l'exécuter via Docker. Cette méthode est souvent préférée car elle évite d'installer psql directement sur votre ordinateur et facilite le changement de versions.

Pour accéder à psql en utilisant Docker, exécutez la commande suivante (assurez-vous de remplacer 18 par le numéro de version majeure de votre service PostgreSQL) :

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

Ensuite, connectez-vous à votre service PostgreSQL en utilisant :

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

Création d'un utilisateur et d'une base de données dans PostgreSQL

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

Utilisation de l'interface web PgAdmin

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

Création d'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éation d'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 recommandé d'utiliser le même nom pour la base de données et l'utilisateur. Par exemple, si votre projet s'appelle "superWebsite", envisagez de créer un utilisateur nommé "superWebsite" et une base de données nommée "superWebsite".

  1. Définissez 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 base de donnéesDéfinir le nom et le propriétaire de la base de données

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

Utilisation de la CLI psql

Création d'un utilisateur

Pour créer un utilisateur sur PostgreSQL en utilisant la 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 nom de connexion et de base de données. Si votre projet s'appelle "superWebsite", envisagez de créer un utilisateur nommé "superWebsite" et une base de données nommée "superWebsite".

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

Création d'une base de données

Pour créer une base de données sur PostgreSQL en utilisant la 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 recommandé d'utiliser le même nom pour la base de données et l'utilisateur. Par exemple, si votre projet s'appelle "superWebsite", envisagez de créer un utilisateur nommé "superWebsite" et une base de données nommée "superWebsite".

Importation de données de votre ordinateur vers PostgreSQL

L'une des façons les plus simples d'importer des données de votre ordinateur vers votre instance PostgreSQL est d'utiliser la CLI PostgreSQL. Sur votre ordinateur, exécutez la commande suivante (remplacez <DB_NAME> par le nom de votre base de données 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

Exportation de données de PostgreSQL vers votre ordinateur

L'exportation de données de votre instance PostgreSQL vers votre ordinateur est tout aussi simple en utilisant la CLI PostgreSQL.

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

    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 de données 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

Utilisation de PostgreSQL avec Node.js

Pour connecter PostgreSQL en utilisant Node.js et le package pg (également connu sous le nom de node-postgres), vous pouvez considérer l'exemple suivant. Notez l'argument ssl, qui est important pour assurer 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 utilisant PostgreSQL avec Node.js et la bibliothèque pg (et async/await) est disponible sur ce dépôt Git : https://github.com/stackhero-io/postgresqlGettingStarted.

Utilisation de PostgreSQL avec Node.js/TypeORM

Pour se connecter à PostgreSQL depuis Node.js en utilisant TypeORM, définissez le drapeau ssl sur true comme montré dans l'exemple ci-dessous :

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

Utilisation de PostgreSQL avec Django

S'il n'est pas déjà installé, installez le module psycopg, qui sera utilisé pour se connecter à PostgreSQL :

pip install psycopg

Dans cette étape initiale, vous allez stocker le mot de passe directement dans le fichier settings.py. Cette méthode est uniquement pour les 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 est destiné uniquement à des fins de test !

Une fois votre 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 répertoire 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 vous assurer que vos identifiants ne sont pas stockés dans votre dépôt Git :

echo ".env" >> .gitignore

Activation de l'extension PostGIS de PostgreSQL

L'extension PostGIS est incluse avec notre service PostgreSQL. Vous devez l'activer sur chaque base de données où vous prévoyez de l'utiliser.

Pour activer l'extension, connectez-vous à votre base de données et exécutez cette requête :

CREATE EXTENSION postgis;

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

SELECT PostGIS_Full_Version();

Alternativement, récupérez une liste de toutes les extensions PostGIS installées :

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

Si nécessaire, vous pouvez ajouter d'autres extensions. Cependant, il est fortement recommandé de ne pas activer les extensions que vous n'utiliserez pas :

-- 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 de règles
CREATE EXTENSION address_standardizer_data_us;

-- Activer le Geocoder US Tiger
CREATE EXTENSION postgis_tiger_geocoder;

Attention : n'activez pas PostGIS sur la base de données postgres !

Désactivation de l'extension PostGIS de PostgreSQL

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

DROP EXTENSION postgis;

Activation de l'extension PgVector

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

CREATE EXTENSION vector;

Activation de l'extension TimescaleDB

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

CREATE EXTENSION timescaledb;

Suivi des statistiques de planification et d'exécution SQL (pg_stat_statements)

Pour activer le support de pg_stat_statements, activez-le d'abord dans votre configuration PostgreSQL via le tableau de bord Stackhero. Ensuite, activez l'extension pour votre base de données actuelle en exécutant :

CREATE EXTENSION pg_stat_statements;

Pour plus d'informations, veuillez vous référer à la documentation officielle de PostgreSQL.

Configuration de PostgreSQL avec Skyvia

Utiliser Skyvia avec Stackhero pour PostgreSQL est simple. Assurez une connexion sécurisée en configurant les paramètres suivants :

  1. Réglez Protocol sur SSL
  2. Réglez Port sur votre port PostgreSQL
  3. Forcez le chiffrement en réglant SSL Mode sur Require
  4. Sélectionnez un SSL TLS Protocol de 1.2 ou supérieur

Pour plus de détails sur la configuration de Skyvia, veuillez vous référer à la documentation officielle de Skyvia.

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

Other articles for PostgreSQL