Managed PostgreSQL Setup

CloudNexus Managed PostgreSQL provides a fully managed, high-availability PostgreSQL service with automated backups, read replicas, and one-click scaling. This guide walks you through provisioning your first cluster, configuring connections, and securing your database in production.

Prerequisites

• CloudNexus account with billing configured
• CloudNexus CLI (@cloudnexus/cli) installed and authenticated
• A selected region for your cluster deployment
• Network configuration (VPC peering or public access enabled)

Step 1: Provision the Instance

Create a new PostgreSQL cluster using the CLI or dashboard. The example below provisions a db-micro-2 instance in the us-east-1 region with automated daily backups.

cloudnexus database create postgres \n  --name "prod-analytics-db" \n  --plan "db-micro-2" \n  --region "us-east-1" \n  --version "16.2" \n  --backup-retention "7d" \n  --ssl-mode "require"

Step 2: Connection Details & Credentials

Once provisioned, retrieve your connection string and credentials. CloudNexus generates a root user and a dedicated app user automatically.

cloudnexus database credentials "prod-analytics-db"

# Output:
POSTGRES_HOST    = pg.prod-analytics-db.us-east-1.cnx.io
POSTGRES_PORT    = 5432
POSTGRES_DB      = analytics
POSTGRES_USER    = cnx_app_user
POSTGRES_PASS    = [REDACTED]
POSTGRES_SSL     = true

Construct your connection string for ORM frameworks or direct drivers:

DATABASE_URL=postgresql://cnx_app_user:[REDACTED]@pg.prod-analytics-db.us-east-1.cnx.io:5432/analytics?sslmode=require

Step 3: Security & Network Configuration

Restrict access to your database using CloudNexus IP allowlists or VPC peering. By default, clusters are exposed on a private subnet with optional public endpoint access.

Update firewall rules via CLI:

cloudnexus database firewall update "prod-analytics-db" \n  --add-ip "203.0.113.45/32" \n  --add-ip "198.51.100.0/24" \n  --remove-default

Step 4: Migration & Data Import

CloudNexus supports direct pg_dump/pg_restore workflows. For large datasets, use our optimized bulk loader:

# Export from local/legacy DB
pg_dump -Fc -h localhost -U admin legacy_db > backup.dump

# Import to CloudNexus
cloudnexus database import "prod-analytics-db" \n  --file ./backup.dump \n  --method "bulk-optimized" \n  --parallel 4

Best Practices

1. Use connection pooling: Deploy PgBouncer via our managed proxy to handle high-concurrency workloads efficiently.
2. Monitor query performance: Enable pg_stat_statements and set up CloudNexus alerts for slow queries (>500ms).
3. Rotate credentials regularly: Use the CLI database credentials rotate command to avoid downtime.
4. Index strategically: Add composite indexes for frequently filtered columns. Run VACUUM ANALYZE periodically.
5. Scale read replicas early: Provision replicas before hitting write limits to distribute read-heavy traffic.

Troubleshooting

Connection Refused / Timeout

• Verify your IP is whitelisted in the firewall settings
• Check if SSL is enabled in your client driver
• Ensure the cluster status is RUNNING (cloudnexus database status)

High CPU / Memory Usage

Check active connections and long-running queries:

SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC LIMIT 5;