DataWorks:Origen de datos PostgreSQL

SHOW SERVER_VERSION;

Lectura y escritura sin conexión

Lea datos desde vistas.

• El origen de datos PostgreSQL es compatible con la autenticación basada en contraseña, incluido el método SCRAM-SHA-256. Si cambia la contraseña o el método de autenticación en la base de datos PostgreSQL, actualice la configuración del origen de datos, pruebe la conexión nuevamente y ejecute manualmente las tareas relacionadas para comprobar los cambios.

• PostgreSQL

• Si el nombre de una tabla o columna en PostgreSQL comienza con un dígito, distingue entre mayúsculas y minúsculas, o incluye un guion (-), encierre el nombre entre comillas dobles (""). De lo contrario, el plugin de PostgreSQL no podrá leer ni escribir datos. En los plugins PostgreSQL Reader y Writer, las comillas dobles ("") son una palabra clave de JSON. Por lo tanto, debe escapar las comillas dobles con una barra invertida (\). Por ejemplo, si una tabla se llama 123Test, el nombre escapado es \"123Test\".

  Nota

  • Tanto las comillas dobles de apertura como las de cierre deben escaparse con una barra invertida (\).

  • El modo asistente no admite el escape de caracteres. Cambie al modo script para escapar caracteres.

  El siguiente código muestra un ejemplo de cómo escapar caracteres en modo script.

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

• No se admite la actualización de datos en un origen de datos PostgreSQL basada en un índice único. Para actualizar datos, primero escriba los datos en una tabla temporal y luego utilice la operación RENAME.

Lectura en tiempo real

Los siguientes límites aplican a las tareas de sincronización en tiempo real en Data Integration:

• Data Integration ofrece compatibilidad especial para ADD COLUMN:

  • Restricción: No combine una operación ADD COLUMN con otras operaciones ADD COLUMN, DROP COLUMN u otras sentencias DDL en una sola transacción.

    Importante

    Si utiliza ADD COLUMN junto con otras operaciones, como DROP COLUMN、RENAME COLUMN u otras operaciones ALTER COLUMN, es posible que la tarea de sincronización de datos no se ejecute correctamente.

  • Limitación: Las operaciones DDL distintas de ADD COLUMN no se reconocen.

• ALTER TABLE/CREATE TABLE no es compatible.

• No se admite la replicación de tablas TEMPORARY, tablas UNLOGGED ni tablas Hyper. PostgreSQL no proporciona un mecanismo para suscribirse al análisis de registros WAL para estos tipos de tablas.

• No se admite la replicación de Sequences (serial/bigserial/identity).

• La operación TRUNCATE no es compatible.

• No se admite la replicación de objetos grandes (Bytea).

• No se admite la replicación de vistas, vistas materializadas ni tablas externas.

• Cuando PostgreSQL se utiliza como origen para la sincronización en tiempo real de una sola tabla o de toda la base de datos, solo pueden sincronizarse las tablas sobre las cuales la cuenta tiene permisos de propietario.

Preparativo 1: Crear una cuenta y configurar permisos

Planifique una cuenta de inicio de sesión en la base de datos para las operaciones posteriores. Esta cuenta debe tener los permisos REPLICATION、 LOGIN en la base de datos.

La sincronización en tiempo real solo admite el mecanismo de replicación lógica. La replicación lógica utiliza un modelo de publicación y suscripción en el que uno o más suscriptores se suscriben a una o más publicaciones en un nodo publicador. Los suscriptores extraen datos de las publicaciones a las que están suscritos.

La replicación lógica de una tabla generalmente comienza tomando una instantánea de los datos en la base de datos publicadora y copiándolos al suscriptor. Una vez completada la instantánea, los cambios en el publicador se envían al suscriptor en tiempo real.

1. Cree una cuenta.

   Para obtener más información, consulte Create an account.

2. Configure los permisos.

   Compruebe si la cuenta tiene el permiso replication.

   select userepl from pg_user where usename='xxx'

   El resultado esperado es True. Si el resultado es False, la cuenta no tiene el permiso. Ejecute la siguiente sentencia para otorgar el permiso.

   ALTER USER <user> REPLICATION;

Preparativo 2: Comprobar si se admite una base de datos en espera

SELECT pg_is_in_recovery()

Solo se admite la base de datos principal. El resultado esperado es False. Si el resultado es True, la base de datos es una base de datos en espera. La sincronización en tiempo real no admite bases de datos en espera. Modifique la configuración del origen de datos para que apunte a la base de datos principal. Para obtener más información, consulte Configure the data source.

Preparativo 3: Comprobar si wal_level está establecido en logical

show wal_level

wal_level especifica el nivel de wal_log. El resultado esperado es logical. De lo contrario, el mecanismo de replicación lógica no es compatible.

Preparativo 4: Comprobar si puede iniciarse un proceso wal_sender

