Configurer Cloud SQL pour PostgreSQL pour une utilisation en production

Ce document explique comment configurer Cloud SQL pour PostgreSQL pour une utilisation en production. Cloud SQL pour PostgreSQL est un service SGBDR entièrement géré qui est intégré à l'écosystème Google Cloud. Cloud SQL pour PostgreSQL dispose d'un certain nombre de fonctionnalités et de capacités principales :

Cloud Platform diffuse des applications et sert des utilisateurs dans le monde entier grâce aux fonctionnalités de sécurité gérées de Google Cloud telles que le VPC et le chiffrement automatique des données au repos et en transit.
Compatible avec l'architecture haute disponibilité, grâce à l'utilisation d'instances principale et de secours, et du basculement automatique entre elles
Compatible avec les charges de travail de base de données distribuées, en permettant la séparation lecture/écriture entre le nœud maître et les instances dupliquées avec accès en lecture sous le même cluster de base de données
Compatible avec les sauvegardes automatiques intégrées à Cloud Storage et la maintenance automatique de la base de données.
Compatible avec diverses charges de travail de traitement transactionnel en ligne (OLTP).

Déployer une instance Cloud SQL pour PostgreSQL.

Vous pouvez configurer une instance Cloud SQL pour PostgreSQL en quelques étapes à l'aide de Google Cloud Console ou de Google Cloud CLI. Ces deux méthodes sont décrites dans le présent article.

Console

Dans la console Google Cloud, accédez à la page SQL>Instances.

