PostgreSQL Server Configuration

This guide explains how to configure your PostgreSQL server for use with DBConvert Streams. For CDC-specific configuration, please refer to the PostgreSQL CDC Configuration Guide.

Server Requirements

DBConvert Streams supports the following PostgreSQL-compatible databases:

• PostgreSQL (version 10 and higher)
• Greenplum Database
• YugabyteDB
• EDB Postgres
• Citus
• CockroachDB

Basic Configuration

Required Privileges

The database user needs different privileges depending on the mode:

• For Convert mode: SELECT on required schemas and tables
• For CDC mode: See CDC Configuration Guide

-- Grant schema access
GRANT USAGE ON SCHEMA schema_name TO "user";
-- Grant table access
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO "user";

Schema Management

PostgreSQL uses schemas to organize database objects. Configure access:

-- Create new schema
CREATE SCHEMA IF NOT EXISTS my_schema;

-- Set search path
ALTER DATABASE your_database SET search_path TO my_schema, public;

-- Grant schema access
GRANT USAGE ON SCHEMA my_schema TO "user";
GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO "user";

Connection Configuration

Create a connection in DBConvert Streams:

{
    "name": "postgresql-source",
    "type": "postgresql",
    "host": "your-postgresql-server",
    "port": 5432,
    "username": "your_user",
    "password": "your_password",
    "database": "your_database",
    "schema": "public",
    "ssl": {
        "mode": "verify-ca",
        "ca": "/path/to/ca.pem",
        "cert": "/path/to/client-cert.pem",
        "key": "/path/to/client-key.pem"
    }
}

SSL/TLS Security

1. Configure PostgreSQL server (postgresql.conf):

ssl = on
ssl_cert_file = '/path/to/server-cert.pem'
ssl_key_file = '/path/to/server-key.pem'
ssl_ca_file = '/path/to/ca.pem'

2. Available SSL modes:

• disable: No SSL
• require: Always use SSL (no verification)
• verify-ca: Verify server certificate
• verify-full: Verify server certificate and hostname

Network Access

1. Enable remote connections (postgresql.conf):

listen_addresses = '*'

2. Configure authentication (pg_hba.conf):

# Allow specific IP
host    all    all    <DBS-IP-ADDRESS>/32    md5
# Allow all IPs (less secure)
host    all    all    0.0.0.0/0             md5

3. Configure firewall:

# UFW (Ubuntu)
sudo ufw allow from DBS-IP-ADDRESS to any port 5432

Performance Configuration

# Memory Settings
shared_buffers = 4GB                  # 25% of RAM
work_mem = 16MB                       # Per-operation memory
maintenance_work_mem = 512MB          # Maintenance operations
effective_cache_size = 12GB           # 75% of RAM

# Write Performance
wal_buffers = 16MB                    # WAL buffer size
checkpoint_timeout = 15min            # Checkpoint interval
max_wal_size = 4GB                    # Max WAL between checkpoints

# Connection Management
max_connections = 100                 # Concurrent connections
pool_max_conn_idle_time = 30m        # Idle connection timeout
pool_max_conn_lifetime = 1h          # Connection lifetime

Monitoring

Essential monitoring queries:

-- Active connections
SELECT datname, usename, application_name, state, query
FROM pg_stat_activity
WHERE state != 'idle';

-- Table statistics
SELECT schemaname, relname, seq_scan, idx_scan, 
       n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
WHERE (n_tup_ins + n_tup_upd + n_tup_del) > 0;

-- Buffer cache efficiency
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit)  as heap_hit,
    sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) as hit_ratio
FROM pg_statio_user_tables;

Cloud Configurations

For cloud-specific setup, refer to:

• Amazon RDS for PostgreSQL
• Amazon Aurora PostgreSQL
• Google Cloud SQL
• Azure Database for PostgreSQL

TIP

For CDC mode setup, see the PostgreSQL CDC Configuration Guide.

Performance Tuning

Use PGTune for hardware-specific configuration recommendations. Adjust based on your workload.