Database Setup

Postiz requires two database services: PostgreSQL for persistent data storage and Redis for caching and session management.

PostgreSQL Setup

Postiz uses PostgreSQL as its primary database with Prisma as the ORM.

Docker Compose (Recommended)

The easiest way to run PostgreSQL is using the included Docker Compose configuration:

docker-compose.yaml

postiz-postgres:
  image: postgres:17-alpine
  container_name: postiz-postgres
  restart: always
  environment:
    POSTGRES_PASSWORD: postiz-password
    POSTGRES_USER: postiz-user
    POSTGRES_DB: postiz-db-local
  volumes:
    - postgres-volume:/var/lib/postgresql/data
  networks:
    - postiz-network
  healthcheck:
    test: pg_isready -U postiz-user -d postiz-db-local
    interval: 10s
    timeout: 3s
    retries: 3

Production Security: Change the default credentials!

environment:
  POSTGRES_PASSWORD: your-strong-random-password
  POSTGRES_USER: your-username
  POSTGRES_DB: postiz-production

Standalone PostgreSQL Installation

If you prefer to run PostgreSQL outside of Docker:

Install PostgreSQL

sudo apt update
sudo apt install postgresql postgresql-contrib

Create Database and User

sudo -u postgres psql

CREATE DATABASE postiz;
CREATE USER postiz_user WITH ENCRYPTED PASSWORD 'your-secure-password';
GRANT ALL PRIVILEGES ON DATABASE postiz TO postiz_user;
\q

Configure Connection

Update your .env file:

DATABASE_URL="postgresql://postiz_user:your-secure-password@localhost:5432/postiz"

PostgreSQL Version

Postiz is tested with PostgreSQL 17, but should work with PostgreSQL 12+.

Recommended version: PostgreSQL 17 Alpine (for Docker deployments)

Connection String Format

The PostgreSQL connection string follows this format:

postgresql://[user]:[password]@[host]:[port]/[database]?[options]

Examples:

# Docker Compose
DATABASE_URL="postgresql://postiz-user:postiz-password@postiz-postgres:5432/postiz-db-local"

# Localhost
DATABASE_URL="postgresql://postiz_user:password@localhost:5432/postiz"

# Remote Server
DATABASE_URL="postgresql://user:[email protected]:5432/postiz"

# With SSL
DATABASE_URL="postgresql://user:[email protected]:5432/postiz?sslmode=require"

# With connection pooling
DATABASE_URL="postgresql://user:password@localhost:5432/postiz?connection_limit=10"

Database Configuration

Connection Pooling

For high-traffic deployments, consider using PgBouncer:

pgbouncer.ini

[databases]
postiz = host=localhost port=5432 dbname=postiz

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Database Migrations

Postiz uses Prisma for database migrations. The application automatically runs migrations on startup.

No manual migration steps are required. Postiz handles schema creation and updates automatically.

Manual Migration (Advanced)

If you need to run migrations manually:

# Inside the Postiz container
docker compose exec postiz npx prisma migrate deploy

# Check migration status
docker compose exec postiz npx prisma migrate status

Backup and Restore

Backup Database

# Backup to file
docker compose exec postiz-postgres pg_dump -U postiz-user postiz-db-local > backup.sql

# Backup with compression
docker compose exec postiz-postgres pg_dump -U postiz-user postiz-db-local | gzip > backup.sql.gz

# Backup to custom format (recommended)
docker compose exec postiz-postgres pg_dump -U postiz-user -Fc postiz-db-local > backup.dump

Restore Database

# From SQL file
docker compose exec -T postiz-postgres psql -U postiz-user postiz-db-local < backup.sql

# From compressed file
gunzip -c backup.sql.gz | docker compose exec -T postiz-postgres psql -U postiz-user postiz-db-local

# From custom format
docker compose exec postiz-postgres pg_restore -U postiz-user -d postiz-db-local backup.dump

Automated Backups

Create a backup script:

backup.sh

#!/bin/bash
BACKUP_DIR="/backups/postiz"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p "$BACKUP_DIR"

