Tech Inject Docs

Appearance

PostgreSQL Setup ​

Overview ​

Our PostgreSQL database runs in a Docker container with SSL proxy access via nginx for secure remote connections.

Docker Configuration ​

Container Details ​

  • Image: postgres:17.4
  • Container Name: oee-oee_docker_postgres-1
  • Internal Port: 5432
  • Host Port: 5430
  • Database: vorne_live

Docker Compose ​

yaml
services:
  postgres:
    image: postgres:17.4
    container_name: oee-oee_docker_postgres-1
    ports:
      - "5430:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: vorne_live
    volumes:
      - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  postgres_data:

SSL Proxy Setup ​

Nginx Stream Configuration ​

Location: /etc/nginx/streams-available/postgres-ssl

nginx
upstream postgres_backend {
    server localhost:5430;
}

server {
    listen 6543 ssl;
    ssl_certificate /etc/letsencrypt/live/zero.hargunrana.com/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/zero.hargunrana.com/privkey.pem;
    ssl_protocols TLSv1.2 TLSv1.3;
    proxy_pass postgres_backend;
    proxy_connect_timeout 10s;
}

Nginx Main Config ​

Add to /etc/nginx/nginx.conf:

nginx
stream {
    include /etc/nginx/streams-enabled/*;
}

Enable the configuration:

bash
sudo ln -sf /etc/nginx/streams-available/postgres-ssl /etc/nginx/streams-enabled/
sudo nginx -t
sudo systemctl reload nginx

Firewall Configuration ​

iptables Rules ​

bash
# Allow port 6543 for SSL PostgreSQL proxy
sudo iptables -I INPUT 7 -p tcp --dport 6543 -j ACCEPT

OCI Security List ​

Required Ingress Rule:

  • Source CIDR: 0.0.0.0/0 (or specific IPs)
  • IP Protocol: TCP
  • Destination Port Range: 6543
  • Description: PostgreSQL SSL proxy

Connection Details ​

Remote Connection String ​

bash
# With SSL (recommended)
psql "postgresql://postgres:postgres@oee.hargunrana.com:6543/vorne_live?sslmode=require"

# Standard format
psql postgresql://postgres:postgres@oee.hargunrana.com:6543/vorne_live

Local Connection (on server) ​

bash
# Direct to container
psql postgresql://postgres:postgres@localhost:5430/vorne_live

# Via Docker
docker exec -it oee-oee_docker_postgres-1 psql -U postgres -d vorne_live

Connection Parameters ​

ParameterValue
Hostoee.hargunrana.com
Port6543
Databasevorne_live
Usernamepostgres
Passwordpostgres
SSL Moderequire

Troubleshooting ​

Check Container Status ​

bash
docker ps | grep postgres
docker logs oee-oee_docker_postgres-1

Check Port Listening ​

bash
netstat -tlnp | grep 5430  # Container port
netstat -tlnp | grep 6543  # Nginx proxy port

Test Connection ​

bash
# From server
telnet localhost 6543

# Check nginx stream
sudo tail -f /var/log/nginx/error.log

Common Issues ​

  1. Connection timeout: Check OCI Security List has port 6543 open
  2. SSL error: Verify SSL certificates are valid
  3. Container not running: Check with docker ps and restart if needed

Security Notes ​

  • Change default password in production
  • Restrict source IPs in OCI Security List
  • Use SSL/TLS for all connections
  • Regular backups recommended
  • Monitor connection logs