### Fresh Discourse PostgreSQL Installation with Docker Compose - Bash

Source: https://context7.com/discourse/discourse-postgres/llms.txt

This bash script outlines the process for a fresh Discourse installation using a specific PostgreSQL Docker image. It includes creating a docker-compose.yml file, setting a secure database password, starting the database service, and connecting to the PostgreSQL instance.

```bash
mkdir -p /opt/discourse/shared/db
cd /opt/discourse
cat > docker-compose.yml <<'EOF'
version: '3' 
services:
  db:
    image: discourse/postgres:17
    volumes:
      - ./shared/db:/var/lib/postgresql
      - ./shared/db/data:/var/lib/postgresql/data
    environment:
      DB_PASSWORD: ${DB_PASSWORD}
      DB_USER: discourse
      POSTGRES_DB: discourse
    restart: always
EOF
export DB_PASSWORD=$(openssl rand -base64 32)
echo "DB_PASSWORD=${DB_PASSWORD}" > .env
docker-compose up -d
docker-compose exec db psql -U discourse -d discourse
```

--------------------------------

### Initialize Database and Setup PostgreSQL Extensions

Source: https://context7.com/discourse/discourse-postgres/llms.txt

Demonstrates the functionality of the bootstrap-db.sh script, which is executed automatically during container initialization. It creates a non-superuser database user with full privileges, sets a password, and installs essential PostgreSQL extensions (hstore, pg_trgm, vector) in both the 'template1' and 'discourse' databases. An option to skip extensions for non-Discourse uses is also shown.

```bash
# bootstrap-db.sh functionality
# Automatically executed during container initialization

# Creates non-superuser with full privileges
psql --username "postgres" --dbname "discourse" <<-"EOSQL"
CREATE USER discourse;
GRANT ALL PRIVILEGES ON DATABASE discourse TO discourse;
ALTER SCHEMA PUBLIC owner TO discourse;
ALTER USER discourse with password 'my_secure_password_123';
EOSQL

# Installs extensions in template1 (for future databases)
psql --username "postgres" --dbname "template1" <<-"EOSQL"
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS vector;
ALTER EXTENSION vector UPDATE;
EOSQL

# Installs extensions in main discourse database
psql --username "postgres" --dbname "discourse" <<-"EOSQL"
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS vector;
ALTER EXTENSION vector UPDATE;
EOSQL
```

```bash
# Skip extensions if using for non-Discourse purposes
docker run -d \
  --name generic-postgres \
  -v /data/postgres:/var/lib/postgresql \
  -e DB_PASSWORD="password" \
  -e DB_USER="myapp" \
  -e SKIP_EXTENSIONS=1 \
  discourse/postgres:17
```

--------------------------------

### Bash: Production PostgreSQL Docker Deployment and Verification

Source: https://context7.com/discourse/discourse-postgres/llms.txt

This example shows a comprehensive production setup for a Discourse PostgreSQL container. It includes volume mounts, password generation, extensive performance tuning environment variables, resource limits (memory, CPUs), restart policies, and health checks. It concludes with commands to verify the applied configuration.

```bash
# Full production configuration example
docker run -d \
  --name discourse-production \
  -v /mnt/ssd/postgres:/var/lib/postgresql \
  -v /mnt/ssd/postgres/data:/var/lib/postgresql/data \
  -e DB_PASSWORD="$(openssl rand -base64 32)" \
  -e DB_USER="discourse" \
  -e POSTGRES_DB="discourse_production" \
  -e DB_SYNCHRONOUS_COMMIT="on" \
  -e DB_SHARED_BUFFERS="4GB" \
  -e DB_WORK_MEM="50MB" \
  -e DB_DEFAULT_TEXT_SEARCH_CONFIG="pg_catalog.english" \
  -e DB_LOGGING_COLLECTOR="on" \
  -e DB_LOG_MIN_DURATION_STATEMENT="500" \
  -e LANG="en_US.utf8" \
  --memory="8g" \
  --cpus="4" \
  --restart=always \
  --health-cmd="pg_isready -U postgres" \
  --health-interval=10s \
  --health-timeout=5s \
  --health-retries=5 \
  -p 127.0.0.1:5432:5432 \
  discourse/postgres:17

# Verify configuration
docker exec discourse-production psql -U postgres -d discourse_production -c "SHOW shared_buffers;"
docker exec discourse-production psql -U postgres -d discourse_production -c "SHOW work_mem;"
docker exec discourse-production psql -U postgres -d discourse_production -c "SELECT * FROM pg_extension;"
```

--------------------------------

### Docker: Build and Run Custom PostgreSQL Images