-- 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 no está vacío y su valor es mayor que el número de entradas max_wal_senders en pg_stat_replication, hay procesos wal_sender inactivos disponibles. La base de datos PostgreSQL inicia procesos wal_sender para que el programa de sincronización de datos envíe registros a los suscriptores.

Para cada tabla que deba sincronizarse, ejecute manualmente la sentencia ALTER TABLE [tableName] REPLICA IDENTITY FULL para otorgar los permisos necesarios. De lo contrario, la tarea de sincronización en tiempo real fallará.

Después de iniciar una tarea de sincronización en tiempo real de PostgreSQL, se crean automáticamente slots y publicaciones en la base de datos. El formato del nombre del slot es di_slot_ + synchronization task ID, y el formato del nombre de la publicación es di_pub_ + Solution ID. Después de detener o retirar la tarea de sincronización en tiempo real, elimine manualmente los slots y las publicaciones. De lo contrario, los archivos WAL de PostgreSQL podrían crecer continuamente. Busque el ID de la tarea de sincronización en la lista de tareas de DataWorks Data Integration, localice la tarea correspondiente y compruebe su estado y configuraciones de programación para confirmar si la tarea se ha detenido o retirado.

Guía de configuración de tareas de sincronización por lotes de tabla única

• Para obtener más información sobre el procedimiento, consulte Configure a batch synchronization task in wizard mode y Configure a batch synchronization task in script mode.

• Para todos los parámetros y demostraciones de scripts para la configuración en modo script, consulte Appendix 1: Script demo and parameter description a continuación.

Guía de configuración de tareas de sincronización de lectura por lotes de base de datos completa y lectura en tiempo real de base de datos completa

Para obtener más información sobre el procedimiento, consulte Configure a full-database batch synchronization task y Configure a full-database real-time synchronization task.

Configurar una tarea de sincronización por lotes mediante el editor de código

Si desea configurar una tarea de sincronización por lotes mediante el editor de código, configure los parámetros relacionados en el script según los requisitos unificados de formato de script. Para obtener más información, consulte Script mode configuration. La siguiente información describe los parámetros que debe configurar para los orígenes de datos cuando configura una tarea de sincronización por lotes mediante el editor de código.

Demostración de script de Reader

Para configurar un trabajo que extraiga datos de una base de datos PostgreSQL, utilice el modo script. Para obtener más información, consulte Configure a batch synchronization task in script mode.

{ "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" } ] } }

Parámetros del script de Reader

Demostración de script de Writer

El siguiente es un ejemplo de configuración de script. Para obtener más información, consulte las descripciones de parámetros anteriores.

{ "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" } ] } }

Parámetros del script de 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"
                    }

                }
            }
        }]
    }
}

Descripción de los archivos de autenticación SSL de PostgreSQL

Al crear o modificar una conexión de origen de datos PostgreSQL en DataWorks, puede configurar la autenticación SSL. A continuación se describen los parámetros de configuración de la autenticación SSL.

• Cuando la configuración de ACL se establece en prefer, no se exige la verificación del contenido del cliente.

  • Si no se configuran archivos para la autenticación SSL, se utiliza una conexión estándar.

  • Si se agregan archivos de autenticación para SSL, consulte las descripciones correspondientes en la tabla anterior.

• Cuando la configuración de ACL se establece en verify-ca, configure el archivo de certificado Keystore, el archivo de clave privada y la contraseña de clave privada para crear el origen de datos.

Obtener archivos de autenticación SSL de PostgreSQL

En esta sección se utiliza una instancia de ApsaraDB RDS for PostgreSQL como ejemplo para generar certificados de autenticación SSL.

1. Obtenga el archivo de certificado Truststore.

   Para obtener más información sobre cómo conseguir el archivo de certificado Truststore, consulte Configure an SSL-encrypted connection.

   1. Ir a la lista de instancias RDS, haga clic en la instancia RDS de la región correspondiente y luego haga clic en el ID de la instancia destino para ir a la página de detalles de la instancia.

   2. Seleccione la cadena de conexión que desea proteger.

      1. En el panel de navegación izquierdo, haga clic en Data Security.

      2. Haga clic en la pestaña SSL.

      3. Haga clic en Use Cloud Certificate.

      4. En la fila Configure Database Certificate (Prevent Database Spoofing), haga clic en Modify.

      5. En el cuadro de diálogo Select Connection String to Protect, seleccione la cadena de conexión destino (por ejemplo, la dirección de red interna).

      6. Haga clic en OK.

   Nota

   • Si el endpoint público está habilitado, se muestran tanto los endpoints internos como los públicos. Un certificado en la nube solo puede proteger un endpoint. Dado que el endpoint interno es relativamente más seguro, le recomendamos proteger el endpoint público. Para obtener más información sobre cómo ver los endpoints internos y públicos, consulte View internal and public endpoints.

   • Si necesita proteger tanto los endpoints internos como los públicos, consulte Configure a custom certificate.

   • Después de configurar el certificado en la nube, el Running Status de la instancia cambia a Modifying SSL. Este estado dura aproximadamente tres minutos. Espere hasta que el estado de ejecución cambie a Running antes de continuar.

   c. Haga clic en Download CA Certificate para obtener el archivo de certificado Truststore.

   El paquete de certificado CA descargado contiene tres archivos. Al configurar el origen de datos PostgreSQL en DataWorks, cargue el archivo con extensión .pem o el archivo con extensión .p7b en el elemento de configuración Archivo de certificado Truststore.

