pg-wire-mock

A comprehensive mock PostgreSQL server that implements the PostgreSQL wire protocol for learning, testing, and development purposes. Built for experimenting with and understanding the PostgreSQL wire protocol.

New! Check out our detailed PostgreSQL Wire Protocol Guide to learn how the protocol works, and our Graceful Shutdown Documentation for production deployment guidance.

🌟 Features

• Complete PostgreSQL Wire Protocol v3.0 Support

  • Authentication flow with parameter negotiation
  • Modern authentication methods (NEW!) - See Authentication Documentation
  • Simple and extended query protocols
  • Transaction management (BEGIN/COMMIT/ROLLBACK)
  • Prepared statements and portals
  • Error handling with proper SQLSTATE codes
  • SSL/TLS encryption support (NEW!) - See SSL Documentation
  • PostgreSQL array type support (NEW!) - See Array Documentation
  • LISTEN/NOTIFY asynchronous notifications (NEW!) - See LISTEN/NOTIFY Documentation
  • COPY protocol for bulk data transfer (NEW!) - See COPY Documentation

• Comprehensive Query Support

  • SELECT queries with various functions
  • Array queries and operations (NEW!)
  • LISTEN/NOTIFY/UNLISTEN commands for pub-sub messaging (NEW!)
  • COPY protocol operations (NEW!) - Bulk data transfer with STDIN/STDOUT support
  • EXPLAIN query plans (NEW!) - Mock query execution plans and analysis
  • SHOW commands for server information
  • DDL commands (CREATE/DROP - mock responses)
  • DML commands (INSERT/UPDATE/DELETE - mock responses)
  • Transaction control statements

• Production-Ready Architecture

  • Modular, well-organized codebase
  • Connection management and pooling
  • Configurable logging and monitoring
  • Advanced Query Logging & Analysis (NEW!) - See Query Logging Guide
    • High-precision execution time tracking
    • Parameter logging with automatic sanitization
    • Slow query detection and analytics
    • File-based logging with automatic rotation
  • Statistics and performance tracking
  • Graceful shutdown handling with connection draining
  • HTTP monitoring endpoints (NEW!) - See HTTP Monitoring Guide

🚀 Quick Start

Installation

# Clone the repository
git clone https://github.com/The-DevOps-Daily/pg-wire-mock.git
cd pg-wire-mock

# Install dependencies (none required for basic usage!)
npm install

# Start the server
npm start

Using the Server

# Start with default settings (port 5432)
npm start

# Start on a different port
npm run start:port  # Uses port 5433

# Start with debug logging
npm run dev

# Start quietly (no logging)
npm run start:quiet

# Start listening on all interfaces
npm run start:all

Connect with psql

psql -h localhost -p 5432 -U postgres

SSL/TLS Connections (NEW!)

# Generate test certificates
npm run generate-certs

# Start server with SSL enabled
# Bash/Linux/macOS:
export PG_MOCK_ENABLE_SSL=true
export PORT=5433
npm run dev

# PowerShell (Windows):
$env:PG_MOCK_ENABLE_SSL = "true"
$env:PORT = "5433"
npm run dev

# Connect with SSL (in a separate terminal)
# Strict SSL mode:
psql "sslmode=require host=localhost port=5433 dbname=postgres user=postgres"

Authentication (NEW!)

pg-wire-mock supports secure authentication methods, with a focus on modern SCRAM-SHA-256 authentication preferred in PostgreSQL 10+:

# Trust authentication (default - no password required)
export PG_MOCK_AUTH_METHOD=trust
npm start

# SCRAM-SHA-256 authentication (recommended for PostgreSQL 10+)
export PG_MOCK_AUTH_METHOD=scram-sha-256
export PG_MOCK_REQUIRE_AUTHENTICATION=true
export PG_MOCK_SCRAM_ITERATIONS=4096  # Optional: iteration count (default: 4096)
npm start

# Connect with authentication
psql -h localhost -p 5432 -U postgres -W
# Enter password when prompted (default mock password: "password")

Authentication Methods:

• trust: No authentication required (default)
• scram-sha-256: SCRAM-SHA-256 authentication (RFC 7677) - Modern, secure authentication

HTTP Monitoring Endpoints (NEW!)

The optional HTTP server exposes operational data for observability and readiness probes. Enable it with environment variables or config (see docs/HTTP_MONITORING.md).

• GET /health returns aggregated health checks (200 healthy, 503 otherwise)
• GET /status supplies server stats, configuration, and health detail
• GET /connections lists active connections with metadata
• GET /metrics exports Prometheus-format metrics (Content-Type: text/plain; version=0.0.4)

Example configuration:

export PG_MOCK_HTTP_ENABLED=true
export PG_MOCK_HTTP_PORT=8081
export PG_MOCK_HTTP_ENABLE_AUTH=true
export PG_MOCK_HTTP_AUTH_TOKEN="super-secret-token"
npm start