Source: https://context7.com/discourse/discourse-postgres/llms.txt

Examples demonstrate building custom PostgreSQL Docker images with specific versions and performance tuning arguments. It also shows how to run these custom images, mounting volumes for data persistence.

```bash
# Build PostgreSQL 16 image locally
docker build \
  --build-arg VERSION=16 \
  --build-arg DB_SHARED_BUFFERS=512MB \
  --build-arg DB_WORK_MEM=25MB \
  -t discourse/postgres:16-custom \
  .

# Build with custom defaults for high-performance setup
docker build \
  --build-arg VERSION=17 \
  --build-arg DB_SYNCHRONOUS_COMMIT=on \
  --build-arg DB_SHARED_BUFFERS=2GB \
  --build-arg DB_WORK_MEM=50MB \
  --build-arg DB_LOGGING_COLLECTOR=on \
  -t discourse/postgres:17-performance \
  .

# Run custom built image
docker run -d \
  --name custom-postgres \
  -v /data/postgres:/var/lib/postgresql \
  -e DB_PASSWORD="secure_password" \
  discourse/postgres:16-custom
```

--------------------------------

### Docker Compose Configuration for Discourse Postgres

Source: https://github.com/discourse/discourse-postgres/blob/main/README.md

Example docker-compose configuration to set up a PostgreSQL database for Discourse. It specifies the image, volume mounts for data persistence, and environment variables for the database password.

```yaml
db:
  image: discourse/postgres
  volumes:
    - ./shared/db:/var/lib/postgresql
    - ./shared/db/data:/var/lib/postgresql/data
  environment:
    DB_PASSWORD: SOME_SECRET
```

--------------------------------

### Dockerfile: Build Discourse PostgreSQL Image

Source: https://context7.com/discourse/discourse-postgres/llms.txt

This Dockerfile sets up a PostgreSQL 17 image with the pgvector extension for Discourse. It defines build arguments for PostgreSQL configuration and installs necessary tools like pwgen. It also copies custom scripts for database bootstrapping and configuration.

```dockerfile
FROM pgvector/pgvector:pg17
ARG VERSION=17
ARG DB_SYNCHRONOUS_COMMIT=off
ARG DB_SHARED_BUFFERS=256MB
ARG DB_WORK_MEM=10MB
ARG DB_DEFAULT_TEXT_SEARCH_CONFIG=pg_catalog.english
ARG DB_LOGGING_COLLECTOR=off
ARG DB_LOG_MIN_DURATION_STATEMENT=100

RUN apt-get update && \
  apt-mark hold locales && \
  apt-get install -y --no-install-recommends pwgen && \
  apt-get autoremove -y && \
  apt-mark unhold locales && \
  rm -rf /var/lib/apt/lists/*

ADD upgrade-postgres.sh /usr/local/bin/upgrade-postgres.sh
ADD run-postgres.sh /usr/local/bin/run-postgres.sh
ADD bootstrap-db.sh /docker-entrypoint-initdb.d/bootstrap-db.sh
ADD configure-postgres.sh /docker-entrypoint-initdb.d/configure-postgres.sh

ENV PGDATA=/var/lib/postgresql/${VERSION}/docker \
  POSTGRES_USER=postgres \
  DB_USER=discourse \
  POSTGRES_DB=discourse \
  DB_SYNCHRONOUS_COMMIT=${DB_SYNCHRONOUS_COMMIT} \
  DB_SHARED_BUFFERS=${DB_SHARED_BUFFERS} \
  DB_WORK_MEM=${DB_WORK_MEM} \
  DB_DEFAULT_TEXT_SEARCH_CONFIG=${DB_DEFAULT_TEXT_SEARCH_CONFIG} \
  DB_LOGGING_COLLECTOR=${DB_LOGGING_COLLECTOR} \
  DB_LOG_MIN_DURATION_STATEMENT=${DB_LOG_MIN_DURATION_STATEMENT}

ENTRYPOINT ["run-postgres.sh"]
CMD ["postgres"]
```

--------------------------------

### Automate PostgreSQL Version Upgrades with upgrade-postgres.sh

Source: https://context7.com/discourse/discourse-postgres/llms.txt

The upgrade-postgres.sh script automates the in-place upgrade of PostgreSQL instances. It detects the old version, installs necessary binaries, performs the `pg_upgrade`, and verifies sufficient disk space before proceeding. This process requires the old and new data directories and binary paths to be specified.

