Aller au contenu
learn
Menu global

PostgreSQL et Amazon Aurora — Mission

Pourquoi j’apprends ça

Section intitulée « Pourquoi j’apprends ça »

J’ai passé des années sur SQL Server : T-SQL, SSMS, plans d’exécution, colonnes IDENTITY, migrations Entity Framework. De plus en plus de projets que je rencontre démarrent plutôt sur PostgreSQL, souvent sur un service géré comme Amazon Aurora, et j’ai remarqué que j’écrivais du SQL Server dans la syntaxe de PostgreSQL. Ça marche, jusqu’à ce qu’un nom de colonne ait une majuscule, qu’un DateTime revienne décalé de trois heures, ou qu’un pool de cent connexions rencontre un serveur qui en autorise cent.

Je veux connaître PostgreSQL pour ce qu’il est : ses types, son SQL, sa façon d’exécuter les transactions et d’utiliser les index, de répliquer et d’être sauvegardé. Ensuite je veux savoir ce qu’Aurora change : ce qu’AWS gère pour moi, ce que je perds, et ce que ça coûte.

À qui s’adresse ce cours

Section intitulée « À qui s’adresse ce cours »

Tu es développeur C# ou Java. Tu connais le SQL de SQL Server ou d’une autre base : jointures, regroupements, index, transactions. Tu as déjà lancé un conteneur. Tu n’as pas besoin de connaître PostgreSQL. Le côté Java suppose le niveau de Java pour développeurs C# ; la leçon 4 renvoie les questions Spring Data à Spring Boot, Spring Cloud et Reactor pour développeurs C#, et la leçon 1 renvoie les questions de conteneurs à WSL containers. Le cours DuckDB interroge les mêmes données de CI avec un moteur analytique : c’est une bonne comparaison, pas un prérequis.

SQL Server, PostgreSQL et Aurora en un tableau

Section intitulée « SQL Server, PostgreSQL et Aurora en un tableau »
SQL Server PostgreSQL Aurora PostgreSQL
Licence commerciale (éditions Express et Developer gratuites) PostgreSQL License, open source un service AWS géré, facturé par instance ou unité de capacité, stockage et E/S
Serveur un processus, des threads un processus par connexion le moteur de PostgreSQL sur le stockage distribué d’AWS
Niveau le plus haut instance → bases → schémas cluster → bases → schémas DB cluster : un writer, des readers, un volume de stockage partagé
Compte d’administration sa, sysadmin postgres, un superutilisateur un membre de rds_superuser, jamais un vrai superutilisateur
Outil client sqlcmd, SSMS psql, pgAdmin psql et la console AWS
Depuis .NET Microsoft.Data.SqlClient Npgsql Npgsql
Depuis Java pilote JDBC de Microsoft pilote JDBC de PostgreSQL pilote JDBC de PostgreSQL, ou l’AWS Advanced JDBC Wrapper

Les données

Section intitulée « Les données »

Le cours réutilise des données que ce site publie déjà, plutôt qu’une boutique ou une bibliothèque inventée :

La leçon 2 construit le modèle que chaque leçon suivante charge depuis sql/schema.sql :

