Guide Complet : Migration MySQL vers PostgreSQL

Formation Professionnelle avec Exemples Réels d’Entreprise

---

LEÇON 1 : Différences Fondamentales des Types de Données

1.1 Types Entiers : La Migration Critique

Problématique Entreprise

Une entreprise e-commerce avec 50 millions d’utilisateurs migre de MySQL vers PostgreSQL. Le champ user_id INT UNSIGNED doit être converti correctement pour éviter la perte de données.

MySQL vs PostgreSQL - Tableaux de Correspondance

Type MySQL	Plage	Type PostgreSQL Équivalent	Plage	Solution Optimale
TINYINT	-128 à 127	SMALLINT	-32 768 à 32 767	SMALLINT suffit
TINYINT UNSIGNED	0 à 255	SMALLINT + CHECK	0 à 32 767	SMALLINT CHECK (val >= 0)
INT UNSIGNED	0 à 4,3 milliards	BIGINT + CHECK	0 à 9,2 quintillions	BIGINT CHECK (val >= 0)
BIGINT UNSIGNED	0 à 18 quintillions	NUMERIC(20,0)	Illimité	NUMERIC pour très grandes valeurs

Code MySQL Original

-- Base de données e-commerce existante
CREATE TABLE users (
    user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    age TINYINT UNSIGNED NOT NULL,
    total_orders INT UNSIGNED DEFAULT 0,
    loyalty_points BIGINT UNSIGNED DEFAULT 0
);

Migration PostgreSQL Correcte

-- Version PostgreSQL optimisée
CREATE TABLE users (
    user_id BIGSERIAL PRIMARY KEY,  -- BIGSERIAL pour 50M+ utilisateurs
    age SMALLINT NOT NULL CHECK (age >= 0 AND age <= 150),
    total_orders BIGINT DEFAULT 0 CHECK (total_orders >= 0),
    loyalty_points NUMERIC(20,0) DEFAULT 0 CHECK (loyalty_points >= 0)
);

Bonne Pratique Professionnelle

-- Toujours ajouter des contraintes métier en plus de CHECK
CREATE TABLE products (
    product_id BIGSERIAL PRIMARY KEY,
    stock_quantity INT CHECK (stock_quantity >= 0),
    reorder_level INT CHECK (reorder_level > 0),
    CONSTRAINT check_reorder_logic CHECK (reorder_level <= stock_quantity * 2)
);

Exemple Réel : Migration Airbnb-like

-- Avant (MySQL) : Système de réservation
CREATE TABLE bookings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    property_views BIGINT UNSIGNED DEFAULT 0
);

-- Après (PostgreSQL) : Version scalable
CREATE TABLE bookings (
    id BIGSERIAL PRIMARY KEY,
    property_views BIGINT DEFAULT 0 CHECK (property_views >= 0),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Index pour performance
CREATE INDEX idx_bookings_views ON bookings (property_views)
WHERE property_views > 1000;  -- Index partiel pour propriétés populaires

---

1.2 Types Booléens : Clarté et Performance

Problématique Entreprise

Un système de gestion de contenu (CMS) avec des millions d’articles utilise TINYINT(1) pour les statuts. La migration doit améliorer la lisibilité et les performances.

MySQL - Approche Ancienne (À Éviter)

-- Ancien système CMS
CREATE TABLE articles (
    id INT PRIMARY KEY,
    is_published TINYINT(1) DEFAULT 0,
    is_featured TINYINT(1) DEFAULT 0,
    is_premium TINYINT(1) DEFAULT 0
);

-- Requêtes peu lisibles
SELECT * FROM articles WHERE is_published = 1 AND is_featured = 1;

PostgreSQL - Approche Moderne

-- Système CMS modernisé
CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    is_published BOOLEAN DEFAULT FALSE,
    is_featured BOOLEAN DEFAULT FALSE,
    is_premium BOOLEAN DEFAULT FALSE,
    published_at TIMESTAMP WITH TIME ZONE
);