```bash
# upgrade-postgres.sh - Automatic upgrade process
# Detects existing PostgreSQL data and upgrades to current image version

# Example: Upgrading from PostgreSQL 13 to 17
# Directory structure before upgrade:
# /var/lib/postgresql/13/docker/PG_VERSION (contains "13")

# Script automatically:
# 1. Detects old version
find /var/lib/postgresql -maxdepth 3 -type f -name PG_VERSION

# 2. Installs old PostgreSQL binaries
apt-get install -y postgresql-13 postgresql-13-pgvector

# 3. Performs pg_upgrade
pg_upgrade \
  --old-datadir=/var/lib/postgresql/13/docker \
  --new-datadir=/var/lib/postgresql/17/docker \
  --old-bindir=/usr/lib/postgresql/13/bin \
  --new-bindir=/usr/lib/postgresql/17/bin \
  --username=postgres

# 4. Verifies disk space (requires 2x current database size)
free_disk=$(df -P -B1 /var/lib/postgresql | tail -n 1 | awk '{print $4}')
required=$(($(du -sb /var/lib/postgresql/13/docker | awk '{print $1}') * 2))

if [ "$free_disk" -lt "$required" ]; then
  echo "ERROR: Need additional $(numfmt --to=si $(($required - $free_disk))) disk space"
  exit 1
fi
```

--------------------------------

### Dynamically Configure PostgreSQL Locales for Discourse

Source: https://context7.com/discourse/discourse-postgres/llms.txt

Demonstrates how to configure Discourse PostgreSQL containers with different locales for internationalization using the `run-postgres.sh` entrypoint. It shows examples for German, French, and default English locales by setting the `LANG` and `DB_DEFAULT_TEXT_SEARCH_CONFIG` environment variables.

```bash
# run-postgres.sh - Main entrypoint with locale support

# Generate German locale dynamically
docker run -d \
  --name postgres-german \
  -v /data/postgres:/var/lib/postgresql \
  -e DB_PASSWORD="password" \
  -e LANG=de_DE.UTF-8 \
  -e DB_DEFAULT_TEXT_SEARCH_CONFIG=pg_catalog.german \
  discourse/postgres:17

# Generate French locale
docker run -d \
  --name postgres-french \
  -v /data/postgres-fr:/var/lib/postgresql \
  -e DB_PASSWORD="password" \
  -e LANG=fr_FR.UTF-8 \
  -e DB_DEFAULT_TEXT_SEARCH_CONFIG=pg_catalog.french \
  discourse/postgres:17

# Default English locale (no special configuration needed)
docker run -d \
  --name postgres-english \
  -v /data/postgres-en:/var/lib/postgresql \
  -e DB_PASSWORD="password" \
  -e LANG=en_US.utf8 \
  discourse/postgres:17
```

--------------------------------

### Perform Discourse PostgreSQL Upgrade Scenario

Source: https://context7.com/discourse/discourse-postgres/llms.txt

This sequence outlines the steps for upgrading a Discourse PostgreSQL instance from version 14 to 17. It involves stopping the existing container, starting a new one with the desired version (which triggers the automatic upgrade), and monitoring the logs for progress and completion. The old data is preserved in a separate directory.

```bash
# Upgrade scenario: Discourse running PostgreSQL 14, upgrading to 17
# Stop existing container
docker stop discourse-postgres

# Start with new version (upgrade happens automatically)
docker run -d \
  --name discourse-postgres-upgraded \
  -v /opt/discourse/shared/db:/var/lib/postgresql \
  -e DB_PASSWORD="my_password" \
  discourse/postgres:17

# Monitor upgrade progress
docker logs -f discourse-postgres-upgraded

# Expected output:
# Installing PostgreSQL version 14 for upgrade..
# Fixing permissions in /var/lib/postgresql/14/docker...
# Upgrading PostgreSQL from version 14 to 17
# Performing Consistency Checks
# --------------------------------
# Checking cluster versions                    ok
# Checking database user is the install user   ok
# Creating databases in the new cluster         ok
# PostgreSQL version 14 upgrade complete
# database system is ready to accept connections

# Old data preserved at: /var/lib/postgresql/14/docker
# New data location: /var/lib/postgresql/17/docker
```

--------------------------------

### Backup and Upgrade PostgreSQL Data - Bash

Source: https://context7.com/discourse/discourse-postgres/llms.txt

This snippet demonstrates backing up existing PostgreSQL data using tar, identifying the PostgreSQL version, and restructuring the data directory for an upgrade. It also shows how to update Docker volume mounts in the configuration.