erDiagram
    RUNS ||--o{ JOBS : "a"
    JOBS ||--o{ STEPS : "a"
    PROJECTS ||--o{ PROJECT_REFS : "référence"
    PROJECTS ||--o{ PACKAGE_REFS : "utilise"
    RUNS {
        bigint run_id PK
        text workflow_name
        conclusion conclusion
        timestamptz started_at
    }
    JOBS {
        bigint job_id PK
        text_array labels
        interval duration "générée"
    }
    STEPS {
        smallint number PK
        tstzrange ran "générée"
    }
    PROJECTS {
        text path PK
        text_array frameworks
        boolean in_solution
    }
    ICONIC_CHORDS {
        integer chord_id PK
        pitch_class_array pitch_classes
        smallint_array guitar_voicing
    }

Le schéma ci contient les trois premières tables, le schéma ga les autres.

À la fin de ce cours, je saurai

Section intitulée « À la fin de ce cours, je saurai »
  • faire tourner PostgreSQL dans un conteneur et travailler dans psql, avec bases, schémas, rôles et privilèges ;
  • choisir les types de PostgreSQL délibérément : text, numeric, timestamptz, uuid, jsonb, tableaux et intervalles, avec contraintes, domaines et colonnes générées ;
  • écrire le SQL que PostgreSQL fait mieux que T-SQL : CTE récursives, fonctions de fenêtrage, LATERAL, DISTINCT ON, RETURNING, upserts et MERGE ;
  • utiliser PostgreSQL depuis C# avec Npgsql et EF Core, et depuis Java avec JDBC et HikariCP, y compris les pools, les instructions préparées et COPY ;
  • lire un plan de requête et choisir le bon index : B-tree, GIN, GiST, BRIN, index partiels et sur expression ;
  • expliquer MVCC, les niveaux d’isolation, les verrous et VACUUM, et reproduire un deadlock ;
  • stocker et chercher du JSON et du texte, et écrire des fonctions, des triggers et des extensions ;
  • partitionner de grandes tables, les répliquer, les sauvegarder, les restaurer et les mettre à niveau ;
  • faire tourner la même base sur Amazon Aurora PostgreSQL, en sachant ce qui diffère, et estimer ce qu’elle coûte, y compris une migration depuis SQL Server.

Plan

Section intitulée « Plan »
# Leçon Si tu connais SQL Server
1 Premiers pas : un conteneur, psql, bases, schémas et rôles sqlcmd, logins et utilisateurs, dbo, TOP, IDENTITY
2 Types et modélisation datetimeoffset, nvarchar, colonnes calculées, index uniques et NULL
3 Requêtes : CTE, fenêtres, LATERAL, upserts et MERGE CROSS APPLY, OUTPUT inserted.*, MERGE
4 PostgreSQL depuis C# et Java pool de SqlConnection, SqlBulkCopy, fournisseurs EF Core
5 Index et plans : B-tree, GIN, BRIN, EXPLAIN (ANALYZE, BUFFERS) plans d’exécution, colonnes incluses, index filtrés
6 Transactions et MVCC : niveaux d’isolation, verrous, VACUUM, bloat, deadlocks READ_COMMITTED_SNAPSHOT, le version store
7 JSON et recherche : opérateurs et index jsonb, recherche plein texte, pg_trgm OPENJSON, catalogues de texte intégral
8 Fonctions et extensions : PL/pgSQL, triggers, pgvector procédures T-SQL, CLR
9 Partitionnement et grandes tables fonctions et schémas de partition, SWITCH
10 Réplication et haute disponibilité : WAL, réplication physique et logique groupes de disponibilité Always On, réplication transactionnelle
11 Sauvegarde, restauration et mises à niveau : pg_dump, restauration à un instant donné, pg_upgrade BACKUP, RESTORE … STOPAT, mises à niveau sur place
12 Amazon Aurora PostgreSQL : stockage, réplicas et basculement, endpoints, Aurora Serverless, Global Database, RDS Proxy, authentification IAM Azure SQL Database, Hyperscale
13 Migrer depuis SQL Server, et les coûts : une migration à la main, par programme et avec pgloader, AWS DMS, Babelfish, les prix d’Aurora le Data Migration Assistant, SqlBulkCopy

Les leçons 1 à 11 se terminent chacune par une courte section sur ce qui change sur Aurora, d’après la documentation d’AWS ; la leçon 12 porte sur Aurora lui-même, avec un basculement reproduit en local ; la leçon 13 déplace une base SQL Server vers PostgreSQL, puis décrit AWS DMS, Babelfish et les prix d’Aurora. Rien de tout cela ne tourne sur AWS : le cours ne crée aucune ressource AWS, donc tout ce qui concerne Aurora est marqué à vérifier.

Journal : ce que j’ai essayé, ce qui m’a surpris, ce qu’il me reste à vérifier.

Ressources

Section intitulée « Ressources »