-- Requêtes plus claires
SELECT * FROM articles WHERE is_published AND is_featured;

Migration PHP - Adaptation du Code

Avant (MySQL) :

// Code PHP ancien
$is_published = 1;
$sql = "UPDATE articles SET is_published = $is_published WHERE id = ?";

Après (PostgreSQL) :

// Code PHP moderne avec PostgreSQL
$is_published = true;
$query = "UPDATE articles SET is_published = $1 WHERE id = $2";
$result = pg_query_params($conn, $query, [
    $is_published ? 't' : 'f',  // PostgreSQL accepte 't'/'f', true/false, 1/0
    $article_id
]);

Exemple Réel : Système de Permissions

-- Plateforme SaaS avec gestion de permissions
CREATE TABLE user_permissions (
    user_id BIGINT NOT NULL,
    can_create BOOLEAN DEFAULT FALSE,
    can_read BOOLEAN DEFAULT TRUE,
    can_update BOOLEAN DEFAULT FALSE,
    can_delete BOOLEAN DEFAULT FALSE,
    can_export BOOLEAN DEFAULT FALSE,
    is_admin BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    PRIMARY KEY (user_id)
);

-- Index pour requêtes de permission rapides
CREATE INDEX idx_admin_users ON user_permissions (user_id) WHERE is_admin = TRUE;

-- Requête optimisée
SELECT user_id FROM user_permissions
WHERE can_delete = TRUE AND can_export = TRUE;

---

1.3 Types ENUM et SET : Gestion des Statuts

Problématique Entreprise

Un système de workflow avec des statuts de commandes doit garantir l’intégrité des données tout en restant flexible pour de futurs ajouts.

MySQL - Approche ENUM Simple

-- Système de commandes e-commerce
CREATE TABLE orders (
    order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled'),
    priority SET('urgent', 'express', 'standard')
);

PostgreSQL - Option 1 : ENUM Typé (Recommandé)

-- Création des types ENUM réutilisables
CREATE TYPE order_status AS ENUM (
    'pending',
    'paid',
    'processing',
    'shipped',
    'delivered',
    'cancelled',
    'refunded'
);

CREATE TYPE order_priority AS ENUM ('standard', 'express', 'urgent');

-- Table avec types personnalisés
CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    status order_status DEFAULT 'pending',
    priority order_priority DEFAULT 'standard',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Index sur les statuts fréquents
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

PostgreSQL - Option 2 : Contrainte CHECK (Plus Flexible)

-- Alternative flexible pour changements fréquents
CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    tags TEXT[] DEFAULT '{}',  -- Remplace SET par tableau
    CONSTRAINT check_order_status CHECK (
        status IN ('pending', 'paid', 'processing', 'shipped', 'delivered', 'cancelled')
    )
);

-- Manipulation des tags (équivalent SET)
UPDATE orders
SET tags = ARRAY['urgent', 'express']
WHERE order_id = 1234;

-- Recherche dans les tags
SELECT * FROM orders WHERE 'urgent' = ANY(tags);

Exemple Réel : Système de Tickets Support

-- Plateforme de support client avec statuts complexes
CREATE TYPE ticket_status AS ENUM (
    'open',
    'in_progress',
    'waiting_customer',
    'waiting_internal',
    'resolved',
    'closed',
    'reopened'
);

CREATE TYPE ticket_priority AS ENUM ('low', 'normal', 'high', 'critical');

