Name: Zetian.Storage.PostgreSQL
Author: Taiizor

Zetian.Storage.PostgreSQL v1.0.0 — nugetz

# Install SMTP Server and Storage Provider
dotnet add package Zetian
dotnet add package Zetian.Storage.PostgreSQL

using Zetian.Server;
using Zetian.Storage.PostgreSQL.Extensions;

// Configure with connection string
var server = new SmtpServerBuilder()
    .Port(25)
    .WithPostgreSqlStorage("Host=localhost;Database=smtp_db;Username=postgres;Password=secret")
    .Build();

await server.StartAsync();

var server = new SmtpServerBuilder()
    .Port(25)
    .WithPostgreSqlStorage(
        "Host=localhost;Database=smtp_storage;Username=postgres;Password=password;",
        config =>
        {
            config.TableName = "smtp_messages";
            config.SchemaName = "public";
            config.AutoCreateTable = true;
            config.EnablePartitioning = true;
            config.PartitionInterval = PartitionInterval.Monthly;
            config.CompressMessageBody = true;
            config.UseJsonbForHeaders = true;
            config.CreateIndexes = true;
        })
    .Build();

Option	Type	Default	Description
`ConnectionString`	string	required	PostgreSQL connection string
`TableName`	string	"smtp_messages"	Name of the messages table
`SchemaName`	string	"public"	Database schema name
`AutoCreateTable`	bool	true	Auto-create table and indexes
`UseJsonbForHeaders`	bool	true	Store headers as JSONB
`EnablePartitioning`	bool	false	Enable table partitioning
`PartitionInterval`	enum	Monthly	Partition interval (Daily, Weekly, Monthly, Yearly)
`CreateIndexes`	bool	true	Create indexes automatically
`MaxMessageSizeMB`	double	100	Maximum message size in MB
`CompressMessageBody`	bool	false	Compress message bodies
`EnableRetry`	bool	true	Enable retry logic
`MaxRetryAttempts`	int	3	Maximum retry attempts
`RetryDelayMs`	int	1000	Delay between retries
`ConnectionTimeoutSeconds`	int	30	Connection timeout
`LogErrors`	bool	true	Whether to log errors

