---
title: "PostgreSQL Protocol Configuration"
---

# PostgreSQL Protocol Configuration

Complete configuration reference for HeliosDB's PostgreSQL protocol implementation.

## Server Configuration

### Network Settings

```toml
[postgresql]
# Listen address for PostgreSQL wire protocol
listen_address = "0.0.0.0"

# PostgreSQL wire protocol port (default: 5432)
port = 5432

# Maximum concurrent connections
max_connections = 100

# Connection timeout in seconds
connection_timeout = 60

# Idle connection timeout (0 = disabled)
idle_timeout = 300
```

### Authentication

```toml
[postgresql.auth]
# Authentication methods: "password", "md5", "scram-sha-256"
method = "scram-sha-256"

# Password file location (HBA-style)
hba_file = "/etc/heliosdb/pg_hba.conf"

# Enable SSL authentication
ssl_auth = false

# Certificate-based authentication
cert_auth = false
```

### SSL/TLS Configuration

```toml
[postgresql.ssl]
# Enable SSL connections
enabled = true

# SSL mode: "disable", "allow", "prefer", "require", "verify-ca", "verify-full"
mode = "prefer"

# Certificate files
cert_file = "/etc/heliosdb/server.crt"
key_file = "/etc/heliosdb/server.key"
ca_file = "/etc/heliosdb/ca.crt"

# Minimum TLS version: "1.2", "1.3"
min_version = "1.2"

# Cipher suites (leave empty for defaults)
ciphers = ""
```

### Protocol Settings

```toml
[postgresql.protocol]
# Wire protocol version (3.0 is standard)
version = "3.0"

# Extended query protocol support
extended_query = true

# Prepared statement cache size per connection
prepared_statement_cache = 100

# Maximum parameter count
max_parameters = 65535

# Message size limits
max_message_size = "1GB"
```

### COPY Protocol

```toml
[postgresql.copy]
# Enable COPY command
enabled = true

# Default format: "text", "csv", "binary"
default_format = "text"

# Maximum rows per batch
batch_size = 10000

# ON_ERROR default: "stop", "ignore"
on_error = "stop"

# Enable error logging
error_logging = true

# Maximum errors to log
max_errors_logged = 1000
```

### Replication Settings

```toml
[postgresql.replication]
# Enable replication protocol
enabled = true

# Logical replication support
logical_replication = true

# Physical replication support
physical_replication = true

# Replication slot failover
slot_failover = true

# Maximum replication connections
max_replication_connections = 10

# WAL sender timeout (ms)
wal_sender_timeout = 60000
```

## Connection String Parameters

Standard PostgreSQL connection string format:

```
postgresql://[user[:password]@][host][:port][/database][?parameters]
```

### Supported Parameters

| Parameter | Description | Default |
|-----------|-------------|---------|
| `host` | Server hostname | localhost |
| `port` | Port number | 5432 |
| `dbname` | Database name | postgres |
| `user` | Username | - |
| `password` | Password | - |
| `sslmode` | SSL mode | prefer |
| `sslcert` | Client certificate | - |
| `sslkey` | Client key | - |
| `sslrootcert` | CA certificate | - |
| `connect_timeout` | Connection timeout | 0 (infinite) |
| `application_name` | Application name | - |
| `options` | Command-line options | - |
| `keepalives` | Enable TCP keepalives | 1 |
| `keepalives_idle` | Keepalive idle time | 0 |
| `target_session_attrs` | Session attributes | any |

### Connection Examples

```bash
# Basic connection
postgresql://localhost/mydb

# With authentication
postgresql://user:password@localhost:5432/mydb

# With SSL
postgresql://user:password@localhost/mydb?sslmode=require

# Full connection string
postgresql://user:password@host1:5432,host2:5432/mydb?sslmode=verify-full&target_session_attrs=read-write
```

## Environment Variables

HeliosDB respects standard PostgreSQL environment variables:

| Variable | Description |
|----------|-------------|
| `PGHOST` | Default hostname |
| `PGPORT` | Default port |
| `PGDATABASE` | Default database |
| `PGUSER` | Default username |
| `PGPASSWORD` | Password (not recommended) |
| `PGPASSFILE` | Password file location |
| `PGSSLMODE` | SSL mode |
| `PGSSLCERT` | SSL certificate |
| `PGSSLKEY` | SSL key |
| `PGSSLROOTCERT` | SSL root certificate |

## Performance Tuning

### Connection Pooling

```toml
[postgresql.pooling]
# Enable server-side connection pooling
enabled = true

# Pool mode: "session", "transaction", "statement"
pool_mode = "transaction"

# Minimum pool size
min_pool_size = 10

# Maximum pool size
max_pool_size = 100

# Connection lifetime (seconds)
max_connection_lifetime = 3600

# Idle connection timeout
idle_timeout = 300
```

### Query Optimization

```toml
[postgresql.query]
# Enable query caching
query_cache = true

# Query cache size (MB)
query_cache_size = 256

# Statement timeout (ms, 0 = disabled)
statement_timeout = 0

# Lock timeout (ms, 0 = disabled)
lock_timeout = 0

# Enable parallel query
parallel_query = true

# Maximum parallel workers
max_parallel_workers = 4
```

### Memory Settings

```toml
[postgresql.memory]
# Work memory per operation (MB)
work_mem = 64

# Maintenance work memory (MB)
maintenance_work_mem = 256

# Shared buffers (MB)
shared_buffers = 1024

# Effective cache size (MB)
effective_cache_size = 4096
```

## Monitoring

### Session Variables

Query current settings using standard PostgreSQL commands:

```sql
-- Show all settings
SHOW ALL;

-- Show specific setting
SHOW work_mem;

-- Set session variable
SET work_mem = '128MB';

-- Show server version
SELECT version();
```

### System Catalogs

Available PostgreSQL-compatible system catalogs:

- `pg_catalog.pg_database` - Databases
- `pg_catalog.pg_tables` - Tables
- `pg_catalog.pg_indexes` - Indexes
- `pg_catalog.pg_stat_user_tables` - Table statistics
- `pg_catalog.pg_stat_activity` - Active sessions
- `pg_catalog.pg_locks` - Current locks
- `information_schema.*` - Standard information schema

## Troubleshooting

### Connection Issues

```bash
# Test connection
psql -h localhost -p 5432 -U user -d database -c "SELECT 1"

# Check SSL
psql "postgresql://localhost/db?sslmode=require" -c "SELECT ssl_is_used()"

# Verbose connection
PGSSLMODE=require psql -h localhost -d db -v
```

### Common Errors

| Error | Cause | Solution |
|-------|-------|----------|
| Connection refused | Server not running | Check Server state |
| Authentication failed | Wrong credentials | Verify username/password |
| SSL required | SSL mode mismatch | Configure sslmode |
| Too many connections | Max connections reached | Increase max_connections |

---

**Related**: [README.md](https://www.heliosdb.com/docs/full/reference/protocols/postgresql/readme/) | [COMPATIBILITY.md](https://www.heliosdb.com/docs/full/reference/protocols/postgresql/compatibility/) | [EXAMPLES.md](https://www.heliosdb.com/docs/full/reference/protocols/postgresql/examples/)