ferrero-naming-tool/DEPLOY-LINUX.md

Ferrero Asset Tracking Database - Linux Production Deployment

This guide covers deploying the PostgreSQL tracking database on a Linux server for production use.

Overview

The Ferrero Asset Tracking Database is a standalone PostgreSQL database that can be accessed by multiple applications and scripts for:

• Asset tracking ID management
• Master asset metadata storage
• Derivative asset lifecycle tracking
• OpenText DAM integration
• Asset metadata inheritance

---

Prerequisites

Server Requirements

• OS: Ubuntu 20.04+, CentOS 8+, Debian 11+, or compatible Linux
• RAM: Minimum 2GB (4GB+ recommended for production)
• Disk: Minimum 10GB free space (more for asset storage)
• CPU: 2+ cores recommended
• Network: Port 5433 available (or configure custom port)

Software Requirements

• Docker: Version 20.10+
• Docker Compose: Version 2.0+
• Root/Sudo Access: Required for initial setup
• PostgreSQL Client (optional, for manual access)

Check Prerequisites

# Docker
docker --version
docker compose version

# Available disk space
df -h

# Available port
netstat -tuln | grep 5433
# or
ss -tuln | grep 5433

# Should show nothing if port is free

---

Quick Deployment

1. Upload Files to Server

Upload these files to your Linux server (e.g., /opt/ferrero-tracking):

ferrero-tracking/
├── deploy-database-linux.sh
├── database/
│   ├── init.sql
│   ├── seed.sql
│   └── migrations/
│       └── 001_add_upload_directory.sql
└── docker-compose.production.yml (will be created by script)

2. Run Deployment Script

# Make script executable
chmod +x deploy-database-linux.sh

# Run as root
sudo ./deploy-database-linux.sh

3. Deployment Process

The script will:

1. ✅ Check Docker and Docker Compose installation
2. ✅ Verify port 5433 is available
3. ✅ Create data directories (/var/lib/ferrero-tracking/postgres)
4. ✅ Create backup directory (/var/backups/ferrero-tracking)
5. ✅ Generate Docker Compose configuration
6. ✅ Deploy PostgreSQL 15 container
7. ✅ Initialize database schema (4 tables, triggers, views)
8. ✅ Load sample data
9. ✅ Configure firewall rules
10. ✅ Set up automated daily backups (2 AM cron job)
11. ✅ Create management scripts

Deployment time: ~2-5 minutes (including Docker image download)

---

Configuration

Environment Variables

The deployment script creates .env.production with these settings:

# Database Configuration
DB_HOST=localhost
DB_PORT=5433
DB_NAME=ferrero_tracking
DB_USER=ferrero_user
DB_PASSWORD=ferrero_pass_2025  # ⚠️ CHANGE THIS!

# Directories
DB_DATA_DIR=/var/lib/ferrero-tracking/postgres
BACKUP_DIR=/var/backups/ferrero-tracking

# Backup Settings
BACKUP_RETENTION_DAYS=30
BACKUP_SCHEDULE="0 2 * * *"

Customize Before Deployment

Edit the deployment script to change defaults:

# Edit deploy-database-linux.sh
nano deploy-database-linux.sh

# Change these variables at the top:
DB_NAME="${DB_NAME:-ferrero_tracking}"
DB_USER="${DB_USER:-ferrero_user}"
DB_PASSWORD="${DB_PASSWORD:-YOUR_SECURE_PASSWORD_HERE}"
DB_PORT="${DB_PORT:-5433}"

---

Post-Deployment Configuration

1. Change Default Password (CRITICAL!)

# Edit environment file
sudo nano /opt/ferrero-tracking/.env.production

# Update DB_PASSWORD

# Update Docker Compose file
sudo nano /opt/ferrero-tracking/docker-compose.production.yml

# Update POSTGRES_PASSWORD

# Restart database
sudo docker compose -f docker-compose.production.yml restart postgres

2. Configure Network Access

By default, the database accepts connections from anywhere. Restrict this:

Option A: Firewall Rules (Recommended)

