Controlador PostgreSQL

Esta documentación explica la implementación del controlador PostgreSQL y cómo los parámetros de conexión se corresponden con los campos de la interfaz de usuario en la aplicación Gigantic.

Parámetros de Conexión

Detalles Técnicos

La implementación del controlador PostgreSQL utiliza la biblioteca Node.js pg con conexiones basadas en Pool.

• El agrupamiento de conexiones se utiliza a través de la clase Pool de pg
• Se soporta la selección de esquema y afecta a la generación de consultas
• El controlador utiliza search_path para establecer el contexto del esquema para operaciones
• Los tipos de datos Point se manejan especialmente en el método normalizeRecordPG
• Para grandes conjuntos de datos, se utiliza TABLESAMPLE para muestreo eficiente

Cuando el túnel SSH está habilitado, las conexiones se establecen primero a través del túnel antes de conectarse al servidor PostgreSQL.

Formato de Cadena de Conexión

Las conexiones PostgreSQL combinan campos individuales de la UI en una cadena de conexión:

postgresql://[user[:password]@][host][:port][/database]

La aplicación toma los campos individuales de la UI (Host, Puerto, Base de Datos, Nombre de Usuario, Contraseña) y los combina en este formato para la biblioteca pg.

Opciones de Autenticación

PostgreSQL soporta varios tipos de autenticación:

Soporte de Túnel SSH

El controlador PostgreSQL soporta túneles SSH para acceso seguro a bases de datos detrás de firewalls o en redes privadas. Para información detallada sobre la configuración de túneles SSH, por favor consulte la documentación de Túneles SSH.

Endpoints de API Utilizados

Las conexiones PostgreSQL se utilizan principalmente en:

• Creación de Tap (descubrimiento de fuente de datos)
• Creación de Sink (destino de datos para datos anonimizados)
• Ejecución de Pipeline (extracción y carga de datos)

Tablas Grandes y Reanudación

Esta sección recoge solo lo propio de PostgreSQL. Cómo funcionan la reanudación, los rangos paralelos y las estimaciones de filas en todos los controladores se explica en Bases de datos grandes.

Lectura

• Las tablas se leen por bloques ordenados por la clave primaria, de modo que un job que se detiene se reanuda tras la última clave que guardó. Solo se usa la clave primaria, no los índices únicos.
• Todas las columnas de la clave primaria deben ser enteras (smallint, integer, bigint, tipos serial) o de texto (char, varchar, text, uuid, inet, enums). No se usa una clave con alguna columna numeric, decimal, real, double precision, money, de fecha u hora, bytea, array o JSON.
• En PostgreSQL 14 o posterior, una tabla sin clave primaria utilizable se lee por rangos de páginas de ctid. Esto solo vale para tablas normales: las tablas particionadas y las que tienen tablas hijas por herencia se leen con un único cursor y empiezan de nuevo si el job se detiene. Un modelo escaneado con una versión anterior de Gigantics no guarda el tipo de tabla; el log del job dice entonces scan the model again to read it by ctid in ranges.
• Con snapshotRead: true en Parámetros Personalizados, cada tabla se copia primero a una tabla temporal y se lee desde ella con un único cursor. Esas lecturas no se reanudan ni se dividen.

Rangos Paralelos y Estimación de Filas

• Los rangos de clave primaria se cortan a partir de una muestra TABLESAMPLE SYSTEM de la clave. Si la muestra es demasiado pequeña, una clave entera de una sola columna se reparte a partes iguales entre su mínimo y su máximo. Las tablas leídas por ctid se dividen en ventanas de páginas iguales.
• La estimación de filas es pg_class.reltuples. Una tabla que nunca se ha analizado no la tiene y se usa el número de filas guardado en el modelo: ejecuta ANALYZE en las tablas grandes cargadas recientemente. Al escanear el modelo, las tablas nunca analizadas que ocupan hasta 256 MB se cuentan con count(*).

Carga

