PostgreSQL

PostgreSQL is a general-purpose relational database for transactional workloads, application state, and structured analytics. In the current provider definitions, this provider is GA.

Capabilities​

Architecture​

Each PostgreSQL service is a dedicated PostgreSQL server running on Codesphere's Kubernetes infrastructure, powered by the CloudNativePG operator. Codesphere manages the full lifecycle: provisioning, configuration changes, minor version updates, volume resizing, and recovery from backups.

• Dedicated instance: Your database runs in its own pod with its own persistent volume. Nothing is shared with other services or teams.
• Storage: Data is stored on Ceph block storage (RBD), which is replicated at the storage layer.
• Backups: Backups are continuously archived to object storage, which enables both restoring a specific backup and point-in-time recovery.
• Container image: Codesphere ships its own PostgreSQL images, based on the CloudNativePG images, with the extensions listed below preinstalled.
• Single instance: Since the service runs a single instance without replicas, configuration updates and version upgrades might cause a short downtime.

Schema​

Config​

Secrets​

Details / Output​

Plan​

The provider exposes one plan, Small (id: 0). Example plan: Small (id: 0).

Example in a Landscape​

schemaVersion: v0.2

run:
  app-db:
    provider:
      name: postgres
      version: v1
    plan:
      id: 0
      parameters:
        storage: 2048
    config:
      version: "17.9"
      userName: "${{ workspace.env.PGUSER }}"
      databaseName: "${{ workspace.env.PGDATABASE }}"
    secrets:
      userPassword: "${{ vault.pgUserPassword }}"
      superuserPassword: "${{ vault.pgSuperuserPassword }}"

Constructing Hostnames​

Landscape-managed service hostnames are deterministic and follow this structure:

ms-{providerName}-{providerVersion}-{teamId}-landscape-{workspaceId}-{serviceName}.ms-postgres

The hostname is converted to lowercase and any invalid characters are replaced with hyphens.

If your PostgreSQL service is named db, the hostname can be constructed as ms-postgres-v1-$TEAM_ID-landscape-$WORKSPACE_ID-db.ms-postgres in shell commands or as ms-postgres-v1-${{ team.id }}-landscape-${{ workspace.id }}-db.ms-postgres in landscape templates.

Example ci.yml using the constructed hostname directly in a command:

schemaVersion: v0.2

run:
  db:
    provider:
      name: postgres
      version: v1
    plan:
      id: 0
      parameters:
        storage: 2048
    config:
      version: "17.9"
    secrets:
      userPassword: "${{ vault.pgUserPassword }}"
      superuserPassword: "${{ vault.pgSuperuserPassword }}"

  api:
    steps:
      - command: >
          psql "postgres://app:${PG_PASSWORD}@ms-postgres-v1-$TEAM_ID-landscape-$WORKSPACE_ID-db.ms-postgres:5432/app" -c "select 1"
    env:
      PG_PASSWORD: "${{ vault.pgUserPassword }}"

Example ci.yml using the constructed hostname through an environment variable:

schemaVersion: v0.2

run:
  db:
    provider:
      name: postgres
      version: v1
    plan:
      id: 0
      parameters:
        storage: 2048
    config:
      version: "17.9"
    secrets:
      userPassword: "${{ vault.pgUserPassword }}"
      superuserPassword: "${{ vault.pgSuperuserPassword }}"

  api:
    steps:
      - command: >
          psql "postgres://app:${PG_PASSWORD}@${PG_HOST}:5432/app" -c "select 1"
    env:
      PG_HOST: ms-postgres-v1-${{ team.id }}-landscape-${{ workspace.id }}-db.ms-postgres
      PG_PASSWORD: "${{ vault.pgUserPassword }}"

Within application runtimes, connect with the returned dsn or with hostname, port, the configured username, and the stored password. The same internal endpoint can also be used from other Codesphere runtimes, including reactives, managed containers, and workloads running inside a Virtual Cluster.

Connecting to a Postgres DB​

Prerequisites​

Once your Postgres DB is deployed, you can connect to it from your Codesphere workspaces. Each service lists its non-sensitive connection details on its settings page in the overview tab (or in the details property of the public API payload).

Connecting via Terminal (psql)​

You can use the psql command-line tool directly from your workspace terminal.

# Install psql
nix-env -iA nixpkgs.postgresql

# General syntax
psql "postgres://<username>@<hostname>:5432/<database>" -W

# Example
psql "postgres://[email protected]:5432/mydb" -W

Connecting via Node.js​

Using the pg library:

const { Client } = require('pg');

const client = new Client({
    connectionString: 'postgres://admin:[email protected]:5432/mydb'
});

await client.connect();
const res = await client.query('SELECT $1::text as message', ['Hello Codesphere!']);
console.log(res.rows[0].message); // Hello Codesphere!
await client.end();

Backups​

The PostgreSQL provider supports automated backups and point-in-time recovery to any S3-compatible backup store. This section contains the PostgreSQL-specific configuration; the general concepts are covered in Managed Service Backups.

Under the hood, backups are implemented with the CloudNativePG Barman Cloud plugin: it takes periodic physical base backups of the database and continuously archives the write-ahead log (WAL) to the configured backup store. A recovery restores a base backup and replays the archived WAL on top of it — this is what enables point-in-time recovery between two backups.