2. Obtenga y configure el archivo de certificado Keystore, el archivo de clave privada y la contraseña de clave privada.

   Requisitos previos: Ha completado configuring an SSL-encrypted connection o configuring a custom certificate, y cuenta con la herramienta OpenSSL.

   Nota

   Si utiliza un sistema Linux, OpenSSL viene preinstalado. Si utiliza un sistema Windows, descargue e instale el paquete de software OpenSSL.

   Para obtener más información sobre cómo conseguir y configurar el archivo de certificado Keystore, el archivo de clave privada y la contraseña de clave privada, consulte Configure a client certificate.

   1. Utilice la herramienta OpenSSL en un sistema Linux o instale el software OpenSSL en un sistema Windows para generar un certificado autofirmado (ca1.crt) y su clave privada (ca1.key).

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

   2. Genere un archivo de solicitud de firma de certificado de cliente (client.csr) y una clave privada de certificado de cliente (client.key).

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

      En este comando, el valor CN después del parámetro -subj debe establecerse con el nombre de usuario que utiliza el cliente para acceder a la base de datos.

   3. Genere el certificado de cliente (client.crt).

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

   4. Si su servidor RDS PostgreSQL requiere verificación del certificado CA del cliente, abra el archivo de certificado autofirmado del cliente generado ca1.crt, copie el contenido del certificado y péguelo en el cuadro de diálogo Client CA Certificate Public Key Content como certificado CA del cliente.

      En el panel Configure Client CA Certificate, haga clic en Enable Client CA Certificate. En el cuadro de diálogo que aparece, pegue el contenido de la clave pública del certificado CA generado (ca1.crt) en el cuadro de texto Public Key y luego haga clic en OK. Después de modificar el certificado CA del cliente, la instancia se reinicia automáticamente.

   5. Después de configurar el certificado CA del cliente en el lado de RDS, al configurar el origen de datos PostgreSQL en DataWorks, convierta la clave privada del certificado de cliente client.key a un archivo client.pk8 y cargue el archivo client.pk8 en el elemento de configuración Archivo de clave privada dentro de la configuración del origen de datos PostgreSQL en DataWorks.

      cp client.key client.pk8

   6. Configure la contraseña de la clave privada.

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

      Nota

      Al ejecutar el comando para configurar la contraseña de la clave privada, debe introducir una contraseña. Si establece una contraseña, utilice la misma para la contraseña de clave privada en la configuración del origen de datos PostgreSQL en DataWorks.

Configurar archivos de autenticación SSL de PostgreSQL

Al cargar los archivos de certificado obtenidos en los elementos de configuración de PostgreSQL en DataWorks, realice las siguientes operaciones:

• Archivo de certificado Truststore: Cargue el archivo con extensión .pem o el archivo con extensión .p7b obtenido en el paso Obtain the Truststore certificate file.

• Archivo de certificado Keystore: Cargue el archivo de certificado de cliente client.crt obtenido en el paso Generate the client certificate.

• Archivo de clave privada: Cargue el archivo client.pk8 convertido a partir de la clave privada del certificado de cliente client.key en el paso Convert the private key file.

• Contraseña de clave privada: La contraseña configurada en el paso Configure the private key password.

Configuración de ACL: Vaya a la lista de instancias RDS, haga clic en la instancia RDS de la región correspondiente, haga clic en el ID de la instancia destino para ir a la página de detalles de la instancia y luego haga clic en Data Security > ACL Configuration para modificar la configuración. Puede seleccionar diferentes métodos de autenticación SSL. Para obtener más información, consulte Configure ACL.

Los métodos de autenticación SSL disponibles son:

• prefer: Cifra el enlace de datos.

• verify-ca: Cifra el enlace de datos y verifica la autenticidad del certificado del cliente.

• verify-full: Cifra el enlace de datos, verifica la autenticidad del certificado del cliente y comprueba que el CN del certificado coincida con el nombre de usuario de la base de datos.

• cert: Utiliza un certificado en lugar de una contraseña para iniciar sesión, cifra el enlace de datos, verifica la autenticidad del certificado del cliente y comprueba que el CN del certificado coincida con el nombre de usuario de la base de datos.

• Si el método de autenticación ACL se establece en prefer, el servidor PostgreSQL no exige la verificación del certificado del cliente.

• Si el método de autenticación ACL se establece en verify-ca en RDS PostgreSQL, debe cargar el certificado de cliente correcto al configurar el origen de datos PostgreSQL en DataWorks, para que el servidor pueda verificar la autenticación del cliente.