DataWorks:Source de données PostgreSQL

SHOW SERVER_VERSION;

Lecture et écriture hors ligne

Vous pouvez lire des données à partir de vues.

• La source de données PostgreSQL prend en charge l'authentification par mot de passe, y compris la méthode SCRAM-SHA-256. Si vous modifiez le mot de passe ou la méthode d'authentification dans la base de données PostgreSQL, mettez à jour la configuration de la source de données, testez à nouveau la connexion et exécutez manuellement les tâches associées pour vérifier les modifications.

• PostgreSQL

• Si un nom de table ou de colonne dans PostgreSQL commence par un chiffre, est sensible à la casse ou contient un trait d'union (-), entourez-le de guillemets doubles (""). Sinon, le plug-in PostgreSQL ne pourra ni lire ni écrire de données. Dans les plug-ins PostgreSQL Reader et Writer, les guillemets doubles ("") constituent un mot clé JSON. Par conséquent, échappez les guillemets doubles avec une barre oblique inverse (\). Par exemple, si une table est nommée 123Test, le nom échappé est \"123Test\".

  Remarque

  • Les guillemets doubles ouvrants et fermants doivent tous deux être échappés avec une barre oblique inverse (\).

  • Le mode assistant ne prend pas en charge l'échappement. Passez en mode script pour échapper les caractères.

  Le code suivant illustre un exemple d'échappement des caractères en mode script.

  "parameter": {
      "datasource": "abc",
      "column": [
          "id",
          "\"123Test\"", //Add escape characters
  ],
  "where": "",
  "splitPk": "id",
  "table": "public.wpw_test"
  },

• La mise à jour des données dans une source de données PostgreSQL basée sur un index unique n'est pas prise en charge. Pour mettre à jour des données, écrivez-les d'abord dans une table temporaire, puis utilisez l'opération RENAME.

Lecture en temps réel

Les limites suivantes s'appliquent aux tâches de synchronisation en temps réel dans Data Integration :

• Data Integration offre une prise en charge spéciale pour ADD COLUMN :

  • Contrainte : Vous ne pouvez pas combiner une opération ADD COLUMN avec d'autres opérations ADD COLUMN, DROP COLUMN ou d'autres instructions DDL dans une seule transaction.

    Important

    Si vous utilisez ADD COLUMN avec d'autres opérations, telles que DROP COLUMN、RENAME COLUMN ou d'autres opérations ALTER COLUMN, la tâche de synchronisation des données risque de ne pas s'exécuter correctement.

  • Limitation : Les opérations DDL autres que ADD COLUMN ne sont pas reconnues.

• ALTER TABLE/CREATE TABLE n'est pas pris en charge.

• La réplication des tables TEMPORARY, UNLOGGED et Hyper n'est pas prise en charge. PostgreSQL ne fournit aucun mécanisme permettant de s'abonner à l'analyse des journaux WAL pour ces types de tables.

• La réplication des Sequences (serial/bigserial/identity) n'est pas prise en charge.

• L'opération TRUNCATE n'est pas prise en charge.

• La réplication d'objets volumineux (Bytea) n'est pas prise en charge.

• La réplication des vues, des vues matérialisées et des tables étrangères n'est pas prise en charge.

• Lorsque PostgreSQL est utilisé comme source pour la synchronisation en temps réel sur une seule table ou sur toute la base de données, seules les tables pour lesquelles le compte dispose des autorisations de propriétaire peuvent être synchronisées.

Préparation 1 : Créer un compte et configurer les autorisations

Prévoyez un compte de connexion à la base de données pour les opérations ultérieures. Ce compte doit disposer des autorisations REPLICATION、 LOGIN sur la base de données.

La synchronisation en temps réel prend uniquement en charge le mécanisme de réplication logique. La réplication logique utilise un modèle de publication et d'abonnement dans lequel un ou plusieurs abonnés s'abonnent à une ou plusieurs publications sur un nœud éditeur. Les abonnés extraient les données des publications auxquelles ils sont abonnés.