```bash
cd /var/discourse/shared/standalone
tar -czf postgres_data_backup_$(date +%Y%m%d).tar.gz postgres_data
cat postgres_data/PG_VERSION
mkdir -p postgres_data_new/13/docker
mv postgres_data/* postgres_data_new/13/docker/
rmdir postgres_data
# Old volume mount: ./shared/standalone/postgres_data:/var/lib/postgresql/data
# New volume mount: ./shared/standalone/postgres_data_new:/var/lib/postgresql
./launcher start app
./launcher enter app
psql -U postgres -d discourse -c "SELECT version();"
psql -U postgres -d discourse -c "SELECT * FROM pg_extension;"
# rm postgres_data_backup_*.tar.gz
```

--------------------------------

### Deploy Discourse PostgreSQL via Docker Run

Source: https://context7.com/discourse/discourse-postgres/llms.txt

Deploys the Discourse PostgreSQL Docker container using a direct 'docker run' command. This method allows for direct configuration of volumes, environment variables, and port mappings for the PostgreSQL instance. It includes a command to verify the container's running status and check logs.

```bash
# Alternative: Direct docker run command
docker run -d \
  --name discourse-postgres \
  -v /opt/discourse/shared/db:/var/lib/postgresql \
  -v /opt/discourse/shared/db/data:/var/lib/postgresql/data \
  -e DB_PASSWORD="my_secure_password_123" \
  -e DB_USER="discourse" \
  -e DB_SHARED_BUFFERS="512MB" \
  -e DB_WORK_MEM="20MB" \
  -p 5432:5432 \
  discourse/postgres:17

# Verify container is running
docker logs discourse-postgres

# Expected output includes:
# PostgreSQL init process complete; ready for start up.
# database system is ready to accept connections
```

--------------------------------

### Run Production-Optimized Discourse PostgreSQL Container

Source: https://context7.com/discourse/discourse-postgres/llms.txt

Launches a PostgreSQL container optimized for production environments with Discourse. This configuration sets specific database parameters, memory, and CPU limits, and mounts a volume for data persistence. It requires a password and specifies synchronous commit and logging settings.

```bash
docker run -d \
  --name discourse-postgres-prod \
  -v /mnt/data/postgres:/var/lib/postgresql \
  -e DB_PASSWORD="prod_password" \
  -e DB_SYNCHRONOUS_COMMIT=on \
  -e DB_SHARED_BUFFERS=4GB \
  -e DB_WORK_MEM=50MB \
  -e DB_LOGGING_COLLECTOR=on \
  -e DB_LOG_MIN_DURATION_STATEMENT=1000 \
  -e DB_DEFAULT_TEXT_SEARCH_CONFIG=pg_catalog.german \
  --memory=8g \
  --cpus=4 \
  discourse/postgres:17
```

--------------------------------

### Build Custom Discourse PostgreSQL Docker Image

Source: https://context7.com/discourse/discourse-postgres/llms.txt

Defines the configuration for building a custom Docker image for Discourse PostgreSQL. This Dockerfile allows for specifying versions and default configurations, enabling reproducible builds for specific PostgreSQL environments.

```dockerfile
# Dockerfile - Custom build configuration

```

--------------------------------

### Bash: PostgreSQL Environment Variables Reference

Source: https://context7.com/discourse/discourse-postgres/llms.txt

This section lists and describes essential environment variables for configuring Discourse's PostgreSQL container. It covers mandatory variables like DB_PASSWORD, database user settings, performance tuning parameters, logging, and localization.

```bash
# Required Environment Variables
DB_PASSWORD="required_password_value"  # MUST be set, container fails without it

# Database User Configuration
DB_USER="discourse"                    # Non-superuser account (default: discourse)
POSTGRES_USER="postgres"               # Superuser account (default: postgres, don't change)
POSTGRES_DB="discourse"                # Database name (default: discourse)
POSTGRES_PASSWORD="auto_generated"     # Superuser password (auto-generated if not set)

# PostgreSQL Performance Tuning
DB_SYNCHRONOUS_COMMIT="off"            # Synchronous commit mode (default: off)
DB_SHARED_BUFFERS="256MB"              # Shared memory buffers (default: 256MB)
DB_WORK_MEM="10MB"                     # Per-operation memory (default: 10MB)

# PostgreSQL Search and Logging
DB_DEFAULT_TEXT_SEARCH_CONFIG="pg_catalog.english"  # Text search config
DB_LOGGING_COLLECTOR="off"             # Enable log collection (default: off)
DB_LOG_MIN_DURATION_STATEMENT="100"    # Log slow queries (ms, default: 100)

# Localization
LANG="en_US.utf8"                      # Locale setting (default: en_US.utf8)

# Feature Flags
SKIP_EXTENSIONS="0"                    # Set to 1 to skip hstore/pg_trgm/vector extensions

# Data Directory (auto-configured, don't override)
PGDATA="/var/lib/postgresql/17/docker" # Version-specific data directory
```