# Allow only specific IPs (Ubuntu/Debian)
sudo ufw delete allow 5433/tcp
sudo ufw allow from 192.168.1.0/24 to any port 5433

# CentOS/RHEL
sudo firewall-cmd --remove-port=5433/tcp --permanent
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port port="5433" protocol="tcp" accept'
sudo firewall-cmd --reload

Option B: Docker Network Configuration

Edit docker-compose.production.yml:

ports:
  - "127.0.0.1:5433:5432"  # Only localhost
  # or
  - "192.168.1.100:5433:5432"  # Specific IP

3. Enable SSL/TLS (Production Recommended)

See PostgreSQL SSL documentation or contact your security team.

---

Management Commands

Status Check

ferrero-db-status.sh

Shows:

• Container status
• Database statistics (asset counts, event counts)

Manual Backup

ferrero-db-backup.sh

Creates compressed backup in /var/backups/ferrero-tracking/

Restart Database

ferrero-db-restart.sh

View Logs

docker compose -f /opt/ferrero-tracking/docker-compose.production.yml logs -f postgres

Access Database

PGPASSWORD=your_password psql -h localhost -p 5433 -U ferrero_user -d ferrero_tracking

---

Database Schema

Tables

1. master_assets - Master assets from OpenText DAM

   • tracking_id (6-char unique ID)
   • opentext_id
   • original_filename
   • upload_directory (NEW!)
   • Complete metadata (brand, country, language, etc.)
   • File properties (size, dimensions, MIME type)
   • Tags and categories

2. derivative_assets - Derivative assets linked to masters

3. asset_lifecycle_events - Complete audit trail

4. tracking_id_log - Collision-free ID generation

Key Fields

Field	Type	Description	Example
tracking_id	VARCHAR(6)	Unique asset ID	5643CQ
opentext_id	VARCHAR(255)	OpenText DAM ID	00188599...
original_filename	VARCHAR(500)	Master filename	8000500247167_8
upload_directory	VARCHAR(1000)	File system path	/data/assets/2025/10/nutella
brand_code	VARCHAR(5)	Brand code	NUT
file_size_bytes	BIGINT	File size	4361906

---

Integration with Applications

Connection String

postgresql://ferrero_user:password@localhost:5433/ferrero_tracking

Python Example

import psycopg2

conn = psycopg2.connect(
    host="your-server-ip",
    port=5433,
    database="ferrero_tracking",
    user="ferrero_user",
    password="your_password"
)

# Lookup tracking ID
cursor = conn.cursor()
cursor.execute(
    "SELECT * FROM v_master_assets_complete WHERE tracking_id = %s",
    ('5643CQ',)
)
asset = cursor.fetchone()

PHP Example

$pdo = new PDO(
    'pgsql:host=your-server-ip;port=5433;dbname=ferrero_tracking',
    'ferrero_user',
    'your_password'
);

// Lookup tracking ID
$stmt = $pdo->prepare('SELECT * FROM v_master_assets_complete WHERE tracking_id = ?');
$stmt->execute(['5643CQ']);
$asset = $stmt->fetch();

cURL Example

# Via the naming tool API (if deployed)
curl -X POST 'http://your-server:port/api.php?action=lookup-tracking-id' \
  -H "Content-Type: application/json" \
  -d '{"tracking_id":"5643CQ"}'

---

Importing Assets

From OpenText DAM

# Basic import
python3 import_opentext_asset.py metadata.json

# With upload directory
python3 import_opentext_asset.py metadata.json /data/assets/2025/10/nutella

Batch Import