# Backup database
docker compose exec -T postiz-postgres pg_dump -U postiz-user -Fc postiz-db-local > "$BACKUP_DIR/postiz_$DATE.dump"

# Keep only last 30 days
find "$BACKUP_DIR" -name "postiz_*.dump" -mtime +30 -delete

echo "Backup completed: postiz_$DATE.dump"

Schedule with cron:

# Daily backup at 2 AM
0 2 * * * /path/to/backup.sh >> /var/log/postiz-backup.log 2>&1

Redis Setup

Redis is used for caching, session storage, and job queues.

Docker Compose (Recommended)

docker-compose.yaml

postiz-redis:
  image: redis:7.2
  container_name: postiz-redis
  restart: always
  healthcheck:
    test: redis-cli ping
    interval: 10s
    timeout: 3s
    retries: 3
  volumes:
    - postiz-redis-data:/data
  networks:
    - postiz-network

Standalone Redis Installation

Install Redis

sudo apt update
sudo apt install redis-server

Configure Redis

Edit /etc/redis/redis.conf:

# Bind to localhost only (for security)
bind 127.0.0.1

# Enable persistence
save 900 1
save 300 10
save 60 10000

# Set max memory
maxmemory 256mb
maxmemory-policy allkeys-lru

Configure Connection

Update your .env file:

REDIS_URL="redis://localhost:6379"

Redis Connection String

The Redis connection string format:

redis://[password@]host[:port][/database]

Examples:

# Docker Compose
REDIS_URL="redis://postiz-redis:6379"

# Localhost
REDIS_URL="redis://localhost:6379"

# With password
REDIS_URL="redis://password@localhost:6379"

# Specific database
REDIS_URL="redis://localhost:6379/0"

# Redis Sentinel
REDIS_URL="redis://sentinel-host:26379?sentinelMasterId=mymaster"

Redis Configuration

Production Settings

For production deployments, configure Redis with these settings:

redis.conf

# Security
requirepass your-strong-redis-password

# Persistence
save 900 1
save 300 10
save 60 10000
appendonly yes
appendfsync everysec

# Memory Management
maxmemory 512mb
maxmemory-policy allkeys-lru

# Performance
tcp-backlog 511
timeout 0
tcp-keepalive 300

# Replication (if using replicas)
repl-diskless-sync yes
repl-diskless-sync-delay 5

High Availability with Redis Sentinel

For production deployments requiring high availability:

sentinel.conf

sentinel monitor postiz-master 127.0.0.1 6379 2
sentinel auth-pass postiz-master your-redis-password
sentinel down-after-milliseconds postiz-master 5000
sentinel parallel-syncs postiz-master 1
sentinel failover-timeout postiz-master 10000

Redis Monitoring

Check Redis Status

# Docker Compose
docker compose exec postiz-redis redis-cli ping
docker compose exec postiz-redis redis-cli info

# Standalone
redis-cli ping
redis-cli info

Monitor Memory Usage

redis-cli info memory

View Connected Clients

redis-cli client list

Database Monitoring

PostgreSQL Monitoring

Check Active Connections

SELECT count(*) FROM pg_stat_activity;

View Database Size

SELECT pg_size_pretty(pg_database_size('postiz'));

Check Table Sizes

SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

Monitor Query Performance

SELECT 
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

Health Checks

The Docker Compose configuration includes automatic health checks:

# PostgreSQL health check
healthcheck:
  test: pg_isready -U postiz-user -d postiz-db-local
  interval: 10s
  timeout: 3s
  retries: 3

# Redis health check
healthcheck:
  test: redis-cli ping
  interval: 10s
  timeout: 3s
  retries: 3

Postiz won’t start until both databases are healthy.

Troubleshooting

PostgreSQL Issues

Connection Refused

# Check if PostgreSQL is running
docker compose ps postiz-postgres

# Check PostgreSQL logs
docker compose logs postiz-postgres

# Verify network connectivity
docker compose exec postiz ping postiz-postgres

