Amazon Relational Database Service
Guide de l'utilisateur

Importation de données dans PostgreSQL sur Amazon RDS

Supposons que vous disposez d'un déploiement PostgreSQL existant que vous souhaitez transférer dans Amazon RDS. La complexité de votre tâche dépend de la taille de votre base de données et des types d'objets de base de données que vous transférez. Prenons l'exemple d'une base de données qui contient des jeux de données se mesurant en gigaoctets, ainsi que des déclencheurs et des procédures stockés. Une telle base de données va être plus compliquée qu'une base de données simple avec seulement quelques mégaoctets de données de test et pas de déclencheurs, ni de procédures stockés.

Nous vous recommandons d'utiliser les outils de migration de base de données PostgreSQL natifs dans les conditions suivantes :

  • Vous avez une migration homogène, dans le sens où vous migrez depuis une base de données avec le même moteur de base de données que la base de données cible.

  • Vous migrez une base de données entière.

  • Les outils natifs vous permettent de migrer votre système avec une interruption minimale.

Dans la plupart des autres cas, l'exécution d'une migration de base de données à l'aide de AWS Database Migration Service (AWS DMS) est la meilleure approche. AWS DMS peut migrer les bases de données sans interruption et, pour de nombreux moteurs de bases de données, poursuivre la réplication en cours jusqu'à ce que vous soyez prêt à basculer sur la base de données cible. Vous pouvez migrer vers le même moteur de base de données ou vers un moteur de base de données différent avec AWS DMS. Si vous migrez vers un moteur de base de données différent de votre base de données source, vous pouvez utiliser AWS Schema Conversion Tool (AWS SCT). Vous utilisez AWS SCT pour migrer des objets de schéma qui ne sont pas migrés par AWS DMS. Pour plus d'informations sur AWS DMS, consultez Qu'est-ce que AWS Database Migration Service ?

Modifiez votre groupe de paramètres de base de données pour inclure les paramètres suivants pour votre importation uniquement. Vous devez tester les réglages des paramètres pour déterminer les réglages les plus efficaces pour la taille de votre instance de base de données. Vous devez également revenir aux valeurs de production pour ces paramètres une fois votre importation terminée.

Modifiez les paramètres de l'instance de base de données comme suit :

  • Désactivez les sauvegardes de l'instance de base de données (affectez la valeur 0 à backup_retention).

  • Désactivez le mode multi-AZ.

Modifiez votre groupe de paramètres DB pour inclure les paramètres suivants. Vous devez utiliser ces paramètres uniquement lors de l'importation des données. Vous devez tester les réglages des paramètres pour déterminer les réglages les plus efficaces pour la taille de votre instance de base de données. Vous devez également revenir aux valeurs de production pour ces paramètres une fois votre importation terminée.

Paramètre Valeur recommandée lors de l'importation Description

maintenance_work_mem

524288, 1048576, 2097152 ou 4194304 (en Ko). Ces paramètres sont comparables à 512 Mo, 1 Go, 2 Go et 4 Go.

La valeur de ce paramètre dépend de la taille de votre hôte. Ce paramètre est utilisé lors des instructions CREATE INDEX et chaque commande parallèle peut utiliser cette quantité de mémoire. Calculez la meilleure valeur afin de ne pas définir de valeur si élevée et risquer de manquer de mémoire.

checkpoint_segments

256

La valeur de ce paramètre consomme plus espace disque mais limite les conflits sur vos journaux WAL. Pour PostgreSQL versions 9.5.x et 9.6.x, cette valeur est max_wal_size.

checkpoint_timeout

1800

La valeur de ce paramètre vous permet une rotation WAL moins fréquente.

synchronous_commit

Désactivé

Désactivez ce paramètre pour accélérer les écritures. Le fait de désactiver ce paramètre peut augmenter le risque de perte de données en cas de défaillance du serveur (ne désactivez pas FSYNC)

wal_buffers

8192

Cette valeur est en unités de 8 Ko. Cela permet de nouveau d'accélérer la génération WAL

autovacuum

Désactivé

Désactivez le paramètre auto-vacuum de PostgreSQL lorsque vous chargez des données afin qu'il n'utilise pas de ressources

Utilisez les commandes pg_dump -Fc (compressé) ou pg_restore -j (parallèle) avec ces paramètres.

Note

La commande PostgreSQL pg_dumpall requiert des autorisations super_user qui ne sont pas accordées lorsque vous créez une instance de base de données, si bien qu'elle ne peut pas être utilisée pour importer des données.

Importation d'une base de données PostgreSQL à partir d'une instance Amazon EC2

Si vous possédez des données sur un serveur PostgreSQL sur une instance Amazon EC2 et que vous souhaitez les déplacer vers une instance de base de données PostgreSQL, vous pouvez utiliser le processus suivant. La liste suivante montre les étapes à suivre. Chaque étape est présentée plus en détail dans les sections suivantes.

  1. Créez un fichier contenant les données à charger à l'aide de pg_dump

  2. Créez l'instance de base de données cible

  3. Utilisez psql pour créer la base de données sur l'instance de base de données et pour charger les données

  4. Créez un instantané de base de données de l'instance de base de données

Étape 1 : Créer un fichier contenant les données à charger à l'aide de pg_dump

L'utilitaire pg_dump utilise la commande COPY pour créer un schéma et un vidage des données d'une base de données PostgreSQL. Le script de vidage généré par pg_dump charge les données dans une base de données dotée du même nom et recrée les tables, les index et les clés étrangères. Vous pouvez utiliser la commande pg_restore et le paramètre -d pour restaurer les données dans une base de données dotée d'un nom différent.

Avant de créer le vidage des données, vous devez interroger les tables à vider pour obtenir le nombre de lignes afin de pouvoir confirmer ce nombre sur l'instance de base de données cible.

La commande suivante crée un fichier de vidage mydb2dump.sql pour une base de données nommée mydb2.

