Skip to content
Klutch.sh Docs

Deploying a Postgres Database

Introduction

PostgreSQL (commonly known as Postgres) is a powerful, open-source object-relational database management system (ORDBMS) that has earned a strong reputation for reliability, data integrity, and extensibility. First released in 1996, PostgreSQL has evolved into one of the most advanced and feature-rich database systems available today.

PostgreSQL is known for its:

  • ACID Compliance: Ensures data integrity through atomicity, consistency, isolation, and durability
  • Advanced Data Types: Native support for JSON, JSONB, arrays, hstore, and custom types
  • Extensibility: Custom functions, operators, and data types can be added
  • Performance: Advanced indexing options (B-tree, Hash, GiST, GIN, BRIN) and query optimization
  • Scalability: Handles workloads from small applications to data warehouses with terabytes of data
  • Standards Compliance: Strong adherence to SQL standards with robust support for complex queries

This comprehensive guide walks you through deploying a PostgreSQL database on Klutch.sh using Docker, including detailed installation steps, sample configurations, and production-ready best practices for persistent storage.

Prerequisites

Before you begin, ensure you have the following:

Installation and Setup

Step 1: Create Your Project Directory

First, create a new directory for your PostgreSQL deployment project:

Terminal window
mkdir postgres-klutch
cd postgres-klutch
git init

Step 2: Create the Dockerfile

Create a Dockerfile in your project root directory. This will define your PostgreSQL container configuration:

FROM postgres:16-alpine


# Set default environment variables
# These can be overridden in the Klutch.sh dashboard
ENV POSTGRES_DB=mydb
ENV POSTGRES_USER=myuser
ENV POSTGRES_PASSWORD=mypassword


# Expose the default PostgreSQL port
EXPOSE 5432


# Optional: Copy custom initialization scripts
# Initialization scripts placed in /docker-entrypoint-initdb.d/
# will be executed automatically when the container starts
# COPY ./init.sql /docker-entrypoint-initdb.d/
# COPY ./init.sh /docker-entrypoint-initdb.d/

Note: The PostgreSQL Alpine image is lightweight and recommended for production deployments.

Step 3: (Optional) Create Initialization Scripts

You can create SQL scripts that will run automatically when the database is first initialized. Create a file named init.sql:

-- init.sql - Database initialization script
-- This script runs automatically on first startup


-- Create a sample table
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- Create an index
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);


-- Insert sample data (optional)
INSERT INTO users (username, email) VALUES
    ('admin', 'admin@example.com'),
    ('testuser', 'test@example.com')
ON CONFLICT DO NOTHING;

If you create initialization scripts, uncomment the COPY line in your Dockerfile to include them.

Step 4: Test Locally (Optional)

Before deploying to Klutch.sh, you can test your PostgreSQL setup locally:

Terminal window
# Build the Docker image
docker build -t my-postgres .


# Run the container
docker run -d \
  --name postgres-test \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD=mysecretpassword \
  my-postgres


# Connect to test the database
docker exec -it postgres-test psql -U myuser -d mydb


# Stop and remove the test container when done
docker stop postgres-test
docker rm postgres-test

Step 5: Push to GitHub

Commit your Dockerfile and any initialization scripts to your GitHub repository:

Terminal window
git add Dockerfile init.sql
git commit -m "Add PostgreSQL Dockerfile and initialization scripts"
git remote add origin https://github.com/yourusername/postgres-klutch.git
git push -u origin main

Connecting to PostgreSQL

Once deployed, you can connect to your PostgreSQL database from any application using a connection string. Since Klutch.sh routes TCP traffic through port 8000, use the following format:

Connection String

postgresql://myuser:mypassword@example-app.klutch.sh:8000/mydb

Replace:

  • myuser with your database username
  • mypassword with your database password
  • example-app.klutch.sh with your actual Klutch.sh app URL
  • mydb with your database name

Example Connection Code

Node.js (using pg library):

const { Client } = require('pg');


const client = new Client({
  host: 'example-app.klutch.sh',
  port: 8000,
  user: 'myuser',
  password: 'mypassword',
  database: 'mydb',
});


client.connect()
  .then(() => console.log('Connected to PostgreSQL'))
  .catch(err => console.error('Connection error', err));

Python (using psycopg2):

import psycopg2


conn = psycopg2.connect(
    host="example-app.klutch.sh",
    port=8000,
    user="myuser",
    password="mypassword",
    database="mydb"
)


cursor = conn.cursor()
cursor.execute("SELECT version();")
print(cursor.fetchone())

Go (using lib/pq):

import (
    "database/sql"
    _ "github.com/lib/pq"
)