Scrape metrics with Prometheus by adding the following job:

scrape_configs:
  - job_name: 'pg-wire-mock'
    static_configs:
      - targets: ['localhost:8081']
    metrics_path: /metrics
    scheme: http
    authorization:
      credentials: super-secret-token

Try Some Queries

-- Basic queries
SELECT 1;
SELECT VERSION();
SELECT NOW();
SELECT CURRENT_USER;

-- Array queries (NEW!)
SELECT ARRAY[1, 2, 3, 4, 5];
SELECT '{apple,banana,cherry}';
SELECT '{1,2,3,4,5}'::int4[];
SELECT '{{a,b},{c,d}}'::text[][];

-- LISTEN/NOTIFY notifications (NEW!)
LISTEN my_channel;
NOTIFY my_channel, 'Hello world!';
UNLISTEN my_channel;

-- Server information
SHOW DOCS;
SHOW SERVER_VERSION;
SHOW TIMEZONE;

-- Transaction management
BEGIN;
SELECT 42;
COMMIT;

📖 Architecture

The server is built with a clean, modular architecture:

pg-wire-mock/
├── server.js                           # Main entry point and CLI
├── src/
│   ├── server/
│   │   └── serverManager.js            # TCP server and connection management
│   ├── protocol/
│   │   ├── messageProcessors.js        # Protocol message handling
│   │   ├── messageBuilders.js          # Protocol message construction
│   │   ├── constants.js                # Protocol constants and types
│   │   └── utils.js                    # Protocol utilities
│   ├── handlers/
│   │   └── queryHandlers.js            # SQL query processing
│   ├── connection/
│   │   └── connectionState.js          # Connection state management
│   └── config/
│       └── serverConfig.js             # Configuration management
├── package.json
├── CONTRIBUTING.md
├── LICENSE
└── README.md

Key Components

• ServerManager: TCP server lifecycle, connection management, statistics
• Message Processors: Handle incoming protocol messages
• Message Builders: Construct outgoing protocol responses
• Query Handlers: Process SQL queries and generate results
• Connection State: Track connection parameters and transaction state
• Configuration: Centralized configuration with environment variable support

⚙️ Configuration

Command Line Options

node server.js [options]

Options:
  -p, --port <port>              Port to listen on (default: 5432)
  -h, --host <host>              Host to bind to (default: localhost)
  --max-connections <num>        Max concurrent connections (default: 100)
  --log-level <level>            Log level: error, warn, info, debug (default: info)
  -q, --quiet                    Disable logging
  --help                         Show help message
  --version                      Show version information

Environment Variables

# Server settings
export PG_MOCK_PORT=5432
export PG_MOCK_HOST=localhost
export PG_MOCK_MAX_CONNECTIONS=100
export PG_MOCK_CONNECTION_TIMEOUT=300000

# Authentication settings (NEW!)
export PG_MOCK_AUTH_METHOD=trust              # trust, scram-sha-256
export PG_MOCK_REQUIRE_AUTHENTICATION=false   # Enable authentication requirement
export PG_MOCK_SCRAM_ITERATIONS=4096          # SCRAM-SHA-256 iteration count

# Logging settings
export PG_MOCK_ENABLE_LOGGING=true
export PG_MOCK_LOG_LEVEL=info

# Query logging settings (NEW!)
export PG_MOCK_QUERY_DETAILED_LOGGING=true
export PG_MOCK_QUERY_LOG_PARAMETERS=true
export PG_MOCK_QUERY_LOG_EXECUTION_TIME=true
export PG_MOCK_QUERY_SLOW_THRESHOLD=1000
export PG_MOCK_QUERY_ANALYTICS=true
export PG_MOCK_QUERY_FILE_LOGGING=false

# Shutdown settings
export PG_MOCK_SHUTDOWN_TIMEOUT=30000
export PG_MOCK_SHUTDOWN_DRAIN_TIMEOUT=10000

# Database settings
export PG_MOCK_SERVER_VERSION="13.0 (Mock)"
export PG_MOCK_DEFAULT_DATABASE=postgres
export PG_MOCK_DEFAULT_USER=postgres
export PG_MOCK_DEFAULT_TIMEZONE=UTC

View current configuration:

npm run config

🔧 Development

Project Structure

The codebase follows a clean architecture pattern:

1. Presentation Layer (server.js): CLI interface and application entry point
2. Infrastructure Layer (src/server/): TCP server management
3. Protocol Layer (src/protocol/): PostgreSQL wire protocol implementation
4. Application Layer (src/handlers/): Business logic for query processing
5. Domain Layer (src/connection/): Connection state and lifecycle management
6. Configuration Layer (src/config/): Application configuration

Adding New Features

1. New Query Types: Add handlers in src/handlers/queryHandlers.js
2. New Protocol Messages: Add processors in src/protocol/messageProcessors.js and builders in src/protocol/messageBuilders.js
3. New Configuration: Update src/config/serverConfig.js
4. New Connection Features: Extend src/connection/connectionState.js

