PostgreSQL with pgvector Docker Setup

This repository contains a Docker Compose configuration for deploying PostgreSQL with pgvector extension, optimized for Dokploy deployment.

Features

• PostgreSQL 16 with pgvector extension
• Persistent data storage using Docker volumes
• Health checks for monitoring
• Environment-based configuration
• Initialization scripts for database setup
• Ready for Dokploy deployment

Quick Start

1. Copy the environment file:

   cp .env.example .env

2. Edit the .env file with your desired configuration:

   • Set a secure password for POSTGRES_PASSWORD
   • Adjust database name and user if needed
   • Change port mapping if required

3. Run the container:

   docker-compose up -d

Configuration Options

Variable	Default	Description
POSTGRES_DB	postgres	Database name
POSTGRES_USER	postgres	Database user
POSTGRES_PASSWORD	postgres	Database password (change this!)
POSTGRES_PORT	5432	External port mapping

Deployment to Dokploy

Prerequisites

• Dokploy instance running
• Git repository with this code pushed
• Environment variables configured in Dokploy

Deployment Steps

1. Push to Git Repository

   • Ensure all files are committed to your Git repository
   • Push to your preferred Git provider (GitHub, GitLab, etc.)

2. Create Application in Dokploy

   • Log in to your Dokploy instance
   • Click "New Application"
   • Select "Git" as the source
   • Connect your Git repository
   • Select the branch containing this code

3. Configure Environment Variables

   • In Dokploy, go to the "Environment" tab
   • Add the following environment variables:

     POSTGRES_DB=your_db_name
     POSTGRES_USER=your_username
     POSTGRES_PASSWORD=your_secure_password
     POSTGRES_PORT=5432
     

4. Set Resource Limits (Optional)

   • Go to "Resources" tab
   • Set appropriate CPU and memory limits based on your needs

5. Configure Persistent Storage (Optional)

   • Go to "Volumes" tab
   • Add a persistent volume for /var/lib/postgresql/data to ensure data persistence

6. Deploy

   • Click "Deploy" to start the deployment
   • Wait for the deployment to complete
   • Check logs to ensure PostgreSQL starts correctly

Using pgvector

Once deployed, you can use pgvector in your SQL queries:

-- Create a table with vector column
CREATE TABLE documents (
    id bigserial PRIMARY KEY,
    content text,
    embedding vector(1536)
);

-- Insert data with embeddings
INSERT INTO documents (content, embedding)
VALUES ('Hello world', '[0.1, 0.2, 0.3, ...]');

-- Perform similarity search
SELECT content, 1 - (embedding <=> '[0.1, 0.2, 0.3, ...]') AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, 0.3, ...]'
LIMIT 10;

Health Check

The container includes a health check that runs every 30 seconds:

• Test: pg_isready -U postgres -d postgres
• Timeout: 10 seconds
• Retries: 3
• Start period: 60 seconds

Volumes

• postgres_data: Persistent storage for PostgreSQL data files
• ./init-scripts: Mounted to /docker-entrypoint-initdb.d for initialization scripts

Port Mapping

• Default: 5432:5432 (host:container)
• Configurable via POSTGRES_PORT environment variable

Connecting to the Database

• Host: localhost (or your server IP)
• Port: 5432 (or configured port)
• Database: ${POSTGRES_DB}
• User: ${POSTGRES_USER}
• Password: ${POSTGRES_PASSWORD}

Connection URL format:

postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@localhost:${POSTGRES_PORT}/${POSTGRES_DB}

Maintenance

Backup Database

docker-compose exec postgres pg_dump -U postgres postgres > backup.sql

Restore Database

docker-compose exec -T postgres psql -U postgres postgres < backup.sql

Access PostgreSQL Shell

docker-compose exec postgres psql -U postgres -d postgres

Security Notes

• Always change the default password in production
• Consider using SSL connections in production
• Regularly update to the latest PostgreSQL version
• Use Dokploy's built-in networking features to secure database access

Troubleshooting

1. Database not starting: Check logs for authentication errors
2. Connection refused: Verify port mapping and firewall settings
3. Data loss: Ensure persistent volumes are properly configured
4. Performance issues: Monitor resource usage and adjust limits in Dokploy