How to Connect PostgreSQL

Basic Connection

import asyncio
from horizon_epoch import Client

async def main():
    async with Client.connect("postgresql://user:pass@localhost/horizon_epoch") as client:
        # Client is now connected to metadata database
        await client.init("my-repo")

asyncio.run(main())

CLI Connection

# Set connection in environment
export EPOCH_METADATA_URL="postgresql://user:pass@localhost/horizon_epoch"

# Or use connection string directly
epoch init my-repo --metadata-url "postgresql://localhost/horizon_epoch"

# Register PostgreSQL table
epoch table add users \
    --location "postgresql://localhost/mydb/public.users"

Register a PostgreSQL Table

from horizon_epoch.client import _native

# Track a PostgreSQL table
loc = _native.StorageLocation.postgresql("main", "public", "users")
await client.track_table("users", loc)

# From a specific schema
loc = _native.StorageLocation.postgresql("main", "sales", "orders")
await client.track_table("orders", loc)

Add PostgreSQL as Storage Backend

# CLI - add storage backend
epoch storage add mydb \
    --type postgresql \
    --url "postgresql://user:pass@localhost/production"

from horizon_epoch import StorageBackend

await client.add_storage(
    name="mydb",
    backend=StorageBackend.POSTGRESQL,
    config={"url": "postgresql://user:pass@localhost/production"}
)

With SSL

# CLI with SSL
epoch storage add mydb \
    --type postgresql \
    --url "postgresql://user:pass@host/db?sslmode=require"

For stricter SSL verification:

epoch storage add mydb \
    --type postgresql \
    --url "postgresql://user:pass@host/db?sslmode=verify-full&sslrootcert=/path/to/ca.crt"

Connection String Format

postgresql://[user[:password]@][host][:port][/database][?param=value&...]

Common parameters:

• sslmode - disable, require, verify-ca, verify-full
• sslrootcert - Path to root certificate
• sslcert - Path to client certificate
• sslkey - Path to client key
• connect_timeout - Connection timeout in seconds
• application_name - Application name for logging

Advanced Authentication

For enterprise environments requiring additional security:

• SSH Tunnels: See SSH Tunnel Guide
• Client Certificates (mTLS): See mTLS Authentication
• AWS RDS IAM Auth: See AWS Secrets Guide
• HashiCorp Vault: See Vault Integration

Troubleshooting

Connection Refused

Error: connection refused (os error 111)

• Verify PostgreSQL is running: pg_isready -h localhost
• Check host and port in connection string
• Verify firewall rules

Authentication Failed

Error: password authentication failed

• Verify username and password
• Check pg_hba.conf for allowed authentication methods
• Ensure user has access to the database

SSL Errors

Error: SSL connection required

Add ?sslmode=require to connection string, or for self-signed certificates:

?sslmode=verify-ca&sslrootcert=/path/to/ca.crt