prompt>pg_dump dbname=mydb2 -f mydb2dump.sql

Étape 2 : Créer l'instance de base de données cible

Créez l'instance de base de données PostgreSQL cible à l'aide soit de la console Amazon RDS, de AWS CLI ou de l'API. Créez l'instance avec le paramètre de rétention des sauvegardes défini sur 0 et désactivez le mode multi-AZ. Cela vous permet d'effectuer une importation plus rapide des données. Vous devez créer une base de données sur l'instance avant de pouvoir vider les données. La base de données peut avoir le même nom que celle qui contenait les données vidées. Sinon, vous pouvez créer une base de données avec un autre nom. Dans ce cas, vous pouvez utiliser la commande pg_restore et le paramètre -d pour restaurer les données dans une base de données dotée d'un nouveau nom.

Par exemple, les commandes suivantes permettent de vider, de restaurer et de renommer une base de données.

pg_dump -Fc -v -h [endpoint of instance] -U [master username] [database] > [database].dump createdb [new database name] pg_restore -v -h [endpoint of instance] -U [master username] -d [new database name] [database].dump

Étape 3 : Utiliser psql pour créer la base de données sur l'instance de base de données et charger les données

Vous pouvez utiliser la même connexion que vous avez utilisée pour exécuter la commande pg_dump pour vous connecter à l'instance de base de données cible et recréer la base de données. Grâce à psql, vous pouvez utiliser l'identifiant principal et le mot de passe principal pour créer la base de données sur l'instance de base de données.

L'exemple suivant utilise psql et un fichier de vidage nommé mydb2dump.sql pour créer une base de données appelée mydb2 sur une instance de base de données PostgreSQL nommée mypginstance :

Pour Linux, OS X ou Unix :

psql \ -f mydb2dump.sql \ --host mypginstance.c6c8mntzhgv0.us-west-2.rds.amazonaws.com \ --port 8199 \ --username myawsuser \ --password password \ --dbname mydb2

Pour Windows :

psql ^ -f mydb2dump.sql ^ --host mypginstance.c6c8mntzhgv0.us-west-2.rds.amazonaws.com ^ --port 8199 ^ --username myawsuser ^ --password password ^ --dbname mydb2

Étape 4 : Créer un instantané de base de données de l'instance de base de données

Une fois que vous avez vérifié que les données ont été chargées dans votre instance de base de données, nous vous conseillons de créer un instantané de base de données de l'instance de base de données PostgreSQL cible. Les snapshots DB sont des sauvegardes complètes de votre instance de base de données qui peuvent être utilisées pour restaurer l'instance de base de données à un état connu. Un instantané de base de données pris immédiatement après le chargement vous évite de devoir charger les données à nouveau en cas d'incident. Vous pouvez également l'utiliser pour créer de nouvelles instances de base de données. Pour plus d'informations sur la création d'un instantané de base de données, consultez Création d'un instantané de base de données.

Utilisation de la commande \copy pour importer des données dans une table sur une instance de base de données PostgreSQL

Vous pouvez exécuter la commande \copy dans l'invite de commandes psql pour importer des données dans un tableau sur une instance de base de données PostgreSQL. La table doit déjà exister sur l'instance de base de données. Pour plus d'informations sur la commande \copy, consultez la documentation sur PostgreSQL.

Note

La commande \copy ne fournit pas la confirmation des actions, telle qu'un nombre de lignes insérées. PostgreSQL ne fournit pas de messages d'erreur si la commande copy échoue en raison d'une erreur.

Créez un fichier .csv à partir des données dans le tableau source, connectez-vous à la base de données cible sur l'instance PostgreSQL à l'aide de psql, puis exécutez la commande suivante. Cet exemple utilise source-table comme nom de tableau source, source-table.csv comme fichier .csv et target-db comme base de données cible :

target-db=> \copy source-table from 'source-table.csv' with DELIMITER ',';

Vous pouvez également exécuter la commande suivante à partir de l'invite de commandes de votre ordinateur client. Cet exemple utilise source-table comme nom de tableau source, source-table.csv comme fichier .csv et target-db comme base de données cible :

Pour Linux, OS X ou Unix :

$psql target-db \ -U <admin user> \ -p <port> \ -h <DB instance name> \ -c "\copy source-table from 'source-table.csv' with DELIMITER ','"

Pour Windows :

$psql target-db ^ -U <admin user> ^ -p <port> ^ -h <DB instance name> ^ -c "\copy source-table from 'source-table.csv' with DELIMITER ','"

Importation de données Amazon S3 dans une instance de base de données RDS for PostgreSQL d'un

Vous pouvez importer des données d'Amazon S3 vers une table appartenant à une instance de base de données RDS pour PostgreSQL. Pour ce faire, utilisez l'extension PostgreSQL aws_s3 fournie par Amazon RDS.

Note

Pour importer d'Amazon S3 vers RDS for PostgreSQL, votre base de données doit exécuter PostgreSQL version 10.7 ou ultérieure.

Pour plus d'informations sur le stockage de données avec Amazon S3, consultez Créer un compartiment dans le Amazon Simple Storage Service Guide de mise en route. Pour savoir comment charger un fichier dans un compartiment Amazon S3, consultez Ajouter un objet à un compartiment dans le Amazon Simple Storage Service Guide de mise en route.

Présentation de l'importation de données Amazon S3

Pour importer des donnes stockées dans un compartiment Amazon S3 dans une table de base de données PostgreSQL, suivez ces étapes.

