Deploying Pgweb

Pgweb is a lightweight, web-based database browser for PostgreSQL written in Go. It provides an intuitive graphical interface for managing, exploring, and querying PostgreSQL databases without needing to install database clients or use command-line tools. With Pgweb, you can browse tables, execute queries, view results, and manage your database through any modern web browser.

Deploying Pgweb on Klutch.sh provides a managed, accessible database management interface that works seamlessly with your PostgreSQL instances. This comprehensive guide covers everything you need to deploy, configure, and use Pgweb in production.

What is Pgweb?

Pgweb is a web-based PostgreSQL client offering:

Web-Based Interface: No client installation needed, access from any browser
Database Exploration: Browse tables, schemas, and database structure
Query Builder: Execute SQL queries and view results
Data Management: Insert, update, and delete records through the web UI
Export Features: Export query results in multiple formats
No Dependencies: Lightweight Go application with minimal resource usage
Zero Configuration: Works immediately with basic PostgreSQL connection
Security: Can be deployed with authentication and HTTPS
Cross-Platform: Runs on Linux, macOS, and Windows
Production Ready: Used in production by organizations worldwide

Prerequisites

Before deploying Pgweb on Klutch.sh, ensure you have:

Klutch.sh account with project creation permissions
GitHub account for repository management
PostgreSQL database that Pgweb will connect to (can be on Klutch.sh or external)
Database credentials (username, password, host, port, database name)
Docker (optional, only if using Dockerfile deployment method)

Getting Started: Understanding Pgweb

Key Concepts

Database Connection: Pgweb connects to a PostgreSQL database via a connection string (DSN)
Connection URL Format: postgres://username:password@host:port/database
Port Configuration: Pgweb default port is 8081
Web Interface: Access Pgweb through a web browser at http://host:port

System Requirements

CPU: Minimal (100m is sufficient)
Memory: Minimal (32-64MB is sufficient)
Disk: Only needs space for logs (persistent storage not required unless storing connections)
Network: Outbound access to your PostgreSQL database

Deploying Without a Dockerfile (Using Nixpacks)

Klutch.sh uses Nixpacks to automatically detect, build, and deploy Go applications. This method requires minimal configuration—simply push your code to GitHub and let Klutch.sh handle the rest.

Prepare Your Repository

Create a new GitHub repository for your Pgweb deployment:

mkdir my-pgweb
cd my-pgweb
git init

Create a main.go file that wraps Pgweb with custom configuration (optional):

package main

import (
    "fmt"
    "os"
    "os/exec"
)

func main() {
    // Get database URL from environment
    dbURL := os.Getenv("DATABASE_URL")
    if dbURL == "" {
        fmt.Println("ERROR: DATABASE_URL environment variable not set")
        os.Exit(1)
    }

    // Get port from environment or default to 8081
    port := os.Getenv("PORT")
    if port == "" {
        port = "8081"
    }

    // Get bind address (default to 0.0.0.0 for Klutch.sh)
    bindAddr := os.Getenv("BIND_ADDR")
    if bindAddr == "" {
        bindAddr = "0.0.0.0"
    }

    // Build pgweb command
    cmd := exec.Command("pgweb",
        "--bind", bindAddr,
        "--port", port,
        "--url", dbURL,
    )

    // Run pgweb
    if err := cmd.Run(); err != nil {
        fmt.Printf("Error starting pgweb: %v\n", err)
        os.Exit(1)
    }
}

Create a go.mod file:

module my-pgweb

go 1.20

require github.com/sosedoff/pgweb v0.15.0

Create a .gitignore file:

/bin
/dist
.DS_Store
*.log

Push your repository to GitHub:

git add .
git commit -m "Initial Pgweb deployment"
git branch -M main
git remote add origin https://github.com/your-username/my-pgweb.git
git push -u origin main

Deploy via Nixpacks

Log in to your Klutch.sh dashboard.
Create a new project by selecting your desired organization and naming your project (e.g., “Database Browser”).
Create a new app within your project with the following configuration:
- Repository: Select your Pgweb GitHub repository
- Branch: Select the branch to deploy (typically main or master)
- Traffic Type: Select HTTP (Pgweb serves HTTP traffic)
- Internal Port: Enter 8081 (the default port Pgweb listens on)
- Region: Choose the region closest to your users
- Compute: Select small compute resources (Pgweb is lightweight)
- Instances: 1 instance is sufficient
- Environment Variables: Add DATABASE_URL with your PostgreSQL connection string
Review your configuration and click “Create” to deploy. Klutch.sh will automatically:
- Detect your Go application
- Install Pgweb
- Build and deploy your application
Monitor the deployment progress in the Klutch.sh dashboard. Once deployment is complete, your application will be available at a URL like example-app.klutch.sh.

