PostgreSQL Setup Guide

This guide explains how to set up PostgreSQL as the storage backend for Cesivi Server.

Overview

PostgreSQL is the recommended storage provider for open-source and cross-platform deployments. It offers: - Open-source (no licensing costs) - Excellent performance and reliability - Cross-platform support (Linux, Windows, macOS) - JSONB column support for flexible schemas - Full-text search capabilities - Multi-server support (load balancing) - High availability (replication, clustering)

Prerequisites

• PostgreSQL 13 or later (16 recommended)
• .NET 10.0 SDK (for running Cesivi Server)
• Linux, Windows, or macOS (cross-platform)
• Docker (optional, recommended for development)

Installation Options

Option 1: Docker (Recommended for Development)

Best for: Quick setup, isolated environment, cross-platform

# Pull PostgreSQL image
docker pull postgres:16-alpine

# Run PostgreSQL container
docker run --name spm-postgres \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_DB=Cesivi \
  -p 5432:5432 \
  -v spm-postgres-data:/var/lib/postgresql/data \
  -d postgres:16-alpine

# Verify running
docker ps | grep spm-postgres

# Connect to database
docker exec -it spm-postgres psql -U postgres -d Cesivi

Connection String:

Host=localhost;Port=5432;Database=Cesivi;Username=postgres;Password=password

---

Option 2: Linux (Ubuntu/Debian)

Best for: Production Linux servers

# Add PostgreSQL repository
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update and install
sudo apt update
sudo apt install -y postgresql-16 postgresql-contrib-16

# Start PostgreSQL
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Verify installation
sudo -u postgres psql --version

Create Database:

# Switch to postgres user
sudo -u postgres psql

# Create database and user
CREATE DATABASE Cesivi;
CREATE USER spmuser WITH ENCRYPTED PASSWORD 'SecurePassword123!';
GRANT ALL PRIVILEGES ON DATABASE Cesivi TO spmuser;

# Connect to database
\c Cesivi

# Grant schema permissions
GRANT ALL ON SCHEMA public TO spmuser;

# Exit
\q

---

Option 3: Windows

Best for: Windows development/production

Download: https://www.postgresql.org/download/windows/

1. Run the installer (postgresql-16.x-windows-x64.exe)
2. Choose installation directory (default: C:\Program Files\PostgreSQL\16)
3. Select components:
4. ✅ PostgreSQL Server
5. ✅ pgAdmin 4
6. ✅ Command Line Tools
7. ❌ Stack Builder (optional)
8. Choose data directory (default: C:\Program Files\PostgreSQL\16\data)
9. Set password for postgres superuser
10. Choose port (default: 5432)
11. Choose locale (default: [Default locale])
12. Complete installation

Create Database via pgAdmin 4:

1. Open pgAdmin 4
2. Connect to PostgreSQL server (localhost)
3. Right-click Databases → Create → Database
4. Database name: Cesivi
5. Owner: postgres
6. Click Save

Create User:

CREATE USER spmuser WITH ENCRYPTED PASSWORD 'SecurePassword123!';
GRANT ALL PRIVILEGES ON DATABASE Cesivi TO spmuser;

---

Option 4: macOS

Best for: macOS development

Using Homebrew:

# Install PostgreSQL
brew install postgresql@16

# Start PostgreSQL service
brew services start postgresql@16

# Create database
createdb Cesivi

# Create user
psql postgres
CREATE USER spmuser WITH ENCRYPTED PASSWORD 'SecurePassword123!';
GRANT ALL PRIVILEGES ON DATABASE Cesivi TO spmuser;
\q

---

Option 5: Cloud PostgreSQL

Best for: Cloud-native, managed database

Azure Database for PostgreSQL

1. Navigate to https://portal.azure.com
2. Create new resource → Azure Database for PostgreSQL
3. Choose Flexible Server (recommended) or Single Server
4. Configure:
5. Resource Group: Create new or select existing
6. Server Name: spm-yourcompany
7. Region: Choose region
8. PostgreSQL version: 16
9. Compute + Storage: Choose tier
   • Development: Burstable, B1ms (1 vCore, 2 GB RAM, ~$12/month)
   • Production: General Purpose or Memory Optimized
10. Admin username: spmadmin
11. Password: Create strong password
12. Networking:
13. Allow public access from: 0.0.0.0 - 255.255.255.255 (development only!)
14. Production: Use Private endpoint or VNET integration
15. Review + Create → Create

Create Database:

psql "host=spm-yourcompany.postgres.database.azure.com port=5432 dbname=postgres user=spmadmin@spm-yourcompany password=YourPassword sslmode=require"

CREATE DATABASE Cesivi;
\q

Connection String:

Host=spm-yourcompany.postgres.database.azure.com;Port=5432;Database=Cesivi;Username=spmadmin@spm-yourcompany;Password=YourPassword;SSL Mode=Require;Trust Server Certificate=true

AWS RDS for PostgreSQL

Similar process via AWS Management Console → RDS → Create Database → PostgreSQL

Google Cloud SQL for PostgreSQL

Similar process via Google Cloud Console → SQL → Create Instance → PostgreSQL

---

Database Setup

1. Create Database (if not already created)

# Using createdb command
createdb -U postgres Cesivi

# Or using psql
psql -U postgres
CREATE DATABASE Cesivi;
\q

2. Create User (Optional, for non-superuser access)

CREATE USER spmuser WITH ENCRYPTED PASSWORD 'SecurePassword123!';
GRANT ALL PRIVILEGES ON DATABASE Cesivi TO spmuser;

-- Connect to database
\c Cesivi

-- Grant schema permissions (PostgreSQL 15+)
GRANT ALL ON SCHEMA public TO spmuser;
GRANT ALL ON ALL TABLES IN SCHEMA public TO spmuser;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO spmuser;

-- Set default privileges for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO spmuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO spmuser;

3. Configure Remote Access (If Needed)

Edit postgresql.conf:

# Find config file location
sudo -u postgres psql -c "SHOW config_file;"

# Edit file (example location)
sudo nano /etc/postgresql/16/main/postgresql.conf

# Change listen_addresses
listen_addresses = '*'  # or '0.0.0.0' for all interfaces

# Restart PostgreSQL
sudo systemctl restart postgresql

Edit pg_hba.conf:

# Find file location
sudo -u postgres psql -c "SHOW hba_file;"

# Edit file (example location)
sudo nano /etc/postgresql/16/main/pg_hba.conf

# Add line for remote connections (example: allow from 192.168.1.0/24)
host    all             all             192.168.1.0/24          scram-sha-256

# Or allow from anywhere (NOT for production!)
host    all             all             0.0.0.0/0               scram-sha-256

# Reload configuration
sudo systemctl reload postgresql

4. Firewall Rules (If Needed)

# Ubuntu/Debian
sudo ufw allow 5432/tcp

# CentOS/RHEL
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

---

EF Core Migrations

Cesivi Server uses Entity Framework Core for database schema management.

1. Install EF Core Tools

dotnet tool install --global dotnet-ef

2. Create Initial Migration

cd Cesivi.Storage.PostgreSql

# Create migration
dotnet ef migrations add InitialCreate --context PostgresDbContext

# Apply migration to database
dotnet ef database update --context PostgresDbContext --connection "Host=localhost;Database=Cesivi;Username=postgres;Password=password"

3. Verify Schema

-- Connect to database
psql -U postgres -d Cesivi

-- List all tables
\dt

-- Expected tables:
-- - WebApplications
-- - SiteCollections
-- - Webs
-- - Lists
-- - ListItems (with JSONB FieldValues column)
-- - Files
-- - Folders
-- - Users
-- - Groups
-- - GroupMemberships
-- - ContentTypes
-- - Fields
-- - Views
-- - RoleDefinitions
-- - RoleAssignments
-- - RecycleBinItems
-- - FileVersions
-- - ListItemVersions
-- - Attachments

-- Describe table (example)
\d "ListItems"

-- Verify JSONB columns and GIN indexes
\d+ "ListItems"

---

Configuration

appsettings.json

{
  "Cesivi": {
    "StorageProvider": "PostgreSql",
    "PostgreSql": {
      "ConnectionString": "Host=localhost;Port=5432;Database=Cesivi;Username=postgres;Password=password",
      "EnableRetryOnFailure": true,
      "MaxRetryCount": 3,
      "CommandTimeout": 30,
      "EnableFullTextSearch": false
    }
  }
}

Connection String Examples

Local PostgreSQL (Development):

Host=localhost;Port=5432;Database=Cesivi;Username=postgres;Password=password

Remote PostgreSQL:

Host=192.168.1.100;Port=5432;Database=Cesivi;Username=spmuser;Password=SecurePassword123!

SSL Connection (Production):

Host=postgres.example.com;Port=5432;Database=Cesivi;Username=spmuser;Password=SecurePassword;SSL Mode=Require;Trust Server Certificate=true

Azure Database for PostgreSQL:

Host=spm-yourcompany.postgres.database.azure.com;Port=5432;Database=Cesivi;Username=spmadmin@spm-yourcompany;Password=YourPassword;SSL Mode=Require;Trust Server Certificate=true

Connection Pooling (Recommended):

Host=localhost;Port=5432;Database=Cesivi;Username=postgres;Password=password;Pooling=true;Minimum Pool Size=1;Maximum Pool Size=20

---

Performance Optimization

1. Create Indexes

The EF Core migrations create basic indexes including GIN indexes for JSONB columns. You can add more:

-- Index on Files.Modified (for sorting)
CREATE INDEX idx_files_modified ON "Files" ("Modified" DESC);

-- Index on ListItems.Created (for sorting)
CREATE INDEX idx_listitems_created ON "ListItems" ("Created" DESC);

-- JSONB index examples (if custom queries needed)
-- Note: GIN index on ListItems.FieldValues already created by migrations

-- Index specific JSONB key
CREATE INDEX idx_listitems_title ON "ListItems" USING GIN (("FieldValues" -> 'Title'));

-- Multicolumn index
CREATE INDEX idx_listitems_list_created ON "ListItems" ("ListId", "Created" DESC);

2. Enable Full-Text Search (Optional)

PostgreSQL has built-in full-text search using tsvector and tsquery.

Add Full-Text Search Column:

-- Add tsvector column to Files
ALTER TABLE "Files" ADD COLUMN search_vector tsvector;

-- Create GIN index on search vector
CREATE INDEX idx_files_search ON "Files" USING GIN (search_vector);

-- Create trigger to auto-update search vector
CREATE OR REPLACE FUNCTION files_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', coalesce(NEW."Title", '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW."ContentType", '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER files_search_update BEFORE INSERT OR UPDATE
ON "Files" FOR EACH ROW EXECUTE FUNCTION files_search_trigger();

-- Update existing rows
UPDATE "Files" SET search_vector =
    setweight(to_tsvector('english', coalesce("Title", '')), 'A') ||
    setweight(to_tsvector('english', coalesce("ContentType", '')), 'B');

Search Query Example:

-- Search for documents containing "invoice"
SELECT * FROM "Files"
WHERE search_vector @@ to_tsquery('english', 'invoice');

-- Ranked search
SELECT *, ts_rank(search_vector, query) AS rank
FROM "Files", to_tsquery('english', 'invoice & report') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

Enable in Configuration:

{
  "Cesivi": {
    "PostgreSql": {
      "EnableFullTextSearch": true
    }
  }
}

3. Configure Memory and Performance

Edit postgresql.conf:

-- Show current settings
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;

-- Recommended settings for production (adjust based on server RAM)
-- For 8GB RAM server:
ALTER SYSTEM SET shared_buffers = '2GB';            -- 25% of RAM
ALTER SYSTEM SET effective_cache_size = '6GB';     -- 75% of RAM
ALTER SYSTEM SET work_mem = '16MB';                -- Per operation
ALTER SYSTEM SET maintenance_work_mem = '512MB';   -- For VACUUM, CREATE INDEX
ALTER SYSTEM SET max_connections = 100;

-- Reload configuration
SELECT pg_reload_conf();

4. Vacuum and Analyze

Regular maintenance for optimal performance:

-- Manual vacuum and analyze
VACUUM ANALYZE;

-- Vacuum specific table
VACUUM ANALYZE "ListItems";

-- Check table statistics
SELECT schemaname, tablename, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_vacuum DESC;

Auto-vacuum Configuration:

-- Check auto-vacuum settings
SHOW autovacuum;

-- Tune auto-vacuum (in postgresql.conf)
ALTER SYSTEM SET autovacuum = on;
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05;
SELECT pg_reload_conf();

---

Backup and Restore

Backup

# Full database dump (SQL format)
pg_dump -U postgres Cesivi > Cesivi_backup.sql

# Compressed backup (custom format, recommended)
pg_dump -U postgres -Fc Cesivi > Cesivi_backup.dump

# Compressed backup with verbose output
pg_dump -U postgres -Fc -v Cesivi > Cesivi_backup_$(date +%Y%m%d).dump

# Backup specific schema
pg_dump -U postgres -n public Cesivi > Cesivi_public.sql

# Backup only schema (no data)
pg_dump -U postgres -s Cesivi > Cesivi_schema.sql

# Backup only data (no schema)
pg_dump -U postgres -a Cesivi > Cesivi_data.sql

Restore

# Restore from SQL dump
psql -U postgres Cesivi < Cesivi_backup.sql

