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 des SQL extensions Postgre avec Amazon RDS pour Postgre SQL
Vous pouvez étendre les fonctionnalités de Postgre SQL en installant une variété d'extensions et de modules. Par exemple, pour travailler avec des données spatiales, vous pouvez installer et utiliser l'GISextension Post. Pour de plus amples informations, veuillez consulter Gestion des données spatiales avec l'GISextension Post. Par exemple, si vous souhaitez améliorer la saisie de données pour des tables très volumineuses, vous pouvez envisager de partitionner vos données en utilisant l'extension pg_partman
. Pour en savoir plus, consultez Gestion des partitions PostgreSQL avec l'extension pg_partman.
Note
RDSDepuis Postgre SQL 14.5, Postgre SQL prend en charge les extensions linguistiques fiables RDS pour Postgre. SQL Cette fonctionnalité est implémentée sous forme d'extensionpg_tle
, que vous pouvez ajouter à votre SQL instance RDS de base de données Postgre. En utilisant cette extension, les développeurs peuvent créer leurs propres SQL extensions Postgre dans un environnement sécurisé qui simplifie l'installation et les exigences de configuration. Pour de plus amples informations, veuillez consulter Utilisation d'extensions linguistiques fiables pour Postgre SQL.
Dans certains cas, plutôt que d'installer une extension, vous pouvez ajouter un module spécifique à la liste du groupe de paramètres de shared_preload_libraries
base RDS de données personnalisé de votre SQL instance de base de données Postgre. 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_cron. 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
Selon votre version de RDS for PostgreSQL, l'installation d'une extension peut nécessiter rds_superuser
des autorisations, comme suit :
RDSPour les SQL versions 12 et antérieures de Postgre, l'installation d'extensions nécessite
rds_superuser
des privilèges.RDSPour les SQL versions 13 et supérieures de Postgre, les utilisateurs (rôles) disposant d'autorisations de création sur une instance de base de données donnée peuvent installer et utiliser toutes les extensions fiables. Pour obtenir la liste des extensions approuvées, consultez Extensions SQL fiables de Postgre.
Vous pouvez également spécifier précisément quelles extensions peuvent être installées sur votre SQL instance de base de données RDS for Postgre, en les listant dans le rds.allowed_extensions
paramètre. Pour de plus amples informations, veuillez consulter Restreindre l'installation des extensions Postgre SQL.
Pour en savoir sur le rôle rds_superuser
, veuillez consulter Comprendre les SQL rôles et les autorisations de Postgre.
Rubriques
- Utilisation des fonctions de l'extension orafce
- Utilisation de la prise en charge RDS déléguée des extensions Amazon pour Postgre SQL
- Gestion des partitions PostgreSQL avec l'extension pg_partman
- Utilisation pgAudit pour enregistrer l'activité de la base de données
- Planification de la maintenance avec l'extension PostgreSQL pg_cron
- Utilisation de pglogical pour synchroniser les données entre les instances
- Utilisation de pgactive pour prendre en charge la réplication active-active
- Réduction du ballonnement des tables et des index avec l'extension pg_repack
- Mise à niveau et utilisation de l'PLV8extension
- Utiliser PL/Rust pour écrire des SQL fonctions Postgre dans le langage Rust
- Gestion des données spatiales avec l'GISextension Post
Utilisation des fonctions de l'extension orafce
L'extension orafce fournit des fonctions et des opérateurs qui émulent un sous-ensemble de fonctions et de packages à partir d'une base de données Oracle. L'extension Oracle facilite le portage d'une application Oracle vers Postgre. SQL RDSpour Postgre, SQL les versions 9.6.6 et supérieures supportent cette extension. Pour plus d'informations sur Oracle, voir orace
Note
RDSfor Postgre SQL ne supporte pas le utl_file
package qui fait partie de l'extension Oracle. La raison en est que les fonctions du schéma utl_file
fournissent des opérations de lecture et d'écriture sur les fichiers de texte des systèmes d'exploitation, ce qui nécessite un accès de super-utilisateur à l'hôte sous-jacent. En tant que service géré, RDS Postgre SQL ne fournit pas d'accès à l'hôte.
Pour utiliser l'extension orafce
Connectez-vous à l'instance de base de données avec le nom d'utilisateur principal que vous avez utilisé pour créer l'instance de base de données.
Si vous souhaitez activer orafce pour une base de données différente dans la même instance de base de données, utilisez la commande psql
/c dbname
. À l'aide de cette commande, vous passez de base de données primaire après avoir initié la connexion.Activez l'extension orafce avec l'instruction
CREATE EXTENSION
.CREATE EXTENSION orafce;
Transférez la propriété du schéma oracle au rôle rds_superuser avec la déclaration
ALTER SCHEMA
.ALTER SCHEMA oracle OWNER TO rds_superuser;
Si vous souhaitez voir la liste des propriétaires du schéma oracle, utilisez la commande psql
\dn
.
Utilisation de pgactive pour prendre en charge la réplication active-active
L'pgactive
extension utilise la réplication active-active pour prendre en charge et coordonner les opérations d'écriture sur plusieurs bases de données RDS SQL Postgre. Amazon RDS pour Postgre SQL prend en charge l'pgactive
extension sur les versions suivantes :
-
RDSpour Postgre SQL 16.1 et versions 16 supérieures
-
RDSpour Postgre SQL 15.4-R2 et versions 15 supérieures
-
RDSpour Postgre SQL 14.10 et versions 14 supérieures
-
RDSpour Postgre SQL 13.13 et versions 13 supérieures
-
RDSpour Postgre SQL 12.17 et versions 12 supérieures
-
RDSpour Postgre 11.22 SQL
Note
Lorsque des opérations d'écriture se produisent sur plusieurs bases de données dans une configuration de réplication, des conflits peuvent survenir. Pour plus d’informations, consultez Gestion des conflits de la réplication active-active.
Rubriques
- Initialisation de la capacité d'extension pgactive
- Configuration de la réplication active-active pour le pour les instances de base de données Postgre SQL
- Gestion des conflits de la réplication active-active
- Gestion des séquences dans une réplication active-active
- Référence des paramètres de l'extension pgactive
- Mesurer le délai de réplication entre les membres actifs
- Limitations liées à l'extension pgactive
Initialisation de la capacité d'extension pgactive
Pour initialiser la fonctionnalité d'pgactive
extension sur votre RDS SQL instance de base de données Postgre, définissez la valeur du rds.enable_pgactive
paramètre sur, 1
puis créez l'extension dans la base de données. Les paramètres rds.logical_replication
et track_commit_timestamp
sont alors automatiquement activés et la valeur de wal_level
est définie sur logical
.
Vous devez disposer d'autorisations en tant que rôle rds_superuser
pour effectuer ces tâches.
Vous pouvez utiliser le AWS Management Console ou le AWS CLI pour créer les éléments requis RDS pour les SQL instances de base de données Postgre. Les étapes suivantes supposent que votre SQL instance de base de données RDS for Postgre est associée à un groupe de paramètres de base de données personnalisé. Pour obtenir des informations sur la création d'un groupe de paramètres de base de données personnalisé, consultez Groupes de paramètres pour Amazon RDS.
Pour initialiser la capacité d'extension pgactive
Connectez-vous à la RDS console Amazon AWS Management Console et ouvrez-la à l'adresse https://console.aws.amazon.com/rds/
. -
Dans le volet de navigation, choisissez votre SQL instance de base de données RDS pour Postgre.
-
Ouvrez l'onglet Configuration de votre SQL instance de base de données RDS pour Postgre. Dans les détails de l'instance, recherchez le lien Groupe de paramètres d'instance de base de données.
-
Cliquez sur le lien pour ouvrir les paramètres personnalisés associés à votre SQL instance RDS de base de données Postgre.
Recherchez le paramètre
rds.enable_pgactive
et définissez-le sur1
pour initialiser la fonctionnalitépgactive
.Sélectionnez Enregistrer les modifications.
Dans le volet de navigation de la RDS console Amazon, sélectionnez Databases.
Sélectionnez votre SQL instance RDS de base de données Postgre, puis choisissez Redémarrer dans le menu Actions.
Confirmez le redémarrage de l'instance de base de données pour que vos modifications prennent effet.
Lorsque l'instance de base de données est disponible, vous pouvez utiliser
psql
un autre SQL client Postgre RDS pour vous connecter à l'instance de SQL base de données Postgre.L'exemple suivant suppose que votre SQL instance de base de données RDS pour Postgre possède une base de données par défaut nommée
postgres
.psql --host=
mydb.111122223333
.aws-region
.rds.amazonaws.com --port=5432 --username=master username
--password --dbname=postgres
Pour vérifier que pgactive est initialisé, exécutez la commande suivante.
postgres=>
SELECT setting ~ 'pgactive' FROM pg_catalog.pg_settings WHERE name = 'shared_preload_libraries';
Si
pgactive
se trouve dansshared_preload_libraries
, la commande précédente renvoie ceci :?column? ---------- t
Créez l'extension, comme suit.
postgres=>
CREATE EXTENSION pgactive;
Pour initialiser la capacité d'extension pgactive
Pour initialiser l'pgactive
utilisation de AWS CLI, appelez l'modify-db-parameter-groupopération pour modifier certains paramètres de votre groupe de paramètres personnalisé, comme indiqué dans la procédure suivante.
Utilisez la AWS CLI commande suivante pour définir sur
rds.enable_pgactive
1
afin d'initialiser lapgactive
fonctionnalité de l'instance de SQL base de données RDS for Postgre.postgres=>
aws rds modify-db-parameter-group \ --db-parameter-group-namecustom-param-group-name
\ --parameters "ParameterName=rds.enable_pgactive,ParameterValue=1,ApplyMethod=pending-reboot" \ --regionaws-region
-
Utilisez la AWS CLI commande suivante pour redémarrer l'SQLinstance de base de données RDS for Postgre afin que la
pgactive
bibliothèque soit initialisée.aws rds reboot-db-instance \ --db-instance-identifier
your-instance
\ --regionaws-region
Lorsque l'instance est disponible, utilisez-la
psql
pour vous connecter à l'. RDSpour une SQL instance de base de données Postgre.psql --host=
mydb.111122223333
.aws-region
.rds.amazonaws.com --port=5432 --username=master user
--password --dbname=postgres
Créez l'extension, comme suit.
postgres=>
CREATE EXTENSION pgactive;
Configuration de la réplication active-active pour le pour les instances de base de données Postgre SQL
La procédure suivante explique comment démarrer une réplication active-active entre deux pour des instances de base de données Postgre exécutant Postgre SQL SQL 15.4 ou une version ultérieure dans la même région. Pour exécuter l'exemple de haute disponibilité multirégionale, vous devez déployer les SQL instances Amazon RDS for Postgre dans deux régions différentes et configurer VPC Peering. Pour plus d'informations, voir VPCpeering.
Note
L'envoi de trafic entre plusieurs régions peut induire des coûts supplémentaires.
Ces étapes supposent que l'SQLinstance de base de données RDS for Postgre a été configurée avec l'pgactive
extension. Pour de plus amples informations, veuillez consulter Initialisation de la capacité d'extension pgactive.
Pour configurer le premier RDS pour l'SQLinstance de base de données Postgre avec l'extension pgactive
L'exemple suivant illustre la façon dont le pgactive
groupe est créé, ainsi que les autres étapes requises pour créer l'pgactive
extension sur l'SQLinstance de base de données RDS for Postgre.
Utilisez
psql
un autre outil client pour vous connecter à votre première SQL instance RDS de base de données Postgre.psql --host=
firstinstance.111122223333
.aws-region
.rds.amazonaws.com --port=5432 --username=master username
--password --dbname=postgres
Créez une base de données sur l'SQLinstance RDS for Postgre à l'aide de la commande suivante :
postgres=>
CREATE DATABASEapp
;Faites basculer la connexion sur la nouvelle base de données à l'aide de la commande suivante :
\c
app
Pour vérifier si le paramètre
shared_preload_libraries
contientpgactive
, exécutez la commande suivante :app=>
SELECT setting ~ 'pgactive' FROM pg_catalog.pg_settings WHERE name = 'shared_preload_libraries';?column? ---------- t
-
Créez et renseignez un exemple de table à l'aide des SQL instructions suivantes :
Créez un exemple de tableau à l'aide de l'SQLinstruction suivante.
app=>
CREATE SCHEMA inventory; CREATE TABLE inventory.products ( id int PRIMARY KEY, product_name text NOT NULL, created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP);Remplissez le tableau avec des exemples de données à l'aide de l'SQLinstruction suivante.
app=>
INSERT INTO inventory.products (id, product_name) VALUES (1, 'soap'), (2, 'shampoo'), (3, 'conditioner');Vérifiez que les données existent dans le tableau à l'aide de l'SQLinstruction suivante.
app=>
SELECT count(*) FROM inventory.products;count ------- 3
Créez l'extension
pgactive
sur la base de données existante.app=>
CREATE EXTENSION pgactive;Créez et initialisez le groupe pgactive à l'aide des commandes suivantes :
app=>
SELECT pgactive.pgactive_create_group( node_name :='node1-app'
, node_dsn := 'dbname=app
host=firstinstance.111122223333
.aws-region
.rds.amazonaws.com user=master username
password=PASSWORD
');node1-app est le nom que vous attribuez pour identifier de manière unique un nœud dans le groupe
pgactive
.Note
Pour pouvoir effectuer cette étape sur une instance de base de données accessible au public, vous devez activer le paramètre
rds.custom_dns_resolution
en le définissant sur1
.Pour vérifier si l'instance de base de données est prête, utilisez la commande suivante :
app=>
SELECT pgactive.pgactive_wait_for_node_ready();Si la commande aboutit, vous obtenez la sortie suivante :
pgactive_wait_for_node_ready ------------------------------ (1 row)
Pour configurer la seconde SQL instance RDS pour Postgre et la joindre au groupe pgactive
L'exemple suivant illustre comment vous pouvez joindre une SQL instance de base de données RDS pour Postgre au pgactive
groupe, ainsi que les autres étapes nécessaires pour créer l'pgactive
extension sur l'instance de base de données.
Ces étapes supposent qu'un autre pour les SQL instances de base de données Postgre a été configuré avec l'pgactive
extension. Pour de plus amples informations, veuillez consulter Initialisation de la capacité d'extension pgactive.
Utilisez
psql
pour vous connecter à l'instance qui doit recevoir les mises à jour du serveur de publication.psql --host=
secondinstance.111122223333
.aws-region
.rds.amazonaws.com --port=5432 --username=master username
--password --dbname=postgres
Créez une base de données sur la seconde SQL instance RDS de base de données Postgre à l'aide de la commande suivante :
postgres=>
CREATE DATABASEapp
;Faites basculer la connexion sur la nouvelle base de données à l'aide de la commande suivante :
\c
app
Créez l'extension
pgactive
sur la base de données existante.app=>
CREATE EXTENSION pgactive;Joignez le pour la SQL deuxième instance de base de données Postgre au
pgactive
groupe comme suit.app=>
SELECT pgactive.pgactive_join_group( node_name :='node2-app'
, node_dsn := 'dbname=app
host=secondinstance.111122223333
.aws-region
.rds.amazonaws.com user=master username
password=PASSWORD
', join_using_dsn := 'dbname=app
host=firstinstance.111122223333
.aws-region
.rds.amazonaws.com user=postgres
password=PASSWORD
');node2-app est le nom que vous attribuez pour identifier de manière unique un nœud dans le groupe
pgactive
.Pour vérifier si l'instance de base de données est prête, utilisez la commande suivante :
app=>
SELECT pgactive.pgactive_wait_for_node_ready();Si la commande aboutit, vous obtenez la sortie suivante :
pgactive_wait_for_node_ready ------------------------------ (1 row)
Si le premier RDS pour la SQL base de données Postgre est relativement volumineux, vous pouvez voir l'
pgactive.pgactive_wait_for_node_ready()
émission du rapport de progression de l'opération de restauration. La sortie ressemble à ce qui suit:NOTICE: restoring database 'app', 6% of 7483 MB complete NOTICE: restoring database 'app', 42% of 7483 MB complete NOTICE: restoring database 'app', 77% of 7483 MB complete NOTICE: restoring database 'app', 98% of 7483 MB complete NOTICE: successfully restored database 'app' from node node1-app in 00:04:12.274956 pgactive_wait_for_node_ready ------------------------------ (1 row)
À partir de cet instant,
pgactive
synchronise les données entre les deux instances de base de données.Vous pouvez utiliser la commande suivante pour vérifier si la base de données de la deuxième instance de base de données contient les données :
app=>
SELECT count(*) FROM inventory.products;Si les données ont bien été synchronisées, vous obtenez la sortie suivante :
count ------- 3
Exécutez la commande suivante pour insérer de nouvelles valeurs :
app=>
INSERT INTO inventory.products (id, product_name) VALUES ('lotion');Connectez-vous à la base de données de la première instance de base de données et exécutez la requête suivante :
app=>
SELECT count(*) FROM inventory.products;Si la réplication active-active est initialisée, vous obtenez une sortie de ce type :
count ------- 4
Pour détacher et supprimer une instance de base de données du groupe pgactive
Vous pouvez détacher et supprimer une instance de base de données du groupe pgactive
en suivant ces étapes :
Vous pouvez détacher la deuxième instance de base de données de la première à l'aide de la commande suivante :
app=>
SELECT * FROM pgactive.pgactive_detach_nodes(ARRAY[‘node2-app
']);Supprimez l'extension
pgactive
de la deuxième instance de base de données à l'aide de la commande suivante :app=>
SELECT * FROM pgactive.pgactive_remove();Pour supprimer l'extension de force :
app=>
SELECT * FROM pgactive.pgactive_remove(true);Supprimez l'extension à l'aide de la commande suivante :
app=>
DROP EXTENSION pgactive;
Gestion des conflits de la réplication active-active
L'extension pgactive
fonctionne par base de données et non par cluster. Chaque instance de base de données qui utilise pgactive
est une instance indépendante et peut accepter les modifications de données de n'importe quelle source. Lorsqu'une modification est envoyée à une instance de base de données, Postgre la SQL valide localement, puis l'utilise pgactive
pour répliquer la modification de manière asynchrone sur d'autres instances de base de données. Lorsque deux SQL instances de base de données Postgre mettent à jour le même enregistrement presque au même moment, un conflit peut survenir.
L'extension pgactive
propose des mécanismes de détection et de résolution automatique des conflits. Il suit l'horodatage du moment où la transaction a été validée sur les deux instances de base de données et applique automatiquement la modification avec l'horodatage le plus récent. L'extension pgactive
journalise également la survenance d'un conflit dans la table pgactive.pgactive_conflict_history
.
Ils pgactive.pgactive_conflict_history
continueront de croître. Vous souhaiterez peut-être définir une politique de purge. Cela peut être fait en supprimant régulièrement certains enregistrements ou en définissant un schéma de partitionnement pour cette relation (puis en détachant, supprimant, tronquant les partitions qui vous intéressent). Pour mettre en œuvre régulièrement la politique de purge, l'une des options consiste à utiliser l'pg_cron
extension. Consultez les informations suivantes concernant un exemple de table d'pg_cron
historique, Planification de la maintenance avec l'extension Postgre SQL pg_cron.
Gestion des séquences dans une réplication active-active
Une SQL instance RDS de base de données Postgre dotée de l'pgactive
extension utilise deux mécanismes de séquence différents pour générer des valeurs uniques.
Séquences globales
Pour utiliser une séquence globale, créez une séquence locale avec l'instruction CREATE SEQUENCE
. Utilisez pgactive.pgactive_snowflake_id_nextval(seqname)
plutôt que usingnextval(seqname)
pour obtenir la prochaine valeur unique de la séquence.
L'exemple suivant crée une séquence globale :
postgres=>
CREATE TABLE gstest ( id bigint primary key, parrot text );
postgres=>
CREATE SEQUENCE gstest_id_seq OWNED BY gstest.id;
postgres=>
ALTER TABLE gstest \ ALTER COLUMN id SET DEFAULT \ pgactive.pgactive_snowflake_id_nextval('gstest_id_seq');
Séquences partitionnées
Dans les séquences partitionnées ou fractionnées, une SQL séquence Postgre normale est utilisée sur chaque nœud. Chaque séquence s'incrémente de la même valeur et débute à des décalages différents. Par exemple, avec un pas de 100, le nœud 1 génère la séquence 101, 201, 301, etc. et le nœud 2 génère la séquence 102, 202, 302, etc. Ce mécanisme fonctionne bien même si les nœuds ne peuvent pas communiquer pendant de longues périodes. Cependant, il impose au concepteur de spécifier un nombre maximal de nœuds au moment d'établir le schéma et nécessite une configuration par nœud. Les erreurs peuvent facilement engendrer un chevauchement de séquences.
Il est relativement simple de configurer cette approche avec pgactive
en créant la séquence souhaitée sur un nœud comme suit :
CREATE TABLE some_table (generated_value bigint primary key);
postgres=>
CREATE SEQUENCE some_seq INCREMENT 100 OWNED BY some_table.generated_value;
postgres=>
ALTER TABLE some_table ALTER COLUMN generated_value SET DEFAULT nextval('some_seq');
Appelez ensuite setval
sur chaque nœud pour donner une valeur de départ de décalage différente comme suit.
postgres=>
-- On node 1 SELECT setval('some_seq', 1); -- On node 2 SELECT setval('some_seq', 2);
Référence des paramètres de l'extension pgactive
Vous pouvez utiliser la requête suivante pour afficher tous les paramètres associés à l'extension pgactive
.
postgres=>
SELECT * FROM pg_settings WHERE name LIKE 'pgactive.%';
Mesurer le délai de réplication entre les membres actifs
Vous pouvez utiliser la requête suivante pour visualiser le délai de réplication entre les pgactive
membres. Exécutez cette requête sur chaque pgactive
nœud pour obtenir une vue d'ensemble complète.
postgres=# SELECT *, (last_applied_xact_at - last_applied_xact_committs) AS lag FROM pgactive.pgactive_node_slots;
-{ RECORD 1 ]----------------+-----------------------------------------------------------------
node_name | node2-app
slot_name | pgactive_5_7332551165694385385_0_5__
slot_restart_lsn | 0/1A898A8
slot_confirmed_lsn | 0/1A898E0
walsender_active | t
walsender_pid | 69022
sent_lsn | 0/1A898E0
write_lsn | 0/1A898E0
flush_lsn | 0/1A898E0
replay_lsn | 0/1A898E0
last_sent_xact_id | 746
last_sent_xact_committs | 2024-02-06 18:04:22.430376+00
last_sent_xact_at | 2024-02-06 18:04:22.431359+00
last_applied_xact_id | 746
last_applied_xact_committs | 2024-02-06 18:04:22.430376+00
last_applied_xact_at | 2024-02-06 18:04:52.452465+00
lag | 00:00:30.022089
Limitations liées à l'extension pgactive
Toutes les tables nécessitent une clé primaire, faute de quoi, les mises à jour et les suppressions ne sont pas autorisées. Les valeurs de la colonne Primary Key (Clé primaire) ne doivent pas être mises à jour.
Les séquences peuvent présenter des écarts et parfois ne suivre aucun ordre. Les séquences ne sont pas répliquées. Pour de plus amples informations, veuillez consulter Gestion des séquences dans une réplication active-active.
DDLet les objets volumineux ne sont pas répliqués.
Les index uniques secondaires peuvent causer des divergences de données.
Le classement doit être identique sur tous les nœuds du groupe.
L'équilibrage de charge entre les nœuds est un anti-modèle.
Les transactions volumineuses peuvent occasionner un retard de réplication.
Réduction du ballonnement des tables et des index avec l'extension pg_repack
Vous pouvez utiliser l'pg_repack
extension pour supprimer la surcharge des tables et des index comme alternative à. VACUUM FULL
Cette extension est prise en charge sur RDS les SQL versions 9.6.3 et supérieures de Postgre. Pour plus d'informations sur l'pg_repack
extension et le réemballage complet de la table, consultez la documentation du GitHub projet
Contrairement à celaVACUUM FULL
, l'pg_repack
extension ne nécessite un lock exclusif (AccessExclusiveLock) que pendant une courte période lors de l'opération de reconstruction de la table dans les cas suivants :
Création initiale de la table de journal — Une table de journal est créée pour enregistrer les modifications survenues lors de la copie initiale des données, comme indiqué dans l'exemple suivant :
postgres=>
\dt+ repack.log_* List of relations -[ RECORD 1 ]-+---------- Schema | repack Name | log_16490 Type | table Owner | postgres Persistence | permanent Access method | heap Size | 65 MB Description |
swap-and-drop Phase finale.
Pour le reste de l'opération de reconstruction, il suffit de ACCESS SHARE
verrouiller la table d'origine pour copier des lignes de celle-ci vers la nouvelle table. Cela permet aux DELETE opérations INSERTUPDATE, et de se dérouler comme d'habitude.
Recommandations
Les recommandations suivantes s'appliquent lorsque vous supprimez le bloat des tables et des index à l'aide de l'pg_repack
extension :
Effectuez le reconditionnement en dehors des heures ouvrables ou pendant une période de maintenance afin de minimiser son impact sur les performances des autres activités de base de données.
Surveillez de près les sessions bloquantes pendant l'activité de reconstruction et assurez-vous qu'aucune activité sur la table d'origine ne risque de bloquer
pg_repack
, en particulier pendant la swap-and-drop phase finale lorsqu'un verrouillage exclusif de la table d'origine est nécessaire. Pour plus d'informations, consultez la section Identification de ce qui bloque une requête. Lorsque vous voyez une session bloquante, vous pouvez y mettre fin à l'aide de la commande suivante après mûre réflexion. Cela permet de continuer
pg_repack
à terminer la reconstruction :SELECT pg_terminate_backend(
pid
);-
Lors de l'application des modifications accumulées à partir de la table des
pg_repack's
journaux sur des systèmes présentant un taux de transactions très élevé, le processus d'application risque de ne pas être en mesure de suivre le rythme des modifications. Dans de tels cas,pg_repack
il ne serait pas en mesure de terminer le processus de candidature. Pour de plus amples informations, veuillez consulter Surveillance de la nouvelle table lors du reconditionnement. Si les index sont très volumineux, une autre solution consiste à effectuer un reconditionnement des index uniquement. Cela permet également aux cycles VACUUM de nettoyage des index de se terminer plus rapidement.Vous pouvez ignorer la phase de nettoyage de l'index à l'aide VACUUM du manuel depuis Postgre SQL version 12, et elle est automatiquement ignorée lors de l'aspiration automatique d'urgence depuis Postgre version 14. SQL Cela permet de VACUUM terminer plus rapidement sans supprimer le gonflement de l'index et n'est destiné qu'aux situations d'urgence telles que la prévention de l'VACUUMencapsulation. Pour plus d'informations, consultez la section Éviter le gonflement des index dans le guide de l'utilisateur Amazon Aurora.
Prérequis
La table doit avoir PRIMARY KEY ou non une UNIQUE contrainte nulle.
La version de l'extension doit être la même pour le client et le serveur.
Assurez-vous que la taille de l'RDSinstance est
FreeStorageSpace
supérieure à la taille totale de la table sans le gonflement. Par exemple, considérez que la taille totale de la table, index TOAST et index inclus, est de 2 To, et que le volume total de la table est de 1 To. La valeur requiseFreeStorageSpace
doit être supérieure à la valeur renvoyée par le calcul suivant :2TB (Table size)
-1TB (Table bloat)
=1TB
Vous pouvez utiliser la requête suivante pour vérifier la taille totale de la table et l'utiliser
pgstattuple
pour en déduire le gonflement. Pour plus d'informations, consultez Diagnostic du gonflement des tables et des index dans le guide de l'utilisateur Amazon AuroraSELECT pg_size_pretty(pg_total_relation_size('table_name')) AS total_table_size;
Cet espace est récupéré une fois l'activité terminée.
Assurez-vous que l'RDSinstance dispose d'une capacité de calcul et d'E/S suffisante pour gérer l'opération de reconditionnement. Vous pouvez envisager d'augmenter la classe d'instance pour un équilibre optimal des performances.
Pour utiliser l'pg_repack
extension
-
Installez l'
pg_repack
extension sur votre SQL instance de base de données RDS for Postgre en exécutant la commande suivante.CREATE EXTENSION pg_repack;
-
Exécutez les commandes suivantes pour accorder un accès en écriture aux tables de journaux temporaires créées par
pg_repack
.ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC; ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;
Connectez-vous à la base de données à l'aide de l'utilitaire
pg_repack
client. Utilisez un compte qui possède les privilègesrds_superuser
. Par exemple, supposons que le rôlerds_test
a les privilègesrds_superuser
. La syntaxe suivante s'appliquepg_repack
aux tables complètes, y compris tous les index de table de lapostgres
base de données.pg_repack -h
db-instance-name
.111122223333.aws-region
.rds.amazonaws.com -Urds_test
-kpostgres
Note
Vous devez vous connecter à l'aide de l'option -k. L'option -a n'est pas prise en charge.
La réponse du
pg_repack
client fournit des informations sur les tables de l'instance de base de données qui sont reconditionnées.INFO: repacking table "pgbench_tellers" INFO: repacking table "pgbench_accounts" INFO: repacking table "pgbench_branches"
-
La syntaxe suivante réemballe une seule table,
orders
y compris les index de la base de données.postgres
pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U
rds_test
--tableorders
-kpostgres
La syntaxe suivante réemballe uniquement les index de la
orders
table danspostgres
la base de données.pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U
rds_test
--tableorders
--only-indexes -kpostgres
Surveillance de la nouvelle table lors du reconditionnement
La taille de la base de données est augmentée de la taille totale de la table moins le surchargement, jusqu'à la swap-and-drop phase de reconditionnement. Vous pouvez surveiller le taux de croissance de la taille de la base de données, calculer la vitesse du reconditionnement et estimer approximativement le temps nécessaire pour terminer le transfert de données initial.
Par exemple, considérez que la taille totale de la table est de 2 To, la taille de la base de données de 4 To et la charge totale de la table de 1 To. La valeur de taille totale de la base de données renvoyée par le calcul à la fin de l'opération de reconditionnement est la suivante :
2TB (Table size)
+4 TB (Database size)
-1TB (Table bloat)
=5TB
Vous pouvez estimer approximativement la vitesse de l'opération de reconditionnement en échantillonnant le taux de croissance en octets entre deux points dans le temps. Si le taux de croissance est de 1 Go par minute, l'opération initiale de création de table peut prendre environ 1 000 minutes ou 16,6 heures. Outre la création initiale de la table, vous
pg_repack
devez également appliquer les modifications accumulées. Le temps nécessaire dépend du taux d'application des modifications en cours et des modifications cumulées.Note
Vous pouvez utiliser
pgstattuple
l'extension pour calculer le gonflement dans le tableau. Pour plus d'informations, consultez pgstattuple. Le nombre de lignes de la table de
pg_repack's
journal, sous le schéma de reconditionnement, représente le volume de modifications en attente d'être appliquées à la nouvelle table après le chargement initial.Vous pouvez consulter la table des
pg_repack's
journauxpg_stat_all_tables
pour surveiller les modifications appliquées à la nouvelle table.pg_stat_all_tables.n_live_tup
indique le nombre d'enregistrements en attente d'être appliqués à la nouvelle table. Pour plus d'informations, consultez pg_stat_all_tables. postgres=>
SELECT relname,n_live_tup FROM pg_stat_all_tables WHERE schemaname = 'repack' AND relname ILIKE '%log%';
-[ RECORD 1 ]--------- relname | log_16490 n_live_tup | 2000000
-
Vous pouvez utiliser l'
pg_stat_statements
extension pour connaître le temps nécessaire à chaque étape de l'opération de reconditionnement. Cela est utile pour préparer l'application de la même opération de reconditionnement dans un environnement de production. Vous pouvez ajuster laLIMIT
clause pour étendre davantage la sortie.postgres=>
SELECT SUBSTR(query, 1, 100) query, round((round(total_exec_time::numeric, 6) / 1000 / 60),4) total_exec_time_in_minutes FROM pg_stat_statements WHERE query ILIKE '%repack%' ORDER BY total_exec_time DESC LIMIT 5;
query | total_exec_time_in_minutes -----------------------------------------------------------------------+---------------------------- CREATE UNIQUE INDEX index_16493 ON repack.table_16490 USING btree (a) | 6.8627 INSERT INTO repack.table_16490 SELECT a FROM ONLY public.t1 | 6.4150 SELECT repack.repack_apply($1, $2, $3, $4, $5, $6) | 0.5395 SELECT repack.repack_drop($1, $2) | 0.0004 SELECT repack.repack_swap($1) | 0.0004 (5 rows)
Le reconditionnement est une out-of-place opération complète, de sorte que la table d'origine n'est pas affectée et nous ne prévoyons aucun problème inattendu nécessitant la restauration de la table d'origine. Si le reconditionnement échoue de façon inattendue, vous devez rechercher la cause de l'erreur et la résoudre.
Une fois le problème résolu, supprimez et recréez l'pg_repack
extension dans la base de données où se trouve la table, puis recommencez l'pg_repack
étape. En outre, la disponibilité des ressources informatiques et l'accessibilité simultanée de la table jouent un rôle crucial dans la réalisation en temps voulu de l'opération de reconditionnement.
Mise à niveau et utilisation de l'PLV8extension
PLV8est une extension de langage Javascript fiable pour PostgreSQL. Vous pouvez l'utiliser pour les procédures stockées, les déclencheurs et tout autre code procédural appelable depuisSQL. Cette extension de langue est prise en charge par toutes les versions actuelles de PostgreSQL.
Si vous utilisez PLV8
Le processus de mise à niveau supprime toutes vos PLV8 fonctions existantes. Nous vous recommandons donc de créer un instantané de votre SQL instance de base de données RDS for Postgre avant la mise à niveau. Pour de plus amples informations, veuillez consulter Création d'un instantané de base de données pour une instance de base de données mono-AZ.
Pour synchroniser les métadonnées de votre catalogue avec une nouvelle version de PLV8
-
Vérifiez que vous devez mettre à jour. Pour ce faire, exécutez la commande suivante tout en étant connecté à votre instance.
SELECT * FROM pg_available_extensions WHERE name IN ('plv8','plls','plcoffee');
Si les résultats contiennent des valeurs pour une version installée avec un numéro inférieur à celui de la version par défaut, poursuivez cette procédure pour mettre à jour vos extensions. Par exemple, l'ensemble de résultats suivant indique que vous devez procéder à la mise à jour.
name | default_version | installed_version | comment --------+-----------------+-------------------+-------------------------------------------------- plls | 2.1.0 | 1.5.3 | PL/LiveScript (v8) trusted procedural language plcoffee| 2.1.0 | 1.5.3 | PL/CoffeeScript (v8) trusted procedural language plv8 | 2.1.0 | 1.5.3 | PL/JavaScript (v8) trusted procedural language (3 rows)
Créez un instantané de votre SQL instance de base de données RDS pour Postgre si vous ne l'avez pas encore fait. Vous pouvez poursuivre avec les étapes suivantes tandis que l'instantané est en cours de création.
-
Déterminez le nombre de PLV8 fonctions de votre instance de base de données afin de vérifier qu'elles sont toutes en place après la mise à niveau. Par exemple, la SQL requête suivante renvoie le nombre de fonctions écrites en plv8, plcoffee et plls.
SELECT proname, nspname, lanname FROM pg_proc p, pg_language l, pg_namespace n WHERE p.prolang = l.oid AND n.oid = p.pronamespace AND lanname IN ('plv8','plcoffee','plls');
-
Utiliser pg_dump pour créer un fichier de vidage schema-only. Par exemple, créez un fichier sur votre ordinateur client dans le répertoire
/tmp
../pg_dump -Fc --schema-only -U master postgres >/tmp/test.dmp
Cet exemple utilise les options suivantes :
-
-Fc
: format personnalisé -
--schema-only : supprime uniquement les commandes nécessaires à la création du schéma (les fonctions dans ce cas)
-
-U
— Le nom d'utilisateur RDS principal -
database
: le nom de base de données dans votre instance de base de données
Pour plus d'informations sur pg_dump, consultez pg_dump
dans la documentation de Postgre. SQL -
-
Extrayez l'DDLinstruction CREATE FUNCTION « » présente dans le fichier de vidage. L'exemple suivant utilise la
grep
commande pour extraire l'DDLinstruction qui crée les fonctions et les enregistrer dans un fichier. Vous l'utiliserez dans les étapes suivantes pour recréer les fonctions../pg_restore -l /tmp/test.dmp | grep FUNCTION > /tmp/function_list/
Pour plus d'informations sur pg_restore, consultez pg_restore
dans la documentation de Postgre. SQL -
Supprimez les fonctions et les extensions. L'exemple suivant supprime tous les objets PLV8 basés. L'option cascade garantit que les objets dépendants sont supprimés.
DROP EXTENSION plv8 CASCADE;
Si votre SQL instance Postgre contient des objets basés sur plcoffee ou plls, répétez cette étape pour ces extensions.
-
Créez les extensions. L'exemple suivant crée les extensions plv8, plcoffee et plls.
CREATE EXTENSION plv8; CREATE EXTENSION plcoffee; CREATE EXTENSION plls;
-
Créez les fonctions à l'aide du fichier de vidage et du fichier « pilote ».
L'exemple suivant recrée les fonctions que vous avez extraites précédemment.
./pg_restore -U master -d postgres -Fc -L /tmp/function_list /tmp/test.dmp
-
Vérifiez que toutes vos fonctions ont été recréées à l'aide de la requête suivante.
SELECT * FROM pg_available_extensions WHERE name IN ('plv8','plls','plcoffee');
La PLV8 version 2 ajoute la ligne supplémentaire suivante à votre jeu de résultats :
proname | nspname | lanname ---------------+------------+---------- plv8_version | pg_catalog | plv8
Utiliser PL/Rust pour écrire des SQL fonctions Postgre dans le langage Rust
PL/Rust est une extension de langage Rust fiable pour Postgre. SQL Vous pouvez l'utiliser pour les procédures stockées, les fonctions et tout autre code procédural appelable depuisSQL. L'extension de langage PL/Rust est disponible dans les versions suivantes :
-
RDSpour Postgre SQL 16.1 et versions 16 supérieures
-
RDSpour Postgre SQL 15.2-R2 et versions 15 supérieures
-
RDSpour Postgre SQL 14.9 et versions 14 supérieures
-
RDSpour Postgre SQL 13.12 et versions 13 supérieures
Pour plus d'informations, voir PL/Rust
Rubriques
Configuration de PL/Rust
Pour installer l'extension plrust sur votre instance de base de données, ajoutez plrust au paramètre shared_preload_libraries
dans le groupe de paramètres de la base de données associé à votre instance de base de données. Une fois l'extension plrust installée, vous pouvez créer des fonctions.
Pour modifier le paramètre shared_preload_libraries
, votre instance de base de données doit être associée à un groupe de paramètres personnalisé. Pour obtenir des informations sur la création d'un groupe de paramètres de base de données personnalisé, consultez Groupes de paramètres pour Amazon RDS.
Vous pouvez installer l'extension plrust en utilisant le AWS Management Console ou le AWS CLI.
Les étapes suivantes supposent que votre instance de base de données est associée à un groupe de paramètres de cluster de bases de données personnalisé.
Installer l'extension plrust dans le paramètre shared_preload_libraries
Effectuez les étapes suivantes à l'aide d'un compte membre du groupe (rôle) rds_superuser
.
Connectez-vous à la RDS console Amazon AWS Management Console et ouvrez-la à l'adresse https://console.aws.amazon.com/rds/
. -
Dans le panneau de navigation, choisissez Databases (Bases de données).
-
Choisissez le nom de votre instance de base de données pour afficher ses détails.
-
Ouvrez l'onglet Configuration de votre instance de base de données et recherchez le lien du groupe de paramètres de l'instance de base de données.
-
Cliquez sur le lien pour ouvrir les paramètres personnalisés associés à votre instance de base de données.
-
Dans le champ de recherche Parameters (Paramètres), tapez
shared_pre
pour trouver le paramètreshared_preload_libraries
. -
Choisissez Edit parameters (Modifier les paramètres) pour accéder aux valeurs des propriétés.
-
Ajoutez plrust à la liste dans le champ Valeurs. Utilisez une virgule pour séparer les éléments de la liste de valeurs.
Redémarrez l'instance de base de données pour que la modification apportée au paramètre
shared_preload_libraries
prenne effet. Le redémarrage initial peut nécessiter plus de temps.Lorsque l'instance est disponible, vérifiez que plrust a été initialisé. Utilisez
psql
pour vous connecter à l'instance de base de données, puis exécutez la commande suivante.SHOW shared_preload_libraries;
Votre sortie doit ressembler à ce qui suit :
shared_preload_libraries -------------------------- rdsutils,plrust (1 row)
Installez l'extension plrust dans le paramètre shared_preload_libraries
Effectuez les étapes suivantes à l'aide d'un compte membre du groupe (rôle) rds_superuser
.
Utilisez la modify-db-parameter-group AWS CLI commande pour ajouter plrust 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=plrust,ApplyMethod=pending-reboot" \ --regionaws-region
-
Utilisez la reboot-db-instance AWS CLI commande pour redémarrer l'instance de base de données et initialiser la bibliothèque plrust. Le redémarrage initial peut nécessiter plus de temps.
aws rds reboot-db-instance \ --db-instance-identifier
your-instance
\ --regionaws-region
Lorsque l'instance est disponible, vous pouvez vérifier que plrust a été initialisé. Utilisez
psql
pour vous connecter à l'instance de base de données, puis exécutez la commande suivante.SHOW shared_preload_libraries;
Votre sortie doit ressembler à ce qui suit :
shared_preload_libraries -------------------------- rdsutils,plrust (1 row)
Création de fonctions avec PL/Rust
PL/Rust compile la fonction sous forme de bibliothèque dynamique, la charger et l'exécute.
La fonction Rust suivante filtre les multiples d'un tableau.
postgres=> CREATE LANGUAGE plrust; CREATE EXTENSION
CREATE OR REPLACE FUNCTION filter_multiples(a BIGINT[], multiple BIGINT) RETURNS BIGINT[] IMMUTABLE STRICT LANGUAGE PLRUST AS $$ Ok(Some(a.into_iter().filter(|x| x.unwrap() % multiple != 0).collect())) $$; WITH gen_values AS ( SELECT ARRAY(SELECT * FROM generate_series(1,100)) as arr) SELECT filter_multiples(arr, 3) from gen_values;
Utilisation de caisses avec PL/Rust
Dans RDS les SQL versions 16.3-R2 et supérieures de Postgre, 15.7-R2 et supérieures 15 versions, 14.12-R2 et versions supérieures 14 versions, et 13.15-R2 et versions supérieures 13 versions, PL/Rust prend en charge des caisses supplémentaires :
-
url
-
regex
-
serde
-
serde_json
Dans RDS les SQL versions 15.5-R2 et supérieures de Postgre, 14.10-R2 et versions 14 et versions 13.13-R2 et supérieures 13 versions, PL/Rust prend en charge deux caisses supplémentaires :
-
croaring-rs
-
num-bigint
À partir des SQL versions 15.4, 14.9 et 13.12 d'Amazon RDS pour Postgre, PL/Rust prend en charge les caisses suivantes :
-
aes
-
ctr
-
rand
Seules les fonctionnalités par défaut sont prises en charge pour ces caisses. Les nouvelles SQL versions RDS de Postgre peuvent contenir des versions mises à jour de caisses, et les anciennes versions de caisses peuvent ne plus être prises en charge.
Suivez les bonnes pratiques pour effectuer une mise à niveau de version majeure afin de tester si vos fonctions PL/Rust sont compatibles avec la nouvelle version majeure. Pour plus d'informations, consultez le blog Meilleures pratiques pour la mise à niveau d'Amazon RDS vers les versions majeures et mineures de Postgre SQL
Des exemples d'utilisation des dépendances lors de la création d'une fonction PL/Rust sont disponibles dans Utiliser les dépendances
Limites de PL/Rust
Par défaut, les utilisateurs de la base de données ne peuvent pas utiliser PL/Rust. Pour fournir un accès à PL/Rust, connectez-vous en tant qu'utilisateur avec le privilège rds_superuser et exécutez la commande suivante :
postgres=> GRANT USAGE ON LANGUAGE PLRUST TO
user
;