Enabling Backups​

The following example enables backups on an existing PostgreSQL service. The same backups block also works when creating a new service with POST /managed-services.

curl -X PATCH "https://api.codesphere.com/managed-services/YOUR_SERVICE_ID" \
  -H "Authorization: Bearer YOUR_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "backups": {
      "enabled": true,
      "intervalH": 12,
      "deleteRetentionDays": 30,
      "config": {
        "endpointUrl": "https://s3.eu-central-1.amazonaws.com",
        "destinationPath": "s3://my-codesphere-backups/",
        "accessKey": "YOUR_S3_ACCESS_KEY"
      },
      "secrets": {
        "secretKey": "YOUR_S3_SECRET_KEY"
      }
    }
  }'

Required S3 Permissions​

The credentials (access key and secret key) used to access your S3-compatible backup store must have sufficient permissions to create, read, update, and delete objects. For AWS S3, this means the IAM user or role associated with your access key must have the following minimum permissions.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "BucketLevelOperations",
      "Effect": "Allow",
      "Action": [
        "s3:GetBucketLocation",
        "s3:ListBucket",
        "s3:ListBucketMultipartUploads"
      ],
      "Resource": "arn:aws:s3:::YOUR_BUCKET_NAME"
    },
    {
      "Sid": "ObjectLevelOperations",
      "Effect": "Allow",
      "Action": [
        "s3:AbortMultipartUpload",
        "s3:DeleteObject",
        "s3:GetObject",
        "s3:ListMultipartUploadParts",
        "s3:PutObject",
        "s3:PutObjectTagging"
      ],
      "Resource": "arn:aws:s3:::YOUR_BUCKET_NAME/*"
    }
  ]
}

Recovery Examples​

Restoring a backup always creates a new managed service; the existing service remains untouched.

Point-in-Time Recovery​

Specify the previous managed service ID (msId) together with a recovery timestamp (time):

curl -X POST "https://api.codesphere.com/managed-services" \
  -H "Authorization: Bearer YOUR_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "teamId": 123,
    "name": "my-postgres-recovered",
    "provider": {
      "name": "postgres",
      "version": "v1"
    },
    "plan": {
      "id": 0,
      "parameters": { "storage": 2048 }
    },
    "config": {
      "version": "17.9"
    },
    "secrets": {
      "userPassword": "secure-password",
      "superuserPassword": "secure-superuser-password"
    },
    "recoverFrom": {
      "msId": "OLD_MANAGED_SERVICE_ID",
      "time": "2026-04-10T12:00:00Z",
      "config": {
        "endpointUrl": "https://s3.eu-central-1.amazonaws.com",
        "destinationPath": "s3://my-codesphere-backups/",
        "accessKey": "YOUR_S3_ACCESS_KEY"
      },
      "secrets": {
        "secretKey": "YOUR_S3_SECRET_KEY"
      }
    }
  }'

Recover from a Specific Backup​

Alternatively, recover from a specific backupId:

curl -X POST "https://api.codesphere.com/managed-services" \
  -H "Authorization: Bearer YOUR_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "teamId": 123,
    "name": "my-postgres-recovered",
    "provider": {
      "name": "postgres",
      "version": "v1"
    },
    "plan": {
      "id": 0,
      "parameters": { "storage": 2048 }
    },
    "config": {
      "version": "17.9"
    },
    "secrets": {
      "userPassword": "secure-password",
      "superuserPassword": "secure-superuser-password"
    },
    "recoverFrom": {
      "id": "BACKUP_UUID_HERE",
      "config": {
        "endpointUrl": "https://s3.eu-central-1.amazonaws.com",
        "destinationPath": "s3://my-codesphere-backups/",
        "accessKey": "YOUR_S3_ACCESS_KEY"
      },
      "secrets": {
        "secretKey": "YOUR_S3_SECRET_KEY"
      }
    }
  }'

Backup Disclaimers and Limitations​

The following limitations and requirements apply specifically to backing up and recovering PostgreSQL databases:

• Transaction Required: To recover a Postgres database, there needs to be at least one database transaction that occurred.
• Prior Backup Required: There must be at least one completed backup before your desired target recovery time.
• WAL-File Timing: The target recovery time must be before the latest transaction in the latest Write-Ahead Log (WAL) file. Note that it can take a few minutes for the latest WAL-file to be saved to the backup storage bucket.
• Version Compatibility: Recovering a backup using a different database version than the original backup is not guaranteed to work.
• Credentials Must Match: When recovering, you cannot change the database users, database name, and passwords to something other than what was originally stored in the backup.

Extensions​

The PostgreSQL image ships with a set of extensions, including PostGIS for spatial data and pgvector for vector similarity search. All extensions listed below are available on every instance, however they are not active until you enable them.

Enabling an Extension​

Extensions are enabled per database with CREATE EXTENSION. Connect to the target database as the postgres superuser and run:

-- Enable an extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Verify which extensions are installed
SELECT extname, extversion FROM pg_extension;

-- Disable an extension again
DROP EXTENSION "uuid-ossp";

You can list all available extensions and their versions directly from your instance:

SELECT name, default_version, installed_version FROM pg_available_extensions ORDER BY name;

Available Extensions​