--------------------------------

### Configure PostgreSQL Settings Dynamically

Source: https://context7.com/discourse/discourse-postgres/llms.txt

Illustrates how the configure-postgres.sh script dynamically updates PostgreSQL configuration parameters in `postgresql.conf`. It uses environment variables to set values for parameters such as `synchronous_commit`, `shared_buffers`, `work_mem`, and logging settings. This script runs on container startup and during upgrades to ensure configurations are up-to-date.

```bash
# configure-postgres.sh - Updates postgresql.conf settings
# Runs on every container start and during upgrades

# Example environment variables that control configuration
export DB_SYNCHRONOUS_COMMIT=off
export DB_SHARED_BUFFERS=256MB
export DB_WORK_MEM=10MB
export DB_DEFAULT_TEXT_SEARCH_CONFIG=pg_catalog.english
export DB_LOGGING_COLLECTOR=off
export DB_LOG_MIN_DURATION_STATEMENT=100

# Script modifies postgresql.conf with these values using sed
sed -Ei "s/^#?synchronous_commit *=.*/synchronous_commit = off/" postgresql.conf
sed -Ei "s/^#?shared_buffers *=.*/shared_buffers = 256MB/" postgresql.conf
sed -Ei "s/^#?work_mem *=.*/work_mem = 10MB/" postgresql.conf
sed -Ei "s/^#?default_text_search_config *=.*/default_text_search_config = 'pg_catalog.english'/" postgresql.conf
sed -Ei "s/^#?logging_collector *=.*/logging_collector = off/" postgresql.conf
sed -Ei "s/^#?log_min_duration_statement *=.*/log_min_duration_statement = 100/" postgresql.conf
```

--------------------------------

### Deploy Discourse PostgreSQL via Docker Compose

Source: https://context7.com/discourse/discourse-postgres/llms.txt

Deploys the Discourse PostgreSQL Docker container using a docker-compose.yml file. It specifies volume mounts for data persistence, environment variables for database credentials and configuration, and port mapping. This is the recommended method for managing the PostgreSQL database for Discourse.

```yaml
version: '3'
services:
  db:
    image: discourse/postgres:17
    volumes:
      - ./shared/db:/var/lib/postgresql
      - ./shared/db/data:/var/lib/postgresql/data
    environment:
      DB_PASSWORD: my_secure_password_123
      DB_USER: discourse
      POSTGRES_DB: discourse
      LANG: en_US.utf8
      DB_SHARED_BUFFERS: 512MB
      DB_WORK_MEM: 20MB
      DB_SYNCHRONOUS_COMMIT: off
      DB_LOGGING_COLLECTOR: on
      DB_LOG_MIN_DURATION_STATEMENT: 500
    ports:
      - "5432:5432"
    restart: unless-stopped
```

--------------------------------

### Verify PostgreSQL Locale Configuration Inside Container

Source: https://context7.com/discourse/discourse-postgres/llms.txt

Provides a command to execute inside a running PostgreSQL container to verify its locale settings. This is useful for confirming that dynamic locale configurations, such as German or French, have been applied correctly.

```bash
# Verify locale inside container
docker exec -it postgres-german locale

# Expected output:
# LANG=de_DE.UTF-8
# LANGUAGE=
# LC_CTYPE="de_DE.UTF-8"
# LC_NUMERIC="de_DE.UTF-8"
# LC_TIME="de_DE.UTF-8"
# LC_COLLATE="de_DE.UTF-8"
# LC_MONETARY="de_DE.UTF-8"
# LC_MESSAGES="de_DE.UTF-8"
# LC_PAPER="de_DE.UTF-8"
# LC_NAME="de_DE.UTF-8"
# LC_ADDRESS="de_DE.UTF-8"
# LC_TELEPHONE="de_DE.UTF-8"
# LC_MEASUREMENT="de_DE.UTF-8"
# LC_IDENTIFICATION="de_DE.UTF-8"
# LC_ALL=
```

--------------------------------

### Bash: Migrate Discourse PostgreSQL Data

Source: https://context7.com/discourse/discourse-postgres/llms.txt

This snippet outlines the initial step for migrating PostgreSQL data for Discourse. It involves stopping the existing Discourse application container before proceeding with data transfer or re-configuration.

```bash
# Scenario: Migrating from discourse/postgres traditional setup
# Old data location: /var/discourse/shared/standalone/postgres_data
# New data location: /var/discourse/shared/standalone/postgres_data_new

# Step 1: Stop existing Discourse container
cd /var/discourse
./launcher stop app
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.