Code Quality

• Modular Design: Each module has a single responsibility
• Comprehensive Documentation: JSDoc comments throughout
• Error Handling: Proper error responses with SQLSTATE codes
• Logging: Configurable logging with structured output
• Configuration: Environment-based configuration with validation

📊 Monitoring and Statistics

The server provides detailed statistics and monitoring:

// Access server statistics (when running programmatically)
const stats = server.getStats();
console.log(stats);
// {
//   connectionsAccepted: 42,
//   messagesProcessed: 156,
//   queriesExecuted: 89,
//   uptime: 3600000,
//   activeConnections: 3
// }

🔄 Graceful Shutdown

The server implements comprehensive graceful shutdown behavior to ensure clean connection termination:

Shutdown Process

1. Stop Accepting New Connections: Server immediately stops accepting new client connections
2. Notify Active Clients: All connected clients receive a shutdown notice
3. Transaction Rollback: Any active transactions are automatically rolled back
4. Connection Draining: Server waits for clients to disconnect gracefully
5. Force Close: After timeout, remaining connections are force-closed
6. Resource Cleanup: All server resources are cleaned up

Configuration

# Shutdown timeout (total time for graceful shutdown)
export PG_MOCK_SHUTDOWN_TIMEOUT=30000

# Connection drain timeout (time to wait for clients to disconnect)
export PG_MOCK_SHUTDOWN_DRAIN_TIMEOUT=10000

Shutdown Status Monitoring

// Check if server is shutting down
if (server.isServerShuttingDown()) {
  console.log('Server is currently shutting down');
}

// Get detailed shutdown status
const status = server.getShutdownStatus();
console.log(status);
// {
//   isShuttingDown: false,
//   activeConnections: 5,
//   shutdownTimeout: 30000,
//   drainTimeout: 10000
// }

Signal Handling

The server automatically handles common termination signals:

• SIGTERM: Graceful shutdown (used by process managers)
• SIGINT: Graceful shutdown (Ctrl+C)
• SIGUSR1: Graceful shutdown (custom signal)

Best Practices

• Client Applications: Implement proper connection cleanup to respond to shutdown notices
• Process Managers: Use SIGTERM for graceful shutdown, SIGKILL only as last resort
• Monitoring: Check isServerShuttingDown() before attempting new operations
• Timeouts: Configure appropriate timeouts based on your application needs

🐞 Debugging

Enable debug logging to see detailed protocol information:

# Start with debug logging
npm run dev

# Or set environment variable
PG_MOCK_LOG_LEVEL=debug npm start

Debug logging shows:

• Individual protocol messages
• Connection state changes
• Query processing steps
• Buffer management details
• Performance statistics

Query Logging Example

With query logging enabled, you'll see detailed execution information:

[2025-10-06T10:30:45.123Z] [INFO] [QUERY] Query Started [session-abc123] {
  "query": "SELECT * FROM users WHERE email = $1",
  "queryType": "SELECT",
  "connectionId": "conn-1",
  "user": "postgres"
}

[2025-10-06T10:30:45.135Z] [INFO] [QUERY] Query Completed [session-abc123] {
  "executionTime": "12.456ms",
  "rowCount": 1,
  "command": "SELECT"
}

See the Query Logging Guide for complete configuration options.

📚 Learning the Protocol

This project is an excellent way to understand the PostgreSQL wire protocol:

1. Start Simple: Run the server and connect with psql
2. Enable Debug Logging: See exactly what messages are exchanged
3. Try Different Queries: Observe how different SQL commands are handled
4. Examine the Code: Follow a query from TCP socket to SQL response
5. Use Wireshark: Capture packets to see the binary protocol

Useful Resources

• PostgreSQL Wire Protocol Documentation
• Protocol Flow Diagrams
• Message Format Reference

🤝 Contributing

We welcome contributions! Please see CONTRIBUTING.md for guidelines.

Development Workflow

1. Fork the repository
2. Create a feature branch: git checkout -b feature/my-feature
3. Make your changes following the existing architecture
4. Test with various PostgreSQL clients
5. Submit a pull request

Areas for Contribution

• Additional SQL query types and functions
• Enhanced protocol message support
• Performance optimizations
• Test coverage improvements
• Documentation enhancements
• Bug fixes and edge case handling

📝 License

This project is licensed under the MIT License - see the LICENSE file for details.

🙏 Acknowledgments

• PostgreSQL community for excellent protocol documentation
• Node.js community for robust TCP server capabilities
• All contributors who help improve this educational tool

📞 Support

• Issues: GitHub Issues
• Discussions: GitHub Discussions
• Documentation: Protocol Documentation

---

Happy learning! 🎓 This project aims to make the PostgreSQL wire protocol approachable and understandable for developers, students, and database enthusiasts.