PostgreSQL: Automate PostgreSQL with the CLI

Start PostgreSQL, retrieve its credentials, and change its configuration programmatically with the Stackhero CLI

👋 Welkom bij de Stackhero-documentatie!

Stackhero biedt een gebruiksklare PostgreSQL cloud oplossing die tal van voordelen biedt, waaronder:

Onbeperkte verbindingen en datatransfers.

PgAdmin webinterface inbegrepen.

Veel modules inbegrepen zoals PostGIS, TimescaleDB en PgVector.

Moeiteloze updates met slechts één klik.

Optimale prestaties en robuuste beveiliging aangedreven door een privé en toegewijde VM.

Bespaar tijd en vereenvoudig uw leven: het kost slechts 5 minuten om de PostgreSQL cloud hosting oplossing van Stackhero te proberen!

This guide shows how to create a PostgreSQL service, read its credentials, and update its configuration entirely from the command line, with no clicks in the dashboard. It is ideal for scripts, CI pipelines, and AI agents.

It uses the Stackhero CLI. Install it first:

curl -fsSL https://www.stackhero.io/install.sh | sh

1. Authenticate

The simplest way is to log in from your browser. The CLI opens a page where you approve the access (no password or 2FA code is ever handled by the CLI):

stackhero login

Your credentials are then saved locally and reused by every following command.

For scripts, CI pipelines, and AI agents, use a non-interactive access token instead. Create one from your dashboard (Account > Access tokens) and export it. The CLI (and any script) picks it up automatically:

export STACKHERO_TOKEN="usr-xxxxxx:your-token"

2. Find the PostgreSQL service store

List your organizations to get your organization id, then find the PostgreSQL store:

# Your organization id (org-xxxxxx)
stackhero organizations-list

# The PostgreSQL service stores available to that organization
stackhero services-store-list --organization-id=org-xxxxxx --name="postgresql"

Pick the svs-xxxxxx id of the version you want.

3. Pick an instance size and a region

# Instance sizes (ist-xxxxxx) available for that service store
stackhero instances-store-list --organization-id=org-xxxxxx --service-store-id=svs-xxxxxx

# Available regions
stackhero regions-list

4. Create the service

The script below creates a stack, adds PostgreSQL to it, waits until it is running, reads its configuration (which contains the generated credentials), and applies a new configuration.

#!/bin/bash
set -e

export STACKHERO_TOKEN="usr-xxxxxx:your-token"

organizationId="org-xxxxxx"
serviceStoreId="svs-xxxxxx"   # a PostgreSQL service store (step 2)
instanceStoreId="ist-xxxxxx"  # an instance size (step 3)
regionId="europe"             # a region id (step 3)

# Create a stack to host the service
stackId=$(stackhero --format=script stack-create \
  --organization-id="${organizationId}" \
  --name="My PostgreSQL stack")
echo "Stack created: ${stackId}"

# Add PostgreSQL to the stack
serviceId=$(stackhero --format=script service-add \
  --stack-id="${stackId}" \
  --service-store-id="${serviceStoreId}" \
  --instance-store-id="${instanceStoreId}" \
  --region-id="${regionId}")
echo "Service added: postgresql"

# Wait until the service is running (a couple of minutes)
stackhero service-wait-for --service-id="postgresql"

# Retrieve the service configuration, including its generated credentials
stackhero service-configuration-get --service-id="postgresql" --format=json

5. Retrieve credentials

service-configuration-get returns the full configuration of the service, including the generated passwords and connection details. As JSON (handy for scripts and agents):

stackhero service-configuration-get --service-id=svc-xxxxxx --format=json

6. Change the configuration

Get an example of the expected configuration, then apply your own:

# See the configuration schema and an example for this service
stackhero service-configuration-example --service-id=svc-xxxxxx

# Apply a new configuration (the service restarts to apply it)
stackhero service-configuration-set \
  --service-id=svc-xxxxxx \
  --configuration='{ "...": "..." }'

# Wait until the new configuration is applied
stackhero service-wait-for --service-id=svc-xxxxxx

That is the full lifecycle: start, read credentials, reconfigure, all scriptable. See the full CLI documentation for every command and the non-interactive STACKHERO_TOKEN authentication used here.