Pour importer des données S3 dans Amazon RDS

  1. Installez les extensions PostgreSQL nécessaires. Il s'agit notamment des extensions aws_s3 et aws_commons. Pour cela, démarrez psql et utilisez la commande suivante.

    psql=> CREATE EXTENSION aws_s3 CASCADE; NOTICE: installing required extension "aws_commons"

    L'extension aws_s3 fournit la fonction aws_s3.table_import_from_s3 que vous utilisez pour importer des données Amazon S3. L'extension aws_commons offre des fonctions d'assistant supplémentaires.

  2. Identifiez la table de base de données et le fichier Amazon S3 à utiliser.

    La fonction aws_s3.table_import_from_s3 a besoin du nom de la table de base de données PostgreSQL dans laquelle vous voulez importer les données. La fonction vous demande également d'identifier le fichier Amazon S3 à importer. Pour fournir ces informations, effectuez les étapes suivantes.

    1. Identifiez la table de base de données PostgreSQL dans laquelle placer les données. Par exemple, la table de base de données t1 ci-dessous est utilisée dans les exemples de cette rubrique.

      psql=> CREATE TABLE t1 (col1 varchar(80), col2 varchar(80), col3 varchar(80));
    2. Obtenez les informations suivantes pour identifier le fichier Amazon S3 que vous voulez importer :

      • Nom du compartiment – Un compartiment est un conteneur d'objets ou de fichiers Amazon S3.

      • Chemin du fichier – Le chemin du fichier permet de localiser le fichier dans le compartiment Amazon S3.

      • Région AWS – La région AWS représente l'emplacement du compartiment Amazon S3. Par exemple, si le compartiment S3 se trouve dans la région USA Est (Virginie du Nord), utilisez us-east-1. Pour obtenir la liste des noms de régions AWS et les valeurs associées, consultez régions et zones de disponibilité

      Pour savoir comment obtenir ces informations, consultez Affichage d'un objet dans le Amazon Simple Storage Service Guide de mise en route. Vous pouvez confirmer ces informations en utilisant la commande AWS CLI aws s3 cp. Si les informations sont correctes, cette commande télécharge une copie du fichier Amazon S3.

      aws s3 cp s3://sample_s3_bucket/sample_file_path ./
    3. Utilisez la fonction aws_commons.create_s3_uri pour créer une structure aws_commons._s3_uri_1 permettant d'accueillir les informations sur le fichier Amazon S3. Vous fournissez cette structure aws_commons._s3_uri_1 comme paramètre dans l'appel à la fonction aws_s3.table_import_from_s3.

      Vous trouverez ci-dessous un exemple psql.

      psql=> SELECT aws_commons.create_s3_uri( 'sample_s3_bucket', 'sample.csv', 'us-east-1' ) AS s3_uri \gset
  3. Fournissez une autorisation d'accès au fichier Amazon S3.

    Pour importer des données à partir d'un fichier Amazon S3, vous devez accorder à l'instance de base de données RDS pour PostgreSQL une autorisation d'accès au compartiment Amazon S3 dans lequel se trouve le fichier. Pour cela, utilisez des informations d'identification de sécurité ou un rôle AWS Identity and Access Management (IAM). Pour plus d'informations, consultez Configuration de l'accès à un compartiment Amazon S3.

  4. Importez les données Amazon S3 en appelant la fonction aws_s3.table_import_from_s3.

    Après avoir effectué les tâches de préparation précédentes, utilisez la fonction aws_s3.table_import_from_s3 pour importer les données Amazon S3. Pour plus d'informations, consultez Utilisation de la fonction aws_s3.table_import_from_s3 pour importer des données Amazon S3.

Configuration de l'accès à un compartiment Amazon S3

Pour importer des données à partir d'un fichier Amazon S3, vous devez accorder à l'instance de base de données RDS pour PostgreSQL une autorisation d'accès au compartiment Amazon S3 dans lequel se trouve le fichier. Pour accorder l'accès à un compartiment Amazon S3, vous pouvez employer une des deux méthodes décrites dans les rubriques suivantes.

Utilisation d'un rôle IAM pour accéder à un compartiment Amazon S3

Avant de charger des données à partir d'un fichier Amazon S3, accordez à votre instance de base de données RDS pour PostgreSQL l'autorisation d'accéder au compartiment Amazon S3 dans lequel se trouve le fichier. De cette façon, vous n'avez pas à gérer d'informations d'identification supplémentaires ni à les fournir dans l'appel de fonction aws_s3.table_import_from_s3.

Pour ce faire, créez une stratégie IAM qui donne accès au compartiment Amazon S3. Créez un rôle IAM et attachez la stratégie à ce rôle. Attribuez ensuite le rôle IAM à votre instance de base de données.