Authentication Failed

Ensure the DATABASE_URL credentials match the PostgreSQL environment variables.

# Test connection manually
docker compose exec postiz-postgres psql -U postiz-user -d postiz-db-local

Out of Disk Space

# Check volume usage
docker system df -v

# Clean up old data
docker volume prune

Redis Issues

Connection Refused

# Check if Redis is running
docker compose ps postiz-redis

# Check Redis logs
docker compose logs postiz-redis

# Test connection
docker compose exec postiz-redis redis-cli ping

Out of Memory

# Check memory usage
docker compose exec postiz-redis redis-cli info memory

# Flush cache (caution: clears all data)
docker compose exec postiz-redis redis-cli FLUSHALL

High Memory Usage

Configure memory limits:

docker-compose.yaml

postiz-redis:
  command: redis-server --maxmemory 512mb --maxmemory-policy allkeys-lru

Performance Tuning

PostgreSQL Optimization

Analyze Slow Queries

Enable query logging:

postgresql.conf

log_min_duration_statement = 1000  # Log queries slower than 1s
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

Add Indexes

Postiz creates necessary indexes automatically, but you can add custom indexes:

CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
CREATE INDEX idx_posts_user_id ON posts(user_id);

Vacuum Database

Regular maintenance:

VACUUM ANALYZE;

Redis Optimization

Use maxmemory-policy allkeys-lru to evict old keys automatically
Enable persistence with appendonly yes for data durability
Monitor memory usage and adjust maxmemory as needed
Use Redis Sentinel for high availability

Next Steps

Configure Authentication

Set up OAuth authentication for social media platforms

Configure Storage

Set up storage configuration for media uploads

Review Environment Variables

Check the complete environment variables reference

Get Started

Core Features

Social Platforms

Self-Hosting

Configuration

​Database Setup

​PostgreSQL Setup

​Docker Compose (Recommended)

​Standalone PostgreSQL Installation

​PostgreSQL Version

​Connection String Format

​Database Configuration

​Recommended PostgreSQL Settings

​Connection Pooling

​Database Migrations

​Manual Migration (Advanced)

​Backup and Restore

​Backup Database

​Restore Database

​Automated Backups

​Redis Setup

​Docker Compose (Recommended)

​Standalone Redis Installation

​Redis Connection String

​Redis Configuration

​Production Settings

​High Availability with Redis Sentinel

​Redis Monitoring

​Check Redis Status

​Monitor Memory Usage

​View Connected Clients

​Database Monitoring

​PostgreSQL Monitoring

​Check Active Connections

​View Database Size

​Check Table Sizes

​Monitor Query Performance

​Health Checks

​Troubleshooting

​PostgreSQL Issues

​Connection Refused

​Authentication Failed

​Out of Disk Space

​Redis Issues

​Connection Refused

​Out of Memory

​High Memory Usage

​Performance Tuning

​PostgreSQL Optimization

​Redis Optimization

​Next Steps

Build docs developers (and LLMs) love

Database Setup

PostgreSQL Setup

Docker Compose (Recommended)

Standalone PostgreSQL Installation

PostgreSQL Version

Connection String Format

Database Configuration

Recommended PostgreSQL Settings

Connection Pooling

Database Migrations

Manual Migration (Advanced)

Backup and Restore

Backup Database

Restore Database

Automated Backups

Redis Setup

Docker Compose (Recommended)

Standalone Redis Installation

Redis Connection String

Redis Configuration

Production Settings

High Availability with Redis Sentinel

Redis Monitoring

Check Redis Status

Monitor Memory Usage

View Connected Clients

Database Monitoring

PostgreSQL Monitoring

Check Active Connections

View Database Size

Check Table Sizes

Monitor Query Performance

Health Checks

Troubleshooting

PostgreSQL Issues

Connection Refused

Authentication Failed

Out of Disk Space

Redis Issues

Connection Refused

Out of Memory

High Memory Usage

Performance Tuning

PostgreSQL Optimization

Redis Optimization

Next Steps