PostgreSQL Driver Documentation

This documentation explains the PostgreSQL driver implementation and how connection parameters map to UI fields in the Gigantic application.

Connection Parameters

Technical Details

The PostgreSQL driver implementation uses the pg Node.js library with Pool-based connections.

• Connection pooling is used through the Pool class from pg
• Schema selection is supported and affects query generation
• The driver uses search_path to set the schema context for operations
• Point data types are specially handled in the normalizeRecordPG method
• For large datasets, TABLESAMPLE is used for efficient sampling

When SSH tunneling is enabled, connections are first established through the tunnel before connecting to the PostgreSQL server.

Connection String Format

PostgreSQL connections combine individual UI fields into a connection string:

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

The application takes the individual fields from the UI (Host, Port, Database, Username, Password) and combines them into this format for the pg library.

Authentication Options

PostgreSQL supports several authentication types:

SSH Tunnel Support

The PostgreSQL driver supports SSH tunneling for secure access to databases behind firewalls or in private networks. For detailed information on configuring SSH tunnels, please refer to the SSH Tunnelling documentation.

API Endpoints Used

PostgreSQL connections are primarily used in:

• Tap creation (data source discovery)
• Sink creation (data destination for anonymized data)
• Pipeline execution (data extraction and loading)

Custom Params

The PostgreSQL driver supports additional custom parameters that can be specified in YAML format to fine-tune connection behavior, pooling, SSL settings, and other advanced options.

Connection Parameters

# SSL Configuration
ssl:
  rejectUnauthorized: false
  ca: |
    -----BEGIN CERTIFICATE-----
    # CA certificate content
    -----END CERTIFICATE-----
  cert: |
    -----BEGIN CERTIFICATE-----
    # Client certificate content
    -----END CERTIFICATE-----
  key: |
    -----BEGIN PRIVATE KEY-----
    # Client private key content
    -----END PRIVATE KEY-----

# Advanced connection options
application_name: my-app
client_encoding: utf8
statement_timeout: 5000
query_timeout: 10000
lock_timeout: 3000
idle_in_transaction_session_timeout: 60000
keepAliveInitialDelayMillis: 10000

Pool Configuration

# Pool sizing options
max: 20
min: 5

# Pool timeout options
connectionTimeoutMillis: 2000
idleTimeoutMillis: 30000
maxLifetimeSeconds: 60

# Pool behavior options
allowExitOnIdle: false

Example Configurations

SSL Configuration

ssl:
  rejectUnauthorized: true
  ca: ${file:/path/to/ca-cert.pem}
application_name: gigantic-app

Advanced Configuration with Timeouts

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