• Las filas se cargan con COPY ... FROM STDIN. Un lote que COPY rechaza se escribe de nuevo con INSERT, que informa de la fila que falla; tras 3 lotes rechazados la tabla sigue solo con INSERT. Las tablas con columnas de tipo compuesto usan siempre INSERT. COPY se activa o desactiva para todos los sinks PostgreSQL con jobs.copy en la configuración de jobs. Un sink con copy: false en sus Custom Params usa siempre INSERT.
• Las cargas update y merge hacen COPY de cada lote a una tabla temporal de staging y lo aplican con INSERT ... ON CONFLICT (clave primaria) DO UPDATE o UPDATE ... FROM, en la misma transacción. Una clave primaria compuesta se compara por todas sus columnas.
• La opción truncate ejecuta TRUNCATE TABLE, y DELETE FROM si falla.
• Antes de cargar, el job elimina las foreign keys y las restricciones check de las tablas que carga, y las foreign keys de otras tablas que apuntan a ellas. Las vuelve a crear al terminar la carga. Los triggers siguen activos.
• Cada restricción eliminada se guarda en gigantics_meta.dropped_constraints, en la misma transacción que la elimina. Si un job muere, el siguiente job sobre la misma base de datos vuelve a crear las restricciones guardadas: al empezar, o al terminar si son de tablas que carga. Las restricciones de un job que sigue en marcha no se tocan. Una restricción que no se puede volver a crear (por ejemplo porque los datos cargados no la cumplen) se queda en esa tabla y aparece en el log de cada job hasta que se corrija.
• Si no se puede crear el esquema o la tabla gigantics_meta, el job avisa en el log y guarda las restricciones eliminadas solo en memoria: un job que muere antes de terminar las pierde.

Permisos

Para un sink PostgreSQL, el usuario de base de datos necesita:

• CREATE sobre la base de datos, solo si aún no existe un esquema que carga el job, o gigantics_meta: el job los crea. Un usuario sin él puede cargar si un administrador de la base de datos los ha creado, con USAGE sobre gigantics_meta y SELECT, INSERT, UPDATE y DELETE sobre gigantics_meta.dropped_constraints (la tabla la crea el primer job que ejecute un usuario que pueda).
• TEMPORARY sobre la base de datos, para las tablas de staging y las funciones auxiliares del job.
• Ser propietario de las tablas que carga y de las tablas con foreign keys que apuntan a ellas, para eliminar y crear restricciones y para truncar.

Parámetros Personalizados

El controlador PostgreSQL soporta parámetros personalizados adicionales que pueden especificarse en formato YAML para ajustar finamente el comportamiento de conexión, agrupamiento, configuraciones SSL y otras opciones avanzadas.

Parámetros de Conexión

# Configuración SSL
ssl:
  rejectUnauthorized: false
  ca: |
    -----BEGIN CERTIFICATE-----
    # Contenido del certificado CA
    -----END CERTIFICATE-----
  cert: |
    -----BEGIN CERTIFICATE-----
    # Contenido del certificado de cliente
    -----END CERTIFICATE-----
  key: |
    -----BEGIN PRIVATE KEY-----
    # Contenido de la clave privada de cliente
    -----END PRIVATE KEY-----

# Opciones avanzadas de conexión
application_name: mi-app
client_encoding: utf8
statement_timeout: 5000
query_timeout: 10000
lock_timeout: 3000
idle_in_transaction_session_timeout: 60000
keepAliveInitialDelayMillis: 10000

Configuración del Pool

# Opciones de dimensionado del pool
max: 20
min: 5

# Opciones de tiempo de espera del pool
connectionTimeoutMillis: 2000
idleTimeoutMillis: 30000
maxLifetimeSeconds: 60

# Opciones de comportamiento del pool
allowExitOnIdle: false

Ejemplos de Configuraciones

Configuración SSL

ssl:
  rejectUnauthorized: true
  ca: ${file:/ruta/a/ca-cert.pem}
application_name: aplicacion-gigantic

Configuración Avanzada con Tiempos de Espera

statement_timeout: 30000
query_timeout: 60000
max: 15
min: 2
idleTimeoutMillis: 10000