PostgreSQL

​

Creating a PostgreSQL Database

1. Navigate to your project
2. Select the environment (e.g., Production, Staging)
3. Click “Add Service” and select “PostgreSQL”
4. Configure the following settings:

​

Basic Configuration

• Name - A friendly name for your database
• App Name - The Docker service name (used for internal DNS)
• Description - Optional description
• Docker Image - Default: postgres:latest (recommended to use specific version like postgres:16)

​

Database Credentials

• Database Name - Name of the database to create
• Database User - Username for database access
• Database Password - Password for the user

​

Advanced Settings

• External Port - Optional port to expose PostgreSQL externally (default internal port: 5432)
• Command - Override the default container command
• Args - Additional arguments to pass to PostgreSQL
• Environment Variables - Additional custom environment variables

​

Connection Strings

​

Internal Connection (from other services)

postgresql://[user]:[password]@[appName]:5432/[database]

postgresql://myuser:mypassword@my-postgres:5432/myapp

​

External Connection

postgresql://[user]:[password]@[host]:[externalPort]/[database]

postgresql://myuser:[email protected]:5433/myapp

​

Connection String Formats

postgresql://username:password@hostname:5432/database

psql -h hostname -p 5432 -U username -d database

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

const client = new Client({
  host: 'my-postgres',
  port: 5432,
  user: 'myuser',
  password: 'mypassword',
  database: 'myapp'
});

import psycopg2

conn = psycopg2.connect(
    host="my-postgres",
    port=5432,
    user="myuser",
    password="mypassword",
    database="myapp"
)

​

Environment Variables

​

Data Persistence

• PostgreSQL 14+: /var/lib/postgresql/data
• Older versions may use different paths

​

Resource Configuration

Memory Limit: 2GB        # Maximum memory
Memory Reservation: 1GB   # Guaranteed memory
CPU Limit: 2.0           # Maximum CPU cores
CPU Reservation: 0.5     # Guaranteed CPU cores

​

Operations

​

Starting the Database

POST /api/postgres/start
{
  "postgresId": "your-postgres-id"
}

​

Stopping the Database

POST /api/postgres/stop
{
  "postgresId": "your-postgres-id"
}

​

Reloading the Database

POST /api/postgres/reload
{
  "postgresId": "your-postgres-id"
}

​

Rebuilding the Database

POST /api/postgres/rebuild
{
  "postgresId": "your-postgres-id"
}

​

Accessing PostgreSQL CLI

docker exec -it $(docker ps -qf "name=my-postgres") psql -U myuser -d myapp

​

Common PostgreSQL Commands

-- List all databases
\l

-- Connect to database
\c database_name

-- List all tables
\dt

-- Describe table
\d table_name

-- Show current database
SELECT current_database();

-- Show current user
SELECT current_user;

-- Create database
CREATE DATABASE new_database;

-- Create user
CREATE USER new_user WITH PASSWORD 'password';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;

​

Backups

pg_dump -U username -d database > backup.sql

​

Troubleshooting

​

Connection Refused

• Verify the service is running: Check the status in Dokploy UI
• Check the service name: Use the exact appName for internal connections
• Verify network: Ensure your application is in the same Docker network

​

Authentication Failed

• Verify credentials in the database settings
• Check environment variables are correctly set
• Try recreating the database with correct credentials

​

Out of Memory

• Increase memory limits in resource configuration
• Check PostgreSQL logs for memory-related errors
• Consider optimizing queries or adding more resources

​

Data Volume Issues

• Check volume mounts in Docker: docker volume ls
• Inspect volume: docker volume inspect {appName}-data
• Verify mount path matches PostgreSQL version

​

Best Practices

1. Use Specific Versions - Pin to specific PostgreSQL versions (e.g., postgres:16.1) instead of latest
2. Strong Passwords - Use strong, randomly generated passwords for production
3. Regular Backups - Configure automated backups and test restoration
4. Monitor Resources - Watch memory and CPU usage, adjust limits as needed
5. Connection Pooling - Use connection pooling in your applications (e.g., pgBouncer)
6. Read Replicas - For high-traffic applications, consider read replicas
7. Maintenance Windows - Schedule regular maintenance for updates and optimization

​

Performance Tuning

# Shared buffers (25% of memory)
shared_buffers=512MB

# Work memory per operation
work_mem=16MB

# Maintenance work memory
maintenance_work_mem=128MB

# Effective cache size (50-75% of memory)
effective_cache_size=2GB

# Max connections
max_connections=100

​

Next Steps