connStr := "host=example-app.klutch.sh port=8000 user=myuser password=mypassword dbname=mydb sslmode=disable"
db, err := sql.Open("postgres", connStr)

Deploying to Klutch.sh

Now that your PostgreSQL project is ready and pushed to GitHub, follow these steps to deploy it on Klutch.sh with persistent storage.

Deployment Steps

    1. Log in to Klutch.sh

      Navigate to klutch.sh/app and sign in to your account.

    2. Create a New Project

      Go to Create Project and give your project a meaningful name (e.g., “PostgreSQL Database”).

    3. Create a New App

      Navigate to Create App and configure the following settings:

    4. Select Your Repository

      • Choose GitHub as your Git source
      • Select the repository containing your Dockerfile
      • Choose the branch you want to deploy (usually main or master)

    5. Configure Traffic Type

      • Traffic Type: Select TCP (PostgreSQL requires TCP traffic for database connections)
      • Internal Port: Set to 5432 (the default PostgreSQL port that your container listens on)

    6. Set Environment Variables

      Add the following environment variables for your PostgreSQL configuration:

      • POSTGRES_DB: The name of your database (e.g., mydb)
      • POSTGRES_USER: The database username (e.g., myuser)
      • POSTGRES_PASSWORD: A strong password for your database user (use a secure password generator)
      • POSTGRES_HOST_AUTH_METHOD: (Optional) Set to md5 or scram-sha-256 for enhanced security

      Security Note: Always use strong, unique passwords for production databases.

    7. Attach a Persistent Volume

      This is critical for ensuring your database data persists across deployments and restarts:

      • In the Volumes section, click “Add Volume”
      • Mount Path: Enter /var/lib/postgresql/data (this is where PostgreSQL stores its data files)
      • Size: Choose an appropriate size based on your expected data volume (e.g., 10GB, 20GB, etc.)

      Important: PostgreSQL requires persistent storage to maintain your data between container restarts.

    8. Configure Additional Settings

      • Region: Select the region closest to your users for optimal latency
      • Compute Resources: Choose CPU and memory based on your workload (minimum 512MB RAM recommended)
      • Instances: Start with 1 instance (databases typically don’t need horizontal scaling)

    9. Deploy Your Database

      Click “Create” to start the deployment. Klutch.sh will:

      • Automatically detect your Dockerfile in the repository root
      • Build the Docker image
      • Attach the persistent volume
      • Start your PostgreSQL container
      • Assign a URL for external connections

    10. Access Your Database

      Once deployment is complete, you’ll receive a URL like example-app.klutch.sh. You can connect to your PostgreSQL database using this URL on port 8000:

      postgresql://myuser:mypassword@example-app.klutch.sh:8000/mydb

Production Best Practices

Security Recommendations

  • Use Strong Passwords: Never use default passwords in production. Generate strong, random passwords using a password manager.
  • Environment Variables: Store sensitive credentials as environment variables in Klutch.sh, not in your Dockerfile.
  • SSL Connections: For production, consider configuring SSL/TLS for encrypted database connections.
  • Regular Backups: Implement a backup strategy using PostgreSQL’s pg_dump or continuous archiving.

Performance Optimization

  • Proper Indexing: Create indexes on frequently queried columns.
  • Connection Pooling: Use connection pooling libraries (like pgBouncer) for applications with many concurrent connections.
  • Resource Allocation: Monitor your database performance and adjust compute resources as needed.
  • Regular Maintenance: Run VACUUM and ANALYZE regularly to maintain database performance.

Monitoring

Monitor your PostgreSQL database for:

  • Connection counts
  • Query performance (slow queries)
  • Disk usage and I/O patterns
  • CPU and memory utilization
  • Database size growth

Troubleshooting

Cannot Connect to Database

  • Verify that you’re using the correct connection string with port 8000
  • Ensure your environment variables are set correctly in Klutch.sh
  • Check that the internal port is set to 5432 in your app configuration

Database Not Persisting Data

  • Verify that the persistent volume is correctly attached at /var/lib/postgresql/data
  • Check that the volume has sufficient space allocated

Performance Issues

  • Review your database queries for optimization opportunities
  • Consider increasing compute resources (CPU/memory)
  • Ensure proper indexes are created on your tables

Additional Resources

Conclusion

Deploying PostgreSQL to Klutch.sh with Docker provides a robust, scalable database solution with persistent storage. By following this guide, you’ve set up a production-ready PostgreSQL database with proper data persistence, security configurations, and connection capabilities. Your database is now ready to support your applications with reliable, high-performance data storage.