CREATE TABLE mail.messages (
    id BIGSERIAL PRIMARY KEY,
    message_id VARCHAR(255) NOT NULL UNIQUE,
    session_id VARCHAR(255) NOT NULL,
    from_address VARCHAR(500),
    to_addresses TEXT NOT NULL,
    cc_addresses TEXT,
    bcc_addresses TEXT,
    subject VARCHAR(1000),
    received_date TIMESTAMP WITH TIME ZONE NOT NULL,
    message_size BIGINT NOT NULL,
    message_body BYTEA NOT NULL,
    is_compressed BOOLEAN NOT NULL DEFAULT FALSE,
    headers JSONB,
    has_attachments BOOLEAN NOT NULL DEFAULT FALSE,
    attachment_count INTEGER NOT NULL DEFAULT 0,
    attachments JSONB,
    priority VARCHAR(50),
    remote_ip INET,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (received_date);  -- When partitioning is enabled

-- Indexes
CREATE INDEX idx_messages_message_id ON mail.messages (message_id);
CREATE INDEX idx_messages_received_date ON mail.messages (received_date DESC);
CREATE INDEX idx_messages_from_address ON mail.messages (from_address);
CREATE INDEX idx_messages_session_id ON mail.messages (session_id);
CREATE INDEX idx_messages_headers ON mail.messages USING GIN (headers);  -- JSONB GIN index

// Basic connection
.WithPostgreSqlStorage("Host=localhost;Database=smtp_db;Username=postgres;Password=secret")

// With SSL
.WithPostgreSqlStorage("Host=localhost;Database=smtp_db;Username=postgres;Password=secret;SSL Mode=Require")

// With connection pooling
.WithPostgreSqlStorage("Host=localhost;Database=smtp_db;Username=postgres;Password=secret;Pooling=true;Minimum Pool Size=5;Maximum Pool Size=100")

// With specific schema
.WithPostgreSqlStorage("Host=localhost;Database=smtp_db;Username=postgres;Password=secret;Search Path=mail,public")

-- Find messages with specific header
SELECT * FROM mail.messages 
WHERE headers @> '{"X-Priority": "High"}';

-- Find messages from specific domain
SELECT * FROM mail.messages 
WHERE headers->>'From' LIKE '%@example.com';

-- Complex JSONB query
SELECT message_id, headers->>'Subject' as subject
FROM mail.messages
WHERE headers @> '{"X-Spam-Score": "0"}'
AND received_date > NOW() - INTERVAL '7 days';

// Configure with partitioning
.WithPostgreSqlStorage(
    connectionString,
    config =>
    {
        config.EnablePartitioning = true;
        config.PartitionInterval = PartitionInterval.Monthly;
    })

// Use JSONB for headers
.WithPostgreSqlStorage(
    connectionString,
    config =>
    {
        config.UseJsonbForHeaders = true;
        config.CreateIndexes = true; // Create indexes including GIN for JSONB
    })

-- Create future partitions
CREATE TABLE mail.messages_2024_02 PARTITION OF mail.messages
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE mail.messages_2024_03 PARTITION OF mail.messages
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- List partitions
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
WHERE tablename LIKE 'messages_%'
ORDER BY tablename;

-- Drop old partitions
DROP TABLE mail.messages_2023_01;

config.ConnectionString = "Host=localhost;Database=smtp_db;" +
    "Username=postgres;Password=secret;" +
    "Pooling=true;" +
    "Minimum Pool Size=10;" +
    "Maximum Pool Size=100;" +
    "Connection Idle Lifetime=300;";

-- Full-text search index
CREATE INDEX idx_messages_fulltext ON mail.messages 
USING GIN (to_tsvector('english', subject || ' ' || message_body));

-- Composite index for common queries
CREATE INDEX idx_messages_date_from ON mail.messages (received_date DESC, from_address);

-- Partial index for unread messages
CREATE INDEX idx_messages_unread ON mail.messages (received_date DESC) 
WHERE headers->>'X-Read' IS NULL;

-- Use EXPLAIN ANALYZE to optimize queries
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM mail.messages 
WHERE received_date > NOW() - INTERVAL '7 days';

-- Vacuum and analyze for statistics
VACUUM ANALYZE mail.messages;

-- Auto-vacuum configuration
ALTER TABLE mail.messages SET (
    autovacuum_vacuum_scale_factor = 0.1,
    autovacuum_analyze_scale_factor = 0.05
);

// Primary server
.WithPostgreSqlStorage("Host=primary.db;Database=smtp_db;Username=postgres;Password=secret")

// Read replica for queries
.WithPostgreSqlStorage("Host=replica.db;Database=smtp_db;Username=postgres;Password=secret;Target Session Attributes=read-only")

// Multiple hosts with failover
config.ConnectionString = 
    "Host=primary.db,replica1.db,replica2.db;" +
    "Database=smtp_db;" +
    "Username=postgres;" +
    "Password=secret;" +
    "Target Session Attributes=read-write;" +
    "Load Balance Hosts=true;";

-- Create trigger for notifications
CREATE OR REPLACE FUNCTION notify_new_message() RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('new_message', NEW.message_id::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER message_notify 
AFTER INSERT ON mail.messages 
FOR EACH ROW EXECUTE FUNCTION notify_new_message();

// Listen for notifications in C#
await using var conn = new NpgsqlConnection(connectionString);
await conn.OpenAsync();
conn.Notification += (o, e) => 
{
    Console.WriteLine($"New message: {e.Payload}");
};
await using (var cmd = new NpgsqlCommand("LISTEN new_message", conn))
    await cmd.ExecuteNonQueryAsync();

-- Enable row-level security
ALTER TABLE mail.messages ENABLE ROW LEVEL SECURITY;

-- Create policy for user access
CREATE POLICY user_messages ON mail.messages
    FOR ALL
    TO smtp_user
    USING (to_addresses LIKE '%' || current_user || '%');

-- Check table sizes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
    n_live_tup as row_count
FROM pg_stat_user_tables 
WHERE schemaname = 'mail';

-- Check slow queries
SELECT 
    query,
    calls,
    mean_exec_time,
    total_exec_time
FROM pg_stat_statements 
WHERE query LIKE '%messages%'
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Reindex periodically
REINDEX TABLE mail.messages;

-- Update table statistics
ANALYZE mail.messages;

-- Clean up old messages
DELETE FROM mail.messages 
WHERE received_date < NOW() - INTERVAL '6 months';

-- Vacuum to reclaim space
VACUUM (VERBOSE, ANALYZE) mail.messages;

config.ConnectionString = 
    "Host=mydb.123456789012.us-east-1.rds.amazonaws.com;" +
    "Port=5432;" +
    "Database=smtp_db;" +
    "Username=postgres;" +
    "Password=secret;" +
    "SSL Mode=Require;";

config.ConnectionString = 
    "Host=myserver.postgres.database.azure.com;" +
    "Database=smtp_db;" +
    "Username=postgres@myserver;" +
    "Password=secret;" +
    "SSL Mode=Require;";

config.ConnectionString = 
    "Host=/cloudsql/project:region:instance;" +
    "Database=smtp_db;" +
    "Username=postgres;" +
    "Password=secret;";

# Check PostgreSQL is running
sudo systemctl status postgresql

# Check pg_hba.conf for authentication
sudo nano /etc/postgresql/14/main/pg_hba.conf

-- Grant necessary permissions
GRANT ALL PRIVILEGES ON DATABASE smtp_db TO smtp_user;
GRANT ALL ON SCHEMA mail TO smtp_user;
GRANT ALL ON ALL TABLES IN SCHEMA mail TO smtp_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA mail TO smtp_user;

-- Check if table is already partitioned
SELECT relname, relkind 
FROM pg_class 
WHERE relname = 'messages';

-- Convert existing table to partitioned
-- (Requires PostgreSQL 11+ and data migration)

Soferity/Zetian.Storage.PostgreSQLv1.0.0

Get Started

Readme

Zetian.Storage.PostgreSQL - PostgreSQL Message Storage

⚡ Features

📦 Installation

🚀 Quick Start

Basic Configuration

Advanced Configuration

🛠️ Configuration Options

📏 Database Schema

Messages Table

🎯 Usage Examples

Connection String Options

JSONB Header Queries

Table Partitioning

Partition Management

🔧 Performance Optimization

Connection Pooling

Additional Indexes

Query Optimization

🌐 Replication & High Availability

Streaming Replication Setup

Connection Failover

📊 Advanced Features

LISTEN/NOTIFY for Real-time Updates

Row-Level Security

🔍 Monitoring & Maintenance

Health Checks

Maintenance Tasks

☁️ Cloud Deployments

Amazon RDS PostgreSQL

Azure Database for PostgreSQL

Google Cloud SQL

🔧 Troubleshooting

Common Issues

📋 Requirements

📚 Documentation & Support

📄 License