CREATE TABLE support_tickets (
    ticket_id BIGSERIAL PRIMARY KEY,
    status ticket_status DEFAULT 'open',
    priority ticket_priority DEFAULT 'normal',
    assigned_to BIGINT,
    tags TEXT[] DEFAULT '{}',  -- ['bug', 'feature', 'question']
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Statistiques par statut (rapide avec ENUM)
SELECT status, COUNT(*) as count, AVG(EXTRACT(EPOCH FROM (NOW() - created_at))) as avg_age_seconds
FROM support_tickets
WHERE status != 'closed'
GROUP BY status
ORDER BY count DESC;

Ajout de Valeurs ENUM (Important pour la Maintenance)

-- Ajouter une nouvelle valeur à un ENUM existant
ALTER TYPE order_status ADD VALUE 'on_hold' AFTER 'processing';
ALTER TYPE order_status ADD VALUE 'archived' AFTER 'cancelled';

-- ATTENTION : Les suppressions d'ENUM sont impossibles !
-- Il faut recréer le type (opération lourde en production)

---

1.4 Types Date et Heure : Fuseaux Horaires

Problématique Entreprise

Une application internationale avec des utilisateurs dans 50 pays doit gérer correctement les fuseaux horaires pour les réservations, logs, et rapports.

MySQL - Comportement Ambigu

-- MySQL : Gestion des dates limitée
CREATE TABLE events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(200),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    event_date DATETIME NOT NULL
);

-- Problème : Pas de gestion native des fuseaux horaires

PostgreSQL - Gestion Professionnelle des Fuseaux

-- PostgreSQL : Types explicites et précis
CREATE TABLE events (
    event_id BIGSERIAL PRIMARY KEY,
    event_name VARCHAR(200) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    event_date TIMESTAMP WITH TIME ZONE NOT NULL,
    event_timezone VARCHAR(50) DEFAULT 'UTC'
);

-- Trigger automatique pour updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_events_timestamp
BEFORE UPDATE ON events
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

Exemple Réel : Système de Réservation Mondial

-- Plateforme de réservation type Booking.com
CREATE TABLE hotel_bookings (
    booking_id BIGSERIAL PRIMARY KEY,
    hotel_timezone VARCHAR(50) NOT NULL,  -- 'Europe/Paris', 'America/New_York'

    -- Toujours stocker en UTC avec fuseau
    check_in TIMESTAMP WITH TIME ZONE NOT NULL,
    check_out TIMESTAMP WITH TIME ZONE NOT NULL,

    -- Métadonnées temporelles
    booked_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    cancelled_at TIMESTAMP WITH TIME ZONE,

    -- Contraintes métier
    CONSTRAINT check_dates CHECK (check_out > check_in),
    CONSTRAINT check_future CHECK (check_in > NOW())
);

-- Index pour recherches par date
CREATE INDEX idx_bookings_checkin ON hotel_bookings (check_in);
CREATE INDEX idx_bookings_upcoming ON hotel_bookings (check_in)
WHERE check_in > NOW() AND cancelled_at IS NULL;

-- Requêtes avec conversion de fuseau
SELECT
    booking_id,
    check_in AT TIME ZONE 'Europe/Paris' as check_in_paris,
    check_in AT TIME ZONE 'America/New_York' as check_in_ny,
    EXTRACT(EPOCH FROM (check_out - check_in))/3600 as duration_hours
FROM hotel_bookings
WHERE check_in::date = CURRENT_DATE;

Manipulation Avancée des Dates

-- Calculs de dates complexes (BI/Analytics)
CREATE TABLE user_activity_log (
    log_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    activity_type VARCHAR(50),
    activity_timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Requête Analytics : Activité par heure de la journée
SELECT
    EXTRACT(HOUR FROM activity_timestamp AT TIME ZONE 'Europe/Paris') as hour_of_day,
    COUNT(*) as activity_count
FROM user_activity_log
WHERE activity_timestamp > NOW() - INTERVAL '7 days'
GROUP BY hour_of_day
ORDER BY hour_of_day;

-- Cohorte utilisateurs : Inscription par semaine
SELECT
    DATE_TRUNC('week', created_at) as week_start,
    COUNT(*) as new_users
FROM users
WHERE created_at > NOW() - INTERVAL '3 months'
GROUP BY week_start
ORDER BY week_start;

---