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.
Connect to your instance and access all enterprise components in minutes.
Connect using your EC2 key pair.
ssh -i your-key.pem ec2-user@<instance-ip>
All passwords are auto-generated on first boot.
sudo cat /var/lib/postgresql/credentials.txt
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
Web-based database management.
http://<instance-ip>/pgadmin4
Email: admin@localhost
Password: (from credentials file)
Store embeddings and perform similarity searches for RAG, semantic search, and AI applications.
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
);
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
);
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;
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);
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.
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
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
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
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
Lightweight connection pooler to efficiently manage database connections.
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
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
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;
Export PostgreSQL metrics for Prometheus and Grafana dashboards.
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
Add scrape target in prometheus.yml
scrape_configs:
- job_name: 'postgresql'
static_configs:
- targets: ['your-instance:9187']
pg_stat_activity - Active connections and queriespg_stat_database - Database-level statisticspg_stat_replication - Replication lag metricspg_locks - Lock monitoringSecure your PostgreSQL Enterprise Stack deployment.
Important: Change all default passwords immediately after first login.
sudo -u postgres psql -c \
"ALTER USER postgres WITH PASSWORD 'new-secure-password';"
Restrict ports 5432, 6432, 9187 to your IP or VPC CIDR only.
SSL/TLS is enabled by default for all connections.
Common issues and solutions.
sudo systemctl status postgresql-18 pgbouncer postgres_exporter
# PostgreSQL logs
sudo tail -100 /pgdata/postgresql/logs/postgresql-$(date +%Y-%m-%d).log
# System journal
sudo journalctl -u postgresql-18 -n 50
sudo systemctl restart postgresql-18
sudo systemctl restart pgbouncer
sudo systemctl restart apache2 # pgAdmin4
Our expert team is available 24/7 to assist you.
support@flowopsconsulting.co.uk