La réplication logique d'une table commence généralement par la prise d'un instantané des données sur la base de données éditrice et leur copie vers l'abonné. Une fois l'instantané terminé, les modifications apportées à l'éditeur sont envoyées à l'abonné en temps réel.

1. Créez un compte.

   Pour plus d'informations, consultez Créer un compte.

2. Configurez les autorisations.

   Vérifiez si le compte dispose de l'autorisation replication.

   select userepl from pg_user where usename='xxx'

   Le résultat attendu est True. Si le résultat est False, le compte ne dispose pas de l'autorisation. Exécutez l'instruction suivante pour accorder l'autorisation.

   ALTER USER <user> REPLICATION;

Préparation 2 : Vérifier si une base de données de secours est prise en charge

SELECT pg_is_in_recovery()

Seule la base de données principale est prise en charge. Le résultat attendu est False. Si le résultat est True, la base de données est une base de données de secours. La synchronisation en temps réel ne prend pas en charge les bases de données de secours. Modifiez la configuration de la source de données pour qu'elle pointe vers la base de données principale. Pour plus d'informations, consultez Configurer la source de données.

Préparation 3 : Vérifier si wal_level est défini sur logical

show wal_level

wal_level spécifie le niveau de wal_log. Le résultat attendu est logical. Dans le cas contraire, le mécanisme de réplication logique n'est pas pris en charge.

Préparation 4 : Vérifier si un processus wal_sender peut être démarré

-- Query max_wal_senders show max_wal_senders; -- Query the number of pg_stat_replication entries select count(*) from pg_stat_replication

Si max_wal_senders n'est pas vide et que sa valeur est supérieure au nombre d'entrées max_wal_senders dans pg_stat_replication, des processus wal_sender inactifs sont disponibles. La base de données PostgreSQL démarre des processus wal_sender pour que le programme de synchronisation des données envoie les journaux aux abonnés.

Pour chaque table à synchroniser, exécutez manuellement l'instruction ALTER TABLE [tableName] REPLICA IDENTITY FULL afin d'accorder les autorisations requises. Dans le cas contraire, la tâche de synchronisation en temps réel échouera.

Après le démarrage d'une tâche de synchronisation en temps réel PostgreSQL, des slots et des publications sont automatiquement créés dans la base de données. Le format du nom du slot est di_slot_ + ID de la tâche de synchronisation et le format du nom de la publication est di_pub_ + ID de la solution. Après l'arrêt ou le retrait du déploiement de la tâche de synchronisation en temps réel, supprimez manuellement les slots et les publications. Dans le cas contraire, les fichiers WAL PostgreSQL risquent de croître continuellement. Recherchez l'ID de la tâche de synchronisation dans la liste des tâches de DataWorks Data Integration, trouvez la tâche correspondante et vérifiez son statut ainsi que ses configurations de planification pour confirmer si la tâche a été arrêtée ou retirée du déploiement.

Guide de configuration des tâches de synchronisation par lots sur une seule table

• Pour plus d'informations sur la procédure, consultez Configurer une tâche de synchronisation par lots en mode assistant et Configurer une tâche de synchronisation par lots en mode script.

• Pour tous les paramètres et les démonstrations de scripts relatifs à la configuration en mode script, consultez Annexe 1 : Démonstration de script et description des paramètres ci-dessous.

Guide de configuration des tâches de synchronisation pour la lecture par lots sur toute la base de données et la lecture en temps réel sur toute la base de données

Pour plus d'informations sur la procédure, consultez Configurer une tâche de synchronisation par lots sur toute la base de données et Configurer une tâche de synchronisation en temps réel sur toute la base de données.

Configurer une tâche de synchronisation par lots à l'aide de l'éditeur de code