#!/bin/bash
for file in /path/to/metadata/*.json; do
    # Extract directory from file path or metadata
    UPLOAD_DIR="/data/assets/$(date +%Y/%m)/$(basename $file .json)"

    python3 import_opentext_asset.py "$file" "$UPLOAD_DIR"
done

Via Application Integration

Your DAM application should populate the database when downloading assets:

INSERT INTO master_assets (
    tracking_id,
    opentext_id,
    original_filename,
    file_extension,
    upload_directory,
    brand_code,
    brand_name,
    -- ... other fields
) VALUES (
    'abc123',
    'OT_67890',
    'MASTER_FILE',
    '.mp4',
    '/data/assets/2025/10/ferrero',
    'FER',
    'FERRERO',
    -- ... other values
);

---

Database Migrations

Apply Migrations to Existing Database

If you already have a database deployed and need to add new fields:

cd /opt/ferrero-tracking/database
chmod +x apply-migrations.sh

# Apply all pending migrations
./apply-migrations.sh localhost 5433 ferrero_tracking ferrero_user

Available Migrations

• 001_add_upload_directory.sql - Adds upload_directory field to master_assets

---

Backup and Recovery

Automated Backups

Backups run automatically daily at 2:00 AM via cron.

# View cron job
sudo crontab -l | grep ferrero

# Manual backup
sudo ferrero-db-backup.sh

Backup Location

/var/backups/ferrero-tracking/ferrero_tracking_YYYYMMDD_HHMMSS.sql.gz

Restore from Backup

# Stop applications using the database first

# Uncompress backup
gunzip /var/backups/ferrero-tracking/ferrero_tracking_20251027_020000.sql.gz

# Restore
PGPASSWORD=your_password psql -h localhost -p 5433 -U ferrero_user -d ferrero_tracking < /var/backups/ferrero-tracking/ferrero_tracking_20251027_020000.sql

# Or restore to new database
PGPASSWORD=your_password createdb -h localhost -p 5433 -U ferrero_user ferrero_tracking_restore
PGPASSWORD=your_password psql -h localhost -p 5433 -U ferrero_user -d ferrero_tracking_restore < backup.sql

---

Monitoring

Database Statistics

ferrero-db-status.sh

Check Container Health

docker ps --filter name=ferrero-tracking-db-prod

Monitor Disk Usage

# Database size
PGPASSWORD=your_password psql -h localhost -p 5433 -U ferrero_user -d ferrero_tracking -c "
SELECT
    pg_size_pretty(pg_database_size('ferrero_tracking')) as database_size;
"

# Data directory
du -sh /var/lib/ferrero-tracking/postgres

Monitor Connections

PGPASSWORD=your_password psql -h localhost -p 5433 -U ferrero_user -d ferrero_tracking -c "
SELECT count(*) as active_connections
FROM pg_stat_activity
WHERE datname = 'ferrero_tracking';
"

---

Security Hardening Checklist

Essential (Do These First!)

• Change default database password
• Restrict network access (firewall rules)
• Use strong passwords (min 20 characters, mixed case, numbers, symbols)
• Limit database user permissions (create read-only users for applications)
• Keep Docker and PostgreSQL images updated

Recommended

• Enable SSL/TLS for database connections
• Set up database connection pooling
• Configure proper logging and log rotation
• Set up monitoring and alerting
• Implement IP whitelisting
• Regular security audits
• Document access procedures

Advanced

• Set up replication for high availability
• Configure automatic failover
• Implement encryption at rest
• Set up VPN access for remote connections
• Integrate with centralized authentication (LDAP/AD)

---

Troubleshooting

Database Won't Start

# Check Docker logs
docker compose -f docker-compose.production.yml logs postgres

# Check if port is in use
sudo lsof -i :5433

# Check disk space
df -h

# Restart Docker
sudo systemctl restart docker
sudo docker compose -f docker-compose.production.yml up -d

Can't Connect from Application

# Test connection locally
PGPASSWORD=your_password psql -h localhost -p 5433 -U ferrero_user -d ferrero_tracking -c "SELECT 1;"

# Test from remote
PGPASSWORD=your_password psql -h your-server-ip -p 5433 -U ferrero_user -d ferrero_tracking -c "SELECT 1;"

# Check firewall
sudo ufw status
sudo iptables -L -n | grep 5433

# Check Docker network
docker inspect ferrero-tracking-db-prod | grep IPAddress

Performance Issues

# Check active queries
PGPASSWORD=your_password psql -h localhost -p 5433 -U ferrero_user -d ferrero_tracking -c "
SELECT pid, now() - query_start as duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;
"

# Check table sizes
PGPASSWORD=your_password psql -h localhost -p 5433 -U ferrero_user -d ferrero_tracking -c "
SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(tablename::regclass)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;
"

# Analyze and vacuum
PGPASSWORD=your_password psql -h localhost -p 5433 -U ferrero_user -d ferrero_tracking -c "VACUUM ANALYZE;"

---

Scaling Considerations

For High Volume

• Connection Pooling: Use PgBouncer or similar
• Replication: Set up read replicas for query load
• Partitioning: Partition large tables by date
• Archiving: Move old data to archive tables

Recommended Limits

Metric	Development	Production
Max Connections	100	200-500
Shared Buffers	256MB	25% of RAM
Work Mem	4MB	16-64MB
Maintenance Work Mem	64MB	256MB-1GB

Configuration Tuning

Create /var/lib/ferrero-tracking/postgres/postgresql.conf.custom:

max_connections = 200
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200

---

Upgrading

Apply New Migrations

cd /opt/ferrero-tracking/database
./apply-migrations.sh localhost 5433 ferrero_tracking ferrero_user

Upgrade PostgreSQL Version

# Backup first!
sudo ferrero-db-backup.sh

# Update docker-compose.production.yml
# Change: image: postgres:15-alpine
# To:     image: postgres:16-alpine

# Stop and remove old container
sudo docker compose -f docker-compose.production.yml down

# Start with new version
sudo docker compose -f docker-compose.production.yml up -d

# Verify
sudo ferrero-db-status.sh

---

Accessing from Multiple Applications

Network Configuration

The database is accessible on:

• localhost:5433 (from the server)
• server-ip:5433 (from other machines, if firewall allows)

Connection Strings for Different Applications

Python (psycopg2):

postgresql://ferrero_user:password@server-ip:5433/ferrero_tracking

PHP (PDO):

pgsql:host=server-ip;port=5433;dbname=ferrero_tracking

Node.js (pg):

{
  host: 'server-ip',
  port: 5433,
  database: 'ferrero_tracking',
  user: 'ferrero_user',
  password: 'password'
}

JDBC (Java):

jdbc:postgresql://server-ip:5433/ferrero_tracking

Create Read-Only User for Applications

-- Connect as ferrero_user
CREATE USER app_readonly WITH PASSWORD 'readonly_password';
GRANT CONNECT ON DATABASE ferrero_tracking TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readonly;

-- Auto-grant on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;

---

Uninstall

Complete Removal

# Stop and remove containers
sudo docker compose -f docker-compose.production.yml down -v

# Remove data (WARNING: Permanent!)
sudo rm -rf /var/lib/ferrero-tracking
sudo rm -rf /var/backups/ferrero-tracking

# Remove management scripts
sudo rm /usr/local/bin/ferrero-db-*

# Remove cron job
sudo crontab -l | grep -v ferrero-db-backup | sudo crontab -

# Remove installation directory
sudo rm -rf /opt/ferrero-tracking

---

Production Deployment Checklist

Before Go-Live

• Change all default passwords
• Configure firewall rules
• Set up SSL/TLS
• Configure automated backups
• Test backup restoration
• Set up monitoring
• Document connection strings for all applications
• Create read-only database users
• Configure log rotation
• Set up alerts for disk space
• Load test with expected volume
• Train operators on management commands
• Document disaster recovery procedures

After Go-Live

• Monitor logs for errors
• Verify backups are running
• Check database performance metrics
• Verify all applications can connect
• Review security logs
• Test failover procedures (if applicable)

---

Support

Logs to Check

1. Database logs: docker compose logs postgres
2. Backup logs: /var/log/ferrero-db-backup.log
3. Deployment log: /var/log/ferrero-db-deployment.log

Common Issues

Connection refused:

• Check firewall rules
• Verify Docker container is running
• Check port is correct

Permission denied:

• Verify password is correct
• Check pg_hba.conf settings
• Ensure user has required permissions

Disk full:

• Check backup directory: /var/backups/ferrero-tracking
• Check data directory: /var/lib/ferrero-tracking/postgres
• Clean old backups or increase retention days

---

License

© Ferrero. All rights reserved.

Version

Database Schema Version: 1.1 (with upload_directory) Deployment Date: 2025-10-27