Accéder à la page "Instances"
Cliquez sur Créer une instance, puis sur Choisir PostgreSQL.
Sur la page Créer une instance PostgreSQL, fournissez les détails suivants :
- ID d'instance : saisissez un nom pour l'instance. Le nom de l'instance est définitif et ne peut pas être modifié ultérieurement.
- Mot de passe utilisateur par défaut : choisissez le mot de passe utilisateur postgres comme compte administrateur par défaut. (Vous pourrez créer des utilisateurs supplémentaires après le déploiement de l'instance PostgreSQL.)
- Région et Zone : sélectionnez une région et une zone. Il est recommandé de déployer l'instance PostgreSQL dans la même région que les services Google Cloud associés (par exemple, les applications) ou à proximité géographique des utilisateurs afin de réduire la latence pour le traitement des données. Une fois que vous avez sélectionné une région, vous ne pouvez plus la modifier par la suite.
- Version de la base de données : choisissez la dernière version ou, si vous en avez besoin, la version la plus récente disponible.
Cliquez sur Créer pour déployer l'instance PostgreSQL. Vous pouvez également cliquer sur Afficher les options de configuration pour définir des configurations supplémentaires.
- Options de configuration supplémentaires :
  - Connectivité : généralement, vous connectez l'instance PostgreSQL au réseau par l'intermédiaire d'une adresse IP publique, d'une adresse IP privée et de réseaux autorisés. Les réseaux autorisés sont des connexions autorisées que vous pouvez définir pour établir une connexion à distance, par exemple pour approuver une connexion à partir d'une adresse IP spécifique d'un client.
  - Type de machine et stockage : choisissez le type de machine en fonction des ressources allouées (processeurs virtuels, mémoire RAM), du type de stockage (SSD ou HDD) et de la capacité de stockage. L'augmentation de la capacité de stockage augmente également le débit de disque accepté (Mo/s) ainsi que les IOPS en lecture et en écriture pour votre base de données. Ajustez la capacité de stockage en fonction du débit de disque attendu et des exigences d'IOPS.
  - Sauvegardes automatiques et haute disponibilité : la fonctionnalité de sauvegardes automatiques, activée par défaut, permet de définir la périodicité des sauvegardes automatiques. En outre, l'option de récupération à un moment précis qui utilise les journaux préalables est également requise pour créer une instance dupliquée avec accès en lecture. Ces journaux sont mis à jour régulièrement et ils utilisent de l'espace de stockage. Pour éviter les problèmes de stockage inattendus, nous vous recommandons d'activer l'augmentation automatique de l'espace de stockage lorsque vous utilisez la récupération à un moment précis. La haute disponibilité est désactivée par défaut (multizone). Pour activer le basculement automatique, vous devez sélectionner l'option Haute disponibilité (régionale).
  - Indicateurs : ce paramètre spécifie la méthode Cloud SQL permettant de contrôler les réglages et paramètres de l'instance. Il est l'équivalent du fichier postgresql.conf d'une instance PostgreSQL non gérée. Pour obtenir la liste complète, consultez la documentation du produit. La modification de la valeur d'une option ou la définition d'une nouvelle option peuvent nécessiter un redémarrage de l'instance.
  - Maintenance : cette section spécifie la période de temps souhaitée pour les tâches de maintenance, y compris les corrections de bugs et les mises à niveau de versions mineures. Notez que les opérations de maintenance nécessitent généralement un redémarrage de l'instance et peuvent entraîner une brève interruption de service. Vous pouvez vous inscrire pour être informé par e-mail des événements de maintenance à venir.
  - Libellés : dans cette section, vous définissez des paires clé/valeur permettant de catégoriser votre instance PostgreSQL, par exemple :

gcloud

Créez l'instance PostgreSQL :

gcloud sql instances create postgresql01 \
    --cpu=2 \
    --memory=7680MB \
    --region=us-central1 --zone=us-central1-a

Attribuez un mot de passe à l'utilisateur par défaut PostgreSQL (exemple de syntaxe) :
```
gcloud sql users set-password postgres \
    --instance INSTANCE_NAME \
    --password PASSWORD
```
Vous pouvez spécifier les options supplémentaires suivantes :
- Version de la base de données : l'une des versions PostgreSQL compatibles.
- Type de stockage : SSD ou HDD.
- Capacité de stockage : paramètres du stockage initial de l'instance.
- Augmentation automatique de l'espace de stockage : automatisation de Cloud SQL permettant d'ajouter de l'espace de stockage supplémentaire lorsque l'espace disponible est presque épuisé.
- Haute disponibilité : haute disponibilité Cloud SQL.
- Sauvegardes automatiques : fenêtre d'heure de début des sauvegardes.
- Récupération à un moment précis : récupération à un moment précis et journalisation préalable.
- Intervalle de maintenance : intervalle d'une heure pendant lequel Cloud SQL peut effectuer des opérations de maintenance perturbatrice.
- Calendrier de maintenance : planification privilégiée pour effectuer des mises à jour sur l'instance PostgreSQL. Vous pouvez spécifier preview pour des mises à jour plus rapides ou production pour des mises à jour plus tardives.
- Options de base de données : options de base de données PostgreSQL permettant de contrôler les paramètres et réglages.
La commande gcloud suivante crée une instance Cloud SQL pour PostgreSQL avec des options supplémentaires :
```
gcloud sql instances create postgresql01 \
    --cpu=2 \
    --memory=7680MB \
    --region=us-central1 \
    --zone=us-central1-a \
    --database-version=POSTGRES_12 \
    --storage-type=SSD \
    --storage-size=100 \
    --storage-auto-increase \
    --availability-type=regional \
    --backup-start-time=23:30 \
    --enable-point-in-time-recovery \
    --maintenance-window-day=sun \
    --maintenance-window-hour=11 \
    --maintenance-release-channel=production \
    --database-flags max_connections=100
```
Pour en savoir plus, consultez la page Créer des instances.

Sélection d'une instance

La sélection ou le dimensionnement d'une instance implique la sélection d'un type de machine compatible avec votre charge de travail Oracle sur Cloud SQL pour PostgreSQL. Les types d'instances sont divisés en deux groupes principaux :

Machines à cœur partagé : économique.
Instances maîtres dédiées : acceptent plusieurs processeurs virtuels et formats de mémoire.

Pour en savoir plus sur les types d'instances, consultez la page Tarifs de Cloud SQL.

Pour dimensionner votre instance, commencez par analyser les ressources attribuées à votre base de données source et celles qu'elle utilise. Vous pouvez obtenir les paramètres des ressources de base de données Oracle à partir de la vue système V$OSSTAT ou d'un rapport Oracle AWR (voir les exemples suivants) :

Mémoire physique (nombre total d'octets de mémoire physique sur le serveur de base de données) :

SQL> SELECT ROUND(MAX(VALUE)/1024/1024/1024) AS MEM_SIZE_GB
     FROM V$OSSTAT
     WHERE STAT_NAME = 'PHYSICAL_MEMORY_BYTES';

Mémoire allouée :

SQL> SELECT NAME, VALUE, DISPLAY_VALUE FROM V$PARAMETER
     WHERE NAME LIKE '%sga%' OR NAME LIKE '%memory%';

Cœurs de processeurs (nombre de cœurs de processeur disponibles) :

SQL> SELECT VALUE FROM V$OSSTAT
     WHERE STAT_NAME = 'NUM_CPU_CORES';

Cœurs de processeurs (identifiés par une instance Oracle à l'aide de la vue V$LICENSE) :

SQL> SELECT CPU_CORE_COUNT_CURRENT FROM V$LICENSE;

Exemple de ressource de rapport Oracle AWR (un rapport Oracle AWR peut fournir des informations supplémentaires sur les caractéristiques de charge de travail d'une instance Oracle spécifique) :

Exemple de rapport de ressource Oracle AWR.

Lorsque vous disposez des informations sur les ressources de votre base de données source, nous vous recommandons de choisir le type d'instance Cloud SQL le plus adapté et d'exécuter des analyses comparatives. Les résultats de vos analyses comparatives vous aideront à finaliser votre choix d'instance.

Configuration de la haute disponibilité

Pour mettre en œuvre une solution de reprise après sinistre semblable à la solution Data Guard d'Oracle, Cloud SQL pour PostgreSQL offre des fonctionnalités de haute disponibilité qui permettent le basculement automatique de l'instance principale du cluster vers l'instance de secours. L'instance de secours est située dans une zone différente de la même région que l'instance principale. L'instance de secours est synchronisée via la réplication synchrone entre les disques persistants des instances principale et de secours. Cette méthode garantit que toutes les modifications de données de la table principale sont également appliquées à la base de données de secours.

En cas de défaillance de l'instance principale, par exemple une instance qui ne répond pas ou une défaillance au niveau de la zone, Cloud SQL effectue un basculement automatique. L'instance principale est surveillée grâce à des pulsations qui se produisent par intervalles d'une seconde. Le basculement s'active au bout d'environ 60 secondes sans pulsation reçue de l'instance principale. À ce stade, l'instance principale bascule vers l'instance de secours pour fournir un accès aux données transparent pour les applications ou clients tandis que les instances dupliquées avec accès en lecture restent opérationnelles. Notez que, contrairement à Active Data Guard, l'instance de secours n'est pas accessible en lecture lorsqu'elle sert de dispositif de secours. Avec Cloud SQL, seules les instances dupliquées avec accès en lecture peuvent être utilisées pour décharger les lectures de l'instance principale.

Vous pouvez activer la fonctionnalité de haute disponibilité Cloud SQL pour PostgreSQL lorsque vous créez l'instance ou pour une instance PostgreSQL existante. Pour ce faire, procédez comme suit :

Console

Sur la page de création d'instance, cliquez sur Afficher les options de configuration>Sauvegardes automatiques et haute disponibilité>Disponibilité, puis sélectionnez l'option Haute disponibilité (régionale).
Pour une instance PostgreSQL existante, modifiez l'instance PostgreSQL en suivant l'étape précédente. Cette opération nécessite un redémarrage de la base de données.
Pour lancer un basculement à des fins de test, accédez à la page Cloud SQL, puis cliquez sur Basculement.

Vous pouvez activer la restauration automatique de la même manière.

gcloud

Activez la haute disponibilité en définissant le paramètre availability-type sur regional :

gcloud sql instances create postgresql01 \
    --cpu=2 \
    --memory=7680MB \
    --region=us-central1 \
    --zone=us-central1-a \
    --availability-type=regional

Vérifiez si la haute disponibilité est déjà configurée sur une instance PostgreSQL existante :
```
gcloud sql instances describe INSTANCE_NAME
```
Si le résultat de cette commande inclut availabilityType: REGIONAL, la haute disponibilité est déjà activée. Si le résultat inclut availabilityType: ZONAL, la haute disponibilité n'est pas configurée et peut être activée à l'aide de la commande patch :
```
gcloud sql instances patch INSTANCE_NAME --availability-type REGIONAL
```
Lancez un test de basculement de l'instance principale vers l'instance de secours :
```
gcloud sql instances failover PRIMARY_INSTANCE_NAME
```
Pour revenir à la configuration antérieure, exécutez la même commande de basculement sur la nouvelle instance principale.

Administrateurs et comptes

Deux comptes utilisateur PostgreSQL par défaut sont fournis avec chaque installation Cloud SQL pour PostgreSQL. Ces comptes sont postgres et cloudsqlimportexport.

Compte Postgres

Le compte postgres est le compte administrateur. Il est l'équivalent des utilisateurs Oracle SYS ou SYSTEM dans Cloud PaaS. Comme Cloud SQL pour PostgreSQL est un service géré, l'utilisateur postgres n'est pas autorisé à accéder à certaines tables et procédures système qui requièrent des privilèges avancés, contrairement aux utilisateurs Oracle SYS ou SYSTEM.

L'utilisateur postgres fait partie du rôle cloudsqlsuperuser et possède les attributs (privilèges) suivants : CREATEROLE, CREATEDB et LOGIN. Il ne possède pas les attributs SUPERUSER ou REPLICATION.

Compte cloudsqlimportexport

Le compte cloudsqlimportexport est créé avec l'ensemble minimal de droits requis pour les opérations d'importation et d'exportation au format CSV. Vous pouvez créer vos propres utilisateurs pour effectuer ces opérations. Sans cela, vous pouvez utiliser l'utilisateur cloudsqlimportexport par défaut. L'utilisateur cloudsqlimportexport est un utilisateur système que vous ne pouvez donc pas utiliser directement.

Gestion des comptes (ajout, suppression ou modification du mot de passe)

La gestion des comptes regroupe la création de comptes utilisateur, la modification du mot de passe d'un compte existant et la suppression d'un compte qui n'est plus nécessaire. Vous pouvez effectuer ces opérations de compte en utilisant la console Google Cloud, l'outil gcloud ou le client PostgreSQL.

Console

Répertoriez les comptes existants dans la console Google Cloud : accédez à Console Cloud SQL>Sélectionner une instance PostgreSQL>Utilisateurs.
Modifiez le mot de passe du compte ou supprimez-le entièrement en cliquant sur les trois points (Plus) à côté du compte.

Dans cet écran, vous pouvez cliquer sur Créer un compte utilisateur pour créer un utilisateur PostgreSQL.

gcloud

Répertoriez les comptes utilisateur existants :
```
gcloud sql users list --instance=postgresql01
```
Le résultat ressemble à ce qui suit :
```
NAME       HOST
Postgres
```

Créez le compte utilisateur appuser, définissez un mot de passe et supprimez appuser :

gcloud sql users create appuser \
    --instance=postgresql01 --password=PASSWORD

gcloud sql users set-password appuser \
    --host=% --instance=postgresql01 --prompt-for-password

gcloud sql users delete appuser --instance=postgresql01

PostgreSQL

Effectuez ces mêmes actions directement à partir d'un client PostgreSQL standard, par exemple :
```
postgres=> create user appuser with login password 'my_password';

postgres=> alter user appuser password 'my_password';

postgres=> drop user appuser;
```
Vous pouvez configurer des autorisations au niveau de la base de données PostgreSQL (par exemple, la lecture d'une table ou d'une vue spécifique) en utilisant les commandes GRANT/REVOKE par l'intermédiaire du client PostgreSQL.

Surveillance et alertes

Cloud Logging est le principal outil de journalisation sur Google Cloud. Il permet de collecter et d'afficher différents journaux de surveillance pour des ressources telles que Cloud SQL pour PostgreSQL.

Cloud Logging vous permet d'afficher les journaux Cloud SQL pour PostgreSQL filtrés par niveau d'événement (par exemple, critique, erreur ou avertissement), par période ou par recherche de texte libre, comme dans la capture d'écran suivante.

Affichage des journaux dans Cloud Logging.

Surveillance de l'instance de base de données PostgreSQL

Les principaux outils de surveillance d'Oracle sont Enterprise Manager et Grid/Cloud Control. Ces outils vous permettent de surveiller en temps réel les instances de base de données au niveau de la session de base de données et des instructions SQL.

Cloud SQL pour PostgreSQL offre des fonctionnalités de surveillance comparables via la console Google Cloud. Vous pouvez obtenir une vue récapitulative de vos instances de base de données incluant l'utilisation du processeur, l'utilisation de l'espace de stockage, l'utilisation de la mémoire, les opérations de lecture/écriture, les connexions actives, les transactions par seconde et les volumes d'entrée/sortie en octets. Notez que Google Cloud Observability fournit des métriques de surveillance supplémentaires pour Cloud SQL pour PostgreSQL, comme par exemple les requêtes de basculement automatique et la latence de réplication entre l'instance principale et les instances dupliquées avec accès en lecture.

L'exemple suivant montre un graphique des transactions par seconde au cours des six dernières heures :

Graphique des transactions par seconde au cours des six dernières heures.

Surveillance des instances dupliquées avec accès en lecture

Vous pouvez surveiller les instances dupliquées avec accès en lecture via la console Google Cloud de la même manière que l'instance principale. Il existe des métriques spécifiques permettant de vérifier l'état de la réplication entre l'instance principale et les instances dupliquées avec accès en lecture. Ces métriques sont utilisées pour renseigner la page de présentation de l'instance dupliquée avec accès en lecture dans la console Google Cloud.

Vous pouvez également vérifier l'état de la réplication depuis la ligne de commande :

gcloud sql instances describe REPLICA_NAME

Une troisième option consiste à vérifier l'état de réplication par l'intermédiaire d'un client PostgreSQL. La commande PostgreSQL suivante vérifie l'état des instances dupliquées avec accès en lecture :

postgres=> \x on
Expanded display is on.
postgres=> select * from pg_stat_replication;
-[ RECORD 1 ]----+-------------------------------------------
pid              | 74733
usesysid         | 16388
usename          | cloudsqlreplica
application_name | PROJECT_ID:REPLICA_NAME
client_addr      | REPLICA_IP
client_hostname  |
client_port      | 41660
backend_start    | 2020-09-28 06:59:38.783981+00
backend_xmin     |
state            | streaming
sent_lsn         | 0/2939FFA8
write_lsn        | 0/2939FFA8
flush_lsn        | 0/2939FFA8
replay_lsn       | 0/2939FFA8
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2020-09-28 07:17:52.714969+00
postgres=>

Surveillance de base de données PostgreSQL

Cette section décrit des tâches de surveillance supplémentaires considérées comme routinières pour une base de données PostgreSQL.

Surveillance des sessions

La surveillance des sessions Oracle s'effectue en interrogeant les vues de performances dynamiques appelées vues "V$". Les vues V$SESSION et V$PROCESS sont couramment utilisées pour obtenir des insights en temps réel sur l'activité de la base de données grâce à des instructions SQL. Vous pouvez surveiller l'activité des sessions dans PostgreSQL de la même manière, en utilisant des commandes PostgreSQL et des instructions SQL.

La vue dynamique pg_stat_activity de PostgreSQL fournit des informations détaillées sur l'activité de session de base de données actuelle :

postgres=> \x on
postgres=> select * from pg_stat_activity where backend_type = 'client backend' and usename != 'cloudsqladmin';
-[ RECORD 1 ]----+-----------------------------------------------------------------------------------------------------
datid            | 14052
datname          | postgres
pid              | 74750
usesysid         | 16389
usename          | postgres
application_name | psql
client_addr      | CLIENT_IP
client_hostname  |
client_port      | 51904
backend_start    | 2020-09-28 07:01:30.214099+00
xact_start       | 2020-09-28 07:28:48.982115+00
query_start      | 2020-09-28 07:28:48.982115+00
state_change     | 2020-09-28 07:28:48.982117+00
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     | 88513
query            | select * from pg_stat_activity where backend_type = 'client backend' and usename != 'cloudsqladmin';
backend_type     | client backend
postgres=>

Surveillance des transactions de longue durée

Pour identifier les transactions de longue durée pouvant entraîner des problèmes de performances, interrogez la vue dynamique pg_stat_activity. Vous pouvez identifier les requêtes de longue durée en appliquant des filtres appropriés à des colonnes telles que query_start et state.

Surveillance des verrous

Vous pouvez surveiller les verrous de base de données via la vue dynamique pg_locks qui fournit des informations en temps réel sur les conflits de verrouillage pouvant causer des problèmes de performances.

Alertes

Vous pouvez utiliser les alertes en plus de la surveillance et de la journalisation. Vous pouvez également créer des alertes pour les conditions.

Scaling

Cloud SQL pour PostgreSQL est compatible avec les options de scaling vertical et horizontal.

Vous pouvez effectuer un scaling vertical en ajoutant des ressources à l'instance Cloud SQL, par exemple en augmentant lenombre de processeurs et la capacité de mémoire attribués à l'instance. Le débit réseau de votre instance dépend des valeurs que vous choisissez pour le processeur et la mémoire.

Cloud SQL est compatible avec jusqu'à 30 To d'espace de stockage. L'ajout de capacité de stockage permet généralement d'augmenter le débit et les IOPS de disque d'une instance. Notez que le débit réseau d'une instance Cloud SQL inclut les lectures/écritures de vos données (débit du disque), le contenu des requêtes, les calculs ainsi que d'autres données qui ne sont pas stockées dans votre base de données. Il est important de prendre en compte ces facteurs lorsque vous effectuez un scaling vertical de votre instance Cloud SQL.

Vous pouvez effectuer un scaling horizontal en créant des instances dupliquées avec accès en lecture. Les instances dupliquées avec accès en lecture vous permettent de mettre à l'échelle vos charges de travail de lecture sur des instances Cloud SQL distinctes, sans affecter les performances et la disponibilité de l'instance principale.

Sauvegarde et récupération

Il existe deux méthodes de sauvegarde de base de données pour Cloud SQL pour PostgreSQL : les sauvegardes à la demande et les sauvegardes automatisées. Vous pouvez effectuer des sauvegardes à la demande à tout moment. Celles-ci sont conservées jusqu'à ce que vous les supprimiez. Les sauvegardes automatisées utilisent un intervalle de sauvegarde de quatre heures et sont conservées pendant sept jours.

Vous pouvez restaurer les sauvegardes de la base de données Cloud SQL pour PostgreSQL sur la même instance, en écrasant les données existantes, ou sur une nouvelle instance. En outre, Cloud SQL pour PostgreSQL vous permet de restaurer une base de données PostgreSQL à un moment précis, à condition que la récupération à un moment précis soit activée et que l'option de sauvegarde automatique soit activée.

Cloud SQL pour PostgreSQL fournit des fonctionnalités de clonage de base de données. Le clone doit être créé à partir de l'instance principale (c'est-à-dire qu'il ne peut pas être créé à partir d'une instance dupliquée). Pour sauvegarder, restaurer et cloner des bases de données, vous pouvez utiliser la console Google Cloud ou gcloud CLI.

Automatisation

Vous pouvez utiliser l'API Cloud SQL Admin pour complètement automatiser l'administration d'une instance Cloud SQL pour PostgreSQL. L'API Admin de Cloud SQL est une API REST permettant de contrôler différents types de ressources tels que les instances, les bases de données, les utilisateurs, les indicateurs, les opérations, les certificats SSL, les niveaux et les sauvegardes. Pour plus d'informations, consultez la section Documentation sur l'API.

Étape suivante

En savoir plus sur les comptes utilisateur Cloud SQL for PostgreSQL.
Apprenez-en plus sur Cloud SQL pour PostgreSQL pour les utilisateurs d'Oracle :
Découvrez des architectures de référence, des schémas et des bonnes pratiques concernant Google Cloud. Consultez notre Centre d'architecture cloud.