Environment Variables for Nixpacks

If you need to customize Pgweb’s behavior, set these environment variables in the Klutch.sh dashboard:

DATABASE_URL=postgres://username:password@host:port/database
PORT=8081
BIND_ADDR=0.0.0.0

For advanced customization with Nixpacks, you can set:

BUILD_COMMAND=go build -o pgweb main.go
START_COMMAND=./pgweb

Deploying With a Dockerfile

If you prefer complete control over your deployment environment, you can use a Dockerfile. Klutch.sh automatically detects and uses a Dockerfile if it exists in your repository’s root directory.

Using the Official Pgweb Docker Image

Create a Dockerfile in your project root:

# Use official Pgweb image
FROM sosedoff/pgweb:latest

# Expose port (must match Pgweb's listening port)
EXPOSE 8081

# Health check
HEALTHCHECK --interval=30s --timeout=3s --start-period=5s --retries=3 \
    CMD wget --no-verbose --tries=1 --spider http://localhost:8081/api/health || exit 1

# Set environment variables
ENV BIND_ADDR=0.0.0.0
ENV PORT=8081

# Run Pgweb with environment variables
CMD pgweb --bind=$BIND_ADDR --port=$PORT --url=$DATABASE_URL

Create a .dockerignore file:

.git
.gitignore
README.md

Push your code to GitHub (including the Dockerfile):

git add Dockerfile .dockerignore
git commit -m "Add Dockerfile for production deployment"
git push

Log in to your Klutch.sh dashboard.
Create a new project (or use an existing one).
Create a new app with the following settings:
- Repository: Select your Pgweb GitHub repository
- Branch: Select your deployment branch
- Traffic Type: Select HTTP (Pgweb serves HTTP traffic)
- Internal Port: Enter 8081 (the port Pgweb listens on)
- Region, Compute, and Instances: Configure according to your needs
- Environment Variables: Add DATABASE_URL with your PostgreSQL connection string
Click “Create” to deploy. Klutch.sh will automatically:
- Detect your Dockerfile
- Build your Docker image
- Deploy the containerized application to your Klutch.sh infrastructure
Once deployment is complete, your application will be accessible at a URL like example-app.klutch.sh.

Building Pgweb from Source with Docker

For advanced deployments, build Pgweb from source:

# Stage 1: Build
FROM golang:1.20-alpine AS builder

WORKDIR /app

# Clone Pgweb repository
RUN apk add --no-cache git && \
    git clone https://github.com/sosedoff/pgweb.git .

# Build the application
RUN CGO_ENABLED=0 GOOS=linux go build -ldflags="-s -w" -o pgweb .

# Stage 2: Runtime
FROM alpine:latest

RUN apk --no-cache add ca-certificates

WORKDIR /app

COPY --from=builder /app/pgweb .

EXPOSE 8081
ENV PORT=8081
ENV BIND_ADDR=0.0.0.0

CMD ["./pgweb", "--bind=${BIND_ADDR}", "--port=${PORT}", "--url=${DATABASE_URL}"]

Environment Variables and Configuration

Essential Environment Variables

Set these in your Klutch.sh app configuration:

DATABASE_URL=postgres://username:password@host:port/database
PORT=8081
BIND_ADDR=0.0.0.0

Optional Pgweb Configuration Environment Variables

# Enable basic authentication
PGWEB_AUTH_USER=admin
PGWEB_AUTH_PASS=securepassword

# Connection pool settings
PGWEB_ROWS_LIMIT=1000

# SSL Mode (disable|allow|prefer|require)
PGWEB_SSL_MODE=require

Example Connection Strings

Local Development:

postgres://postgres:password@localhost:5432/mydb

Remote PostgreSQL on Klutch.sh:

postgres://username:password@postgres-instance.klutch.sh:5432/mydb

Cloud PostgreSQL (AWS RDS, Azure Database, etc.):

postgres://username:password@db-instance.region.rds.amazonaws.com:5432/mydb

Accessing Pgweb

Once deployed, access Pgweb through your browser:

Navigate to your app URL (e.g., https://example-app.klutch.sh)
You should see the Pgweb login interface or database browser
If not connected, you’ll be prompted to enter your database connection details

Common Operations in Pgweb

Browse Tables:

Click on a table name in the left sidebar to view its contents
See column names, types, and data in a grid format

Execute Queries:

Use the query editor to write custom SQL
Click “Run” to execute the query
View results in the results panel

View Table Structure:

Click the table name to see schema information
View column definitions and data types

Export Data:

Select results and export in CSV, JSON, or other formats
Useful for data analysis and backups

Security Best Practices

Implement these security measures for production Pgweb deployments:

Use HTTPS: Klutch.sh provides automatic HTTPS. Always use HTTPS in production.
Secure Database Credentials: Store database credentials as environment variables in Klutch.sh, never hardcode in your application.
Authentication: Consider implementing basic authentication or running behind a reverse proxy that requires authentication.
Network Security: If your PostgreSQL database is external, ensure it’s accessible only from your Pgweb instance.
SSL Connections to PostgreSQL: Use SSL mode require or prefer when connecting to PostgreSQL:

DATABASE_URL=postgres://user:pass@host:5432/db?sslmode=require

Limit Data Exposure: Restrict which tables and schemas Pgweb users can access by using PostgreSQL role-based access control.
Monitoring: Enable monitoring in Klutch.sh to track resource usage and access patterns.
Regular Updates: Keep Pgweb updated to get security patches and improvements.

Custom Domain Configuration

To access your Pgweb instance on a custom domain:

In the Klutch.sh dashboard, navigate to your application settings.
Find the Domains or Custom Domain section.
Add your custom domain (e.g., db-browser.mycompany.com).
Update your domain’s DNS records to point to Klutch.sh:
- Add a CNAME record pointing to your Klutch.sh app URL
- Or add A records pointing to the IP address provided by Klutch.sh
Verify the domain ownership through Klutch.sh’s verification process.
Once verified, your Pgweb instance will be accessible at your custom domain with automatic HTTPS.

Monitoring and Logging

Monitor your Pgweb deployment through Klutch.sh’s built-in tools:

In the Klutch.sh dashboard, view your application’s Logs to troubleshoot connection issues or errors.
Check Metrics for CPU usage and memory consumption. Pgweb typically uses minimal resources.
Set up Alerts to be notified if the application becomes unavailable.
Monitor database query performance through Pgweb’s interface or PostgreSQL logs.

Health Checks

Pgweb provides a health check endpoint useful for monitoring:

curl https://example-app.klutch.sh/api/health

Troubleshooting Common Issues

Cannot Connect to Database

Problem: Pgweb shows “Connection refused” or “Connection failed” errors.

Solution:

Verify the DATABASE_URL environment variable is set correctly
Ensure your PostgreSQL database is accessible from Klutch.sh
Check database credentials (username, password, host, port)
Confirm the database name exists
If using an external database, verify network connectivity

502 Bad Gateway Errors

Problem: Pgweb returns 502 Bad Gateway errors.

Solution:

Check application logs in Klutch.sh dashboard
Verify DATABASE_URL is valid and database is accessible
Confirm internal port is set to 8081
Restart the application deployment

Slow Performance

Problem: Pgweb interface is slow or unresponsive.

Solution:

Check network connectivity to PostgreSQL database
Review PostgreSQL query performance
Reduce row limits if querying large tables
Increase compute resources if needed

SSL/TLS Errors

Problem: SSL certificate errors when connecting to PostgreSQL.

Solution:

Use sslmode=disable for development (not recommended for production)
Use sslmode=require or sslmode=prefer for production
Ensure PostgreSQL certificate is valid and trusted
Update your Klutch.sh app’s compute resources if needed

Best Practices for Production Pgweb Deployments

Use Environment Variables: Store all configuration in Klutch.sh environment variables, never hardcode credentials.
Enable HTTPS: Always use HTTPS in production. Klutch.sh provides automatic HTTPS.
Implement Authentication: Use PostgreSQL role-based access control or a reverse proxy for user authentication.
Secure Database Access: Use SSL connections to PostgreSQL (sslmode=require).
Minimize Resource Usage: Pgweb is lightweight; allocate minimal compute resources.
Monitor Access: Enable logging and monitoring to track who accesses your database.
Regular Backups: Don’t rely on Pgweb for data management; implement regular PostgreSQL backups.
Limit Query Results: Use reasonable row limits to prevent overwhelming the web interface.
Version Control: Track your Pgweb deployment configuration in Git.
Keep Updated: Regularly update Pgweb to get security patches and new features.

Resources and Further Reading

Conclusion

Deploying Pgweb on Klutch.sh provides a simple, web-based interface for managing your PostgreSQL databases. Whether you choose the Nixpacks deployment method for simplicity or the Docker method for complete control, Klutch.sh makes it easy to access your databases from anywhere.

With automatic HTTPS, environment variable configuration, and integrated monitoring, you can deploy a production-ready Pgweb instance in minutes. Use this guide as a reference for deploying, configuring, and maintaining your Pgweb instance.

For additional support or questions about deploying Pgweb, visit the Klutch.sh website or consult the Pgweb documentation.