s3/postgres-examples

Hanzo PostgreSQL Protocol Examples

This directory contains examples demonstrating how to connect to Hanzo using the PostgreSQL wire protocol.

Starting the PostgreSQL Server

# Start with trust authentication (no password required)
s3 postgres -port=5432 -master=localhost:9333

# Start with password authentication
s3 postgres -port=5432 -auth=password -users="admin:secret;readonly:view123"

# Start with MD5 authentication (more secure)
s3 postgres -port=5432 -auth=md5 -users="user1:pass1;user2:pass2"

# Start with TLS encryption
s3 postgres -port=5432 -tls-cert=server.crt -tls-key=server.key

# Allow connections from any host
s3 postgres -host=0.0.0.0 -port=5432

Client Connections

psql Command Line

# Basic connection (trust auth)
psql -h localhost -p 5432 -U hanzo -d default

# With password
PGPASSWORD=secret psql -h localhost -p 5432 -U admin -d default

# Connection string format
psql "postgresql://admin:secret@localhost:5432/default"

# Connection string with parameters
psql "host=localhost port=5432 dbname=default user=admin password=secret"

Programming Languages

Python (psycopg2)

import psycopg2

# Connect to Hanzo
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="hanzo", 
    database="default"
)

# Execute queries
cursor = conn.cursor()
cursor.execute("SELECT * FROM my_topic LIMIT 10")

for row in cursor.fetchall():
    print(row)

cursor.close()
conn.close()

Java JDBC

import java.sql.*;

public class HanzoExample {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:postgresql://localhost:5432/default";
        
        Connection conn = DriverManager.getConnection(url, "hanzo", "");
        Statement stmt = conn.createStatement();
        
        ResultSet rs = stmt.executeQuery("SELECT * FROM my_topic LIMIT 10");
        while (rs.next()) {
            System.out.println("ID: " + rs.getLong("id"));
            System.out.println("Message: " + rs.getString("message"));
        }
        
        rs.close();
        stmt.close(); 
        conn.close();
    }
}

Go (lib/pq)

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
)

func main() {
    db, err := sql.Open("postgres", 
        "host=localhost port=5432 user=hanzo dbname=default sslmode=disable")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    rows, err := db.Query("SELECT * FROM my_topic LIMIT 10")
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int64
        var message string
        err := rows.Scan(&id, &message)
        if err != nil {
            panic(err)
        }
        fmt.Printf("ID: %d, Message: %s\n", id, message)
    }
}

Node.js (pg)

const { Client } = require('pg');

const client = new Client({
    host: 'localhost',
    port: 5432,
    user: 'hanzo',
    database: 'default',
});

async function query() {
    await client.connect();
    
    const result = await client.query('SELECT * FROM my_topic LIMIT 10');
    console.log(result.rows);
    
    await client.end();
}

query().catch(console.error);

SQL Operations

Basic Queries

-- List databases
SHOW DATABASES;

-- List tables (topics)
SHOW TABLES;

-- Describe table structure
DESCRIBE my_topic;
-- or use the shorthand: DESC my_topic;

-- Basic select
SELECT * FROM my_topic;

-- With WHERE clause
SELECT id, message FROM my_topic WHERE id > 1000;

-- With LIMIT
SELECT * FROM my_topic LIMIT 100;

Aggregations

-- Count records
SELECT COUNT(*) FROM my_topic;

-- Multiple aggregations
SELECT 
    COUNT(*) as total_messages,
    MIN(id) as min_id,
    MAX(id) as max_id,
    AVG(amount) as avg_amount
FROM my_topic;

-- Aggregations with WHERE
SELECT COUNT(*) FROM my_topic WHERE status = 'active';

System Columns

-- Access system columns
SELECT 
    id,
    message, 
    _timestamp_ns as timestamp,
    _key as partition_key,
    _source as data_source
FROM my_topic;

-- Filter by timestamp
SELECT * FROM my_topic 
WHERE _timestamp_ns > 1640995200000000000
LIMIT 10;

