Home
Enterprise Stack on AWS Marketplace

PostgreSQL 18 Enterprise Stack

Amazon Linux 2023 | Production-Ready | AI-Enabled

Complete enterprise database solution with pgvector for AI/ML workloads, pgBackRest for S3 backups, pgBouncer for connection pooling, and Prometheus monitoring.

PostgreSQL 18.7 pgvector 0.8.7 pgBackRest 2.53.1 pgBouncer 1.22.1 postgres_exporter 0.15.0

Quick Start Guide

Connect to your instance and access all enterprise components in minutes.

1

SSH to Your Instance

Connect using your EC2 key pair.

ssh -i your-key.pem ec2-user@<instance-ip>
2

View Credentials

All passwords are auto-generated on first boot.

sudo cat /var/lib/postgresql/credentials.txt
3

Connect to PostgreSQL

Direct connection or via pgBouncer (port 6432).

# Direct connection sudo -u postgres psql # Via pgBouncer (recommended for apps) psql -h localhost -p 6432 -U postgres
4

Access pgAdmin4

Web-based database management.

http://<instance-ip>/pgadmin4

Email: admin@localhost
Password: (from credentials file)

Service Ports

  • 5432 - PostgreSQL (direct)
  • 6432 - pgBouncer (connection pooling)
  • 80 - pgAdmin4 (web UI)
  • 9187 - postgres_exporter (Prometheus metrics)

pgvector - Vector Similarity Search

Store embeddings and perform similarity searches for RAG, semantic search, and AI applications.

Enable pgvector

Create the extension in your database.

-- Enable pgvector extension CREATE EXTENSION IF NOT EXISTS vector; -- Create a table with vector column CREATE TABLE documents ( id SERIAL PRIMARY KEY, content TEXT, embedding vector(1536) -- OpenAI dimension );

Insert Embeddings

Store vector embeddings from your AI model.

-- Insert with embedding INSERT INTO documents (content, embedding) VALUES ( 'Your document text', '[0.1, 0.2, ..., 0.9]'::vector );

Similarity Search

Find similar documents using cosine distance.

-- Find 5 most similar documents SELECT content, 1 - (embedding <=> query_vec) AS similarity FROM documents ORDER BY embedding <=> query_vec LIMIT 5;

Create Index for Performance

HNSW index for fast approximate searches.

-- Create HNSW index (recommended) CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); -- Or IVFFlat for larger datasets CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

RAG Application Example

  • Use OpenAI/Cohere/HuggingFace to generate embeddings
  • Store embeddings in pgvector with your documents
  • Query with user's question embedding to find relevant context
  • Pass context to LLM for accurate, grounded responses

pgBackRest - S3 Backups with PITR

Enterprise backup solution with S3 storage, compression, and point-in-time recovery.

Setup Required: pgBackRest needs an S3 bucket and IAM role configured before use. Follow the steps below.

1. Create S3 Bucket & IAM Role

Create resources in AWS Console or CLI.

# Create S3 bucket aws s3 mb s3://my-pg-backups-bucket # Attach IAM role to EC2 instance with: # - s3:PutObject, s3:GetObject, s3:DeleteObject # - s3:ListBucket on your backup bucket

2. Configure pgBackRest

Edit the configuration file.

sudo nano /etc/pgbackrest/pgbackrest.conf # Add/update these settings: [global] repo1-type=s3 repo1-s3-bucket=my-pg-backups-bucket repo1-s3-region=us-east-1 repo1-path=/backups repo1-retention-full=2 [main] pg1-path=/pgdata/postgresql/data

3. Initialize & Run Backup

Create stanza and run first backup.

# Initialize stanza sudo -u postgres pgbackrest --stanza=main stanza-create # Run full backup sudo -u postgres pgbackrest --stanza=main backup --type=full # Check backup info sudo -u postgres pgbackrest --stanza=main info

4. Point-in-Time Recovery

Restore to any point in time.

# Stop PostgreSQL sudo systemctl stop postgresql-18 # Restore to specific time sudo -u postgres pgbackrest --stanza=main \ --target="2024-01-15 14:30:00" \ --target-action=promote restore # Start PostgreSQL sudo systemctl start postgresql-18

Backup Schedule Recommendation

  • Daily: Incremental backup at off-peak hours
  • Weekly: Full backup on weekends
  • Retention: Keep 2 full + 14 incremental

pgBouncer - Handle Thousands of Connections

Lightweight connection pooler to efficiently manage database connections.

Connect via pgBouncer

Use port 6432 instead of 5432 for pooled connections.

# Connection via pgBouncer psql -h localhost -p 6432 -U postgres -d mydb # Connection string for apps postgresql://postgres:PASSWORD@localhost:6432/mydb

Configuration

Tune pooling settings as needed.

# Edit config sudo nano /etc/pgbouncer/pgbouncer.ini # Key settings: pool_mode = transaction max_client_conn = 1000 default_pool_size = 20 # Restart after changes sudo systemctl restart pgbouncer

Monitor Pools

Check connection pool status.

# Connect to admin console psql -h localhost -p 6432 -U postgres pgbouncer # Show pool stats SHOW POOLS; SHOW STATS; SHOW CLIENTS;

Pool Modes

  • transaction (default) - Connection returned after each transaction
  • session - Connection held for entire session
  • statement - Connection returned after each statement

Prometheus Monitoring with postgres_exporter

Export PostgreSQL metrics for Prometheus and Grafana dashboards.

Enable Monitoring

Start the postgres_exporter service.

# Enable and start exporter sudo systemctl enable postgres_exporter sudo systemctl start postgres_exporter # Verify metrics endpoint curl http://localhost:9187/metrics | head

Prometheus Configuration

Add scrape target in prometheus.yml

scrape_configs: - job_name: 'postgresql' static_configs: - targets: ['your-instance:9187']

Key Metrics Available

  • pg_stat_activity - Active connections and queries
  • pg_stat_database - Database-level statistics
  • pg_stat_replication - Replication lag metrics
  • pg_locks - Lock monitoring

Security Recommendations

Secure your PostgreSQL Enterprise Stack deployment.

Important: Change all default passwords immediately after first login.

Change Passwords

sudo -u postgres psql -c \ "ALTER USER postgres WITH PASSWORD 'new-secure-password';"

Security Groups

Restrict ports 5432, 6432, 9187 to your IP or VPC CIDR only.

SSL Enabled

SSL/TLS is enabled by default for all connections.

Troubleshooting

Common issues and solutions.

Check Service Status

sudo systemctl status postgresql-18 pgbouncer postgres_exporter

View Logs

# PostgreSQL logs sudo tail -100 /pgdata/postgresql/logs/postgresql-$(date +%Y-%m-%d).log # System journal sudo journalctl -u postgresql-18 -n 50

Connection Issues

  • Check security group allows your IP
  • Verify correct port (5432 or 6432)
  • Check credentials file for password

Restart Services

sudo systemctl restart postgresql-18 sudo systemctl restart pgbouncer sudo systemctl restart apache2 # pgAdmin4

Need Help?

Our expert team is available 24/7 to assist you.

support@flowopsconsulting.co.uk
24/7 Support Available