1

PostgreSQL compatible Server weed db

chrislusf edited this page 2025-09-15 22:24:39 -07:00

Table of Contents

• PostgreSQL-compatible Server (weed db)
• What you get
• Start the server
• Connect with clients
• SQL you can run
• Limitations (current)
• Operations and scaling
• How this fits your data lake
• Related

PostgreSQL-compatible Server (weed db)

SeaweedFS exposes a PostgreSQL wire protocol server so you can connect with any PostgreSQL client (psql, JDBC/ODBC, BI tools) and run SQL over Seaweed Message Queue (SMQ) topics. It is read-only and designed for analytics: query live streams and Parquet-backed history without ETL.

What you get

• PostgreSQL wire protocol compatibility (psql, JDBC/ODBC, BI tools)
• Query live + historical data (hybrid scanner: brokers + Parquet files)
• Simple ops: stateless server, MD5 auth, optional TLS
• Works alongside pub/sub and Kafka ingestion — one structured data lake

Start the server

# Development (no auth)
weed db

# Production (MD5 auth)
weed db -auth=md5 -users='{"admin":"secret","analyst":"readonly"}' -host=0.0.0.0 -port=5432

# Credentials from file (recommended)
echo '{"admin":"secret","analyst":"readonly"}' > /etc/seaweedfs/users.json
weed db -auth=md5 -users="@/etc/seaweedfs/users.json"

# With TLS
auth_users='@/etc/seaweedfs/users.json'
weed db -auth=md5 -users="$auth_users" -tls-cert=/etc/ssl/server.crt -tls-key=/etc/ssl/server.key

Key options:

• -auth: trust (dev), password, md5 (recommended)
• -users: inline JSON or @/path/to/users.json
• -master: SeaweedFS master (comma-separated for HA)
• -max-connections, -idle-timeout, -database
• -tls-cert, -tls-key for encrypted connections

Connect with clients

# psql
PGPASSWORD=secret psql -h localhost -p 5432 -U admin -d default
psql "postgresql://admin:secret@localhost:5432/default"

// JDBC
String url = "jdbc:postgresql://localhost:5432/default";
Connection conn = DriverManager.getConnection(url, "admin", "secret");

# psycopg2
import psycopg2
conn = psycopg2.connect(host="localhost", port=5432, user="admin", password="secret", database="default")

BI tools: use the native PostgreSQL connector (host, port 5432, database default, user/password).

SQL you can run

Schema/navigation:

SHOW DATABASES;         -- MQ namespaces
USE my_namespace;       -- switch database
SHOW TABLES;            -- topics in namespace
DESCRIBE topic_name;    -- schema

Queries (read-only):

SELECT * FROM events LIMIT 10;
SELECT * FROM events WHERE _ts >= '2025-01-01' LIMIT 1000;
SELECT COUNT(*) FROM events;                -- fast-path optimized
SELECT MIN(timestamp), MAX(timestamp) FROM events;  -- fast-path

-- System columns available on all tables
SELECT _ts, _key, _source, * FROM events LIMIT 5;

Notes:

• _ts supports convenient string-to-timestamp parsing in WHERE clauses
• Live + Parquet results are merged transparently

Limitations (current)

• Read-only: no INSERT/UPDATE/DELETE, no DDL (CREATE/DROP/ALTER)
• Not supported yet: JOIN, ORDER BY, GROUP BY, HAVING, window functions, subqueries

Operations and scaling

• Stateless server; run multiple weed db instances behind a load balancer
• Use MD5 auth and TLS in production
• Tune -max-connections and -idle-timeout

How this fits your data lake

• Producers (Kafka or pub/sub) write schematized messages into SMQ
• SeaweedFS persists to Parquet; brokers serve live data
• PostgreSQL-compatible server exposes SQL over both — one data lake

Related

• Structured Data Lake with SMQ and SQL
• SQL Queries on Message Queue
• SQL Quick Reference
• Seaweed Message Queue

Introduction

• Components
• Getting Started
• Production Setup
• Benchmarks
• FAQ
• Applications

API

• Master Server API
• Volume Server API
• Filer Server API
• Client Libraries
• SeaweedFS Java Client

Configuration

• Replication
• Store file with a Time To Live
• Failover Master Server
• Erasure coding for warm storage
• Server Startup via Systemd
• Environment Variables

Filer

• Filer Setup
• Directories and Files
• File Operations Quick Reference
• Data Structure for Large Files
• Filer Data Encryption
• Filer Commands and Operations
• Filer JWT Use

Filer Stores

• Filer Cassandra Setup
• Filer Redis Setup
• Super Large Directories
• Path-Specific Filer Store
• Choosing a Filer Store
• Customize Filer Store

Management

• Admin UI
• Worker

Advanced Filer Configurations

• Migrate to Filer Store
• Add New Filer Store
• Filer Store Replication
• Filer Active Active cross cluster continuous synchronization
• Filer as a Key-Large-Value Store
• Path Specific Configuration
• Filer Change Data Capture

FUSE Mount

• FIO benchmark
• fstab

WebDAV

Cloud Drive

• Cloud Drive Benefits
• Cloud Drive Architecture
• Configure Remote Storage
• Mount Remote Storage
• Cache Remote Storage
• Cloud Drive Quick Setup
• Gateway to Remote Object Storage

AWS S3 API

• S3 Credentials
• Amazon S3 API
• S3 Conditional Operations
• S3 CORS
• S3 Object Lock and Retention
• S3 Object Versioning
• AWS CLI with SeaweedFS
• s3cmd with SeaweedFS
• rclone with SeaweedFS
• restic with SeaweedFS
• nodejs with Seaweed S3
• S3 API Benchmark
• S3 API FAQ
• S3 Bucket Quota
• S3 API Audit log
• S3 Nginx Proxy
• Docker Compose for S3

Server-Side Encryption

• Server-Side Encryption
• Server-Side Encryption SSE-KMS
• Server-Side Encryption SSE-C

AWS IAM

• Amazon IAM API
• AWS IAM CLI
• Keycloak Integration

Machine Learning

• TensorFlow with SeaweedFS

HDFS

• Hadoop Compatible File System
• run Spark on SeaweedFS
• run HBase on SeaweedFS
• run Presto on SeaweedFS
• Hadoop Benchmark
• HDFS via S3 connector

Replication and Backup

• Async Replication to another Filer [Deprecated]
• Async Backup
• Async Filer Metadata Backup
• Async Replication to Cloud [Deprecated]
• Kubernetes Backups and Recovery with K8up

Metadata Change Events

• Filer Metadata Events

Messaging

• Structured Data Lake with SMQ and SQL
• Seaweed Message Queue
• SQL Queries on Message Queue
• SQL Quick Reference
• PostgreSQL-compatible Server weed db
• Pub-Sub to SMQ to SQL
• Kafka to Kafka Gateway to SMQ to SQL

Use Cases

• Use Cases
• Actual Users

Operations

• System Metrics
• weed shell
• Data Backup
• Deployment to Kubernetes and Minikube

Advanced

• Large File Handling
• Optimization
• Volume Management
• Tiered Storage
• Cloud Tier
• Cloud Monitoring
• Load Command Line Options from a file
• SRV Service Discovery
• Volume Files Structure

Security

• Security Overview
• Security Configuration
• Run Blob Storage on Public Internet

Misc Use Case Examples

• UrBackup with SeaweedFS
• Docker Image Registry with SeaweedFS
• SeaweedFS in Docker Swarm
• Words from SeaweedFS Users
• Independent Benchmarks
• Hardware