# Restore from custom format dump
pg_restore -U postgres -d Cesivi Cesivi_backup.dump

# Restore with create database
pg_restore -U postgres -C -d postgres Cesivi_backup.dump

# Restore only specific table
pg_restore -U postgres -d Cesivi -t "ListItems" Cesivi_backup.dump

Automated Backups

Using cron (Linux):

# Create backup script
cat > /usr/local/bin/backup-spm-postgres.sh <<'EOF'
#!/bin/bash
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
pg_dump -U postgres -Fc Cesivi > "$BACKUP_DIR/Cesivi_$DATE.dump"
# Keep only last 7 days
find "$BACKUP_DIR" -name "Cesivi_*.dump" -mtime +7 -delete
EOF

chmod +x /usr/local/bin/backup-spm-postgres.sh

# Add to cron (daily at 2 AM)
sudo crontab -e
0 2 * * * /usr/local/bin/backup-spm-postgres.sh

---

High Availability (Optional)

Streaming Replication

Setup Primary Server (Master):

-- Create replication user
CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'ReplicatorPassword123!';

-- Edit postgresql.conf
ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM SET max_wal_senders = 3;
ALTER SYSTEM SET wal_keep_size = '1GB';

-- Restart PostgreSQL
-- sudo systemctl restart postgresql

Edit pg_hba.conf (Primary):

# Add replication entry
host    replication     replicator      replica-server-ip/32    scram-sha-256

Setup Standby Server (Replica):

# Stop PostgreSQL on standby
sudo systemctl stop postgresql

# Remove data directory
sudo rm -rf /var/lib/postgresql/16/main/*

# Create base backup from primary
sudo -u postgres pg_basebackup -h primary-server-ip -D /var/lib/postgresql/16/main -U replicator -P -X stream -R

# Start PostgreSQL on standby
sudo systemctl start postgresql

Verify Replication:

-- On primary
SELECT * FROM pg_stat_replication;

-- On standby
SELECT pg_is_in_recovery();  -- Should return 't' (true)

Connection String with Failover:

Host=primary-server,standby-server;Port=5432;Database=Cesivi;Username=spmuser;Password=SecurePassword;Target Session Attributes=read-write

---

Troubleshooting

Common Issues

1. Cannot connect to PostgreSQL

Error: could not connect to server: Connection refused

Solutions: - Verify PostgreSQL is running: sudo systemctl status postgresql - Check port is open: sudo netstat -tulpn | grep 5432 - Check listen_addresses in postgresql.conf - Check firewall rules - Verify connection string (host, port, username, password)

---

2. Password authentication failed

Error: password authentication failed for user "spmuser"

Solutions: - Verify user exists: SELECT * FROM pg_user WHERE usename = 'spmuser'; - Verify password is correct - Check pg_hba.conf authentication method (should be scram-sha-256 or md5) - Reload configuration: SELECT pg_reload_conf();

---

3. Database does not exist

Error: FATAL: database "Cesivi" does not exist

Solutions: - Create database: createdb -U postgres Cesivi - Verify database name in connection string - List databases: psql -U postgres -l

---

4. Permission denied

Error: permission denied for schema public

Solutions: - Grant schema permissions (PostgreSQL 15+):

GRANT ALL ON SCHEMA public TO spmuser;

- Grant table permissions:

GRANT ALL ON ALL TABLES IN SCHEMA public TO spmuser;

---

5. JSONB query slow

-- Slow query on JSONB column

Solutions: - Ensure GIN index exists (already created by migrations):

\d "ListItems"  -- Check for GIN index on FieldValues

- If needed, create specific JSONB path index:

CREATE INDEX ON "ListItems" USING GIN (("FieldValues" -> 'Title'));

- Run VACUUM ANALYZE "ListItems";

---

Monitoring

Active Connections

SELECT
    datname,
    count(*) AS connections
FROM pg_stat_activity
GROUP BY datname
ORDER BY connections DESC;

Database Size

SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

Table Sizes

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Slow Queries

-- Enable pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries
SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Index Usage

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

---

See Also

• STORAGE_PROVIDERS.md - Storage provider comparison
• STORAGE_MIGRATION.md - Migration guide
• SQL_SERVER_SETUP.md - SQL Server setup
• DEPLOYMENT_GUIDE.md - Production deployment
• TROUBLESHOOTING.md - General troubleshooting

External Resources

• PostgreSQL Documentation
• Entity Framework Core with PostgreSQL
• PostgreSQL Performance Tuning
• PostgreSQL High Availability