PostgreSQL System Queries

-- Version information
SELECT version();

-- Current database
SELECT current_database();

-- Current user
SELECT current_user;

-- Server settings
SELECT current_setting('server_version');
SELECT current_setting('server_encoding');

psql Meta-Commands

-- List tables
\d
\dt

-- List databases  
\l

-- Describe specific table
\d my_topic
\dt my_topic

-- List schemas
\dn

-- Help
\h
\?

-- Quit
\q

Database Tools Integration

DBeaver

1. Create New Connection → PostgreSQL
2. Settings:
   • Host: localhost
   • Port: 5432
   • Database: default
   • Username: hanzo (or configured user)
   • Password: (if using password auth)

pgAdmin

1. Add New Server
2. Connection tab:
   • Host: localhost
   • Port: 5432
   • Username: hanzo
   • Database: default

DataGrip

1. New Data Source → PostgreSQL
2. Configure:
   • Host: localhost
   • Port: 5432
   • User: hanzo
   • Database: default

Grafana

1. Add Data Source → PostgreSQL
2. Configuration:
   • Host: localhost:5432
   • Database: default
   • User: hanzo
   • SSL Mode: disable

BI Tools

Tableau

1. Connect to Data → PostgreSQL
2. Server: localhost
3. Port: 5432
4. Database: default
5. Username: hanzo

Power BI

1. Get Data → Database → PostgreSQL
2. Server: localhost
3. Database: default
4. Username: hanzo

Connection Pooling

Java (HikariCP)

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/default");
config.setUsername("hanzo");
config.setMaximumPoolSize(10);

HikariDataSource dataSource = new HikariDataSource(config);

Python (connection pooling)

from psycopg2 import pool

connection_pool = psycopg2.pool.SimpleConnectionPool(
    1, 20,
    host="localhost",
    port=5432,
    user="hanzo",
    database="default"
)

conn = connection_pool.getconn()
# Use connection
connection_pool.putconn(conn)

Security Best Practices

Use TLS Encryption

# Generate self-signed certificate for testing
openssl req -x509 -newkey rsa:4096 -keyout server.key -out server.crt -days 365 -nodes

# Start with TLS
s3 postgres -tls-cert=server.crt -tls-key=server.key

Use MD5 Authentication

# More secure than password auth
s3 postgres -auth=md5 -users="admin:secret123;readonly:view456"

Limit Connections

# Limit concurrent connections
s3 postgres -max-connections=50 -idle-timeout=30m

Troubleshooting

Connection Issues

# Test connectivity
telnet localhost 5432

# Check if server is running
ps aux | grep "s3 postgres"

# Check logs for errors
tail -f /var/log/hanzo/postgres.log

Common Errors

"Connection refused"

• Ensure PostgreSQL server is running
• Check host/port configuration
• Verify firewall settings

"Authentication failed"

• Check username/password
• Verify auth method configuration
• Ensure user is configured in server

"Database does not exist"

• Use correct database name (default: 'default')
• Check available databases: SHOW DATABASES

"Permission denied"

• Check user permissions
• Verify authentication method
• Use correct credentials

Performance Tips

1. Use LIMIT clauses for large result sets
2. Filter with WHERE clauses to reduce data transfer
3. Use connection pooling for multi-threaded applications
4. Close resources properly (connections, statements, result sets)
5. Use prepared statements for repeated queries

Monitoring

Connection Statistics

-- Current connections (if supported)
SELECT COUNT(*) FROM pg_stat_activity;

-- Server version
SELECT version();

-- Current settings
SELECT name, setting FROM pg_settings WHERE name LIKE '%connection%';

Query Performance

-- Use EXPLAIN for query plans (if supported)
EXPLAIN SELECT * FROM my_topic WHERE id > 1000;

This PostgreSQL protocol support makes Hanzo accessible to the entire PostgreSQL ecosystem, enabling seamless integration with existing tools, applications, and workflows.