Pour permettre à une instance de base de données RDS pour PostgreSQL d'accéder à Amazon S3 via un rôle IAM

  1. Créez une stratégie IAM. Celle-ci fournit au compartiment et à l'objet les autorisations permettant à votre instance de base de données RDS pour PostgreSQL d'accéder à Amazon S3.

    Incluez à la stratégie les actions obligatoires suivantes pour permettre le transfert de fichiers d'un compartiment Amazon S3 vers Amazon RDS :

    • s3:GetObject

    • s3:ListBucket

    Incluez à la stratégie les ressources suivantes pour identifier le compartiment Amazon S3 et les objets qu'il contient. Voici le format Amazon Resource Name (ARN) permettant d'accéder à Amazon S3 :

    • arn:aws:s3:::your-s3-bucket

    • arn:aws:s3:::your-s3-bucket/*

    Pour plus d'informations sur la création d'une stratégie IAM pour Amazon RDS pour PostgreSQL, consultez Création et utilisation d'une stratégie IAM pour l'accès à une base de données IAM. Voir aussi Didacticiel : Créer et attacher votre première stratégie gérée par le client dans le IAM Guide de l'utilisateur.

    La commande AWS CLI suivante crée une stratégie IAM nommée rds-s3-import-policy avec ces options. Elle accorde un accès à un compartiment nommé your-s3-bucket.

    Note

    Après avoir créé la stratégie, notez son ARN (Amazon Resource Name). Vous en aurez besoin par la suite pour attacher la stratégie à un rôle IAM.

    Exemple

    Pour Linux, OS X ou Unix :

    aws iam create-policy \ --policy-name rds-s3-import-policy \ --policy-document '{ "Version": "2012-10-17", "Statement": [ { "Sid": "s3import", "Action": [ "s3:GetObject", "s3:ListBucket" ], "Effect": "Allow", "Resource": [ "arn:aws:s3:::your-s3-bucket", "arn:aws:s3:::your-s3-bucket/*" ] } ] }'

    Pour Windows :

    aws iam create-policy ^ --policy-name rds-s3-import-policy ^ --policy-document '{ "Version": "2012-10-17", "Statement": [ { "Sid": "s3import", "Action": [ "s3:GetObject", "s3:ListBucket" ], "Effect": "Allow", "Resource": [ "arn:aws:s3:::your-s3-bucket", "arn:aws:s3:::your-s3-bucket/*" ] } ] }'
  2. Créez un rôle IAM. L’objectif est ici de permettre à Amazon RDS d'endosser ce rôle IAM en votre nom pour accéder à vos compartiments Amazon S3. Pour plus d'informations, consultez Création d'un rôle pour déléguer les autorisations à un utilisateur IAM dans le IAM Guide de l'utilisateur.

    L'exemple suivant montre comment utiliser la commande AWS CLI pour créer un rôle nommé rds-s3-import-role.

    Exemple

    Pour Linux, OS X ou Unix :

    aws iam create-role \ --role-name rds-s3-import-role \ --assume-role-policy-document '{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": "rds.amazonaws.com" }, "Action": "sts:AssumeRole" } ] }'

    Pour Windows :

    aws iam create-role ^ --role-name rds-s3-import-role ^ --assume-role-policy-document '{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": "rds.amazonaws.com" }, "Action": "sts:AssumeRole" } ] }'
  3. Attachez la stratégie IAM que vous avez créée au rôle IAM que vous venez de créer.

    La commande AWS CLI suivante attache la stratégie créée précédemment au rôle nommé rds-s3-import-role. Remplacez your-policy-arn par l'ARN de la stratégie que vous avez noté à une étape précédente.

    Exemple

    Pour Linux, OS X ou Unix :

    aws iam attach-role-policy \ --policy-arn your-policy-arn \ --role-name rds-s3-import-role

    Pour Windows :

    aws iam attach-role-policy ^ --policy-arn your-policy-arn ^ --role-name rds-s3-import-role
  4. Ajoutez le rôle IAM à l'instance de base de données. Pour ce faire, utilisez AWS Management Console ou AWS CLI, comme décrit à la suite.

Console

Pour ajouter un rôle IAM à l'instance du de base de données PostgreSQL à l'aide de la console

  1. Connectez-vous à AWS Management Console et ouvrez la console Amazon RDS à l'adresse https://console.aws.amazon.com/rds/.

  2. Choisissez le nom de l'instance du de base de données PostgreSQL pour afficher ses détails.

  3. Dans l'onglet Connectivity & security (Connexion & sécurité) de la section Manage IAM roles (Gérer les rôles IAM), choisissez le rôle à ajouter sous Add IAM roles to this instance (Ajouter les rôles IAM à cette instance).

  4. Sous Feature (Fonctionnalité), choisissez s3Import.

  5. Choisissez Add role (Ajouter un rôle).

AWS CLI

Pour ajouter un rôle IAM à une instance de base de données PostgreSQL à l'aide de l'interface de ligne de commande

  • Utilisez la commande suivante pour ajouter le rôle à l'instance de base de données PostgreSQL nommée my-db-instance. Remplacez your-role-arn par l'ARN de rôle que vous avez noté lors d'une étape précédente. Utilisez s3Import comme valeur de l'option --feature-name.

    Exemple

    Pour Linux, OS X ou Unix :

    aws rds add-role-to-db-instance \ --db-instance-identifier my-db-instance \ --feature-name s3Import \ --role-arn your-role-arn \ --region your-region

    Pour Windows :

    aws rds add-role-to-db-instance ^ --db-instance-identifier my-dbi-nstance ^ --feature-name s3Import ^ --role-arn your-role-arn ^ --region your-region

Utilisation d'informations d'identification de sécurité pour accéder à un compartiment Amazon S3

Si vous préférez, au lieu de donner à accès un compartiment Amazon S3 avec un rôle IAM, vous pouvez utiliser des informations d'identification de sécurité. Pour cela, utilisez le paramètre credentials dans l'appel de fonction aws_s3.table_import_from_s3.

Le paramètre credentials est une structure de type aws_commons._aws_credentials_1, qui contient des informations d'identification AWS. Utilisez la fonction aws_commons.create_aws_credentials pour définir la clé d'accès et la clé secrète dans une structure aws_commons._aws_credentials_1, comme indiqué ci-après.

psql=> SELECT aws_commons.create_aws_credentials( 'sample_access_key', 'sample_secret_key', '') AS creds \gset

Après avoir créé la structure aws_commons._aws_credentials_1 , utilisez la fonction aws_s3.table_import_from_s3 avec le paramètre credentials pour importer les données, comme indiqué ci-après.

psql=> SELECT aws_s3.table_import_from_s3( 't', '', '(format csv)', :'s3_uri', :'creds' );

Vous pouvez également inclure l'appel de fonction aws_commons.create_aws_credentials en ligne au sein de l'appel de fonction aws_s3.table_import_from_s3.

psql=> SELECT aws_s3.table_import_from_s3( 't', '', '(format csv)', :'s3_uri', aws_commons.create_aws_credentials('sample_access_key', 'sample_secret_key', '') );

Résolution des problèmes d'accès à Amazon S3

Si vous rencontrez des problèmes de connexion lors de la tentative d'importation de données de fichier Amazon S3, reportez-vous aux recommandations suivantes :

Utilisation de la fonction aws_s3.table_import_from_s3 pour importer des données Amazon S3

Importez vos données Amazon S3 en appelant la fonction aws_s3.table_import_from_s3.

Note

Les exemples suivants utilisent la méthode du rôle IAM pour donner accès au compartiment Amazon S3. Il n'y a donc pas de paramètres d'informations d'identification dans les appels de fonction aws_s3.table_import_from_s3.

Voici un exemple PostgreSQL type qui utilise psql.

psql=> SELECT aws_s3.table_import_from_s3( 't1', '', '(format csv)', :'s3_uri' );

Les paramètres sont les suivants :

  • t1 – Nom de la table de dans l'instance de base de données PostgreSQL dans laquelle copier les données.

  • '' – Liste facultative des colonnes de la table de base de données. Vous pouvez utiliser ce paramètre pour indiquer quelles colonnes des données S3 sont copiées dans quelles colonnes de table. Si aucune colonne n'est spécifiée, toutes les colonnes sont copiées dans la table. Pour obtenir un exemple d'utilisation d'une liste de colonnes, veuillez consulter Importation d'un fichier Amazon S3 qui utilise un délimiteur personnalisé.

  • (format csv) – Arguments PostgreSQL COPY. Le processus de copie utilise les arguments et le format de la commande PostgreSQL COPY. Dans l'exemple précédent, la commande COPY utilise le format de fichier CSV (valeurs séparées par des virgules) pour copier les données.

  • s3_uri – Structure contenant les informations d'identification du fichier Amazon S3. Pour obtenir un exemple d'utilisation de la fonction aws_commons.create_s3_uri pour créer une structure s3_uri, consultez Présentation de l'importation de données Amazon S3.

Pour obtenir des informations de référence complètes sur cette fonction, consultez aws_s3.table_import_from_s3.

Les exemples suivants montrent comment spécifier différents types de fichiers lors de l'importation de données Amazon S3.

Importation d'un fichier Amazon S3 qui utilise un délimiteur personnalisé

L'exemple suivant montre comment importer un fichier qui utilise un délimiteur personnalisé. Il montre également comment définir l'emplacement de destination des données dans la table de base de données à l'aide du paramètre column_list de la fonction aws_s3.table_import_from_s3.

Pour cet exemple, supposons que les informations suivantes sont organisées en colonnes délimitées par une barre verticale dans le fichier Amazon S3.

1|foo1|bar1|elephant1 2|foo2|bar2|elephant2 3|foo3|bar3|elephant3 4|foo4|bar4|elephant4 ...

Pour importer un fichier qui utilise un délimiteur personnalisé

  1. Créez une table dans la base de données pour les données importées.

    psql=> CREATE TABLE test (a text, b text, c text, d text, e text); CREATE TABLE
  2. Utilisez le format suivant de la fonction aws_s3.table_import_from_s3 pour importer des données à partir du fichier Amazon S3.

    Vous pouvez inclure l'appel de fonction aws_commons.create_s3_uri en ligne au sein de l'appel de fonction aws_s3.table_import_from_s3 pour spécifier le fichier.

    psql=> SELECT aws_s3.table_import_from_s3( 'test', 'a,b,d,e', 'DELIMITER ''|''', aws_commons.create_s3_uri('sampleBucket', 'pipeDelimitedSampleFile', 'us-east-2') );

Les données se retrouvent désormais dans la table dans les colonnes suivantes.

psql=> SELECT * FROM test; a | b | c | d | e ---+------+---+---+------+----------- 1 | foo1 | | bar1 | elephant1 2 | foo2 | | bar2 | elephant2 3 | foo3 | | bar3 | elephant3 4 | foo4 | | bar4 | elephant4

Importation d'un fichier compressé Amazon S3 (gzip)

L'exemple suivant montre comment importer un fichier compressé avec gzip à partir d'Amazon S3.

Vérifiez que le fichier contient les métadonnées Amazon S3 suivantes :

  • Clé : Content-Encoding

  • Valeur : gzip

Pour de plus amples informations sur l'ajout de ces valeurs aux métadonnées Amazon S3, veuillez consulter Comment ajouter des métadonnées à un objet S3 ? dans le manuel Amazon Simple Storage Service Guide de l'utilisateur de la console.

Importez le fichier gzip dans l'instance de base de données RDS for PostgreSQL de votre comme décrit ci-après.

psql=> CREATE TABLE test_gzip(id int, a text, b text, c text, d text); CREATE TABLE psql=> SELECT aws_s3.table_import_from_s3( 'test_gzip', '', '(format csv)', 'myS3Bucket', 'test-data.gz', 'us-east-2' );

Importation d'un fichier codé Amazon S3

L'exemple suivant montre comment importer un fichier codé en Windows-1252 à partir d'Amazon S3.

psql=> SELECT aws_s3.table_import_from_s3( 'test_table', '', 'encoding ''WIN1252''', aws_commons.create_s3_uri('sampleBucket', 'SampleFile', 'us-east-2') );

Guide de référence des fonctions

aws_s3.table_import_from_s3

Importe les données Amazon S3 vers une table Amazon RDS. L'extension aws_s3 fournit la fonction aws_s3.table_import_from_s3.

Les trois paramètres obligatoires sont table_name, column_list et options. Ils identifient la table de base de données et spécifient la façon dont les données sont copiées dans la table.

Vous pouvez également utiliser les paramètres suivants :

  • Le paramètre s3_info spécifie le fichier Amazon S3 à importer. Lorsque vous utilisez ce paramètre, l'accès à Amazon S3 est fourni par un rôle IAM pour l'instance du de base de données PostgreSQL.

    aws_s3.table_import_from_s3 ( table_name text, column_list text, options text, s3_info aws_commons._s3_uri_1 )
  • Le paramètre credentials spécifie les informations d'identification permettant d'accéder à Amazon S3. Lorsque vous utilisez ce paramètre, vous n'utilisez pas de rôle IAM.

    aws_s3.table_import_from_s3 ( table_name text, column_list text, options text, s3_info aws_commons._s3_uri_1, credentials aws_commons._aws_credentials_1 )

Les paramètres aws_s3.table_import_from_s3 sont décrits dans le tableau suivant.

Paramètre Description
table_name Chaîne de texte obligatoire contenant le nom de la table de base de données PostgreSQL dans laquelle importer les données.
column_list

Chaîne de texte obligatoire contenant la liste facultative des colonnes de la table de base de données PostgreSQL dans lesquelles copier les données. Si la chaîne est vide, toutes les colonnes de la table sont utilisées. Pour obtenir un exemple, veuillez consulter Importation d'un fichier Amazon S3 qui utilise un délimiteur personnalisé.

options

Chaîne de texte obligatoire contenant les arguments de la commande COPY de PostgreSQL. Ces arguments spécifient la façon dont les données sont copiées dans la table PostgreSQL. Pour de plus amples informations, veuillez consulter la documentation sur la commande COPY de PostgreSQL.

s3_info

Type composite aws_commons._s3_uri_1 contenant les informations suivantes sur l'objet S3 :

  • bucket – Nom du compartiment Amazon S3 contenant le fichier.

  • file_path – Chemin d'accès Amazon S3 au fichier.

  • region – Région AWS dans laquelle se trouve le fichier. Pour obtenir la liste des noms de régions AWS et les valeurs associées, consultez régions et zones de disponibilité.

Pour créer une structure composite aws_commons._s3_uri_1, consultez aws_commons.create_s3_uri.

credentials

Type composite aws_commons._aws_credentials_1 contenant les informations d'identification suivantes à utiliser pour l'opération d'importation :

  • Clé d'accès

  • Clé secrète

  • Jeton de session

Pour créer une structure composite aws_commons._aws_credentials_1, veuillez consulter aws_commons.create_aws_credentials.

Autres paramètres

Pour faciliter le test, vous pouvez utiliser un ensemble étendu de paramètres au lieu des paramètres s3_info et credentials. Plusieurs variations de syntaxe supplémentaires pour la fonction aws_s3.table_import_from_s3 sont fournies ci-dessous.

  • Au lieu d'utiliser le paramètre s3_info pour identifier un fichier Amazon S3, utilisez la combinaison des paramètres bucket, file_path et region. Sous cette forme, l'accès à Amazon S3 est fourni par un rôle IAM sur l'instance de base de données PostgreSQL.

    aws_s3.table_import_from_s3 ( table_name text, column_list text, options text, bucket text, file_path text, region text )
  • Au lieu d'utiliser le paramètre credentials pour spécifier l'accès à Amazon S3, utilisez la combinaison des paramètres access_key, session_key et session_token.

    aws_s3.table_import_from_s3 ( table_name text, column_list text, options text, bucket text, file_path text, region text, access_key text, secret_key text, session_token text )

Le tableau ci-dessous contient la description de ces autres paramètres.

Paramètre Description
bucket

Chaîne de texte comportant le nom du compartiment Amazon S3 qui contient le fichier.

file_path

Chaîne de texte contenant le chemin d'accès Amazon S3 au fichier.

region Chaîne de texte contenant la région AWS dans laquelle se trouve le fichier. Pour obtenir la liste des noms de régions AWS et les valeurs associées, consultez régions et zones de disponibilité
access_key Chaîne de texte contenant la clé d'accès à utiliser pour l'opération d'importation. La valeur par défaut est NULL.
secret_key Chaîne de texte contenant la clé secrète à utiliser pour l'opération d'importation. La valeur par défaut est NULL.
session_token (Facultatif) Chaîne de texte contenant la clé de session à utiliser pour l'opération d'importation. La valeur par défaut est NULL.

aws_commons.create_s3_uri

Crée une structure aws_commons._s3_uri_1 pour contenir les informations relatives au fichier Amazon S3. Vous utilisez les résultats de la fonction aws_commons.create_s3_uri dans le paramètre s3_info de la fonction aws_s3.table_import_from_s3. La syntaxe de la fonction est la suivante.

aws_commons.create_s3_uri( bucket text, file_path text, region text )

Les paramètres de la fonction aws_commons.create_s3_uri sont décrits dans le tableau suivant.

Paramètre Description
bucket

Chaîne de texte obligatoire contenant le nom du compartiment Amazon S3 pour le fichier.

file_path

Chaîne de texte obligatoire contenant le chemin d'accès Amazon S3 au fichier.

region

Chaîne de texte obligatoire contenant la région AWS dans laquelle se trouve le fichier. Pour obtenir la liste des noms de régions AWS et les valeurs associées, consultez régions et zones de disponibilité.

aws_commons.create_aws_credentials

Définit une clé d'accès et une clé secrète dans une structure aws_commons._aws_credentials_1. Utilisez les résultats de la fonction aws_commons.create_aws_credentials dans le paramètre credentials de la fonction aws_s3.table_import_from_s3. La syntaxe de la fonction est la suivante.

aws_commons.create_aws_credentials( access_key text, secret_key text, session_token text )

Les paramètres de la fonction aws_commons.create_aws_credentials sont décrits dans le tableau suivant.

Paramètre Description
access_key

Chaîne de texte obligatoire contenant la clé d'accès à utiliser pour l'importation d'un fichier Amazon S3. La valeur par défaut est NULL.

secret_key Chaîne de texte obligatoire contenant la clé secrète à utiliser pour l'importation d'un fichier Amazon S3. La valeur par défaut est NULL.
session_token Chaîne de texte facultative contenant le jeton de session à utiliser pour l'importation d'un fichier Amazon S3. La valeur par défaut est NULL. Notez que si vous saisissez le paramètre session_token facultatif, vous pouvez utiliser les informations d'identification temporaires.

Transport de bases de données PostgreSQL entre des instances de base de données

Les bases de données transportables PostgreSQL pour Amazon RDS vous permettent de transporter une base de données PostgreSQL entre deux instances de base de données. Cette méthode permet de migrer très rapidement des bases de données volumineuses entre des instances de base de données distinctes. Pour que vous puissiez transporter des bases de données à l'aide de cette méthode, vos instances de base de données doivent toutes les deux exécuter la version majeure de PostgreSQL.

Pour utiliser des bases de données transportables, installez l'extension pg_transport. Cette extension offre un mécanisme physique de transport permettant de déplacer chaque base de données. En transmettant les fichiers de base de données avec un traitement minimal, le mécanisme de transport physique déplace les données bien plus rapidement qu'avec les processus traditionnels de vidage et de chargement, en provoquant une interruption minimale. Les bases de données transportables PostgreSQL utilisent un modèle d'extraction dans lequel l'instance de base de données de destination importe la base de données à partir de l'instance de base de données source.

Note

Les bases de données transportables PostgreSQL sont disponibles dans RDS pour les versions PostgreSQL 10.10 et ultérieures, et 11.5 et ultérieures.

Limites à l'utilisation de bases de données transportables PostgreSQL

Les bases de données transportables présentent les limites suivantes :

  • Réplicas en lecture – Vous ne pouvez pas utiliser les bases de données transportables sur les réplicas en lecture ou les instances parent de réplicas en lecture.

  • Types de colonne non pris en charge – Vous ne pouvez pas utiliser les types de données reg dans les tables de bases de données que vous souhaitez transporter avec cette méthode. Ces types dépendent des ID d'objet de catalogue système (OID), qui varient souvent durant le transport.

  • Espaces de tables – Tous les objets de base de données source doivent se trouver dans l'espace de table par défaut pg_default.

  • Compatibilité – Les instances de base de données source et destination doivent exécuter la même version majeure de PostgreSQL.

    Avant que le transport ne débute, la fonction transport.import_from_server compare les instances de base de données source et de destination pour vérifier la compatibilité des bases de données. Elle vérifie en particulier la compatibilité avec la version majeure de PostgreSQL. La fonction vérifie aussi que l'instance de base de données de destination possède suffisamment d'espace pour recevoir la base de données source. Elle effectue plusieurs autres vérifications pour s'assurer que le transport est possible.

  • Extensions – La seule extension que vous pouvez installer sur l'instance de base de données source durant le transport est pg_transport.

  • Rôles et ACL – Les droits d'accès et les informations de propriété de la base de données source ne sont pas transportés vers la base de données de destination. Tous les objets de base de données sont créés par l'utilisateur de destination locale du transport et sont sa propriété.

  • Transports simultanés – Vous pouvez effectuer jusqu'à 32 transports en même temps sur une instance de base de données, en incluant les importations et les exportations. Pour définit les processus de travail utilisés pour chaque transport, utilisez les paramètres pg_transport.work_mem et pg_transport.num_workers. Pour pouvoir effectuer des transports simultanés, il peut s'avérer nécessaire d'augmenter un peu la valeur du paramètre max_worker_processes. Pour de plus amples informations, veuillez consulter Paramètre de configuration de l'extension pg_transport.

Configuration du transport de bases de données PostgreSQL

Pour préparer le transport d'une base de données PostgreSQL d'une instance de base de données vers une autre, procédez comme indiqué ci-après.

Pour configurer le transport d'une base de données PostgreSQL

  1. Veillez à ce que le groupe de sécurité de l'instance de base de données source autorise le trafic entrant depuis l'instance de base de données de destination. Cela est nécessaire, car l'instance de base de données de destination débute le transport de base de données par un appel d'importation en direction de l'instance de base de données source. Pour de plus amples informations sur l'utilisation des groupes de sécurité, veuillez consulter Contrôle d'accès par groupe de sécurité.

  2. Ajoutez pour les instances de base de données source et de destination la valeur pg_transport au paramètre shared_preload_libraries dans chaque groupe de paramètres. Le paramètre shared_preload_libraries est statique et nécessite donc un redémarrage de la base de données pour prendre effet. Pour de plus amples informations sur les groupes de paramètres, veuillez consulter Utilisation de groupes de paramètres de base de données.

  3. Installez pour les instances de base de données source et de destination l'extension PostgreSQL pg_transport requise.

    Pour cela, démarrez psql en tant qu'utilisateur ayant le rôle rds_superuser pour chaque instance de base de données, puis exécutez la commande suivante :

    psql=> CREATE EXTENSION pg_transport;

Transport d'une base de données PostgreSQL à l'aide de la fonction transport.import_from_server

Une fois terminé le processus décrit dans Configuration du transport de bases de données PostgreSQL, vous pouvez démarrer le transport. Pour cela, exécutez la fonction transport.import_from_server sur l'instance de base de données de destination.

Note

L'utilisateur de destination du transport et l'utilisateur source de la connexion doivent tous deux être membres du rôle rds_superuser.

L'instance de base de données de destination ne doit pas déjà contenir une base de données portant le même nom que la base de données source à transporter. Si tel est le cas, le transport échoue.

L'illustration suivante présente un exemple de transport.

SELECT transport.import_from_server( 'source-db-instance-endpoint', source-db-instance-port, 'source-db-instance-user', 'source-user-password', 'source-database-name', 'destination-user-password', false);

Cette fonction nécessite que vous fournissiez les mots de passe utilisateur de la base de données. Nous vous recommandons donc de modifier les mots de passe des rôles utilisateur que vous avez utilisés une fois le transport terminé. Vous pouvez aussi utiliser des variables de liaison SQL pour créer des rôles utilisateur temporaires. Utilisez ces rôles temporaires pour le transport, puis supprimez-les une fois que vous n'en avez plus besoin.

Pour de plus amples informations sur la fonction transport.import_from_server et ses paramètres, veuillez consulter Fonction transport.import_from_server.

Que se passe-t-il durant le transport d'une base de données ?

La fonction transport.import_from_server crée la base de données en transit sur l'instance de base de données de destination. La base de données en transit est inaccessible sur l'instance de base de données de destination pendant toute la durée du transport.

Lorsque le transport commence, toutes les sessions en cours sur la base de données source cessent. Les bases de données autres que la base de données source sur l'instance de base de données source ne sont pas affectées par le transport.

La base de données source est placée dans un mode lecture seule spécial. Lorsqu'elle est dans ce mode, vous pouvez vous connecter à la base de données source et exécuter des requêtes de lecture seule. Par contre, les requêtes d'écriture et certains autres types de commandes sont bloqués. Seule la base de données source qui fait l'objet du transport est affectée par ces restrictions.

Durant le transport, vous ne pouvez pas restaurer l'instance de base de données de destination à un instant dans le passé. En effet, le transport n'est pas transactionnel et n'utilise pas le journal write-ahead (WAL) PostgreSQL pour enregistrer les modifications. Si les sauvegardes automatiques sont activées pour l'instance de base de données de destination, une sauvegarde est automatiquement effectuée une fois le transport terminé. Des restaurations à un instant dans le passé sont disponibles pour les périodes postérieures à la sauvegarde.

En cas d'échec du transport, l'extension pg_transport tente d'annuler toutes les modifications apportées aux instances de base de données source et de destination. Cela inclut la suppression de la base de données partiellement transportée sur la destination. Selon le type de défaillance, la base de données source peut continuer à rejeter les requêtes d'écriture. Si tel est le cas, utilisez la commande suivante pour autoriser les requêtes d'écriture.

ALTER DATABASE my-database SET default_transaction_read_only = false;

Fonction transport.import_from_server

La fonction transport.import_from_server transporte une base de données PostgreSQL en l'important d'une instance de base de données source vers une instance de base de données de destination. Elle effectue cette opération en utilisant un mécanisme de transport physique de connexion de base de données.

Syntaxe

transport.import_from_server( host text, port int, username text, password text, database text, local_password text, dry_run bool )

Valeur renvoyée

Aucun.

Paramètres

Le tableau ci-dessous contient les descriptions des paramètres de la fonction transport.import_from_server.

Paramètre Description
host

Point de terminaison de l'instance de base de données source.

port Entier représentant le port de l'instance de base de données source.

Les instances de base de données PostgreSQL utilisent souvent le port 5432.

username

Utilisateur de l'instance de base de données source. Cet utilisateur doit être membre du rôle rds_superuser.

password

Mot de passe utilisateur de l'instance de base de données source.

database

Nom de la base de données à transporter à partir de l'instance de base de données source.

local_password

Mot de passe local de l'utilisateur actuel pour l'instance de base de données de destination. Cet utilisateur doit être membre du rôle rds_superuser.

dry_run

Valeur booléenne facultative spécifiant si un essai est nécessaire. La valeur par défaut est false, ce qui signifie que le transport est effectué.

Pour vérifier la compatibilité entre les instances de base de données source et de destination sans effectuer le transport réel, définissez dry_run sur true.

Exemple

Pour obtenir un exemple, consultez Transport d'une base de données PostgreSQL à l'aide de la fonction transport.import_from_server.

Paramètre de configuration de l'extension pg_transport

Utilisez les paramètres suivants pour configurer le comportement de l'extension pg_transport.

SET pg_transport.num_workers = integer; SET pg_transport.work_mem = kilobytes; SET pg_transport.timing = Boolean;

Le tableau ci-dessous contient les descriptions de ces paramètres.

Paramètre Description
pg_transport.num_workers

Nombre d'unités de travail à utiliser pour un transport physique. La valeur par défaut est 3. Les valeurs valides vont de 1 à 32. Même les transports volumineux atteignent généralement leur débit maximal avec moins de 8 unités de travail.

Durant le transport, la valeur de pg_transport.num_workers sur l'instance de base de données de destination est utilisée à la fois sur les instances de base de données source et de destination.

Un paramètre associé est le paramètre PostgreSQL max_worker_processes. Le processus de transport crée plusieurs processus de travail en arrière-plan. La valeur du paramètre pg_transport.num_workers peut vous obliger à définir le paramètre max_worker_processes sur une valeur beaucoup plus élevée à la fois sur les instances de base de données source et de destination.

Nous vous recommandons de définir max_worker_processes à la fois sur les instances de base de données source et de destination sur une valeur au moins trois fois supérieure à celle du paramètre pg_transport.num_workers sur l'instance de base de données de destination. Augmentez un peu la valeur pour pouvoir fournir des processus de travail autres que ceux du transport en arrière-plan.

Pour de plus amples informations sur le paramètre max_worker_processes, veuillez consulter la documentation PostgreSQL sur le Comportement asynchrone.

pg_transport.timing

Valeur booléenne qui indique s'il est nécessaire de signaler des informations temporelles durant le transport. La valeur par défaut est true. Les valeurs valides sont true pour signaler des informations temporelles et false pour désactiver le signalement de ce type d'informations.

Nous vous déconseillons de définir ce paramètre sur false. La désactivation de pg_transport.timing réduit considérablement votre capacité de suivi de la progression du transport.

pg_transport.work_mem

Quantité de mémoire maximale à allouer à chaque unité de travail. La valeur par défaut est de 131 072 kilo-octets (Ko). La valeur minimale est de 64 méga-octets (65 536 Ko). Les valeurs valides sont exprimées en kilo-octets (Ko) sous forme d'unités binaires de base 2, où 1 Ko = 1 024 octets.

Le transport peut utiliser moins de mémoire que spécifié dans ce paramètre. Même les transports volumineux atteignent généralement leur débit maximal avec moins de 256 Mo (262 144 Ko) de mémoire par unité de travail.