Si vous souhaitez configurer une tâche de synchronisation par lots à l'aide de l'éditeur de code, configurez les paramètres associés dans le script conformément aux exigences de format de script unifié. Pour plus d'informations, consultez Configuration en mode script. Les informations suivantes décrivent les paramètres que vous devez configurer pour les sources de données lorsque vous configurez une tâche de synchronisation par lots à l'aide de l'éditeur de code.

Démonstration de script Reader

Pour configurer un job qui extrait des données d'une base de données PostgreSQL, utilisez le mode script. Pour plus d'informations, consultez Configurer une tâche de synchronisation par lots en mode script.

{ "type":"job", "version":"2.0",// The version number. "steps":[ { "stepType":"postgresql",// The plugin name. "parameter":{ "datasource":"",// The data source. "column":[// The columns. "col1", "col2" ], "where":"",// The filter condition. "splitPk":"",// The column used as the split key for data sharding. Data synchronization starts concurrent tasks for data synchronization. "table":""// The table name. }, "name":"Reader", "category":"reader" }, { "stepType":"stream", "parameter":{}, "name":"Writer", "category":"writer" } ], "setting":{ "errorLimit":{ "record":"0"// The error count. }, "speed":{ "throttle":true, // A value of false for throttle indicates that throttling is disabled and the mbps parameter does not take effect. A value of true indicates that throttling is enabled. "concurrent":1, // The concurrency of the job. "mbps":"12"// The throttling rate. 1 mbps = 1 MB/s. } }, "order":{ "hops":[ { "from":"Reader", "to":"Writer" } ] } }

Paramètres du script Reader

Démonstration de script Writer

Voici un exemple de configuration de script. Pour plus d'informations, consultez les descriptions des paramètres ci-dessus.

{ "type":"job", "version":"2.0",// The version number. "steps":[ { "stepType":"stream", "parameter":{}, "name":"Reader", "category":"reader" }, { "stepType":"postgresql",// The plugin name. "parameter":{ "datasource":"",// The data source. "column":[// The columns. "col1", "col2" ], "table":"",// The table name. "preSql":[],// The SQL statements executed before the data synchronization task. "postSql":[],// The SQL statements executed after the data synchronization task. }, "name":"Writer", "category":"writer" } ], "setting":{ "errorLimit":{ "record":"0"// The error count. }, "speed":{ "throttle":true,// A value of false for throttle indicates that throttling is disabled and the mbps parameter does not take effect. A value of true indicates that throttling is enabled. "concurrent":1, // The concurrency of the job. "mbps":"12"// The throttling rate. 1 mbps = 1 MB/s. } }, "order":{ "hops":[ { "from":"Reader", "to":"Writer" } ] } }

Paramètres du script Writer

{
    "job":
    {
        "content":
        [{
            "reader": {...},
            "writer":
            {
                "parameter":
                {
                    "column":
                    [
                        // Destination table field list
                        "bigint_arr",
                        "double_arr",
                        "text_arr",
                        "jsonb_obj",
                        "json_obj"
                    ],
                    "pgType":
                    {
                        // Special type settings. The key is the field name of the destination table, and the value is the field type.
                        "bigint_arr": "bigint[]",
                        "double_arr": "double[]",
                        "text_arr": "text[]",
                        "jsonb_obj": "jsonb",
                        "json_obj": "json"
                    }

                }
            }
        }]
    }
}

Description des fichiers d'authentification SSL PostgreSQL

Lorsque vous créez ou modifiez une connexion de source de données PostgreSQL dans DataWorks, vous pouvez configurer l'authentification SSL. Les paramètres de configuration de l'authentification SSL sont décrits ci-dessous.

• Lorsque la configuration ACL est définie sur prefer, le contenu du client n'est pas imposé pour la vérification.

  • Si aucun fichier n'est configuré pour l'authentification SSL, une connexion régulière est utilisée.

  • Si des fichiers d'authentification sont ajoutés pour l'authentification SSL, reportez-vous aux descriptions correspondantes dans le tableau ci-dessus.

• Lorsque la configuration ACL est définie sur verify-ca, configurez le Fichier de certificat Keystore, le Fichier de clé privée et le Mot de passe de la clé privée pour créer la source de données.

Obtenir les fichiers d'authentification SSL PostgreSQL

Cette section utilise une instance ApsaraDB RDS for PostgreSQL comme exemple pour générer des certificats d'authentification SSL.

1. Obtenez le Fichier de certificat Truststore.

   Pour plus d'informations sur l'obtention du Fichier de certificat Truststore, consultez Configurer une connexion chiffrée SSL.

   1. Accédez à la liste des instances RDS, cliquez sur l'instance RDS dans la région correspondante, puis cliquez sur l'ID de l'instance cible pour accéder à la page des détails de l'instance.

   2. Sélectionnez la chaîne de connexion que vous souhaitez protéger.

      1. Dans le volet de navigation de gauche, cliquez sur Data Security.

      2. Cliquez sur l'onglet SSL.

      3. Cliquez sur Use Cloud Certificate.

      4. Dans la ligne Configure Database Certificate (Prevent Database Spoofing), cliquez sur Modify.

      5. Dans la boîte de dialogue Select Connection String to Protect, sélectionnez la chaîne de connexion cible (par exemple, l'adresse du réseau interne).

      6. Cliquez sur OK.

   Remarque

   • Si le point de terminaison public est activé, les points de terminaison internes et publics sont affichés. Un certificat cloud ne peut protéger qu'un seul point de terminaison. Étant donné que le point de terminaison interne est relativement plus sécurisé, nous vous recommandons de protéger le point de terminaison public. Pour plus d'informations sur la façon de visualiser les points de terminaison internes et publics, consultez Afficher les points de terminaison internes et publics.

   • Si vous devez protéger à la fois les points de terminaison internes et publics, consultez Configurer un certificat personnalisé.

   • Après avoir configuré le certificat cloud, le Running Status de l'instance passe à Modifying SSL. Cet état dure environ trois minutes. Attendez que l'état d'exécution passe à Running avant de poursuivre.

   c. Cliquez sur Download CA Certificate pour obtenir le fichier Truststore certificate.

   Le package de certificats CA téléchargé contient trois fichiers. Lorsque vous configurez la source de données PostgreSQL dans DataWorks, téléchargez le fichier avec l'extension .pem ou le fichier avec l'extension .p7b dans l'élément de configuration Truststore certificate file.

2. Obtenez et configurez le Fichier de certificat Keystore, le Fichier de clé privée et le Mot de passe de la clé privée.

   Prérequis : Vous avez terminé la configuration d'une connexion chiffrée SSL ou la configuration d'un certificat personnalisé, et vous disposez de l'outil OpenSSL.

   Remarque

   Si vous utilisez un système Linux, OpenSSL est préinstallé. Si vous utilisez un système Windows, téléchargez et installez le package logiciel OpenSSL.

   Pour plus d'informations sur l'obtention et la configuration du Fichier de certificat Keystore, du Fichier de clé privée et du Mot de passe de la clé privée, consultez Configurer un certificat client.

   1. Utilisez l'outil OpenSSL sur un système Linux ou installez le logiciel OpenSSL sur un système Windows pour générer un certificat autosigné (ca1.crt) et sa clé privée (ca1.key).

      openssl req -new -x509 -days 3650 -nodes -out ca1.crt -keyout ca1.key -subj "/CN=root-ca1"

   2. Générez un fichier de demande de signature de certificat client (client.csr) et une clé privée de certificat client (client.key).

      openssl req -new -nodes -text -out client.csr -keyout client.key -subj "/CN=<client_username>"

      Dans cette commande, la valeur CN après le paramètre -subj doit être définie sur le nom d'utilisateur utilisé par le client pour accéder à la base de données.

   3. Générez le certificat client (client.crt).

      openssl x509 -req -in client.csr -text -days 365  -CA ca1.crt -CAkey ca1.key -CAcreateserial  -out client.crt

   4. Si votre serveur RDS PostgreSQL requiert la vérification du certificat CA client, ouvrez le fichier de certificat autosigné client généré ca1.crt, copiez le contenu du certificat et collez-le dans la boîte de dialogue Client CA Certificate Public Key Content en tant que certificat CA client.

      Dans le panneau Configure Client CA Certificate, cliquez sur Enable Client CA Certificate. Dans la boîte de dialogue qui s'affiche, collez le contenu de la clé publique du certificat CA généré (ca1.crt) dans la zone de texte Public Key, puis cliquez sur OK. Après la modification du certificat CA client, l'instance redémarre automatiquement.

   5. Après avoir configuré le certificat CA client côté RDS, lorsque vous configurez la source de données PostgreSQL dans DataWorks, convertissez la clé privée du certificat client client.key en un fichier client.pk8 et téléchargez le fichier client.pk8 dans l'élément de configuration Private key file de la configuration de la source de données PostgreSQL DataWorks.

      cp client.key client.pk8

   6. Configurez le mot de passe de la clé privée.

      openssl pkcs8 -topk8 -inform PEM -in client.key -outform der -out client.pk8 -v1 PBE-MD5-DES

      Remarque

      Lorsque vous exécutez la commande pour configurer le mot de passe de la clé privée, vous devez saisir un mot de passe. Si vous définissez un mot de passe, utilisez le même mot de passe pour le mot de passe de la clé privée dans la configuration de la source de données PostgreSQL DataWorks.

Configurer les fichiers d'authentification SSL PostgreSQL

Lorsque vous téléchargez les fichiers de certificat obtenus dans les éléments de configuration PostgreSQL DataWorks, effectuez les opérations suivantes :

• Truststore certificate file : Téléchargez le fichier avec l'extension .pem ou le fichier avec l'extension .p7b obtenu à l'étape Obtenir le fichier de certificat Truststore.

• Keystore certificate file : Téléchargez le fichier de certificat client client.crt obtenu à l'étape Générer le certificat client.

• Private key file : Téléchargez le fichier client.pk8 converti à partir de la clé privée du certificat client client.key à l'étape Convertir le fichier de clé privée.

• Private key password : Mot de passe configuré à l'étape Configurer le mot de passe de la clé privée.

ACL configuration : Accédez à la liste des instances RDS, cliquez sur l'instance RDS dans la région correspondante, cliquez sur l'ID de l'instance cible pour accéder à la page des détails de l'instance, puis cliquez sur Data Security > ACL Configuration pour modifier le paramètre. Vous pouvez sélectionner différentes méthodes d'authentification SSL. Pour plus d'informations, consultez Configurer ACL.

Les méthodes d'authentification SSL disponibles sont :

• prefer : Chiffre la liaison de données.

• verify-ca : Chiffre la liaison de données et vérifie l'authenticité du certificat client.

• verify-full : Chiffre la liaison de données, vérifie l'authenticité du certificat client et vérifie que le CN du certificat correspond au nom d'utilisateur de la base de données.

• cert : Utilise un certificat au lieu d'un mot de passe pour se connecter, chiffre la liaison de données, vérifie l'authenticité du certificat client et vérifie que le CN du certificat correspond au nom d'utilisateur de la base de données.

• Si la méthode d'authentification ACL est définie sur prefer, le serveur PostgreSQL n'impose pas la vérification du certificat client.

• Si la méthode d'authentification ACL est définie sur verify-ca dans RDS PostgreSQL, vous devez télécharger le certificat client correct lors de la configuration de la source de données PostgreSQL dans DataWorks, afin que le serveur puisse vérifier l'authentification du client.