Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.
# Utilisation d'extensions avec encapsuleurs de données externes
Pour prolonger la fonctionnalités à votre cluster de bases de données Édition compatible avec Aurora PostgreSQL, vous pouvez installer et utiliser diverses *extensions* PostgreSQL. Par exemple, si votre cas d'utilisation nécessite une saisie intensive de données dans de très grandes tables, vous pouvez installer l'extension `[pg\$1partman](https://pgxn.org/dist/pg_partman/doc/pg_partman.html)` pour partitionner vos données et ainsi répartir la charge de travail.
**Note**
Depuis Aurora PostgreSQL 14.5, Aurora PostgreSQL prend en charge le kit Trusted Language Extensions pour PostgreSQL. Cette fonction est mise en œuvre sous forme d'extension `pg_tle`, que vous pouvez ajouter à votre instance Aurora PostgreSQL. En utilisant cette extension, les développeurs peuvent créer leurs propres extensions PostgreSQL dans un environnement sûr qui simplifie les exigences d'installation et de configuration, ainsi qu'une grande partie des tests préliminaires pour les nouvelles extensions. Pour de plus amples informations, veuillez consulter [Utilisation de Trusted Language Extensions pour PostgreSQL](PostgreSQL_trusted_language_extension.md).
Dans certains cas, plutôt que d'installer une extension, vous pouvez ajouter un *module* spécifique à la liste de `shared_preload_libraries` dans votre groupe de paramètres de cluster de bases de données personnalisé de votre cluster de bases de données Aurora PostgreSQL. Généralement, le groupe de paramètres du cluster de bases de données par défaut charge uniquement le `pg_stat_statements`, mais plusieurs autres modules peuvent être ajoutés à la liste. Par exemple, vous pouvez ajouter une fonctionnalité de planification en ajoutant le module `pg_cron`, comme indiqué dans [Planification de la maintenance avec l’extension PostgreSQL pg\$1cron](PostgreSQL_pg_cron.md). Autre exemple, vous pouvez enregistrer les plans d'exécution des requêtes en chargeant le module `auto_explain`. Pour en savoir plus, consultez la section [Enregistrement des plans d'exécution des requêtes](https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-tune-query-performance/#) dans le centre de AWS connaissances.
Une extension qui donne accès à des données externes est plus spécifiquement appelée *foreign data wrapper* (encapsuleur de données externes) (FDW). Par exemple, l'extension `oracle_fdw` permet à votre cluster de bases de données Aurora PostgreSQL de fonctionner avec des bases de données Oracle.
Vous pouvez également spécifier précisément quelles extensions peuvent être installées sur votre instance de base de données Aurora PostgreSQL, en les répertoriant dans le paramètre `rds.allowed_extensions`. Pour plus d'informations, consultez [Restriction de l'installation des extensions PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction.html).
Vous trouverez ci-dessous des informations sur la configuration et l'utilisation de certains modules et extensions FDWs disponibles pour Aurora PostgreSQL. Par souci de simplicité, elles sont toutes appelées « extensions ». Pour obtenir la liste des extensions que vous pouvez utiliser avec les versions d'Aurora PostgreSQL actuellement disponibles, consultez [Versions d'extension pour Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html) dans les *Notes de mise à jour pour Aurora PostgreSQL*.
+ [Gestion des objets volumineux avec le module lo](PostgreSQL_large_objects_lo_extension.md)
+ [Gestion des données spatiales avec l’extension PostGIS](Appendix.PostgreSQL.CommonDBATasks.PostGIS.md)
+ [Gestion des partitions PostgreSQL avec l’extension pg\$1partman](PostgreSQL_Partitions.md)
+ [Planification de la maintenance avec l’extension PostgreSQL pg\$1cron](PostgreSQL_pg_cron.md)
+ [Utilisation de pgAudit pour journaliser l'activité de la base de données](Appendix.PostgreSQL.CommonDBATasks.pgaudit.md)
+ [Utilisation de pglogical pour synchroniser les données entre les instances](Appendix.PostgreSQL.CommonDBATasks.pglogical.md)
+ [Utilisation des bases de données Oracle avec l’extension oracle\$1fdw](postgresql-oracle-fdw.md)
+ [Utilisation de bases de données SQL Server avec l'extension tds\$1fdw](postgresql-tds-fdw.md)
# Utilisation de la prise en charge de la délégation des extensions Amazon Aurora pour PostgreSQL
Grâce à la prise en charge de la délégation des extensions d’Amazon Aurora pour PostgreSQL, vous pouvez déléguer la gestion des extensions à un utilisateur qui n’a pas besoin d’être un `rds_superuser`. Avec cette prise en charge de la délégation des extensions, un rôle appelé `rds_extension` est créé. Vous devrez l’attribuer à un utilisateur pour qu’il puisse gérer les autres extensions. Ce rôle peut créer, mettre à jour et supprimer des extensions.
Vous pouvez spécifier les extensions qui peuvent être installées sur votre instance de base de données Aurora PostgreSQL, en les répertoriant dans le paramètre `rds.allowed_extensions`. Pour en savoir plus, consultez [Utilisation des extensions PostgreSQL avec Amazon RDS pour PostgreSQL](https://docs.aws.amazon.com//AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.html).
Vous pouvez restreindre la liste des extensions disponibles qui peuvent être gérées par l’utilisateur ayant le rôle `rds_extension` à l’aide du paramètre `rds.allowed_delegated_extensions`.
La prise en charge de la délégation des extensions est disponible dans les versions suivantes :
+ Toutes les versions supérieures
+ 15.5 et versions 15 ultérieures
+ 14.10 et versions 14 ultérieures
+ 13.13 et versions 13 ultérieures
+ 12.17 et versions 12 ultérieures
**Topics**
+ [Activation de la prise en charge de la délégation des extensions pour un utilisateur](#AuroraPostgreSQL.delegated_ext_mgmt)
+ [Configuration utilisée dans la prise en charge de la délégation des extensions Aurora pour PostgreSQL](#AuroraPostgreSQL.delegated_ext_config)
+ [Désactiver la prise en charge de la délégation des extensions](#AuroraPostgreSQL.delegated_ext_disable)
+ [Avantages liés à l’utilisation de la prise en charge de la délégation des extensions Amazon Aurora](#AuroraPostgreSQL.delegated_ext_benefits)
+ [Limitation de la prise en charge de la délégation des extensions Aurora pour PostgreSQL](#AuroraPostgreSQL.delegated_ext_limit)
+ [Autorisations requises pour certaines extensions](#AuroraPostgreSQL.delegated_ext_perm)
+ [Considérations de sécurité](#AuroraPostgreSQL.delegated_ext_sec)
+ [Désactivation de la suppression d’extension en cascade](#AuroraPostgreSQL.delegated_ext_drop)
+ [Exemples d’extensions pouvant être ajoutées à l’aide de la prise en charge de la délégation des extensions](#AuroraPostgreSQL.delegated_ext_support)
## Activation de la prise en charge de la délégation des extensions pour un utilisateur
Vous devez effectuer les opérations suivantes pour activer la prise en charge de la délégation des extensions pour un utilisateur :
1. **Accorder un rôle `rds_extension` à un utilisateur** : connectez-vous à la base de données en tant que `rds_superuser` et exécutez la commande suivante.
```
Postgres => grant rds_extension to user_name;
```
1. **Définir la liste des extensions que les utilisateurs délégués pourront gérer** : `rds.allowed_delegated_extensions` permet de spécifier un sous-ensemble des extensions disponibles à l’aide de `rds.allowed_extensions` dans le paramètre de cluster de bases de données. Vous pouvez effectuer cette opération à l’un des niveaux suivants :
+ Dans le cluster ou le groupe de paramètres d'instance, via l'API AWS Management Console or. Pour de plus amples informations, veuillez consulter [Groupes de paramètres pour Amazon Aurora](USER_WorkingWithParamGroups.md).
+ Utilisez la commande suivante au niveau de la base de données :
```
alter database database_name set rds.allowed_delegated_extensions = 'extension_name_1,
extension_name_2,...extension_name_n';
```
+ Utilisez la commande suivante au niveau de l’utilisateur :
```
alter user user_name set rds.allowed_delegated_extensions = 'extension_name_1,
extension_name_2,...extension_name_n';
```
**Note**
Il n’est pas nécessaire de redémarrer la base de données après avoir modifié le paramètre dynamique `rds.allowed_delegated_extensions`.
1. **Autoriser l’utilisateur délégué à accéder aux objets créés lors du processus de création des extensions** : certaines extensions créent des objets qui nécessitent l’octroi d’autorisations supplémentaires avant que l’utilisateur ayant le rôle `rds_extension` puisse y accéder. `rds_superuser` doit accorder à l’utilisateur délégué l’accès à ces objets. L’une des options consiste à utiliser un déclencheur d’événement pour accorder automatiquement l’autorisation à l’utilisateur délégué.
**Exemple de déclencheur d'événement**
Si vous souhaitez autoriser un utilisateur délégué ayant le rôle `rds_extension` à utiliser des extensions qui nécessitent de définir des autorisations sur ses objets créés lors de la création des extensions, vous pouvez personnaliser l’exemple de déclencheur d’événement ci-dessous et ajouter uniquement les extensions pour lesquelles vous souhaitez que les utilisateurs délégués aient accès à toutes les fonctionnalités. Ce déclencheur d’événement peut être créé sur template1 (modèle par défaut). Par conséquent, toutes les bases de données créées à partir de template1 auront ce déclencheur d’événement. Lorsqu’un utilisateur délégué installe l’extension, ce déclencheur octroie automatiquement la propriété des objets créés par l’extension.
```
CREATE OR REPLACE FUNCTION create_ext()
RETURNS event_trigger AS $$
DECLARE
schemaname TEXT;
databaseowner TEXT;
r RECORD;
BEGIN
IF tg_tag = 'CREATE EXTENSION' and current_user != 'rds_superuser' THEN
RAISE NOTICE 'SECURITY INVOKER';
RAISE NOTICE 'user: %', current_user;
FOR r IN SELECT * FROM pg_catalog.pg_event_trigger_ddl_commands()
LOOP
CONTINUE WHEN r.command_tag != 'CREATE EXTENSION' OR r.object_type != 'extension';
schemaname = (
SELECT n.nspname
FROM pg_catalog.pg_extension AS e
INNER JOIN pg_catalog.pg_namespace AS n
ON e.extnamespace = n.oid
WHERE e.oid = r.objid
);
databaseowner = (
SELECT pg_catalog.pg_get_userbyid(d.datdba)
FROM pg_catalog.pg_database d
WHERE d.datname = current_database()
);
RAISE NOTICE 'Record for event trigger %, objid: %,tag: %, current_user: %, schema: %, database_owenr: %', r.object_identity, r.objid, tg_tag, current_user, schemaname, databaseowner;
IF r.object_identity = 'address_standardizer_data_us' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_gaz TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_lex TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_rules TO %I WITH GRANT OPTION;', schemaname, databaseowner);
ELSIF r.object_identity = 'dict_int' THEN
EXECUTE pg_catalog.format('ALTER TEXT SEARCH DICTIONARY %I.intdict OWNER TO %I;', schemaname, databaseowner);
ELSIF r.object_identity = 'pg_partman' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.part_config TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.part_config_sub TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.custom_time_partitions TO %I WITH GRANT OPTION;', schemaname, databaseowner);
ELSIF r.object_identity = 'postgis_topology' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT USAGE ON SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
END IF;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE EVENT TRIGGER log_create_ext ON ddl_command_end EXECUTE PROCEDURE create_ext();
```
## Configuration utilisée dans la prise en charge de la délégation des extensions Aurora pour PostgreSQL
| Nom de la configuration | Description | Valeur par défaut | Remarques | Qui peut modifier ou accorder l’autorisation |
| --- | --- | --- | --- | --- |
| `rds.allowed_delegated_extensions` | Ce paramètre limite les extensions qu’un rôle rds\$1extension peut gérer dans une base de données. Il doit s’agir d’un sous-ensemble de rds.allowed\$1extensions. | chaîne vide | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/AmazonRDS/latest/AuroraUserGuide/Aurora_delegated_ext.html) Pour en savoir plus sur la configuration de ce paramètre, consultez [Activation de la prise en charge de la délégation des extensions pour un utilisateur](#AuroraPostgreSQL.delegated_ext_mgmt). | rds\$1superuser |
| `rds.allowed_extensions` | Ce paramètre permet au client de limiter les extensions qui peuvent être installées dans l’instance de base de données RDS pour PostgreSQL. Pour plus d’informations, consultez [Restriction de l’installation des extensions PostgreSQL](https://docs.aws.amazon.com//AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction). | "\$1" | Par défaut, ce paramètre est défini sur \$1, ce qui signifie que toutes les extensions prises en charge par RDS pour PostgreSQL et Aurora PostgreSQL peuvent être créées par les utilisateurs disposant des privilèges nécessaires. Une chaîne vide signifie qu’aucune extension ne peut être installée dans l’instance de base de données Aurora PostgreSQL. | administrateur |
| `rds-delegated_extension_allow_drop_cascade` | Ce paramètre contrôle la capacité de l’utilisateur ayant le rôle `rds_extension` à supprimer l’extension à l’aide de l’option en cascade. | off | Par défaut, la propriété `rds-delegated_extension_allow_drop_cascade` a la valeur `off`. Cela signifie que les utilisateurs ayant le rôle `rds_extension` ne sont pas autorisés à supprimer une extension à l’aide de l’option en cascade. Pour qu’ils puissent effectuer cette action, le paramètre `rds.delegated_extension_allow_drop_cascade` doit être défini sur `on`. | rds\$1superuser |
## Désactiver la prise en charge de la délégation des extensions
**Désactivation partielle**
Les utilisateurs délégués ne peuvent pas créer d’extensions, mais peuvent toujours mettre à jour les extensions existantes.
+ Réinitialisez `rds.allowed_delegated_extensions` dans le groupe de paramètres de cluster de bases de données.
+ Utilisez la commande suivante au niveau de la base de données :
```
alter database database_name reset rds.allowed_delegated_extensions;
```
+ Utilisez la commande suivante au niveau de l’utilisateur :
```
alter user user_name reset rds.allowed_delegated_extensions;
```
**Désactivation complète**
La révocation du rôle `rds_extension` d’un utilisateur rétablit ses autorisations standard. L’utilisateur ne pourra plus créer, mettre à jour ni supprimer des extensions.
```
postgres => revoke rds_extension from user_name;
```
## Avantages liés à l’utilisation de la prise en charge de la délégation des extensions Amazon Aurora
Grâce à la prise en charge de la délégation des extensions d’Amazon Aurora pour PostgreSQL, vous déléguez en toute sécurité la gestion des extensions aux utilisateurs qui n’ont pas le rôle `rds_superuser`. Cette fonctionnalité présente les avantages suivants :
+ Vous pouvez facilement déléguer la gestion des extensions aux utilisateurs de votre choix.
+ Cela ne nécessite pas le rôle `rds_superuser`.
+ Permet de prendre en charge différents ensembles d’extensions pour différentes bases de données dans le même cluster de bases de données.
## Limitation de la prise en charge de la délégation des extensions Aurora pour PostgreSQL
+ Les objets créés pendant le processus de création d’une extension peuvent nécessiter des privilèges supplémentaires pour que l’extension fonctionne correctement.
## Autorisations requises pour certaines extensions
Pour créer, utiliser ou mettre à jour les extensions suivantes, l’utilisateur délégué doit disposer des privilèges nécessaires sur les fonctions, tables et schémas suivants.
| Extensions nécessitant des droits de propriété ou des autorisations | Fonction | Tables | Schema | Dictionnaire de recherche de texte | Comment |
| --- | --- | --- | --- | --- | --- |
| address\$1standardizer\$1data\$1us | | us\$1gaz, us\$1lex, us\$1lex, I.us\$1rules | | | |
| amcheck | bt\$1index\$1check, bt\$1index\$1parent\$1check | | | | |
| dict\$1int | | | | intdict | |
| pg\$1partman | | custom\$1time\$1partitions, part\$1config, part\$1config\$1sub | | | |
| pg\$1stat\$1statements | | | | | |
| PostGIS | st\$1tileenvelope | spatial\$1ref\$1sys | | | |
| postgis\$1raster | | | | | |
| postgis\$1topology | | topologie, couche | topologie | | l’utilisateur délégué doit être le propriétaire de la base de données |
| log\$1fdw | create\$1foreign\$1table\$1for\$1log\$1file | | | | |
| rds\$1tools | role\$1password\$1encryption\$1type | | | | |
| postgis\$1tiger\$1geocoder | | geocode\$1settings\$1default, geocode\$1settings | tiger | | |
| pg\$1freespacemap | pg\$1freespace | | | | |
| pg\$1visibility | pg\$1visibility | | | | |
## Considérations de sécurité
N’oubliez pas qu’un utilisateur doté d’un rôle `rds_extension` peut gérer les extensions sur toutes les bases de données sur lesquelles il dispose du privilège de connexion. Si l’intention est de permettre à l’utilisateur délégué de ne gérer une extension que sur une seule base de données, il est recommandé de révoquer tous les privilèges publics sur chaque base de données, puis d’accorder explicitement le privilège de connexion pour cette base de données spécifique à l’utilisateur délégué.
Plusieurs extensions peuvent permettre à un utilisateur d’accéder aux informations de plusieurs bases de données. Assurez-vous que les utilisateurs auxquels vous accordez `rds_extension` disposent de fonctionnalités entre les bases de données avant d’ajouter ces extensions à `rds.allowed_delegated_extensions`. Par exemple, `postgres_fdw` et `dblink` fournissent des fonctionnalités permettant d’interroger les bases de données sur la même instance ou sur des instances distantes. `log_fdw` lit les fichiers journaux du moteur Postgres, qui concernent toutes les bases de données de l’instance et peuvent contenir des requêtes lentes ou des messages d’erreur provenant de plusieurs bases de données. `pg_cron` permet d’exécuter des tâches d’arrière-plan planifiées sur l’instance de base de données et peut configurer des tâches pour qu’elles s’exécutent dans une autre base de données.
## Désactivation de la suppression d’extension en cascade
La possibilité de supprimer l’extension avec l’option en cascade par un utilisateur ayant le rôle `rds_extension` est contrôlée par le paramètre `rds.delegated_extension_allow_drop_cascade`. Par défaut, la propriété `rds-delegated_extension_allow_drop_cascade` a la valeur `off`. Cela signifie que les utilisateurs dotés du rôle `rds_extension` ne sont pas autorisés à supprimer une extension à l’aide de l’option en cascade, comme indiqué dans la requête ci-dessous.
```
DROP EXTENSION CASCADE;
```
Car cela supprime automatiquement les objets qui dépendent de l’extension, puis tous les objets qui dépendent de ces objets. Toute tentative d’utilisation de l’option en cascade entraînera une erreur.
Pour qu’ils puissent effectuer cette action, le paramètre `rds.delegated_extension_allow_drop_cascade` doit être défini sur `on`.
La modification du paramètre dynamique `rds.delegated_extension_allow_drop_cascade` ne nécessite pas de redémarrage de la base de données. Vous pouvez procéder à l’un des niveaux suivants :
+ Dans le cluster ou le groupe de paramètres d'instance, via l'API AWS Management Console or.
+ À l’aide de la commande suivante au niveau de la base de données :
```
alter database database_name set rds.delegated_extension_allow_drop_cascade = 'on';
```
+ À l’aide de la commande suivante au niveau de l’utilisateur :
```
alter role tenant_user set rds.delegated_extension_allow_drop_cascade = 'on';
```
## Exemples d’extensions pouvant être ajoutées à l’aide de la prise en charge de la délégation des extensions
+ `rds_tools`
```
extension_test_db=> create extension rds_tools;
CREATE EXTENSION
extension_test_db=> SELECT * from rds_tools.role_password_encryption_type() where rolname = 'pg_read_server_files';
ERROR: permission denied for function role_password_encryption_type
```
+ `amcheck`
```
extension_test_db=> CREATE TABLE amcheck_test (id int);
CREATE TABLE
extension_test_db=> INSERT INTO amcheck_test VALUES (generate_series(1,100000));
INSERT 0 100000
extension_test_db=> CREATE INDEX amcheck_test_btree_idx ON amcheck_test USING btree (id);
CREATE INDEX
extension_test_db=> create extension amcheck;
CREATE EXTENSION
extension_test_db=> SELECT bt_index_check('amcheck_test_btree_idx'::regclass);
ERROR: permission denied for function bt_index_check
extension_test_db=> SELECT bt_index_parent_check('amcheck_test_btree_idx'::regclass);
ERROR: permission denied for function bt_index_parent_check
```
+ `pg_freespacemap`
```
extension_test_db=> create extension pg_freespacemap;
CREATE EXTENSION
extension_test_db=> SELECT * FROM pg_freespace('pg_authid');
ERROR: permission denied for function pg_freespace
extension_test_db=> SELECT * FROM pg_freespace('pg_authid',0);
ERROR: permission denied for function pg_freespace
```
+ `pg_visibility`
```
extension_test_db=> create extension pg_visibility;
CREATE EXTENSION
extension_test_db=> select * from pg_visibility('pg_database'::regclass);
ERROR: permission denied for function pg_visibility
```
+ `postgres_fdw`
```
extension_test_db=> create extension postgres_fdw;
CREATE EXTENSION
extension_test_db=> create server myserver foreign data wrapper postgres_fdw options (host 'foo', dbname 'foodb', port '5432');
ERROR: permission denied for foreign-data wrapper postgres_fdw
```
# Gestion des objets volumineux avec le module lo
Le module lo (extension) est destiné aux utilisateurs et aux développeurs de base de données qui travaillent avec des bases de données PostgreSQL via des pilotes JDBC ou ODBC. JDBC et ODBC s’attendent à ce que la base de données gère la suppression des objets volumineux lorsque les références à ces derniers changent. Cependant, PostgreSQL ne fonctionne pas de cette façon. PostgreSQL ne suppose pas qu’un objet doit être supprimé lorsque sa référence change. Les objets restent donc sur le disque, sans référence. L’extension lo inclut une fonction qui se déclenche en cas de changement de référence afin de supprimer des objets si nécessaire.
**Astuce**
Pour déterminer si votre base de données peut bénéficier de l’extension lo, utilisez l’utilitaire `vacuumlo` pour vérifier la présence d’objets volumineux orphelins. Pour obtenir le nombre d’objets volumineux orphelins sans effectuer aucune action, exécutez l’utilitaire avec l’option `-n` (no-op). Pour savoir comment procéder, consultez [vacuumlo utility](#vacuumlo-utility).
Le module lo est disponible pour Aurora PostgreSQL 13.7, 12.11, 11.16, 10.21 et versions mineures ultérieures.
Pour installer le module (extension), vous avez besoin de privilèges `rds_superuser`. L’installation de l’extension lo ajoute ce qui suit à votre base de données :
+ `lo`— Il s'agit d'un type de données d'objet volumineux (lo) que vous pouvez utiliser pour les grands objets binaires (BLOBs) et d'autres objets de grande taille. Le type de données `lo` est un domaine du type de données `oid`. En d’autres termes, il s’agit d’un identifiant d’objet avec des contraintes facultatives. Pour en savoir plus, consultez [Identifiants d’objet](https://www.postgresql.org/docs/14/datatype-oid.html) dans la documentation PostgreSQL. En termes simples, vous pouvez utiliser le type de `lo` données pour distinguer les colonnes de votre base de données contenant de grandes références d'objets des autres identificateurs d'objets (OIDs).
+ `lo_manage` : fonction que vous pouvez utiliser dans les déclencheurs sur les colonnes de la table contenant des références d’objets volumineux. Lorsque vous supprimez ou modifiez une valeur qui fait référence à un objet volumineux, le déclencheur dissocie l’objet (`lo_unlink`) de sa référence. Utilisez le déclencheur sur une colonne uniquement si la colonne est la seule référence de base de données à l’objet volumineux.
Pour en savoir plus sur le module d’objets volumineux, consultez [lo](https://www.postgresql.org/docs/current/lo.html) dans la documentation PostgreSQL.
## Installation de l’extension lo
Avant d’installer l’extension lo, assurez-vous que vous disposez de privilèges `rds_superuser`.
**Pour installer l’extension Io**
1. Utilisez `psql` pour vous connecter à l’instance de base de données principale de votre cluster de bases de données Aurora PostgreSQL.
```
psql --host=your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
Lorsque vous y êtes invité, saisissez votre mot de passe. Le client `psql` se connecte à la base de données de connexions administratives par défaut, `postgres=>`, et l’affiche sous forme d’invite.
1. Installez l’extension comme suit.
```
postgres=> CREATE EXTENSION lo;
CREATE EXTENSION
```
Vous pouvez désormais utiliser le type de données `lo` pour définir des colonnes dans vos tables. Vous pouvez, par exemple, créer une table (`images`) qui contient des données d’image tramée. Vous pouvez utiliser le type de données `lo` pour une colonne `raster`, comme illustré dans l’exemple suivant, qui crée une table.
```
postgres=> CREATE TABLE images (image_name text, raster lo);
```
## Utilisation de la fonction de déclenchement lo\$1manage pour supprimer des objets
Vous pouvez utiliser la fonction `lo_manage` dans un déclencheur sur une colonne `lo` ou d’autres colonnes d’objets volumineux pour les nettoyer (et éviter les objets orphelins) lorsque `lo` est mis à jour ou supprimé.
**Pour configurer des déclencheurs sur les colonnes qui font référence à des objets volumineux**
+ Effectuez l’une des actions suivantes :
+ Créez un déclencheur BEFORE UPDATE OR DELETE sur chaque colonne de sorte qu’il contienne des références uniques à des objets volumineux, en utilisant le nom de colonne comme argument.
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON images
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
+ Appliquez un déclencheur uniquement lorsque la colonne est en cours de mise à jour.
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OF images
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
La fonction de déclenchement `lo_manage` fonctionne uniquement dans le contexte de l’insertion ou de la suppression de données de colonne, en fonction de la façon dont vous définissez le déclencheur. Elle n’a aucun effet lorsque vous effectuez une opération `DROP` ou `TRUNCATE` sur une base de données. Cela signifie que vous devriez supprimer les colonnes d’objets de n’importe quelle table avant de procéder à la suppression de la base de données, pour éviter la création d’objets orphelins.
Par exemple, supposons que vous souhaitiez supprimer la base de données contenant la table `images`. Vous supprimez la colonne comme suit.
```
postgres=> DELETE FROM images COLUMN raster
```
En supposant que la fonction `lo_manage` est définie sur cette colonne pour gérer les suppressions, vous pouvez maintenant supprimer la table en toute sécurité.
## Suppression d’objets volumineux orphelins à l’aide de `vacuumlo`
L’utilitaire `vacuumlo` identifie les objets volumineux orphelins et peut les supprimer des bases de données. Cet utilitaire est disponible depuis PostgreSQL 9.1.24. Si les utilisateurs de base de données travaillent régulièrement avec des objets volumineux, nous vous recommandons d’exécuter `vacuumlo` occasionnellement pour nettoyer les objets volumineux orphelins.
Avant d’installer l’extension lo, vous pouvez utiliser `vacuumlo` pour déterminer si votre cluster de bases de données Aurora PostgreSQL peut en bénéficier. Pour ce faire, exécutez `vacuumlo` avec l’option `-n` (no-op) pour afficher les objets qui seraient supprimés, comme illustré dans l’exemple suivant :
```
$ vacuumlo -v -n -h your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com -p 5433 -U postgres docs-lab-spatial-db
Password:*****
Connected to database "docs-lab-spatial-db"
Test run: no large objects will be removed!
Would remove 0 large objects from database "docs-lab-spatial-db".
```
Comme indiqué dans la sortie, les objets volumineux orphelins ne posent aucun problème pour cette base de données.
Pour plus d’informations sur cet utilitaire, consultez [https://www.postgresql.org/docs/current/vacuumlo.html](https://www.postgresql.org/docs/current/vacuumlo.html) dans la documentation PostgreSQL.
## Comprendre le fonctionnement d’`vacuumlo`
La `vacuumlo` commande supprime les grands objets orphelins (LOs) de votre base de données PostgreSQL sans affecter ni entrer en conflit avec vos tables utilisateur.
La commande fonctionne comme suit :
1. `vacuumlo`commence par créer une table temporaire contenant tous les Object IDs (OIDs) des grands objets de votre base de données.
1. `vacuumlo` parcourt ensuite chaque colonne de la base de données qui utilise les types de données `oid` ou `lo`. Si `vacuumlo` identifie un OID correspondant dans ces colonnes, il le supprime de la table temporaire. `vacuumlo` ne vérifie que les colonnes portant spécifiquement le nom `oid` ou `lo`, et non les domaines définis à partir de ces types.
1. Les entrées restantes de la table temporaire représentent des entrées orphelines LOs, qui sont `vacuumlo` ensuite supprimées en toute sécurité.
## Amélioration des performances de `vacuumlo`
Vous pouvez potentiellement améliorer les performances de `vacuumlo` en augmentant la taille du lot à l’aide de l’option `-l`. Cela permet d'`vacuumlo`en traiter plusieurs LOs à la fois.
Si la mémoire disponible sur votre système est suffisante pour maintenir la table temporaire en mémoire, augmenter le paramètre `temp_buffers` au niveau de la base de données peut contribuer à de meilleures performances. La table peut ainsi être conservée entièrement en mémoire, ce qui améliore les performances globales.
La requête suivante estime la taille de la table temporaire :
```
SELECT
pg_size_pretty(SUM(pg_column_size(oid))) estimated_lo_temp_table_size
FROM
pg_largeobject_metadata;
```
## Considérations relatives aux objets volumineux
Voici quelques points importants à garder à l’esprit lors de l’utilisation d’objets volumineux.
+ `Vacuumlo`est la seule solution car il n'existe actuellement aucune autre méthode pour supprimer les orphelins LOs.
+ Les outils tels que pglogical, la réplication logique native et AWS DMS qui utilisent des technologies de réplication ne prennent pas en charge la réplication d'objets de grande taille.
+ Lorsque vous concevez le schéma de votre base de données, évitez d’utiliser des objets volumineux dans la mesure du possible et envisagez d’utiliser d’autres types de données, comme `bytea`.
+ Exécutez `vacuumlo` régulièrement, au moins une fois par semaine, pour éviter les problèmes avec les orphelins LOs.
+ Utilisez un déclencheur avec cette `lo_manage` fonction sur les tables qui stockent des objets de grande taille pour empêcher la création d'orphelins LOs .
# Gestion des données spatiales avec l’extension PostGIS
PostGIS est une extension de PostgreSQL pour le stockage et la gestion des informations spatiales. Pour en savoir plus sur PostGIS, consultez [PostGIS.net](https://postgis.net/).
À partir de la version 10.5, PostgreSQL prend en charge la bibliothèque libprotobuf 1.3.0 utilisée par PostGIS pour travailler avec les données des tuiles vectorielles des boîtes de cartes.
La configuration de l’extension PostGIS nécessite des privilèges `rds_superuser`. Nous vous recommandons de créer un utilisateur (rôle) pour gérer l’extension PostGIS et vos données spatiales. L’extension PostGIS et ses composants associés ajoutent des milliers de fonctions à PostgreSQL. Pensez à créer l’extension PostGIS dans son propre schéma si cela est logique pour votre cas d’utilisation. L’exemple suivant montre comment installer l’extension dans sa propre base de données, mais cela n’est pas nécessaire.
**Topics**
+ [Étape 1 : créer un utilisateur (rôle) pour gérer l’extension PostGIS](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Connect)
+ [Étape 2 : Chargez les extensions PostGIS](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.LoadExtensions)
+ [Étape 3 : Transfert de la propriété des schémas d’extension](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferOwnership)
+ [Étape 4 : Transfert de la propriété des tables PostGIS](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferObjects)
+ [Étape 5 : Testez les extensions](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Test)
+ [Étape 6 : Mettre à niveau l’extension PostGIS](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Update)
+ [Versions de l’extension PostGIS](#CHAP_PostgreSQL.Extensions.PostGIS)
+ [Mise à niveau de PostGIS 2 vers PostGIS 3](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-3)
## Étape 1 : créer un utilisateur (rôle) pour gérer l’extension PostGIS
Tout d’abord, connectez-vous à votre instance de base de données RDS pour PostgreSQL en tant qu’utilisateur disposant de privilèges `rds_superuser`. Si vous avez conservé le nom par défaut lors de la configuration de votre instance, vous vous connectez en tant que `postgres`.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
Créez un rôle (utilisateur) distinct pour administrer l’extension PostGIS.
```
postgres=> CREATE ROLE gis_admin LOGIN PASSWORD 'change_me';
CREATE ROLE
```
Accordez des privilèges `rds_superuser` à ce rôle, pour lui permettre d’installer l’extension.
```
postgres=> GRANT rds_superuser TO gis_admin;
GRANT
```
Créez une base de données à utiliser pour vos artefacts PostGIS. Cette étape est facultative. Vous pouvez également créer un schéma dans votre base de données utilisateur pour les extensions PostGIS, mais cela n’est pas non plus nécessaire.
```
postgres=> CREATE DATABASE lab_gis;
CREATE DATABASE
```
Accordez à `gis_admin` tous les privilèges sur la base de données `lab_gis`.
```
postgres=> GRANT ALL PRIVILEGES ON DATABASE lab_gis TO gis_admin;
GRANT
```
Quittez la session et reconnectez-vous à votre instance de base de données RDS pour PostgreSQL en tant que `gis_admin`.
```
postgres=> psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=gis_admin --password --dbname=lab_gis
Password for user gis_admin:...
lab_gis=>
```
Continuez à configurer l’extension comme indiqué dans les étapes suivantes.
## Étape 2 : Chargez les extensions PostGIS
L’extension PostGIS comprend plusieurs extensions connexes qui fonctionnent ensemble pour fournir des fonctionnalités géospatiales. En fonction de votre cas d’utilisation, vous n’aurez peut-être pas besoin de toutes les extensions créées dans cette étape.
Utilisez les instructions `CREATE EXTENSION` pour charger les extensions PostGIS.
```
CREATE EXTENSION postgis;
CREATE EXTENSION
CREATE EXTENSION postgis_raster;
CREATE EXTENSION
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
CREATE EXTENSION postgis_topology;
CREATE EXTENSION
CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION
```
Vous pouvez vérifier les résultats en exécutant la requête SQL présentée dans cet exemple, qui répertorie les extensions et leurs propriétaires.
```
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
List of schemas
Name | Owner
--------------+-----------
public | postgres
tiger | rdsadmin
tiger_data | rdsadmin
topology | rdsadmin
(4 rows)
```
## Étape 3 : Transfert de la propriété des schémas d’extension
Utilisez les instructions ALTER SCHEMA pour transférer la propriété des schémas au rôle `gis_admin`.
```
ALTER SCHEMA tiger OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA tiger_data OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA topology OWNER TO gis_admin;
ALTER SCHEMA
```
Vous pouvez confirmer le changement de propriétaire en exécutant la requête SQL suivante. Vous pouvez également utiliser la méta-commande `\dn` à partir de la ligne de commande psql.
```
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
List of schemas
Name | Owner
--------------+---------------
public | postgres
tiger | gis_admin
tiger_data | gis_admin
topology | gis_admin
(4 rows)
```
## Étape 4 : Transfert de la propriété des tables PostGIS
**Note**
Ne changez pas la propriété des fonctions PostGIS. Le bon fonctionnement et les futures mises à niveau de PostGIS nécessitent que ces fonctions conservent leur propriété d’origine. Pour plus d’informations sur les autorisations PostGIS, consultez [Sécurité de PostgreSQL](https://postgis.net/workshops/postgis-intro/security.html).
Utilisez la fonction suivante pour transférer la propriété des tables PostGIS au rôle `gis_admin`. Exécutez l’instruction suivante à partir de l’invite psql pour créer la fonction.
```
CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
CREATE FUNCTION
```
Ensuite, exécutez cette requête pour exécuter la fonction `exec` qui à son tour exécute les instructions et modifie les autorisations.
```
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO gis_admin;')
FROM (
SELECT nspname, relname
FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname in ('tiger','topology') AND
relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;
```
## Étape 5 : Testez les extensions
Pour éviter d’avoir à spécifier le nom du schéma, ajoutez le schéma `tiger` à votre chemin de recherche en utilisant la commande suivante.
```
SET search_path=public,tiger;
SET
```
Testez le schéma `tiger` à l’aide de l’instruction SELECT suivante.
```
SELECT address, streetname, streettypeabbrev, zip
FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
address | streetname | streettypeabbrev | zip
---------+------------+------------------+-------
1 | Devonshire | Pl | 02109
(1 row)
```
Pour en savoir plus sur cette extension, consultez [Tiger Geocoder](https://postgis.net/docs/Extras.html#Tiger_Geocoder) dans la documentation de PostGIS.
Testez l’accès au schéma `topology` en utilisant l’instruction `SELECT` suivante. Cela appelle la fonction `createtopology` qui enregistre un nouvel objet topologique (my\$1new\$1topo) avec l’identifiant de référence spatiale spécifié (26986) et la tolérance par défaut (0.5). Pour en savoir plus, consultez [CreateTopology](https://postgis.net/docs/CreateTopology.html)la documentation de PostGIS.
```
SELECT topology.createtopology('my_new_topo',26986,0.5);
createtopology
----------------
1
(1 row)
```
## Étape 6 : Mettre à niveau l’extension PostGIS
Chaque nouvelle version de PostgreSQL prend en charge une ou plusieurs versions de l’extension PostGIS compatibles avec cette version. La mise à niveau du moteur PostgreSQL vers une nouvelle version ne met pas automatiquement à niveau l’extension PostGIS. Avant de mettre à niveau le moteur PostgreSQL, vous mettez généralement à niveau PostGIS vers la version la plus récente disponible pour la version actuelle de PostgreSQL. Pour en savoir plus, consultez [Versions de l’extension PostGIS](#CHAP_PostgreSQL.Extensions.PostGIS).
Après la mise à niveau du moteur PostgreSQL, vous mettez à nouveau à niveau l’extension PostGIS, vers la version prise en charge par la nouvelle version du moteur PostgreSQL. Pour obtenir plus d’informations sur la mise à niveau du moteur PostgreSQL, consultez [Test d’une mise à niveau de votre cluster de bases de données de production vers une nouvelle version majeure](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary).
Vous pouvez vérifier à tout moment si des mises à jour de l’extension PostGIS sont disponibles sur votre cluster de bases de données Aurora PostgreSQL . Pour ce faire, exécutez la commande suivante. Cette fonction est disponible avec PostGIS 2.5.0 et les versions ultérieures.
```
SELECT postGIS_extensions_upgrade();
```
Si votre application ne prend pas en charge la dernière version de PostGIS, vous pouvez installer une version plus ancienne de PostGIS qui est disponible dans votre version majeure comme suit.
```
CREATE EXTENSION postgis VERSION "2.5.5";
```
Si vous souhaitez effectuer une mise à niveau vers une version PostGIS spécifique à partir d’une version antérieure, vous pouvez également utiliser la commande suivante.
```
ALTER EXTENSION postgis UPDATE TO "2.5.5";
```
Selon la version à partir de laquelle vous effectuez la mise à niveau, vous devrez peut-être utiliser à nouveau cette fonction. Le résultat de la première exécution de la fonction détermine si une mise à niveau supplémentaire est nécessaire. C’est le cas, par exemple, pour la mise à niveau de PostGIS 2 vers PostGIS 3. Pour plus d’informations, consultez [Mise à niveau de PostGIS 2 vers PostGIS 3](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-3).
Si vous avez mis à niveau cette extension pour vous préparer à une mise à niveau de version majeure du moteur PostgreSQL, vous pouvez continuer avec d’autres tâches préliminaires. Pour plus d’informations, consultez [Test d’une mise à niveau de votre cluster de bases de données de production vers une nouvelle version majeure](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary).
## Versions de l’extension PostGIS
Nous vous recommandons d’installer les versions de toutes les extensions, telles que PostGIS, telles qu’elles sont répertoriées dans [Versions des extensions pour l’Édition compatible avec PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html) dans *Notes de mise à jour d’Aurora PostgreSQL*. Pour obtenir une liste des versions qui sont disponibles dans votre version, utilisez la commande suivante.
```
SELECT * FROM pg_available_extension_versions WHERE name='postgis';
```
Vous pouvez trouver des informations sur les versions dans les sections suivantes des *Notes de mise à jour d’Aurora PostgreSQL* :
+ [Versions d’extension pour Aurora PostgreSQL 14](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.14)
+ [Versions d’extension pour Aurora Édition compatible avec PostgreSQL 13](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.13)
+ [Versions d’extension pour Aurora Édition compatible avec PostgreSQL 12](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.12)
+ [Versions d’extension pour Aurora Édition compatible avec PostgreSQL 11](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.11)
+ [Versions d’extension pour Aurora Édition compatible avec PostgreSQL 10](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.10)
+ [Versions d’extension pour Aurora Édition compatible avec PostgreSQL 9.6](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.96)
## Mise à niveau de PostGIS 2 vers PostGIS 3
Depuis la version 3.0, la fonctionnalité matricielle de PostGIS est désormais une extension distincte, `postgis_raster`. Cette extension dispose de son propre chemin d’installation et de mise à niveau. Cela supprime des dizaines de fonctions, de types de données et d’autres artefacts nécessaires au traitement des images matricielles de l’extension `postgis` de base. Cela signifie que si votre cas d’utilisation ne nécessite pas de traitement matriciel, vous n’avez pas besoin d’installer l’extension `postgis_raster`.
Dans l’exemple de mise à niveau suivant, la première commande de mise à niveau extrait la fonctionnalité raster dans l’extension `postgis_raster`. Une deuxième commande de mise à niveau est alors nécessaire pour mettre à niveau `postgis_raster` vers la nouvelle version.
**Pour effectuer une mise à niveau de PostGIS 2 vers PostGIS 3**
1. Identifiez la version par défaut de PostGIS qui est disponible pour la version de PostgreSQL sur votre cluster de bases de données Aurora PostgreSQL. Pour ce faire, exécutez la requête suivante.
```
SELECT * FROM pg_available_extensions
WHERE default_version > installed_version;
name | default_version | installed_version | comment
---------+-----------------+-------------------+------------------------------------------------------------
postgis | 3.1.4 | 2.3.7 | PostGIS geometry and geography spatial types and functions
(1 row)
```
1. Identifiez les versions de PostGIS installées dans chaque base de données sur l’instance d’enregistreur de votre cluster de bases de données Aurora PostgreSQL. En d’autres termes, interrogez chaque base de données utilisateur comme suit.
```
SELECT
e.extname AS "Name",
e.extversion AS "Version",
n.nspname AS "Schema",
c.description AS "Description"
FROM
pg_catalog.pg_extension e
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
e.extname LIKE '%postgis%'
ORDER BY
1;
Name | Version | Schema | Description
---------+---------+--------+---------------------------------------------------------------------
postgis | 2.3.7 | public | PostGIS geometry, geography, and raster spatial types and functions
(1 row)
```
Ce décalage entre la version par défaut (PostGIS 3.1.4) et la version installée (PostGIS 2.3.7) signifie que vous devez mettre à niveau l’extension PostGIS.
```
ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION
WARNING: unpackaging raster
WARNING: PostGIS Raster functionality has been unpackaged
```
1. Exécutez la requête suivante pour vérifier que la fonctionnalité raster est maintenant dans son propre package.
```
SELECT
probin,
count(*)
FROM
pg_proc
WHERE
probin LIKE '%postgis%'
GROUP BY
probin;
probin | count
--------------------------+-------
$libdir/rtpostgis-2.3 | 107
$libdir/postgis-3 | 487
(2 rows)
```
Le résultat montre qu’il y a toujours une différence entre les versions. Les fonctions PostGIS sont en version 3 (postgis-3), tandis que les fonctions raster (rtpostgis) sont en version 2 (rtpostgis-2.3). Pour terminer la mise à niveau, vous exécutez à nouveau la commande de mise à niveau, comme suit.
```
postgres=> SELECT postgis_extensions_upgrade();
```
Vous pouvez ignorer les messages d’avertissement, il n’y a aucun risque. Exécutez à nouveau la requête suivante pour vérifier que la mise à niveau est terminée. La mise à niveau est terminée lorsque PostGIS et toutes les extensions associées ne sont plus marquées comme nécessitant une mise à niveau.
```
SELECT postgis_full_version();
```
1. Utilisez la requête suivante pour voir le processus de mise à niveau terminé et les extensions packagées séparément, et vérifiez que leurs versions correspondent.
```
SELECT
e.extname AS "Name",
e.extversion AS "Version",
n.nspname AS "Schema",
c.description AS "Description"
FROM
pg_catalog.pg_extension e
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
e.extname LIKE '%postgis%'
ORDER BY
1;
Name | Version | Schema | Description
----------------+---------+--------+---------------------------------------------------------------------
postgis | 3.1.5 | public | PostGIS geometry, geography, and raster spatial types and functions
postgis_raster | 3.1.5 | public | PostGIS raster types and functions
(2 rows)
```
Le résultat montre que l’extension PostGIS 2 a été mise à niveau vers PostGIS 3, et que `postgis` et l’extension `postgis_raster` maintenant séparée sont en version 3.1.5.
Une fois cette mise à niveau terminée, si vous ne prévoyez pas d’utiliser la fonctionnalité raster, vous pouvez abandonner l’extension comme suit.
```
DROP EXTENSION postgis_raster;
```
# Gestion des partitions PostgreSQL avec l’extension pg\$1partman
Le partitionnement de table PostgreSQL fournit un cadre à des fins de traitement hautes performances des entrées de données et des rapports. Utilisez le partitionnement pour les bases de données nécessitant une saisie très rapide de grandes quantités de données. Le partitionnement permet également d’interroger plus rapidement les tables volumineuses. Le partitionnement permet de maintenir les données sans affecter l’instance de base de données, car il nécessite moins de ressources d’I/O.
Le partitionnement vous permet de diviser les données en morceaux de taille personnalisée à des fins de traitement. Par exemple, vous pouvez choisir de partitionner des données chronologiques pour des plages telles que les plages horaires, quotidiennes, hebdomadaires, mensuelles, trimestrielles, annuelles, personnalisées ou toute autre combinaison de celles-ci. Pour un exemple de données chronologiques, si vous partitionnez la table par heure, chaque partition contiendra une heure de données. Si vous partitionnez la table chronologique par jour, chaque partition contiendra un jour de données, etc. La clé de partition contrôle la taille d’une partition.
Lorsque vous utilisez une commande SQL `INSERT` ou `UPDATE` sur une table partitionnée, le moteur de base de données achemine les données vers la partition qui convient. Les partitions de table PostgreSQL qui stockent les données sont des tables enfants de la table principale.
Lors de la lecture d’une requête de base de données, l’optimiseur PostgreSQL examine la clause `WHERE` de la requête et, si possible, dirige l’analyse de base de données vers les seules partitions pertinentes.
À partir de la version 10, PostgreSQL utilise le partitionnement déclaratif pour implémenter le partitionnement de table. Cette technique est également connue sous le nom de partitionnement PostgreSQL natif. Avant PostgreSQL version 10, il fallait utiliser des déclencheurs pour implémenter des partitions.
Le partitionnement de table PostgreSQL offre les fonctionnalités suivantes :
+ Création de nouvelles partitions à tout moment.
+ Plages de partitions variables.
+ Partitions détachables et ré-attachables à l’aide d’instructions DDL (data definition language).
Par exemple, les partitions détachables sont utiles pour supprimer les données historiques de la partition principale, tout en les conservant à des fins d’analyse.
+ Les nouvelles partitions héritent des propriétés de la table de base de données parent, et notamment :
+ Index
+ Clés primaires devant inclure la colonne de clé de partition
+ Clés étrangères
+ Contraintes de validation
+ Références
+ Création d’index pour la table complète ou chaque partition spécifique.
Vous ne pouvez pas modifier le schéma d’une partition individuelle. Vous pouvez cependant modifier la table parent (par exemple, ajouter une nouvelle colonne), qui se propage aux partitions.
**Topics**
+ [Présentation de l’extension PostgreSQL pg\$1partman](#PostgreSQL_Partitions.pg_partman)
+ [Activation de l’extension pg\$1partman](#PostgreSQL_Partitions.enable)
+ [Configuration des partitions à l’aide de la fonction create\$1parent](#PostgreSQL_Partitions.create_parent)
+ [Configuration de la maintenance des partitions à l’aide de la fonction run\$1maintenance\$1proc](#PostgreSQL_Partitions.run_maintenance_proc)
## Présentation de l’extension PostgreSQL pg\$1partman
Vous pouvez utiliser l’extension `pg_partman` PostgreSQL pour automatiser la création et la maintenance des partitions de table. Pour plus d’informations générales, consultez [PG Partition Manager](https://github.com/pgpartman/pg_partman) dans la documentation `pg_partman`.
**Note**
L’extension `pg_partman` est prise en charge sur Aurora PostgreSQL versions 12.6 et ultérieures.
Plutôt que de créer manuellement chaque partition, vous configurez `pg_partman` avec les paramètres suivants :
+ Table à partitionner
+ Type de partition
+ Clé de partition
+ Granularité de partition
+ Options de pré-création et de gestion des partitions
Après avoir créé une table partitionnée PostgreSQL, vous l’enregistrez auprès de `pg_partman` en appelant la fonction `create_parent`. Cela crée les partitions nécessaires en fonction des paramètres passés dans la fonction.
L’extension `pg_partman` propose également la fonction `run_maintenance_proc` que vous pouvez appeler sur une base planifiée pour gérer automatiquement les partitions. Programmez cette fonction de manière à ce qu’elle s’exécute périodiquement (par exemple, toutes les heures) pour vous assurer que les partitions appropriées sont créées, si besoin. Vous pouvez également vous assurer que les partitions sont automatiquement supprimées.
## Activation de l’extension pg\$1partman
En présence de plusieurs bases de données au sein de la même instance de base de données pour laquelle vous souhaitez gérer les partitions, activez l’extension `pg_partman` séparément pour chaque base de données. Pour activer l’extension `pg_partman` pour une base de données spécifique, créez le schéma de maintenance de partition, puis créez l’extension `pg_partman` comme suit.
```
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
```
**Note**
Pour créer l’extension `pg_partman`, assurez-vous que vous disposez des privilèges `rds_superuser`.
Si vous recevez une erreur similaire à la suivante, accordez les privilèges `rds_superuser` au compte ou utilisez votre compte de super-utilisateur.
```
ERROR: permission denied to create extension "pg_partman"
HINT: Must be superuser to create this extension.
```
Pour accorder des privilèges `rds_superuser`, connectez-vous avec votre compte de super-utilisateur et exécutez la commande suivante :
```
GRANT rds_superuser TO user-or-role;
```
Pour les exemples illustrant l’utilisation de l’extension pg\$1partman, nous utilisons l’exemple de table et de partition de base de données ci-dessous. Cette base de données utilise une table partitionnée basée sur un horodatage. Un schéma `data_mart` contient une table nommée `events` avec une colonne nommée `created_at`. Les paramètres suivants sont inclus dans la table `events` :
+ Clés primaires `event_id` et `created_at`, qui doivent utiliser la colonne pour guider la partition.
+ Contrainte de vérification `ck_valid_operation` pour appliquer des valeurs pour une colonne de table `operation`.
+ Deux clés étrangères, l’une (`fk_orga_membership)`) pointant vers la table externe `organization` et l’autre (`fk_parent_event_id`) correspondant à un clé étrangère auto-référencée.
+ Deux index, l’un (`idx_org_id`) correspondant à la clé étrangère et l’autre (`idx_event_type`) au type d’événement.
Les instructions DDL suivantes créent ces objets, qui sont automatiquement inclus dans chaque partition.
```
CREATE SCHEMA data_mart;
CREATE TABLE data_mart.organization ( org_id BIGSERIAL,
org_name TEXT,
CONSTRAINT pk_organization PRIMARY KEY (org_id)
);
CREATE TABLE data_mart.events(
event_id BIGSERIAL,
operation CHAR(1),
value FLOAT(24),
parent_event_id BIGINT,
event_type VARCHAR(25),
org_id BIGSERIAL,
created_at timestamp,
CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at),
CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'),
CONSTRAINT fk_orga_membership
FOREIGN KEY(org_id)
REFERENCES data_mart.organization (org_id),
CONSTRAINT fk_parent_event_id
FOREIGN KEY(parent_event_id, created_at)
REFERENCES data_mart.events (event_id,created_at)
) PARTITION BY RANGE (created_at);
CREATE INDEX idx_org_id ON data_mart.events(org_id);
CREATE INDEX idx_event_type ON data_mart.events(event_type);
```
## Configuration des partitions à l’aide de la fonction create\$1parent
Après avoir activé l’extension `pg_partman`, utilisez la fonction `create_parent` pour configurer les partitions dans le schéma de maintenance des partitions. L’exemple suivant utilise l’exemple de table `events` créé dans [Activation de l’extension pg\$1partmanConfiguration de la maintenance des partitions à l’aide de la fonction run\$1maintenance\$1proc](#PostgreSQL_Partitions.enable). Appelez la fonction `create_parent` comme suit.
```
SELECT partman.create_parent(
p_parent_table => 'data_mart.events',
p_control => 'created_at',
p_type => 'range',
p_interval => '1 day',
p_premake => 30);
```
Les paramètres sont les suivants :
+ `p_parent_table` – Table parent partitionnée. Cette table doit être présente et pleinement qualifiée, y compris le schéma.
+ `p_control` – Colonne sur laquelle le partitionnement doit être basé. Le type de données doit être un entier ou une valeur basée sur le temps.
+ `p_type` – Le type est `'range'` ou `'list'`.
+ `p_interval` : intervalle de temps ou plage d’entiers pour chaque partition. Par exemple : `1 day`, `1 hour` et d’autres valeurs similaires.
+ `p_premake` : nombre de partitions à créer à l’avance pour prendre en charge les nouvelles insertions.
Pour une description complète de la fonction `create_parent`, consultez [Fonctions de création](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#user-content-creation-functions) dans la documentation `pg_partman`.
## Configuration de la maintenance des partitions à l’aide de la fonction run\$1maintenance\$1proc
Vous pouvez exécuter des opérations de maintenance des partitions pour créer automatiquement de nouvelles partitions, détacher des partitions ou supprimer d’anciennes partitions. La maintenance des partitions repose sur la fonction `run_maintenance_proc` de l’extension `pg_partman`, et l’extension `pg_cron`, qui lance un planificateur interne. Le planificateur `pg_cron` exécute automatiquement les instructions SQL, fonctions et procédures définies dans vos bases de données.
L’exemple suivant utilise l’exemple de table `events` créé dans [Activation de l’extension pg\$1partmanConfiguration de la maintenance des partitions à l’aide de la fonction run\$1maintenance\$1proc](#PostgreSQL_Partitions.enable) pour définir l’exécution automatique des opérations de maintenance des partitions. Au préalable, ajoutez `pg_cron` au paramètre `shared_preload_libraries` dans le groupe de paramètres de l’instance de base de données.
```
CREATE EXTENSION pg_cron;
UPDATE partman.part_config
SET infinite_time_partitions = true,
retention = '3 months',
retention_keep_table=true
WHERE parent_table = 'data_mart.events';
SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);
```
Vous trouverez ci-dessous une step-by-step explication de l'exemple précédent :
1. Modifiez le groupe de paramètres associé à votre instance de base de données et ajoutez `pg_cron` à la valeur du paramètre `shared_preload_libraries`. Pour prendre effet, cette modification implique un redémarrage de l’instance de base de données. Pour plus d’informations, consultez [Modification de paramètres dans un groupe de paramètres de base de données dans Amazon Aurora](USER_WorkingWithParamGroups.Modifying.md).
1. Exécutez la commande `CREATE EXTENSION pg_cron;` à l’aide d’un compte disposant des autorisations `rds_superuser`. Cela permet d’activer l’extension `pg_cron`. Pour plus d’informations, consultez [Planification de la maintenance avec l’extension PostgreSQL pg\$1cron](PostgreSQL_pg_cron.md).
1. Exécutez la commande `UPDATE partman.part_config` pour ajuster les paramètres `pg_partman` de la table `data_mart.events`.
1. Exécutez la commande `SET` . . . pour configurer la table `data_mart.events` avec les clauses suivantes :
1. `infinite_time_partitions = true,` – Configure la table pour créer automatiquement de nouvelles partitions sans aucune limite.
1. `retention = '3 months',` – Configure la table pour présenter une rétention maximale de trois mois.
1. `retention_keep_table=true ` : configure la table de telle sorte qu’au terme de la période de rétention, la table ne soit pas supprimée automatiquement. Les partitions antérieures à la période de rétention sont uniquement détachées de la table parent.
1. Exécutez la commande `SELECT cron.schedule` . . . pour faire un appel de fonction `pg_cron`. Cet appel définit la fréquence à laquelle le planificateur exécute la procédure de maintenance `pg_partman`, `partman.run_maintenance_proc`. Pour cet exemple, la procédure s’exécute toutes les heures.
Pour une description complète de la fonction `run_maintenance_proc`, consultez [Fonctions de maintenance](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#maintenance-functions) dans la documentation `pg_partman`.
# Planification de la maintenance avec l’extension PostgreSQL pg\$1cron
Vous pouvez utiliser l’extension `pg_cron` PostgreSQL pour planifier des commandes de maintenance dans une base de données PostgreSQL. Pour plus d’informations concernant l’extension, consultez [What is pg\$1cron?](https://github.com/citusdata/pg_cron) (Qu’est-ce que pg\$1cron ?) dans la documentation pg\$1cron.
L’extension `pg_cron` est prise en charge par le moteur Aurora PostgreSQL à partir de la version 12.6.
Pour en savoir plus sur l’utilisation de `pg_cron`, consultez [Schedule jobs with pg\$1cron on your RDS pour PostgreSQL or your Aurora PostgreSQL-Compatible Edition databases](https://aws.amazon.com/blogs/database/schedule-jobs-with-pg_cron-on-your-amazon-rds-for-postgresql-or-amazon-aurora-for-postgresql-databases/) (Planifier des tâches avec pg\$1cron sur votre RDS pour PostgreSQL ou sur vos bases de données Aurora Édition compatible avec PostgreSQL).
**Note**
La version d’extension `pg_cron` est affichée sous forme de version à deux chiffres, par exemple 1.6, dans la vue pg\$1available\$1extensions. Bien que des versions à trois chiffres, comme 1.6.4 ou 1.6.5, puissent apparaître dans certains contextes, il est nécessaire d’indiquer la version à deux chiffres lors de la mise à niveau d’une extension.
**Topics**
+ [Configuration de l’extension pg\$1cron](#PostgreSQL_pg_cron.enable)
+ [Octroi d’autorisations utilisateurs de la base de données pour l’utilisation de pg\$1cron](#PostgreSQL_pg_cron.permissions)
+ [Planification des tâches pg\$1cron](#PostgreSQL_pg_cron.examples)
+ [Référence pour l’extension pg\$1cron](#PostgreSQL_pg_cron.reference)
## Configuration de l’extension pg\$1cron
Configurez l’extension `pg_cron` comme suit :
1. Modifiez le groupe de paramètres personnalisé employé avec votre instance de base de données PostgreSQL en ajoutant `pg_cron` à la valeur du paramètre `shared_preload_libraries`.
Redémarrez l’instance de la base de données PostgreSQL pour que les modifications du groupe de paramètres prennent effet. Pour en savoir plus sur l’utilisation des groupes de paramètres, consultez [Paramètres Amazon Aurora PostgreSQL.](AuroraPostgreSQL.Reference.ParameterGroups.md).
1. Après le redémarrage de l’instance de base de données PostgreSQL, exécutez la commande suivante à l’aide d’un compte disposant d’autorisations `rds_superuser`. Par exemple, si vous avez utilisé les paramètres par défaut lors de la création de votre cluster de bases de données Aurora PostgreSQL, connectez-vous en tant qu’utilisateur `postgres` et créez l’extension.
```
CREATE EXTENSION pg_cron;
```
Le planificateur `pg_cron` est défini dans la base de données PostgreSQL par défaut nommée `postgres`. Les objets `pg_cron` sont créés dans cette base de données `postgres` et toutes les actions de planification s’y exécutent.
1. Vous pouvez utiliser les paramètres par défaut ou planifier des tâches à exécuter dans d’autres bases de données de votre instance de base de données PostgreSQL. Pour planifier des tâches dans d’autres bases de données de votre instance de base de données PostgreSQL, consultez l’exemple disponible dans [Planification d’une tâche cron pour une base de données autre que la base de données par défaut](#PostgreSQL_pg_cron.otherDB).
## Octroi d’autorisations utilisateurs de la base de données pour l’utilisation de pg\$1cron
L’installation de l’extension `pg_cron` requiert les privilèges `rds_superuser`. Toutefois, les autorisations d’utiliser le `pg_cron` peuvent être accordées (par un membre du groupe/rôle `rds_superuser`) à d’autres utilisateurs de la base de données, afin qu’ils puissent planifier leurs propres tâches. Nous vous recommandons de n’accorder des autorisations au schéma `cron` qu’en cas de besoin, si cela améliore les opérations dans votre environnement de production.
Pour accorder à un utilisateur de base de données des autorisations dans le schéma `cron`, exécutez la commande suivante :
```
postgres=> GRANT USAGE ON SCHEMA cron TO db-user;
```
Cela donne *db-user* l'autorisation d'accéder au `cron` schéma pour planifier des tâches cron pour les objets auxquels ils sont autorisés à accéder. Si l’utilisateur de la base de données ne dispose pas des autorisations nécessaires, la tâche échoue après avoir validé le message d’erreur dans le fichier `postgresql.log`, comme indiqué ci-dessous :
```
2020-12-08 16:41:00 UTC::@:[30647]:ERROR: permission denied for table table-name
2020-12-08 16:41:00 UTC::@:[27071]:LOG: background worker "pg_cron" (PID 30647) exited with exit code 1
```
En d’autres termes, assurez-vous que les utilisateurs de la base de données disposant d’autorisations sur le schéma `cron` ont également des autorisations sur les objets (tables, schémas, etc.) qu’ils prévoient de planifier.
Les détails de la tâche cron et sa réussite ou son échec sont également saisis dans la table `cron.job_run_details`. Pour plus d’informations, consultez [Tableaux pour planifier les tâches et capturer leur statut](#PostgreSQL_pg_cron.tables).
## Planification des tâches pg\$1cron
Les sections suivantes montrent comment vous pouvez planifier diverses tâches de gestion à l’aide de tâches `pg_cron`.
**Note**
Lorsque vous créez des tâches `pg_cron`, vérifiez que le paramètre `max_worker_processes` est supérieur au nombre de `cron.max_running_jobs`. Une tâche `pg_cron` échoue si elle manque de processus de travail en arrière-plan. Le nombre de tâches `pg_cron` par défaut est de `5`. Pour plus d’informations, consultez [Paramètres de gestion de l’extension pg\$1cron](#PostgreSQL_pg_cron.parameters).
**Topics**
+ [Vidage d’une table](#PostgreSQL_pg_cron.vacuum)
+ [Purge de la table Historique pg\$1cron](#PostgreSQL_pg_cron.job_run_details)
+ [Journalisation des erreurs dans le fichier postgresql.log uniquement](#PostgreSQL_pg_cron.log_run)
+ [Planification d’une tâche cron pour une base de données autre que la base de données par défaut](#PostgreSQL_pg_cron.otherDB)
### Vidage d’une table
Autovacuum gère la maintenance dans la plupart des cas. Toutefois, vous pouvez vider une table spécifique quand bon vous semble.
L’exemple suivant montre comment utiliser la fonction `cron.schedule` pour configurer une tâche de manière à ce qu’elle utilise `VACUUM FREEZE` sur une table spécifique tous les jours à 22:00 (GMT).
```
SELECT cron.schedule('manual vacuum', '0 22 * * *', 'VACUUM FREEZE pgbench_accounts');
schedule
----------
1
(1 row)
```
Une fois l’exemple précédent exécuté, vous pouvez vérifier l’historique dans la table `cron.job_run_details` comme suit.
```
postgres=> SELECT * FROM cron.job_run_details;
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-------+-------+---------+----------+----------+--------------------------------+-----------+----------------+-------------------------------+-------------------------------
1 | 1 | 3395 | postgres | adminuser| vacuum freeze pgbench_accounts | succeeded | VACUUM | 2020-12-04 21:10:00.050386+00 | 2020-12-04 21:10:00.072028+00
(1 row)
```
Voici une requête de la table `cron.job_run_details` permettant d’afficher les tâches qui ont échoué.
```
postgres=> SELECT * FROM cron.job_run_details WHERE status = 'failed';
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
------+-------+---------+----------+----------+-------------------------------+--------+--------------------------------------------------+-------------------------------+------------------------------
5 | 4 | 30339 | postgres | adminuser| vacuum freeze pgbench_account | failed | ERROR: relation "pgbench_account" does not exist | 2020-12-04 21:48:00.015145+00 | 2020-12-04 21:48:00.029567+00
(1 row)
```
Pour plus d’informations, consultez [Tableaux pour planifier les tâches et capturer leur statut](#PostgreSQL_pg_cron.tables).
### Purge de la table Historique pg\$1cron
La table `cron.job_run_details` contient un historique des tâches cron et celui-ci peut considérablement s’étoffer au fil du temps. Nous vous recommandons de planifier une tâche afin de purger cette table. Par exemple, conserver les entrées d’une semaine peut s’avérer suffisant à des fins de dépannage.
L’exemple suivant utilise la fonction [cron.schedule](#PostgreSQL_pg_cron.schedule) pour planifier une tâche qui s’exécute tous les jours à minuit afin de purger la table `cron.job_run_details`. Cette tâche ne conserve que les entrées des sept derniers jours. Utilisez votre compte `rds_superuser` pour planifier la tâche comme suit :
```
SELECT cron.schedule('0 0 * * *', $$DELETE
FROM cron.job_run_details
WHERE end_time < now() - interval '7 days'$$);
```
Pour plus d’informations, consultez [Tableaux pour planifier les tâches et capturer leur statut](#PostgreSQL_pg_cron.tables).
### Journalisation des erreurs dans le fichier postgresql.log uniquement
Pour empêcher les écritures dans la table `cron.job_run_details`, modifiez le groupe de paramètres associé à l’instance de base de données PostgreSQL et désactivez le paramètre `cron.log_run`. L’extension `pg_cron` n’écrit plus dans la table et consigne uniquement des erreurs dans le fichier `postgresql.log`. Pour plus d’informations, consultez [Modification de paramètres dans un groupe de paramètres de base de données dans Amazon Aurora](USER_WorkingWithParamGroups.Modifying.md).
Utilisez la commande suivante pour vérifier la valeur du paramètre `cron.log_run`.
```
postgres=> SHOW cron.log_run;
```
Pour plus d’informations, consultez [Paramètres de gestion de l’extension pg\$1cron](#PostgreSQL_pg_cron.parameters).
### Planification d’une tâche cron pour une base de données autre que la base de données par défaut
Toutes les métadonnées de `pg_cron` sont conservées dans la base de données par défaut PostgreSQL nommée `postgres`. Des exécutants étant utilisés en arrière-plan pour exécuter les tâches de maintenance cron, vous pouvez planifier une tâche dans n’importe quelle base de données de l’instance de base de données PostgreSQL.
**Note**
Seuls les utilisateurs disposant du rôle `rds_superuser` ou de privilèges `rds_superuser` peuvent répertorier toutes les tâches cron de la base de données. Les autres utilisateurs peuvent uniquement consulter leurs propres tâches dans la table `cron.job`.
1. Dans la base de données cron, planifiez la tâche comme vous le faites normalement à l’aide de la fonction [cron.schedule](#PostgreSQL_pg_cron.schedule).
```
postgres=> SELECT cron.schedule('database1 manual vacuum', '29 03 * * *', 'vacuum freeze test_table');
```
1. En tant qu’utilisateur ayant le rôle `rds_superuser`, mettez à jour la colonne de base de données correspondant à la tâche que vous venez de créer de manière à l’exécuter dans une autre base de données de votre instance de base de données PostgreSQL.
```
postgres=> UPDATE cron.job SET database = 'database1' WHERE jobid = 106;
```
1. Procédez à une vérification en interrogeant la table `cron.job`.
```
postgres=> SELECT * FROM cron.job;
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
------+-------------+--------------------------------+-----------+----------+----------+-----------+--------+-------------------------
106 | 29 03 * * * | vacuum freeze test_table | localhost | 8192 | database1| adminuser | t | database1 manual vacuum
1 | 59 23 * * * | vacuum freeze pgbench_accounts | localhost | 8192 | postgres | adminuser | t | manual vacuum
(2 rows)
```
**Note**
Dans certains cas, vous pouvez ajouter une tâche cron que vous avez l’intention d’exécuter sur une base de données différente. Dans de tels cas, le tâche peut essayer de s’exécuter dans la base de données par défaut (`postgres`) avant la mise à jour de la colonne de base de données correcte. Si le nom d’utilisateur dispose d’autorisations, la tâche s’exécute correctement dans la base de données par défaut.
## Référence pour l’extension pg\$1cron
Vous pouvez utiliser les paramètres, fonctions et tables suivants avec l’extension `pg_cron`. Pour plus d’informations, consultez [Qu’est-ce que pg\$1cron](https://github.com/citusdata/pg_cron) dans la documentation pg\$1cron.
**Topics**
+ [Paramètres de gestion de l’extension pg\$1cron](#PostgreSQL_pg_cron.parameters)
+ [Référence de fonction : cron.schedule](#PostgreSQL_pg_cron.schedule)
+ [Référence de fonction : cron.unschedule](#PostgreSQL_pg_cron.unschedule)
+ [Tableaux pour planifier les tâches et capturer leur statut](#PostgreSQL_pg_cron.tables)
### Paramètres de gestion de l’extension pg\$1cron
La liste ci-dessous répertorie les paramètres permettant de contrôler le comportement de l’extension `pg_cron`.
| Paramètre | Description |
| --- | --- |
| cron.database\$1name | Base de données dans laquelle les métadonnées `pg_cron` sont conservées. |
| cron.host | Nom d’hôte permettant de se connecter à PostgreSQL. Vous ne pouvez pas modifier cette valeur. |
| cron.log\$1run | Enregistrez chaque tâche qui s’exécute dans la table `job_run_details`. Les valeurs sont `on` ou `off`. Pour plus d’informations, consultez [Tableaux pour planifier les tâches et capturer leur statut](#PostgreSQL_pg_cron.tables). |
| cron.log\$1statement | Enregistre toutes les instructions cron avant leur exécution. Les valeurs sont `on` ou `off`. |
| cron.max\$1running\$1jobs | Nombre maximal de tâches pouvant être exécutées simultanément. |
| cron.use\$1background\$1workers | Utilisez des exécutants en arrière-plan plutôt que des sessions client. Vous ne pouvez pas modifier cette valeur. |
Utilisez la commande SQL suivante pour afficher ces paramètres et leurs valeurs.
```
postgres=> SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'cron.%' ORDER BY name;
```
### Référence de fonction : cron.schedule
Cette fonction planifie une tâche cron. Cette tâche est initialement planifiée dans la base de données `postgres` par défaut. La fonction renvoie une valeur `bigint` correspondant à l’identifiant de la tâche. Pour planifier l’exécution de tâches dans d’autres bases de données de votre instance de base de données PostgreSQL, consultez l’exemple disponible dans [Planification d’une tâche cron pour une base de données autre que la base de données par défaut](#PostgreSQL_pg_cron.otherDB).
La fonction présente deux formats de syntaxe.
**Syntaxe**
```
cron.schedule (job_name,
schedule,
command
);
cron.schedule (schedule,
command
);
```
**Paramètres**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_pg_cron.html)
**Exemples**
```
postgres=> SELECT cron.schedule ('test','0 10 * * *', 'VACUUM pgbench_history');
schedule
----------
145
(1 row)
postgres=> SELECT cron.schedule ('0 15 * * *', 'VACUUM pgbench_accounts');
schedule
----------
146
(1 row)
```
### Référence de fonction : cron.unschedule
Cette fonction supprime une tâche cron. Vous pouvez spécifier soit le `job_name` ou le `job_id`. Une politique assure que vous soyez le propriétaire pouvant supprimer la planification de la tâche. La fonction renvoie une valeur booléenne indiquant la réussite ou l’échec.
La fonction a les formats de syntaxe suivants.
**Syntaxe**
```
cron.unschedule (job_id);
cron.unschedule (job_name);
```
**Paramètres**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_pg_cron.html)
**Exemples**
```
postgres=> SELECT cron.unschedule(108);
unschedule
------------
t
(1 row)
postgres=> SELECT cron.unschedule('test');
unschedule
------------
t
(1 row)
```
### Tableaux pour planifier les tâches et capturer leur statut
Les tables suivantes sont utilisées pour planifier les tâches cron et enregistrer la façon dont elles ont été accomplies.
| Tableau | Description |
| --- | --- |
| cron.job | Contient les métadonnées relatives à chaque tâche planifiée. La plupart des interactions avec cette table doivent être effectuées à l’aide des fonctions `cron.schedule` et `cron.unschedule`. Nous vous recommandons de ne pas accorder de privilèges de mise à jour ou d’insertion directement à cette table. Ce faisant, l’utilisateur pourrait mettre à jour la colonne `username` à exécuter en tant que `rds-superuser`. |
| cron.job\$1run\$1details | Contient des informations historiques sur l’exécution de tâches planifiées antérieures. Ces informations sont utiles pour examiner l’état, les messages renvoyés et les heures de début et de fin d’exécution de la tâche. Pour éviter que cette table évolue indéfiniment, purgez-la de manière régulière. Pour obtenir un exemple, consultez [Purge de la table Historique pg\$1cron](#PostgreSQL_pg_cron.job_run_details). |
# Utilisation de pgAudit pour journaliser l'activité de la base de données
Les institutions financières, les agences gouvernementales et de nombreux secteurs doivent tenir des *journaux d'audit* pour se conformer aux exigences réglementaires. En utilisant l'extension d'audit PostgreSQL (pgAudit) avec votre cluster de bases de données Aurora PostgreSQL, vous pouvez capturer les enregistrements détaillés généralement utiles aux auditeurs ou pour répondre aux exigences réglementaires. Par exemple, vous pouvez configurer l'extension pgAudit pour suivre les modifications apportées à des bases de données et à des tables spécifiques, pour enregistrer l'utilisateur qui a effectué la modification et de nombreux autres détails.
L'extension pgAudit s'appuie sur les fonctionnalités de l'infrastructure de journalisation PostgreSQL native en étendant les messages de journal de manière plus détaillée. En d'autres termes, vous utilisez la même approche pour consulter votre journal d'audit que pour consulter les messages du journal. Pour plus d'informations sur la journalisation PostgreSQL, consultez [Fichiers journaux de base de données Aurora PostgreSQL PostgreSQL](USER_LogAccess.Concepts.PostgreSQL.md).
L'extension pgAudit supprime les données sensibles, telles que les mots de passe en texte clair, des journaux. Si votre cluster de bases de données Aurora PostgreSQL est configuré(e) pour enregistrer les instructions du langage de manipulation de données (DML) comme indiqué dans [Activation de la journalisation des requêtes pour votre instance de base de données Aurora PostgreSQL pour PostgreSQL](USER_LogAccess.Concepts.PostgreSQL.Query_Logging.md), vous pouvez éviter le problème de mot de passe en texte clair en utilisant l'extension PostgreSQL Audit.
Vous pouvez configurer l'audit sur vos instances de base de données avec une grande précision. Vous pouvez auditer toutes les bases de données et tous les utilisateurs. Vous pouvez également choisir de n'auditer que certaines bases de données, certains utilisateurs et d'autres objets. Vous pouvez également exclure explicitement certains utilisateurs et certaines bases de données de l'audit. Pour de plus amples informations, consultez [Exclusion d'utilisateurs ou de bases de données de la journalisation d'audit](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md).
Compte tenu de la quantité de détails qui peuvent être capturés, nous vous recommandons, si vous utilisez pgAudit, de surveiller votre consommation de stockage.
L'extension pgAudit est prise en charge sur toutes les versions d'Aurora PostgreSQL disponibles. Pour une liste des versions de pgAudit prises en charge par la version d'Aurora PostgreSQL, consultez [Versions d'extension pour Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html) dans les *Notes de mise à jour d'Aurora PostgreSQL*.
**Topics**
+ [Configuration de l’extension pgAudit](Appendix.PostgreSQL.CommonDBATasks.pgaudit.basic-setup.md)
+ [Audit d'objets de base de données](Appendix.PostgreSQL.CommonDBATasks.pgaudit.auditing.md)
+ [Exclusion d'utilisateurs ou de bases de données de la journalisation d'audit](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md)
+ [Référence pour l'extension pgAudit](Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.md)
# Configuration de l’extension pgAudit
Pour configurer l’extension pgAudit sur votre cluster de bases de données Aurora PostgreSQL, vous devez d’abord ajouter pgAudit aux bibliothèques partagées sur le groupe de paramètres de cluster de bases de données personnalisé pour votre cluster de bases de données Aurora PostgreSQL. Pour plus d’informations sur la création d’un groupe de paramètres de cluster de bases de données, consultez [Groupes de paramètres pour Amazon Aurora](USER_WorkingWithParamGroups.md). Ensuite, vous installez l’extension pgAudit. Enfin, vous spécifiez les bases de données et les objets que vous souhaitez auditer. Les procédures de cette section vous guident. Pour ce faire, vous pouvez utiliser la AWS Management Console ou la AWS CLI.
Vous devez disposer d’autorisations en tant que rôle `rds_superuser` pour effectuer toutes ces tâches.
Les étapes suivantes supposent que votre cluster de bases de données Aurora PostgreSQL est associé(e) à un groupe de paramètres de cluster de bases de données personnalisé.
## Console
**Configurer l’extension pgAudit**
1. Connectez-vous à la AWS Management Console et ouvrez la console Amazon RDS à l’adresse [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Dans le panneau de navigation, choisissez l’instance d’enregistreur de votre cluster de bases de données Aurora PostgreSQL .
1. Ouvrez l’onglet **Configuration** de l’instance d’enregistreur de votre cluster de bases de données Aurora PostgreSQL. Parmi les détails de l’instance, trouvez le lien **Groupe de paramètres**.
1. Cliquez sur le lien pour ouvrir les paramètres personnalisés associés à votre cluster de bases de données Aurora PostgreSQL.
1. Dans le champ de recherche **Parameters** (Paramètres), tapez `shared_pre` pour trouver le paramètre `shared_preload_libraries`.
1. Choisissez **Edit parameters** (Modifier les paramètres) pour accéder aux valeurs des propriétés.
1. Ajoutez `pgaudit` à la liste dans le champ **Values** (Valeurs). Utilisez une virgule pour séparer les éléments de la liste de valeurs.
![\[Image du paramètre shared_preload_libaries avec pgAudit ajouté.\]](http://docs.aws.amazon.com/fr_fr/AmazonRDS/latest/AuroraUserGuide/images/apg_rpg_shared_preload_pgaudit.png)
1. Redémarrez l’instance d’enregistreur de votre cluster de bases de données Aurora PostgreSQL afin que vos modifications du paramètre `shared_preload_libraries` prennent effet.
1. Lorsque l’instance est disponible, vérifiez que pgAudit a été initialisé. Utilisez `psql` pour vous connecter à l’instance d’enregistreur de votre cluster de bases de données Aurora PostgreSQL, puis exécutez la commande suivante.
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)
```
1. Une fois pgAudit initialisé, vous pouvez maintenant créer l’extension. Vous devez créer l’extension après avoir initialisé la bibliothèque, car l’extension `pgaudit` installe des déclencheurs d’événements pour auditer les instructions du langage de définition des données (DDL).
```
CREATE EXTENSION pgaudit;
```
1. Fermez la session `psql`.
```
labdb=> \q
```
1. Connectez-vous à la AWS Management Console et ouvrez la console Amazon RDS à l’adresse [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Trouvez le paramètre `pgaudit.log` dans la liste et définissez la valeur appropriée pour votre cas d’utilisation. Par exemple, la définition du paramètre `pgaudit.log` en `write` comme indiqué dans l’image suivante permet de capturer des insertions, des mises à jour, des suppressions et d’autres types de modifications dans le journal.
![\[Image du paramètre pgaudit.log avec la définition.\]](http://docs.aws.amazon.com/fr_fr/AmazonRDS/latest/AuroraUserGuide/images/rpg_set_pgaudit-log-level.png)
Vous pouvez également choisir l’une des valeurs suivantes pour le paramètre `pgaudit.log`.
+ none – La valeur par défaut. Aucune modification de base de données n’est journalisée.
+ all – Journalise tout (lecture, écriture, fonction, rôle, ddl, divers).
+ ddl – Journalise toutes les instructions en langage de définition de données (DDL) qui ne sont pas incluses dans la classe `ROLE`.
+ function – Journalise les appels de fonction et les blocs `DO`.
+ misc – Journalise diverses commandes, telles que `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM` et `SET`.
+ read – Journalise `SELECT` et `COPY` lorsque la source est une relation (comme une table) ou une requête.
+ role – Journalise les instructions relatives aux rôles et privilèges, telles que `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE` et `DROP ROLE`.
+ write – Journalise `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE` et `COPY` lorsque la destination est une relation (table).
1. Sélectionnez **Enregistrer les modifications**.
1. Ouvrez la console Amazon RDS à l’adresse [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Choisissez l’instance d’enregistreur de votre cluster de bases de données Aurora PostgreSQL dans la liste des bases de données.
## AWS CLI
**Configurer pgAudit**
Pour configurer pgAudit à l’aide de l’AWS CLI, vous devez appeler l’opération [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) afin de modifier les paramètres du journal d’audit dans votre groupe de paramètres personnalisé, comme indiqué dans la procédure suivante.
1. Utilisez la commande AWS CLI suivante pour ajouter `pgaudit` au paramètre `shared_preload_libraries`.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Utilisez la commande AWS CLI suivante pour redémarrer l’instance d’enregistreur de votre cluster de bases de données Aurora PostgreSQL afin que la bibliothèque pgAudit soit initialisée.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. Lorsque l’instance est disponible, vous pouvez vérifier que `pgaudit` a été initialisé. Utilisez `psql` pour vous connecter à l’instance d’enregistreur de votre cluster de bases de données Aurora PostgreSQL, puis exécutez la commande suivante.
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)
```
Une fois pgAudit initialisé, vous pouvez maintenant créer l’extension.
```
CREATE EXTENSION pgaudit;
```
1. Fermez la session `psql` afin de pouvoir utiliser l’AWS CLI.
```
labdb=> \q
```
1. Utilisez la commande AWS CLI suivante pour spécifier les classes d’instructions qui doivent être journalisées par journalisation des audits de session. L’exemple définit le paramètre `pgaudit.log` sur `write`, qui capture les insertions, les mises à jour et les suppressions dans le journal.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=pgaudit.log,ParameterValue=write,ApplyMethod=pending-reboot" \
--region aws-region
```
Vous pouvez également choisir l’une des valeurs suivantes pour le paramètre `pgaudit.log`.
+ none – La valeur par défaut. Aucune modification de base de données n’est journalisée.
+ all – Journalise tout (lecture, écriture, fonction, rôle, ddl, divers).
+ ddl – Journalise toutes les instructions en langage de définition de données (DDL) qui ne sont pas incluses dans la classe `ROLE`.
+ function – Journalise les appels de fonction et les blocs `DO`.
+ misc – Journalise diverses commandes, telles que `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM` et `SET`.
+ read – Journalise `SELECT` et `COPY` lorsque la source est une relation (comme une table) ou une requête.
+ role – Journalise les instructions relatives aux rôles et privilèges, telles que `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE` et `DROP ROLE`.
+ write – Journalise `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE` et `COPY` lorsque la destination est une relation (table).
Redémarrez l’instance d’enregistreur de votre cluster de bases de données Aurora PostgreSQL, à l’aide de la commande AWS CLI suivante.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# Audit d'objets de base de données
Une fois que pgAudit est défini sur votre cluster de bases de données Aurora PostgreSQL et qu'il est configuré en fonction de vos besoins, des informations plus détaillées sont capturées dans le journal PostgreSQL. Par exemple, alors que la configuration de journalisation PostgreSQL par défaut identifie la date et l'heure auxquelles une modification a été apportée à une table de base de données, avec l'extension pgAudit, l'entrée du journal peut inclure le schéma, l'utilisateur qui a effectué la modification et d'autres détails en fonction de la manière dont les paramètres de l'extension sont configurés. Vous pouvez configurer l'audit pour suivre les modifications de différentes manières.
+ Pour chaque session, par utilisateur. Au niveau de la session, vous pouvez capturer le texte de commande complet.
+ Pour chaque objet, par utilisateur et par base de données.
La fonctionnalité d'audit des objets est activée lorsque vous créez le rôle `rds_pgaudit` sur votre système, puis que vous ajoutez ce rôle au paramètre `pgaudit.role` dans votre groupe de paramètres personnalisé. Par défaut, le paramètre `pgaudit.role` n'est pas défini et la seule valeur autorisée est `rds_pgaudit`. Les étapes suivantes supposent que `pgaudit` a été initialisé et que vous avez créé l'extension `pgaudit` en suivant la procédure décrite dans [Configuration de l’extension pgAudit](Appendix.PostgreSQL.CommonDBATasks.pgaudit.basic-setup.md).
![\[Image du fichier journal PostgreSQL après la configuration de pgAudit.\]](http://docs.aws.amazon.com/fr_fr/AmazonRDS/latest/AuroraUserGuide/images/pgaudit-log-example.png)
Comme le montre cet exemple, la ligne « LOG: AUDIT: SESSION » fournit des informations sur la table et son schéma, entre autres détails.
**Configurer l'audit d'objets**
1. Utilisez `psql` pour vous connecter à l'instance d'écriture de votre cluster de bases de données Aurora PostgreSQL..
```
psql --host=your-instance-name.aws-region.rds.amazonaws.com --port=5432 --username=postgrespostgres --password --dbname=labdb
```
1. Créez un rôle de base de données appelé `rds_pgaudit` à l'aide de la commande suivante.
```
labdb=> CREATE ROLE rds_pgaudit;
CREATE ROLE
labdb=>
```
1. Fermez la session `psql`.
```
labdb=> \q
```
Dans les étapes suivantes, utilisez l'AWS CLI pour modifier les paramètres du journal d'audit dans votre groupe de paramètres personnalisé.
1. Utilisez la commande AWS CLI suivante pour définir le paramètre `pgaudit.role` à `rds_pgaudit`. Par défaut, ce paramètre est vide et `rds_pgaudit` est la seule valeur autorisée.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Utilisez la commande AWS CLI suivante pour redémarrer l'instance d'écriture de votre cluster de bases de données Aurora PostgreSQL afin que les modifications apportées aux paramètres prennent effet.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. Exécutez la commande suivante pour confirmer que `pgaudit.role` est défini sur `rds_pgaudit`.
```
SHOW pgaudit.role;
pgaudit.role
------------------
rds_pgaudit
```
Pour tester la journalisation pgAudit, vous pouvez exécuter plusieurs exemples de commandes que vous souhaitez auditer. Par exemple, vous pouvez exécuter les commandes suivantes.
```
CREATE TABLE t1 (id int);
GRANT SELECT ON t1 TO rds_pgaudit;
SELECT * FROM t1;
id
----
(0 rows)
```
Les journaux de base de données doivent contenir une entrée similaire à ce qui suit.
```
...
2017-06-12 19:09:49 UTC:...:rds_test@postgres:[11701]:LOG: AUDIT:
OBJECT,1,1,READ,SELECT,TABLE,public.t1,select * from t1;
...
```
Pour obtenir des informations sur l'affichage des journaux, veuillez consulter [Surveillance des fichiers journaux Amazon Aurora](USER_LogAccess.md).
Pour en savoir plus sur l'extension pgAudit, veuillez consulter [pgAudit](https://github.com/pgaudit/pgaudit/blob/master/README.md) sur GitHub.
# Exclusion d'utilisateurs ou de bases de données de la journalisation d'audit
Comme indiqué dans [Fichiers journaux de base de données Aurora PostgreSQL PostgreSQL](USER_LogAccess.Concepts.PostgreSQL.md), les journaux PostgreSQL consomment de l'espace de stockage. L'utilisation de l'extension pgAudit augmente le volume de données collectées dans vos journaux à des degrés divers, en fonction des modifications que vous suivez. Vous n'avez peut-être pas besoin d'auditer chaque utilisateur ou base de données de votre cluster de bases de données Aurora PostgreSQL.
Pour minimiser les impacts sur votre stockage et éviter de capturer inutilement des enregistrements d'audit, vous pouvez exclure les utilisateurs et les bases de données de l'audit. Vous pouvez également modifier la journalisation au cours d'une session donnée. Les exemples suivants montrent comment procéder.
**Note**
Les paramètres au niveau de la session ont priorité sur les paramètres du groupe de paramètres du cluster de bases de données personnalisé pour l'instance d'écriture du cluster de bases de données Aurora PostgreSQL. Si vous ne souhaitez pas que les utilisateurs de base de données contournent vos paramètres de configuration de journalisation des audits, veillez à modifier leurs autorisations.
Supposons que votre cluster de bases de données Aurora PostgreSQL soit configuré(e) pour auditer le même niveau d'activité pour tous les utilisateurs et bases de données. Vous pouvez ensuite décider de ne pas auditer l'utilisateur `myuser`. Vous pouvez désactiver l'audit pour `myuser` à l'aide de la commande SQL suivante.
```
ALTER USER myuser SET pgaudit.log TO 'NONE';
```
Vous pouvez ensuite utiliser la requête suivante pour vérifier la colonne `user_specific_settings` pour `pgaudit.log` afin de confirmer que le paramètre est défini sur `NONE`.
```
SELECT
usename AS user_name,
useconfig AS user_specific_settings
FROM
pg_user
WHERE
usename = 'myuser';
```
Vous devez voir la sortie suivante.
```
user_name | user_specific_settings
-----------+------------------------
myuser | {pgaudit.log=NONE}
(1 row)
```
Vous pouvez désactiver la journalisation pour un utilisateur donné au cours de sa session avec la base de données à l'aide de la commande suivante.
```
ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'none';
```
Utilisez la requête suivante pour vérifier la colonne des paramètres du fichier pgaudit.log pour une combinaison utilisateur et base de données spécifique.
```
SELECT
usename AS "user_name",
datname AS "database_name",
pg_catalog.array_to_string(setconfig, E'\n') AS "settings"
FROM
pg_catalog.pg_db_role_setting s
LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
LEFT JOIN pg_catalog.pg_user r ON r.usesysid = setrole
WHERE
usename = 'myuser'
AND datname = 'mydatabase'
ORDER BY
1,
2;
```
Vous voyez des résultats similaires à ce qui suit.
```
user_name | database_name | settings
-----------+---------------+------------------
myuser | mydatabase | pgaudit.log=none
(1 row)
```
Après avoir désactivé l'audit pour `myuser`, vous décidez de ne pas suivre les modifications apportées à `mydatabase`. Vous pouvez désactiver l'audit pour cette base de données spécifique à l'aide de la commande suivante.
```
ALTER DATABASE mydatabase SET pgaudit.log to 'NONE';
```
Utilisez ensuite la requête suivante pour vérifier la colonne database\$1specific\$1settings afin de confirmer que le fichier pgaudit.log est défini sur NONE.
```
SELECT
a.datname AS database_name,
b.setconfig AS database_specific_settings
FROM
pg_database a
FULL JOIN pg_db_role_setting b ON a.oid = b.setdatabase
WHERE
a.datname = 'mydatabase';
```
Vous devez voir la sortie suivante.
```
database_name | database_specific_settings
---------------+----------------------------
mydatabase | {pgaudit.log=NONE}
(1 row)
```
Pour rétablir les paramètres par défaut pour myuser, utilisez la commande suivante :
```
ALTER USER myuser RESET pgaudit.log;
```
Pour rétablir les paramètres par défaut pour une base de données, utilisez la commande suivante.
```
ALTER DATABASE mydatabase RESET pgaudit.log;
```
Pour rétablir les paramètres par défaut pour l'utilisateur et la base de données, utilisez la commande suivante.
```
ALTER USER myuser IN DATABASE mydatabase RESET pgaudit.log;
```
Vous pouvez également capturer des événements spécifiques dans le journal en définissant `pgaudit.log` pour l'une des autres valeurs autorisées pour le paramètre `pgaudit.log`. Pour de plus amples informations, consultez [Liste des paramètres autorisés pour le paramètre `pgaudit.log`](Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.md#Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.pgaudit-log-settings).
```
ALTER USER myuser SET pgaudit.log TO 'read';
ALTER DATABASE mydatabase SET pgaudit.log TO 'function';
ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'read,function'
```
# Référence pour l'extension pgAudit
Vous pouvez spécifier le niveau de détail que vous souhaitez pour votre journal d'audit en modifiant un ou plusieurs des paramètres répertoriés dans cette section.
## Contrôle du comportement de pgAudit
Vous pouvez contrôler la journalisation d'audit en modifiant un ou plusieurs des paramètres répertoriés dans la table suivante.
| Paramètre | Description |
| --- | --- |
| `pgaudit.log` | Spécifie quelles classes d'instructions seront journalisées par la journalisation de l'audit de session. Les valeurs autorisées incluent ddl, function, misc, read, role, write, none, all. Pour de plus amples informations, veuillez consulter [Liste des paramètres autorisés pour le paramètre `pgaudit.log`](#Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.pgaudit-log-settings). |
| `pgaudit.log_catalog` | Lorsque cette option est activée (définie sur 1), cela ajoute des instructions à la piste d'audit si toutes les relations d'une instruction se trouvent dans pg\$1catalog. |
| `pgaudit.log_level` | Spécifie le niveau de journal qui sera utilisé pour les entrées de journal. Valeurs autorisées : debug5, debug4, debug3, debug2, debug1, info, notice, warning, log |
| `pgaudit.log_parameter` | Lorsque cette option est activée (définie sur 1), les paramètres transmis avec l'instruction sont capturés dans le journal d'audit. |
| `pgaudit.log_relation` | Lorsque cette option est activée (définie sur 1), le journal d'audit de session crée une entrée de journal distincte pour chaque relation (TABLE, VIEW, etc.) référencée dans une instruction SELECT ou DML. |
| `pgaudit.log_statement_once` | Spécifie si la journalisation inclura le texte de l'instruction et les paramètres avec la première entrée de journal d'une statement/substatement combinaison ou avec chaque entrée. |
| `pgaudit.role` | Spécifie le rôle principal à utiliser pour la journalisation de l’audit des objets. La seule entrée autorisée est `rds_pgaudit`. |
## Liste des paramètres autorisés pour le paramètre `pgaudit.log`
| Value | Description |
| --- | --- |
| Aucune | Il s’agit de l’option par défaut. Aucune modification de base de données n'est journalisée. |
| tout | Journalise tout (lecture, écriture, fonction, rôle, ddl, divers). |
| ddl | Journalise toutes les instructions en langage de définition de données (DDL) qui ne sont pas incluses dans la classe `ROLE`. |
| fonction | Journalise les appels de fonction et les blocs `DO`. |
| Misc | Journalise diverses commandes, telles que `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM` et `SET`. |
| lire | Journalise `SELECT` et `COPY` lorsque la source est une relation (comme une table) ou une requête. |
| rôle | Journalise les instructions relatives aux rôles et privilèges, telles que `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE` et `DROP ROLE`. |
| write | Journalise `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE` et `COPY` lorsque la destination est une relation (table). |
Pour journaliser plusieurs types d'événements avec l'audit de session, utilisez une liste séparée par des virgules. Pour journaliser tous les types d'événements, définissez `pgaudit.log` à la valeur `ALL`. Redémarrez l'instance de base de données pour appliquer les modifications.
Avec les audits d'objet, vous pouvez affiner la journalisation d'audit pour que celle-ci fonctionne avec des relations spécifiques. Par exemple, vous pouvez spécifier que vous souhaitez une journalisation d'audit pour les opérations `READ` sur une ou plusieurs tables.
# Utilisation de pglogical pour synchroniser les données entre les instances
Toutes les versions d’Aurora PostgreSQL actuellement disponibles prennent en charge l’extension `pglogical`. L’extension pglogical est antérieure à la fonction de réplication logique qui fonctionne de la même manière et qui a été introduite par PostgreSQL dans la version 10. Pour plus d’informations, consultez [Présentation de la réplication logique PostgreSQL avec Aurora](AuroraPostgreSQL.Replication.Logical.md).
L’extension `pglogical` prend en charge la réplication logique entre deux ou plusieurs clusters de bases de données Aurora PostgreSQL. Elle prend également en charge la réplication entre différentes versions de PostgreSQL, ainsi qu’entre des bases de données fonctionnant sur RDS pour les instances de base de données PostgreSQL et les clusters de bases de données Aurora PostgreSQL. L’extension `pglogical` utilise un modèle de publication et d’abonnement pour répliquer les changements apportés aux tables et aux autres objets, tels que les séquences, d’un serveur de publication à un abonné. Elle s’appuie sur un emplacement de réplication pour assurer la synchronisation des changements d’un nœud de serveur de publication à un nœud abonné, défini comme suit.
+ Le *nœud de serveur de publication* est le cluster de bases de données Aurora PostgreSQL qui est la source des données à répliquer vers les autres nœuds. Le nœud de serveur de publication définit les tables à répliquer dans un ensemble de publication.
+ Le *nœud abonné* est le cluster de bases de données Aurora PostgreSQL qui reçoit les mises à jour WAL du serveur de publication. L’abonné crée un abonnement pour se connecter au serveur de publication et obtenir les données WAL décodées. Lorsque l’abonné crée l’abonnement, l’emplacement de réplication est créé sur le nœud de serveur de publication.
Vous trouverez ci-après des informations sur la configuration de l’extension `pglogical`.
**Topics**
+ [Exigences et limites de l’extension pglogique](#Appendix.PostgreSQL.CommonDBATasks.pglogical.requirements-limitations)
+ [Configuration de l'extension pglogical](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md)
+ [Configuration de la réplication logique pour le cluster de bases de données Aurora PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.pglogical.setup-replication.md)
+ [Rétablissement de la réplication logique après une mise à niveau majeure](Appendix.PostgreSQL.CommonDBATasks.pglogical.recover-replication-after-upgrade.md)
+ [Gestion des emplacements de réplication logiques pour Aurora PostgreSQL RDS pour](Appendix.PostgreSQL.CommonDBATasks.pglogical.handle-slots.md)
+ [Référence des paramètres de l'extension pglogical](Appendix.PostgreSQL.CommonDBATasks.pglogical.reference.md)
## Exigences et limites de l’extension pglogique
Toutes les versions actuellement disponibles d’Aurora PostgreSQL prennent en charge l’extension `pglogical`.
Le nœud de serveur de publication et le nœud abonné doivent tous deux être configurés pour la réplication logique.
Les tables que vous voulez répliquer d’un diffuseur de publication à un abonné doivent avoir les mêmes noms et le même schéma. Ces tables doivent également contenir les mêmes colonnes, et les colonnes doivent utiliser les mêmes types de données. Les tables des serveurs de publication et des abonnés doivent avoir les mêmes clés primaires. Nous vous recommandons d’utiliser uniquement PRIMARY KEY comme contrainte unique.
Les tables du nœud abonné peuvent avoir des contraintes plus permissives que celles du nœud de serveur de publication pour les contraintes CHECK et NOT NULL.
L’extension `pglogical` fournit des fonctionnalités telles que la réplication bidirectionnelle qui ne sont pas prises en charge par la fonctionnalité de réplication logique intégrée à PostgreSQL (versions 10 et ultérieures). Pour plus d’informations, consultez [PostgreSQL bi-directional replication using pglogical](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/) (Réplication bidirectionnelle PostgreSQL utilisant pglogical).
# Configuration de l'extension pglogical
Pour configurer l'extension `pglogical` sur votre cluster de bases de données Aurora PostgreSQL, vous ajoutez `pglogical` aux bibliothèques partagées sur le groupe de paramètres de cluster de bases de données personnalisé pour votre cluster de bases de données Aurora PostgreSQL. Vous devez également définir la valeur du paramètre `rds.logical_replication` sur `1`, pour activer le décodage logique. Enfin, vous créez l'extension dans la base de données. Vous pouvez utiliser le AWS Management Console ou AWS CLI pour ces tâches.
Vous devez disposer d'autorisations en tant que rôle `rds_superuser` pour effectuer ces tâches.
Les étapes suivantes supposent que votre cluster de bases de données Aurora PostgreSQL est associé(e) à un groupe de paramètres de cluster de bases de données personnalisé. Pour plus d’informations sur la création d’un groupe de paramètres de cluster de bases de données, consultez [Groupes de paramètres pour Amazon Aurora](USER_WorkingWithParamGroups.md).
## Console
**Pour configurer l'extension pglogical**
1. Connectez-vous à la console Amazon RDS AWS Management Console et ouvrez-la à [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)l'adresse.
1. Dans le panneau de navigation, choisissez l’instance d’enregistreur de votre cluster de bases de données Aurora PostgreSQL .
1. Ouvrez l’onglet **Configuration** de l’instance d’enregistreur de votre cluster de bases de données Aurora PostgreSQL. Parmi les détails de l’instance, trouvez le lien **Groupe de paramètres**.
1. Cliquez sur le lien pour ouvrir les paramètres personnalisés associés à votre cluster de bases de données Aurora PostgreSQL.
1. Dans le champ de recherche **Parameters** (Paramètres), tapez `shared_pre` pour trouver le paramètre `shared_preload_libraries`.
1. Choisissez **Edit parameters** (Modifier les paramètres) pour accéder aux valeurs des propriétés.
1. Ajoutez `pglogical` à la liste dans le champ **Values** (Valeurs). Utilisez une virgule pour séparer les éléments de la liste de valeurs.
![\[Image du paramètre shared_preload_libraries avec pglogical ajouté.\]](http://docs.aws.amazon.com/fr_fr/AmazonRDS/latest/AuroraUserGuide/images/apg_rpg_shared_preload_pglogical.png)
1. Recherchez le paramètre `rds.logical_replication` et définissez-le sur `1`, pour activer la réplication logique.
1. Redémarrez l'instance d'écriture de votre cluster de bases de données Aurora PostgreSQL pour que vos modifications soient prises en compte.
1. Lorsque l'instance est disponible, vous pouvez utiliser `psql` (ou pgAdmin) pour vous connecter à l'instance d'écriture de votre cluster de bases de données Aurora PostgreSQL.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. Pour vérifier que pglogical est initialisé, exécutez la commande suivante.
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pglogical
(1 row)
```
1. Vérifiez le paramètre qui active le décodage logique, comme suit.
```
SHOW wal_level;
wal_level
-----------
logical
(1 row)
```
1. Créez l'extension, comme suit.
```
CREATE EXTENSION pglogical;
EXTENSION CREATED
```
1. Sélectionnez **Enregistrer les modifications**.
1. Ouvrez la console Amazon RDS à l'adresse [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Sélectionnez votre instance d'écriture du cluster de bases de données Aurora PostgreSQL dans la liste des bases de données, puis choisissez **Reboot** (Redémarrer) dans le menu Actions.
## AWS CLI
**Pour configurer l'extension pglogical**
Pour configurer pglogical à l'aide de AWS CLI, vous devez appeler l'[modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html)opération pour modifier certains paramètres de votre groupe de paramètres personnalisé, comme indiqué dans la procédure suivante.
1. Utilisez la AWS CLI commande suivante pour ajouter des éléments `pglogical` au `shared_preload_libraries` paramètre.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=shared_preload_libraries,ParameterValue=pglogical,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Utilisez la AWS CLI commande suivante pour définir sur `rds.logical_replication` `1` afin d'activer la fonctionnalité de décodage logique pour l'instance d'écriture du cluster de base de données Aurora PostgreSQL.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Utilisez la AWS CLI commande suivante pour redémarrer l'instance d'écriture de votre instance de base de données Aurora PostgreSQL afin que la bibliothèque pglogical soit initialisée.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. Lorsque l'instance est disponible, utilisez `psql` pour vous connecter à l'instance d'écriture de votre cluster de bases de données Aurora PostgreSQL.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. Créez l'extension, comme suit.
```
CREATE EXTENSION pglogical;
EXTENSION CREATED
```
1. Redémarrez l'instance d'écriture de votre instance de base de données Aurora PostgreSQL suivante. AWS CLI
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# Configuration de la réplication logique pour le cluster de bases de données Aurora PostgreSQL
La procédure suivante vous montre comment démarrer la réplication logique entre deux clusters de bases de données Aurora PostgreSQL.. Les étapes supposent que la source (serveur de publication) et la cible (abonné) ont toutes deux l’extension `pglogical` configurée comme indiqué dans le document [Configuration de l'extension pglogical](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md).
**Note**
Le `node_name` du nœud d’un abonné ne peut pas commencer par `rds`.
**Pour créer le nœud de serveur de publication et définir les tables à répliquer**
Ces étapes supposent que votre cluster de bases de données Aurora PostgreSQL possède une instance d’enregistreur avec une base de données qui contient une ou plusieurs tables que vous voulez répliquer vers un autre nœud. Vous devez recréer la structure de la table du serveur de publication sur l’abonné, donc d’abord, récupérer la structure de la table si nécessaire. Vous pouvez le faire en utilisant la métacommande `psql` `\d tablename` et en créant ensuite la même table sur l’instance de l’abonné. La procédure suivante crée un exemple de table sur le serveur de publication (source) à des fins de démonstration.
1. Utilisez `psql` pour vous connecter à l’instance qui possède la table que vous voulez utiliser comme source pour les abonnés.
```
psql --host=source-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
Si vous ne disposez pas d’une table existante que vous souhaitez répliquer, vous pouvez créer un exemple de table comme suit.
1. Créez un exemple de table en utilisant l’instruction SQL suivante.
```
CREATE TABLE docs_lab_table (a int PRIMARY KEY);
```
1. Remplissez la table avec les données générées en utilisant l’instruction SQL suivante.
```
INSERT INTO docs_lab_table VALUES (generate_series(1,5000));
INSERT 0 5000
```
1. Vérifiez que les données existent dans la table à l’aide de l’instruction SQL suivante.
```
SELECT count(*) FROM docs_lab_table;
```
1. Identifiez ce cluster de bases de données Aurora PostgreSQL comme le nœud de serveur de publication, comme suit.
```
SELECT pglogical.create_node(
node_name := 'docs_lab_provider',
dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 dbname=labdb');
create_node
-------------
3410995529
(1 row)
```
1. Ajoutez la table que vous souhaitez répliquer à l’ensemble de réplication par défaut. Pour plus d’informations sur les ensembles de réplication, consultez [Replication sets](https://github.com/2ndQuadrant/pglogical/tree/REL2_x_STABLE/docs#replication-sets) (Ensembles de réplication) dans la documentation pglogical.
```
SELECT pglogical.replication_set_add_table('default', 'docs_lab_table', 'true', NULL, NULL);
replication_set_add_table
---------------------------
t
(1 row)
```
La configuration du nœud de diffuseur de publication est terminée. Vous pouvez maintenant configurer le nœud abonné pour recevoir les mises à jour du serveur de publication.
**Pour configurer le nœud abonné et créer un abonnement pour recevoir des mises à jour**
Ces étapes supposent que le cluster de bases de données Aurora PostgreSQL a été configuré avec l’extension `pglogical`. Pour plus d’informations, consultez [Configuration de l'extension pglogical](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md).
1. Utilisez `psql` pour vous connecter à l’instance qui doit recevoir les mises à jour du serveur de publication.
```
psql --host=target-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. Sur le cluster de bases de données Aurora PostgreSQL de l’abonné, créez la même table que celle qui existe sur le serveur de publication. Pour cet exemple, la table est `docs_lab_table`. Vous pouvez créer la table comme suit.
```
CREATE TABLE docs_lab_table (a int PRIMARY KEY);
```
1. Vérifiez que cette table est vide.
```
SELECT count(*) FROM docs_lab_table;
count
-------
0
(1 row)
```
1. Identifiez ce cluster de bases de données Aurora PostgreSQL comme le nœud abonné, comme suit.
```
SELECT pglogical.create_node(
node_name := 'docs_lab_target',
dsn := 'host=target-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=********');
create_node
-------------
2182738256
(1 row)
```
1. Créez l’abonnement.
```
SELECT pglogical.create_subscription(
subscription_name := 'docs_lab_subscription',
provider_dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=*******',
replication_sets := ARRAY['default'],
synchronize_data := true,
forward_origins := '{}' );
create_subscription
---------------------
1038357190
(1 row)
```
Lorsque vous terminez cette étape, les données de la table du serveur de publication sont créées dans la table de l’abonné. Vous pouvez le vérifier en utilisant la requête SQL suivante.
```
SELECT count(*) FROM docs_lab_table;
count
-------
5000
(1 row)
```
À partir de ce moment, les modifications apportées à la table sur le serveur de publication sont répliquées sur la table sur l’abonné.
# Rétablissement de la réplication logique après une mise à niveau majeure
Avant de pouvoir effectuer une mise à niveau majeure d'un cluster de bases de données Aurora PostgreSQL qui est configuré comme un nœud d'édition pour la réplication logique, vous devez supprimer tous les emplacements de réplication, même ceux qui ne sont pas actifs. Nous vous recommandons de détourner temporairement les transactions de base de données du nœud d'édition, de supprimer les emplacements de réplication, de mettre à niveau le cluster de bases de données Aurora PostgreSQL, puis de rétablir et de relancer la réplication.
Les emplacements de réplication sont hébergés uniquement sur le nœud de serveur de publication. Le nœud abonné Aurora PostgreSQL dans un scénario de réplication logique n'a pas d'emplacements à supprimer. Le processus de mise à niveau de la version majeure d'Aurora PostgreSQL prend en charge la mise à niveau de l'abonné vers une nouvelle version majeure de PostgreSQL indépendamment du nœud de serveur de publication. Cependant, le processus de mise à niveau perturbe le processus de réplication et interfère avec la synchronisation des données WAL entre le nœud de serveur de publication et le nœud abonné. Vous devez rétablir la réplication logique entre le serveur de publication et l'abonné après avoir mis à niveau le serveur de publication, l'abonné ou les deux. La procédure suivante vous montre comment déterminer que la réplication a été perturbée et comment résoudre le problème.
## Détermination de la perturbation de la réplication logique
Vous pouvez déterminer que le processus de réplication a été interrompu en interrogeant le nœud de serveur de publication ou le nœud abonné, comme suit.
**Pour vérifier le nœud de serveur de publication**
+ Utilisez `psql` pour vous connecter au nœud de serveur de publication, puis interrogez la fonction `pg_replication_slots`. Notez la valeur dans la colonne active. Normalement, cela renvoie la valeur `t` (true), ce qui montre que la réplication est active. Si la requête renvoie la valeur `f` (false), cela indique que la réplication vers l'abonné a cessé.
```
SELECT slot_name,plugin,slot_type,active FROM pg_replication_slots;
slot_name | plugin | slot_type | active
-------------------------------------------+------------------+-----------+--------
pgl_labdb_docs_labcb4fa94_docs_lab3de412c | pglogical_output | logical | f
(1 row)
```
**Pour vérifier le nœud abonné**
Sur le nœud abonné, vous pouvez vérifier l'état de la réplication de trois manières différentes.
+ Consultez les journaux PostgreSQL sur le nœud abonné pour trouver des messages d'échec. Le journal identifie l'échec avec des messages qui incluent le code de sortie 1, comme indiqué ci-dessous.
```
2022-07-06 16:17:03 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 14610) exited with exit code 1
2022-07-06 16:19:44 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 21783) exited with exit code 1
```
+ Interrogez la fonction `pg_replication_origin`. Connectez-vous à la base de données sur le nœud abonné en utilisant `psql` et interrogez la fonction `pg_replication_origin`, comme suit.
```
SELECT * FROM pg_replication_origin;
roident | roname
---------+--------
(0 rows)
```
L'ensemble de résultats vide signifie que la réplication a été perturbée. Normalement, vous obtenez un résultat qui ressemble au suivant.
```
roident | roname
---------+----------------------------------------------------
1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
+ Interrogez la fonction `pglogical.show_subscription_status` comme indiqué dans l'exemple suivant.
```
SELECT subscription_name,status,slot_name FROM pglogical.show_subscription_status();
subscription_name | status | slot_name
---====----------------+--------+-------------------------------------
docs_lab_subscription | down | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
Cette sortie montre que la réplication a été perturbée. Son statut est `down`. Normalement, la sortie indique le statut `replicating`.
Si votre processus de réplication logique a été perturbé, vous pouvez rétablir la réplication en suivant les étapes suivantes.
**Pour rétablir la réplication logique entre les nœuds de serveur de publication et abonné.**
Pour rétablir la réplication, vous devez d'abord déconnecter l'abonné du nœud de serveur de publication, puis rétablir l'abonnement, comme indiqué dans les étapes suivantes.
1. Connectez-vous au nœud abonné à l'aide de `psql`, comme suit.
```
psql --host=222222222222.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. Désactivez l'abonnement en utilisant la fonction `pglogical.alter_subscription_disable`.
```
SELECT pglogical.alter_subscription_disable('docs_lab_subscription',true);
alter_subscription_disable
----------------------------
t
(1 row)
```
1. Obtenez l'identifiant du nœud de serveur de publication en interrogeant `pg_replication_origin`, comme suit.
```
SELECT * FROM pg_replication_origin;
roident | roname
---------+-------------------------------------
1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
1. Utilisez la réponse de l'étape précédente avec la commande `pg_replication_origin_create` pour attribuer l'identifiant qui peut être utilisé par l'abonnement lorsqu'il est rétabli.
```
SELECT pg_replication_origin_create('pgl_labdb_docs_labcb4fa94_docs_lab3de412c');
pg_replication_origin_create
------------------------------
1
(1 row)
```
1. Activez l'abonnement en transmettant son nom avec un statut `true`, comme indiqué dans l'exemple suivant.
```
SELECT pglogical.alter_subscription_enable('docs_lab_subscription',true);
alter_subscription_enable
---------------------------
t
(1 row)
```
Vérifiez le statut du nœud. Son statut doit être `replicating`, tel qu'indiqué dans cet exemple.
```
SELECT subscription_name,status,slot_name
FROM pglogical.show_subscription_status();
subscription_name | status | slot_name
-------------------------------+-------------+-------------------------------------
docs_lab_subscription | replicating | pgl_labdb_docs_lab98f517b_docs_lab3de412c
(1 row)
```
Vérifiez le statut de l'emplacement de réplication de l'abonné sur le nœud de serveur de publication. La colonne `active` de l'emplacement doit retourner `t` (true), indiquant que la réplication a été rétablie.
```
SELECT slot_name,plugin,slot_type,active
FROM pg_replication_slots;
slot_name | plugin | slot_type | active
-------------------------------------------+------------------+-----------+--------
pgl_labdb_docs_lab98f517b_docs_lab3de412c | pglogical_output | logical | t
(1 row)
```
# Gestion des emplacements de réplication logiques pour Aurora PostgreSQL RDS pour
Avant de pouvoir effectuer une mise à niveau de version majeure sur une instance de base de données RDS for PostgreSQL qui sert de nœud de serveur de publication dans un scénario de réplication logique, vous devez supprimer les emplacements de réplication sur l'instance. Le processus de pré-vérification des mises à niveau de versions majeures vous informe que la mise à niveau ne peut pas avoir lieu tant que les emplacements ne sont pas supprimés.
Pour identifier les emplacements de réplication qui ont été créés à l'aide de l'extension `pglogical`, connectez-vous à chaque base de données et obtenez le nom des nœuds. Lorsque vous interrogez le nœud abonné, vous obtenez à la fois le nœud de serveur de publication et le nœud abonné dans la sortie, comme le montre cet exemple.
```
SELECT * FROM pglogical.node;
node_id | node_name
------------+-------------------
2182738256 | docs_lab_target
3410995529 | docs_lab_provider
(2 rows)
```
Vous pouvez obtenir les détails de l'abonnement avec la requête suivante.
```
SELECT sub_name,sub_slot_name,sub_target
FROM pglogical.subscription;
sub_name | sub_slot_name | sub_target
----------+--------------------------------+------------
docs_lab_subscription | pgl_labdb_docs_labcb4fa94_docs_lab3de412c | 2182738256
(1 row)
```
Vous pouvez maintenant supprimer l'abonnement, comme suit.
```
SELECT pglogical.drop_subscription(subscription_name := 'docs_lab_subscription');
drop_subscription
-------------------
1
(1 row)
```
Après avoir supprimé l'abonnement, vous pouvez supprimer le nœud.
```
SELECT pglogical.drop_node(node_name := 'docs-lab-subscriber');
drop_node
-----------
t
(1 row)
```
Vous pouvez vérifier que le nœud n'existe plus, comme suit.
```
SELECT * FROM pglogical.node;
node_id | node_name
---------+-----------
(0 rows)
```
# Référence des paramètres de l'extension pglogical
Dans le tableau, vous pouvez trouver les paramètres associés à l'extension `pglogical`. Les paramètres tels que `pglogical.conflict_log_level` et `pglogical.conflict_resolution` sont utilisés pour gérer les conflits de mise à jour. Des conflits peuvent survenir lorsque des modifications sont apportées localement aux mêmes tables qui sont abonnées aux modifications du serveur de publication. Des conflits peuvent également se produire au cours de divers scénarios, tels que la réplication bidirectionnelle ou lorsque plusieurs abonnés se répliquent à partir du même serveur de publication. Pour plus d'informations, consultez [PostgreSQL bi-directional replication using pglogical](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/) (Réplication bidirectionnelle PostgreSQL utilisant pglogical).
| Paramètre | Description |
| --- | --- |
| pglogical.batch\$1inserts | Insertions de lots si possible Non défini par défaut. Remplacez par « 1 » pour activer, par « 0 » pour désactiver. |
| pglogical.conflict\$1log\$1level | Définit le niveau de journalisation à utiliser pour la journalisation des conflits résolus. Les valeurs de chaîne prises en charge sont debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, panic. |
| pglogical.conflict\$1resolution | Définit la méthode à utiliser pour résoudre les conflits lorsque ceux-ci sont résolubles. Les valeurs de chaîne prises en charge sont error, apply\$1remote, keep\$1local, last\$1update\$1wins, first\$1update\$1wins. |
| pglogical.extra\$1connection\$1options | Options de connexion à ajouter à toutes les connexions de nœuds de pairs. |
| pglogical.synchronous\$1commit | valeur de validation synchrone spécifique pglogical |
| pglogical.use\$1spi | Utilisez l'interface de programmation du serveur (SPI) au lieu de l'API de bas niveau pour appliquer les modifications. Définissez sur « 1 » pour activer, sur « 0 » pour désactiver. Pour plus d'informations sur SPI, consultez [Server Programming Interface](https://www.postgresql.org/docs/current/spi.html) (Interface de programmation du serveur) dans la documentation PostgreSQL. |
# Utilisation des encapsuleurs de données externes pris en charge pour Amazon Aurora PostgreSQL
Un encapsuleur de données externes est un type d’extension spécifique qui permet d’accéder à des données externes. Par exemple, l’extension `oracle_fdw` permet à votre instance de base de données Aurora PostgreSQL de fonctionner avec des bases de données Oracle.
Vous trouverez ci-dessous des informations sur plusieurs encapsuleurs de données externes PostgreSQL pris en charge.
**Topics**
+ [Utilisation de l'extension log\$1fdw pour accéder au journal de base de données à l'aide de SQL](CHAP_PostgreSQL.Extensions.log_fdw.md)
+ [Utilisation de l’extension postgres\$1fdw pour accéder à des données externes](postgresql-commondbatasks-fdw.md)
+ [Travailler avec des bases de données MySQL en utilisant l'extension mysql\$1fdw](postgresql-mysql-fdw.md)
+ [Utilisation des bases de données Oracle avec l’extension oracle\$1fdw](postgresql-oracle-fdw.md)
+ [Utilisation de bases de données SQL Server avec l'extension tds\$1fdw](postgresql-tds-fdw.md)
# Utilisation de l'extension log\$1fdw pour accéder au journal de base de données à l'aide de SQL
Le cluster de base de données Aurora PostgreSQL prend en charge l'extension `log_fdw`, qui vous permet d'accéder au journal de votre moteur de base de données à l'aide d'une interface SQL. L'extension `log_fdw` fournit deux fonctions qui facilitent la création de tables source pour les journaux de base de données :
+ `list_postgres_log_files` – Répertorie les fichiers dans le répertoire du journal de base de données et indique la taille des fichiers en octets.
+ `create_foreign_table_for_log_file(table_name text, server_name text, log_file_name text)` – Crée un tableau source pour le fichier spécifié dans la base de données actuelle.
Toutes les fonctions créées par `log_fdw` appartiennent à `rds_superuser`. Les membres du rôle `rds_superuser` peuvent accorder l'accès à ces fonctions à d'autres utilisateurs de base de données.
Par défaut, les fichiers journaux sont générés par Amazon Aurora au format `stderr` (erreur standard), comme spécifié dans le paramètre `log_destination`. Il n'y a que deux options pour ce paramètre, `stderr` et `csvlog` (valeurs séparées par des virgules, CSV). Si vous ajoutez l'option `csvlog` au paramètre, Amazon Aurora génère les journaux `stderr` et `csvlog`. Cela peut affecter la capacité de stockage de votre cluster de base de données. Vous devez donc connaître les autres paramètres qui affectent la gestion des journaux. Pour de plus amples informations, veuillez consulter [Définition de la destination du journal (`stderr`, `csvlog`)](USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.md#USER_LogAccess.Concepts.PostgreSQL.Log_Format).
L'un des avantages de la génération de journaux `csvlog` est que l'extension `log_fdw` vous permet de créer des tables externes dont les données sont soigneusement réparties en plusieurs colonnes. Pour ce faire, votre instance doit être associée à un groupe de paramètres de base de données personnalisé afin que vous puissiez modifier le paramètre de `log_destination`. Pour plus d'informations sur la manière de procéder, consultez [Groupes de paramètres pour Amazon Aurora](USER_WorkingWithParamGroups.md).
L'exemple suivant suppose que le paramètre `log_destination` comprend le champ `cvslog`.
**Pour utiliser l'extension log\$1fdw**
1. Installez l’extension `log_fdw`.
```
postgres=> CREATE EXTENSION log_fdw;
CREATE EXTENSION
```
1. Créez le serveur de journal en tant qu'encapsuleur de données externes.
```
postgres=> CREATE SERVER log_server FOREIGN DATA WRAPPER log_fdw;
CREATE SERVER
```
1. Sélectionnez l'ensemble des fichiers journaux d'une liste.
```
postgres=> SELECT * FROM list_postgres_log_files() ORDER BY 1;
```
Voici un exemple de réponse.
```
file_name | file_size_bytes
------------------------------+-----------------
postgresql.log.2023-08-09-22.csv | 1111
postgresql.log.2023-08-09-23.csv | 1172
postgresql.log.2023-08-10-00.csv | 1744
postgresql.log.2023-08-10-01.csv | 1102
(4 rows)
```
1. Créez une table avec une seule colonne « log\$1entry » pour le fichier sélectionné.
```
postgres=> SELECT create_foreign_table_for_log_file('my_postgres_error_log',
'log_server', 'postgresql.log.2023-08-09-22.csv');
```
La réponse ne fournit aucun détail autre que l'existence de la table.
```
-----------------------------------
(1 row)
```
1. Sélectionnez un exemple de fichier journal. Le code suivant récupère l'heure du journal et la description du message d'erreur.
```
postgres=> SELECT log_time, message FROM my_postgres_error_log ORDER BY 1;
```
Voici un exemple de réponse.
```
log_time | message
----------------------------------+---------------------------------------------------------------------------
Tue Aug 09 15:45:18.172 2023 PDT | ending log output to stderr
Tue Aug 09 15:45:18.175 2023 PDT | database system was interrupted; last known up at 2023-08-09 22:43:34 UTC
Tue Aug 09 15:45:18.223 2023 PDT | checkpoint record is at 0/90002E0
Tue Aug 09 15:45:18.223 2023 PDT | redo record is at 0/90002A8; shutdown FALSE
Tue Aug 09 15:45:18.223 2023 PDT | next transaction ID: 0/1879; next OID: 24578
Tue Aug 09 15:45:18.223 2023 PDT | next MultiXactId: 1; next MultiXactOffset: 0
Tue Aug 09 15:45:18.223 2023 PDT | oldest unfrozen transaction ID: 1822, in database 1
(7 rows)
```
# Utilisation de l’extension postgres\$1fdw pour accéder à des données externes
Vous pouvez accéder aux données d’un tableau sur un serveur de bases de données distant à l’aide de l’extension [postgres\$1fdw](https://www.postgresql.org/docs/current/static/postgres-fdw.html). Si vous configurez une connexion distante à partir de votre instance de base de données PostgreSQL, l’accès à votre réplica en lecture est également disponible.
**Pour utiliser postgres\$1fdw pour accéder à un serveur de bases de données distant**
1. Installez l’extension postgres\$1fdw.
```
CREATE EXTENSION postgres_fdw;
```
1. Créez un serveur de données externes à l’aide de CREATE SERVER.
```
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');
```
1. Créez un mappage utilisateur pour identifier le rôle à utiliser sur le serveur distant.
**Important**
Pour éviter que le mot de passe ne soit consigné dans les journaux, définissez le paramètre `log_statement=none` au niveau de la session. Définir ce réglage au niveau du paramètre ne permet pas de masquer le mot de passe.
```
CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'foreign_user', password 'password');
```
1. Créez une table mappée à la table sur le serveur distant.
```
CREATE FOREIGN TABLE foreign_table (
id integer NOT NULL,
data text)
SERVER foreign_server
OPTIONS (schema_name 'some_schema', table_name 'some_table');
```
# Travailler avec des bases de données MySQL en utilisant l'extension mysql\$1fdw
Pour accéder à une base de données compatible MySQL à partir de votre cluster de base de données Aurora PostgreSQL, vous pouvez installer et utiliser l'extension `mysql_fdw`. Cet encapsuleur de données externes vous permet de travailler avec RDS for MySQL, Aurora MySQL, MariaDB et d'autres bases de données compatibles avec MySQL. La connexion de votre cluster de base de données Aurora PostgreSQL à la base de données MySQL est chiffrée au mieux, en fonction des configurations du client et du serveur. Cependant, vous pouvez imposer le chiffrement si vous le souhaitez. Pour de plus amples informations, veuillez consulter [Utilisation du chiffrement en transit avec l'extension](#postgresql-mysql-fdw.encryption-in-transit).
L'extension `mysql_fdw` est prise en charge par Amazon Aurora PostgreSQL versions 15.4, 14.9, 13.12 et 12.16, et ultérieures. Elle prend en charge la sélection, l'insertion, la mise à jour et la suppression d'une base de données RDS for PostgreSQL vers des tables sur une instance de base de données compatible MySQL.
**Topics**
+ [Configuration de votre base de données Aurora PostgreSQL pour utiliser l'extension mysql\$1fdw](#postgresql-mysql-fdw.setting-up)
+ [Exemple : utilisation d'une base de données Aurora MySQL à partir d'Aurora PostgreSQL](#postgresql-mysql-fdw.using-mysql_fdw)
+ [Utilisation du chiffrement en transit avec l'extension](#postgresql-mysql-fdw.encryption-in-transit)
## Configuration de votre base de données Aurora PostgreSQL pour utiliser l'extension mysql\$1fdw
La configuration de l'extension `mysql_fdw` sur votre cluster de base de données Aurora PostgreSQL implique le chargement de l'extension dans votre cluster de base de données, puis la création du point de connexion à l'instance de base de données MySQL. Pour cette tâche, vous devez disposer des informations suivantes sur l'instance de base de données MySQL :
+ Nom d'hôte ou point de terminaison. Pour un cluster de base de données Aurora MySQL, vous pouvez trouver le point de terminaison à l'aide de la console. Sélectionnez l'onglet Connectivité et sécurité et regardez dans la section « Point de terminaison et port ».
+ Numéro de port. Le numéro de port par défaut pour MySQL est 3306.
+ Nom du moteur de la base de données. L'identifiant de la base de données.
Vous devez également fournir un accès sur le groupe de sécurité ou la liste de contrôle d'accès (ACL) pour le port MySQL 3306. Les clusters de bases de données Aurora PostgreSQL et Aurora MySQL doivent avoir accès au port 3306. Si l'accès n'est pas configuré correctement, lorsque vous essayez de vous connecter à une table compatible avec MySQL, vous voyez apparaître un message d'erreur similaire au suivant :
```
ERROR: failed to connect to MySQL: Can't connect to MySQL server on 'hostname.aws-region.rds.amazonaws.com:3306' (110)
```
Dans la procédure suivante, vous (en tant que compte `rds_superuser`) créez le serveur externe. Vous accordez ensuite l'accès au serveur externe à des utilisateurs spécifiques. Ces utilisateurs créent ensuite leurs propres mappages vers les comptes utilisateurs MySQL appropriés pour travailler avec l'instance de base de données MySQL.
**Pour utiliser mysql\$1fdw pour accéder à un serveur de base de données MySQL**
1. Connectez-vous à votre instance de base de données PostgreSQL en utilisant un compte qui a le rôle `rds_superuser`. Si vous avez accepté les valeurs par défaut lors de la création de votre cluster de base de données Aurora PostgreSQL, le nom d'utilisateur est `postgres`, et vous pouvez vous connecter à l'aide de l'outil de ligne de commande `psql` comme suit :
```
psql --host=your-DB-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres –-password
```
1. Installez l'extension `mysql_fdw` comme suit :
```
postgres=> CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
```
Une fois l'extension installée sur votre cluster de base de données Aurora PostgreSQL, vous devez configurer le serveur externe qui fournit la connexion à une base de données MySQL.
**Pour créer le serveur externe**
Effectuez ces tâches sur le cluster de base de données Aurora PostgreSQL. Les étapes supposent que vous êtes connecté en tant qu'utilisateur avec des privilèges `rds_superuser`, tels que `postgres`.
1. Créer un serveur externe dans le cluster de base de données Aurora PostgreSQL :
```
postgres=> CREATE SERVER mysql-db FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'db-name.111122223333.aws-region.rds.amazonaws.com', port '3306');
CREATE SERVER
```
1. Accordez aux utilisateurs appropriés l'accès au serveur externe. Il doit s'agir d'utilisateurs non administrateurs, c'est-à-dire d'utilisateurs sans rôle `rds_superuser`.
```
postgres=> GRANT USAGE ON FOREIGN SERVER mysql-db to user1;
GRANT
```
Les utilisateurs de PostgreSQL créent et gèrent leurs propres connexions à la base de données MySQL via le serveur externe.
## Exemple : utilisation d'une base de données Aurora MySQL à partir d'Aurora PostgreSQL
Supposons que vous ayez une table simple sur une instance de base de données Aurora PostgreSQL. Vos utilisateurs Aurora PostgreSQL souhaitent interroger les éléments (`SELECT`), `INSERT`, `UPDATE` et `DELETE` de cette table. Supposons que l'extension `mysql_fdw` a été créée sur votre instance de base de données RDS for PostgreSQL, comme indiqué dans la procédure précédente. Après vous être connecté à l'instance de base de données RDS for PostgreSQL en tant qu'utilisateur disposant de privilèges `rds_superuser`, vous pouvez procéder aux étapes suivantes.
1. Créez un serveur externe sur l'instance de base de données Aurora PostgreSQL :
```
test=> CREATE SERVER mysqldb FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'your-DB.aws-region.rds.amazonaws.com', port '3306');
CREATE SERVER
```
1. Accordez l'utilisation à un utilisateur dépourvu d'autorisations `rds_superuser`, par exemple `user1` :
```
test=> GRANT USAGE ON FOREIGN SERVER mysqldb TO user1;
GRANT
```
1. Connectez-vous en tant que*user1*, puis créez un mappage vers l'utilisateur MySQL :
```
test=> CREATE USER MAPPING FOR user1 SERVER mysqldb OPTIONS (username 'myuser', password 'mypassword');
CREATE USER MAPPING
```
1. Créez une table externe liée à la table MySQL :
```
test=> CREATE FOREIGN TABLE mytab (a int, b text) SERVER mysqldb OPTIONS (dbname 'test', table_name '');
CREATE FOREIGN TABLE
```
1. Exécutez une requête simple dans la table externe :
```
test=> SELECT * FROM mytab;
a | b
---+-------
1 | apple
(1 row)
```
1. Vous pouvez ajouter, modifier et supprimer des données de la table MySQL. Par exemple :
```
test=> INSERT INTO mytab values (2, 'mango');
INSERT 0 1
```
Exécutez à nouveau la requête `SELECT` pour voir les résultats :
```
test=> SELECT * FROM mytab ORDER BY 1;
a | b
---+-------
1 | apple
2 | mango
(2 rows)
```
## Utilisation du chiffrement en transit avec l'extension
La connexion à MySQL à partir d'Aurora PostgreSQL utilise le chiffrement en transit (TLS/SSL) par défaut. Toutefois, la connexion redevient non chiffrée lorsque la configuration du client et du serveur diffère. Vous pouvez imposer le chiffrement pour toutes les connexions sortantes en spécifiant l'option `REQUIRE SSL` sur les comptes d'utilisateur RDS for MySQL. Cette même approche fonctionne également pour les comptes utilisateurs MariaDB et Aurora MySQL.
Pour les comptes utilisateurs MySQL configurés pour `REQUIRE SSL`, la tentative de connexion échoue si une connexion sécurisée ne peut être établie.
Pour appliquer le chiffrement aux comptes d'utilisateurs de bases de données MySQL existants, vous pouvez utiliser la commande `ALTER USER`. La syntaxe varie en fonction de la version MySQL, comme indiqué dans le tableau suivant. Pour plus d'informations, consultez [ALTER USER](https://dev.mysql.com/doc/refman/8.0/en/alter-user.html) dans le *Manuel de référence de MySQL*.
| MySQL 5.7, MySQL 8.0 | MySQL 5.6 |
| --- | --- |
| `ALTER USER 'user'@'%' REQUIRE SSL;` | `GRANT USAGE ON *.* to 'user'@'%' REQUIRE SSL;` |
Pour plus d'informations sur l'extension `mysql_fdw`, consultez la documentation [mysql\$1fdw](https://github.com/EnterpriseDB/mysql_fdw).
# Utilisation des bases de données Oracle avec l’extension oracle\$1fdw
Pour accéder à une base de données Oracle depuis votre cluster de bases de données Aurora PostgreSQL , vous pouvez installer et utiliser l’extension `oracle_fdw`. Cette extension est un encapsuleur de données externes pour les bases de données Oracle. Pour en savoir plus sur cette extension, consultez la documentation [oracle\$1fdw](https://github.com/laurenz/oracle_fdw).
L’extension `oracle_fdw` est prise en charge sur Aurora PostgreSQL 12.7 (Amazon Aurora version 4.2) et les versions ultérieures.
**Topics**
+ [Activation de l’extension oracle\$1fdw](#postgresql-oracle-fdw.enabling)
+ [Exemple : utilisation d’un serveur externe lié à une base de données Amazon RDS for Oracle Database](#postgresql-oracle-fdw.example)
+ [Utilisation du chiffrement en transit](#postgresql-oracle-fdw.encryption)
+ [Comprendre la vue et les autorisations pg\$1user\$1mappings](#postgresql-oracle-fdw.permissions)
## Activation de l’extension oracle\$1fdw
Pour utiliser l’extension oracle\$1fdw, suivez la procédure suivante.
**Pour activer l’extension oracle\$1fdw**
+ Exécutez la commande suivante en utilisant un compte disposant d’autorisations `rds_superuser`.
```
CREATE EXTENSION oracle_fdw;
```
## Exemple : utilisation d’un serveur externe lié à une base de données Amazon RDS for Oracle Database
Les exemples suivants démontrent l’utilisation d’un serveur externe lié à une base de données Amazon RDS for Oracle.
**Pour créer un serveur externe lié à une base de données RDS for Oracle**
1. Notez ce qui suit sur l’instance de base de données RDS for Oracle :
+ Point de terminaison
+ Port
+ Nom de base de données
1. Créez un serveur externe.
```
test=> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name');
CREATE SERVER
```
1. Accordez l’utilisation à un utilisateur dépourvu d’autorisations `rds_superuser`, par exemple `user1`.
```
test=> GRANT USAGE ON FOREIGN SERVER oradb TO user1;
GRANT
```
1. Connectez-vous en tant que `user1` et créez un mappage à un utilisateur Oracle.
```
test=> CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user 'oracleuser', password 'mypassword');
CREATE USER MAPPING
```
1. Créez une table externe liée à une table Oracle.
```
test=> CREATE FOREIGN TABLE mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE');
CREATE FOREIGN TABLE
```
1. Interrogez la table externe.
```
test=> SELECT * FROM mytab;
a
---
1
(1 row)
```
Si la requête signale l’erreur suivante, vérifiez votre groupe de sécurité et votre liste de contrôle d’accès (ACL) pour vous assurer que les deux instances peuvent communiquer.
```
ERROR: connection for foreign table "mytab" cannot be established
DETAIL: ORA-12170: TNS:Connect timeout occurred
```
## Utilisation du chiffrement en transit
Le chiffrement PostgreSQL vers Oracle en transit est basé sur une combinaison de paramètres de configuration client et serveur. Pour un exemple d’utilisation d’Oracle 21c, consultez [A propos de la négociation du chiffrement et de l’intégrité](https://docs.oracle.com/en/database/oracle/oracle-database/21/dbseg/configuring-network-data-encryption-and-integrity.html#GUID-3A2AF4AA-AE3E-446B-8F64-31C48F27A2B5) dans la documentation Oracle. Le client utilisé pour oracle\$1fdw sur Amazon RDS est configuré avec `ACCEPTED`, ce qui signifie que le chiffrement dépend de la configuration du serveur de base de données Oracle et qu’il utilise Oracle Security Library (libnnz) pour le chiffrement.
Si votre base de données se trouve sur RDS for Oracle, consultez la section [Oracle native network encryption](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.NetworkEncryption.html) (Chiffrement réseau natif Oracle) pour configurer le chiffrement.
## Comprendre la vue et les autorisations pg\$1user\$1mappings
Le catalogue PostgreSQL `pg_user_mapping` stocke le mappage d’un utilisateur Aurora PostgreSQL vers l’utilisateur d’un serveur de données externe (distant). L’accès au catalogue est restreint, mais vous utilisez la vue `pg_user_mappings` pour visualiser les mappages. Dans ce qui suit, vous trouverez un exemple qui présente comment les autorisations s’appliquent avec un exemple de base de données Oracle, mais ces informations s’appliquent plus généralement à tout encapsuleur de données externes.
Dans la sortie suivante, vous pouvez trouver des rôles et des autorisations mappés à trois exemples d’utilisateurs différents. Les utilisateurs `rdssu1` et `rdssu2` sont membres du rôle `rds_superuser`, et `user1` ne l’est pas. L’exemple utilise la métacommande `psql` `\du` pour lister les rôles existants.
```
test=> \du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+-------------------------------------------------------------
rdssu1 | | {rds_superuser}
rdssu2 | | {rds_superuser}
user1 | | {}
```
Tous les utilisateurs, y compris ceux qui disposent de privilèges `rds_superuser`, sont autorisés à voir leurs propres mappages d’utilisateurs (`umoptions`) dans la table `pg_user_mappings`. Comme le montre l’exemple suivant, lorsque `rdssu1` tente d’obtenir tous les mappages d’utilisateurs, une erreur s’affiche en dépit des privilèges `rds_superuser` de `rdssu1` :
```
test=> SELECT * FROM pg_user_mapping;
ERROR: permission denied for table pg_user_mapping
```
Voici quelques exemples.
```
test=> SET SESSION AUTHORIZATION rdssu1;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+----------------------------------
16414 | 16411 | oradb | 16412 | user1 |
16423 | 16411 | oradb | 16421 | rdssu1 | {user=oracleuser,password=mypwd}
16424 | 16411 | oradb | 16422 | rdssu2 |
(3 rows)
test=> SET SESSION AUTHORIZATION rdssu2;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+----------------------------------
16414 | 16411 | oradb | 16412 | user1 |
16423 | 16411 | oradb | 16421 | rdssu1 |
16424 | 16411 | oradb | 16422 | rdssu2 | {user=oracleuser,password=mypwd}
(3 rows)
test=> SET SESSION AUTHORIZATION user1;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+--------------------------------
16414 | 16411 | oradb | 16412 | user1 | {user=oracleuser,password=mypwd}
16423 | 16411 | oradb | 16421 | rdssu1 |
16424 | 16411 | oradb | 16422 | rdssu2 |
(3 rows)
```
En raison des différences dans l’implémentation de `information_schema._pg_user_mappings` et de `pg_catalog.pg_user_mappings`, un `rds_superuser` créé manuellement nécessite des autorisations supplémentaires pour afficher les mots de passe dans `pg_catalog.pg_user_mappings`.
Un `rds_superuser` n’a besoin d’aucune autorisation supplémentaire pour afficher les mots de passe dans `information_schema._pg_user_mappings`.
Les utilisateurs qui n’ont pas le rôle `rds_superuser` peuvent afficher les mots de passe dans `pg_user_mappings` uniquement dans les conditions suivantes :
+ L’utilisateur actif est celui faisant l’objet du mappage. Il possède le serveur ou détient le privilège `USAGE` sur celui-ci.
+ L’utilisateur actuel est le propriétaire du serveur et le mappage est pour `PUBLIC`.
# Utilisation de bases de données SQL Server avec l'extension tds\$1fdw
Vous pouvez utiliser l'extension PostgreSQL `tds_fdw` pour accéder aux bases de données qui prennent en charge le protocole TDS (tabular data stream), comme les bases de données Sybase et Microsoft SQL Server. Cet encapsuleur de données externes vous permet de vous connecter à partir de votre ou de votre cluster de base de données PostgreSQL à des bases de données qui utilisent le protocole TDS, y compris Amazon RDS for Microsoft SQL Server. Pour plus d'informations, consultez la documentation de [tds-fdw/tds\$1fdw](https://github.com/tds-fdw/tds_fdw) sur GitHub.
L'extension `tds_fdw` est prise en charge sur Amazon Aurora PostgreSQL version 13.6 et versions ultérieures.
## Configuration de votre base de données Aurora PostgreSQL pour utiliser l'extension tds\$1fdw
Dans les procédures suivantes, vous trouverez un exemple de configuration et d'utilisation de `tds_fdw` avec un cluster de base de données Aurora PostgreSQL. Avant de pouvoir vous connecter à une base de données SQL Server à l'aide de `tds_fdw`, vous devez obtenir les détails suivants pour l'instance :
+ Nom d'hôte ou point de terminaison. Pour une instance de base de données RDS for SQL Server, vous pouvez trouver le point de terminaison en utilisant la console. Sélectionnez l'onglet Connectivité et sécurité et regardez dans la section « Point de terminaison et port ».
+ Numéro de port. Le numéro de port par défaut de Microsoft SQL Server est 1433.
+ Nom du moteur de la base de données. L'identifiant de la base de données.
Vous devez également fournir un accès au groupe de sécurité ou à la liste de contrôle d'accès (ACL) pour le port du serveur SQL 1433. Le cluster de base de données Aurora PostgreSQL et l'instance de base de données RDS for SQL Server ont tous deux besoin d'accéder au port 1433. Si l'accès n'est pas configuré correctement, lorsque vous essayez d'interroger le serveur Microsoft SQL, le message d'erreur suivant s'affiche :
```
ERROR: DB-Library error: DB #: 20009, DB Msg: Unable to connect:
Adaptive Server is unavailable or does not exist (mssql2019.aws-region.rds.amazonaws.com), OS #: 0, OS Msg: Success, Level: 9
```
**Pour utiliser tds\$1fdw pour vous connecter à une base de données SQL Server**
1. Connectez-vous à votre instance principale du cluster de base de données Aurora PostgreSQL en utilisant un compte qui dispose du rôle `rds_superuser` :
```
psql --host=your-cluster-name-instance-1.aws-region.rds.amazonaws.com --port=5432 --username=test –-password
```
1. Installez l'extension `tds_fdw` :
```
test=> CREATE EXTENSION tds_fdw;
CREATE EXTENSION
```
Une fois l'extension installée sur votre cluster de base de données Aurora PostgreSQL , vous configurez le serveur externe.
**Pour créer le serveur externe**
Effectuez ces tâches sur le cluster de base de données Aurora PostgreSQL en utilisant un compte qui dispose de privilèges `rds_superuser`.
1. Créer un serveur externe dans le cluster de base de données Aurora PostgreSQL :
```
test=> CREATE SERVER sqlserverdb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019.aws-region.rds.amazonaws.com', port '1433', database 'tds_fdw_testing');
CREATE SERVER
```
Pour accéder à des données non-ASCII côté SQL Server, créez un lien vers le serveur avec l'option character\$1set dans le cluster de base de données Aurora PostgreSQL :
```
test=> CREATE SERVER sqlserverdb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019.aws-region.rds.amazonaws.com', port '1433', database 'tds_fdw_testing', character_set 'UTF-8');
CREATE SERVER
```
1. Accordez des autorisations à un utilisateur qui n'a pas de privilèges de rôle `rds_superuser`, par exemple, `user1` :
```
test=> GRANT USAGE ON FOREIGN SERVER sqlserverdb TO user1;
```
1. Connectez-vous en tant que user1 et créez un mappage vers un utilisateur SQL Server :
```
test=> CREATE USER MAPPING FOR user1 SERVER sqlserverdb OPTIONS (username 'sqlserveruser', password 'password');
CREATE USER MAPPING
```
1. Créez une table externe liée à une table SQL Server :
```
test=> CREATE FOREIGN TABLE mytab (a int) SERVER sqlserverdb OPTIONS (table 'MYTABLE');
CREATE FOREIGN TABLE
```
1. Interrogez la table externe :
```
test=> SELECT * FROM mytab;
a
---
1
(1 row)
```
### Utilisation du chiffrement en transit pour la connexion
La connexion d'Aurora PostgreSQL à SQL Server utilise le chiffrement en transit (TLS/SSL) selon la configuration de la base de données SQL Server. Si le serveur SQL n'est pas configuré pour le chiffrement, le client RDS for PostgreSQL qui émet la requête à la base de données du serveur SQL revient au mode non chiffré.
Vous pouvez renforcer le chiffrement de la connexion aux instances de base de données RDS for SQL Server en définissant le paramètre `rds.force_ssl`. Pour savoir comment procéder, consultez [Forcer les connexions à votre instance de base de données pour utiliser SSL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html#SQLServer.Concepts.General.SSL.Forcing). Pour plus d'informations sur la configuration SSL/TLS pour RDS for SQL Server, consultez [Utilisation de SSL avec une instance DB Microsoft SQL Server](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html).