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.

Property	Value	Notes
Provider name	postgres	Use this in landscape provider definitions.
Version	v1	Current schema version exposed by the provider.
Category	Database	Shown in the managed services catalog.
Scope	global	Available at resource group scope rather than being tied to a single workspace runtime.
Resource group singleton	false	Resource groups can create multiple PostgreSQL service instances.
Pause support	true	This provider supports pausing.

Capabilities

Capability	Supported	Notes
Backups	✅	Backups of the database are supported.
Point-in-time recovery	✅	The database can be restored to a specific point in time.

Good to know

• Upgrades are never applied automatically. Manual minor version upgrades are supported via the version config field.
• Storage does not grow automatically, but you can increase the storage plan parameter manually at any time.

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 resource groups.
• 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: A service with a single instance has no replicas, so configuration updates, version upgrades, and platform updates cause a short downtime. See Shutdown behavior.
• High availability: With three instances, restarts cause only a short interruption. See Shutdown behavior.

Schema

Config

Field	Type	Required on create	Notes
version	string	No	PostgreSQL engine version. Default: 17.9. Allowed values: 17.9, 17.6, 16.13, 16.10, 15.17, 15.14, 14.22, 14.19. Minor upgrades only.
userName	string	No	Default: app. Immutable after creation. Cannot be postgres.
databaseName	string	No	Default: app. Immutable after creation.

Secrets

Field	Type	Required on create	Notes
userPassword	string	Yes	Password for the application user defined by userName.
superuserPassword	string	Yes	Password for the postgres superuser.

Details / Output

Field	Type	Availability	Notes
hostname	string	Exposed after provisioning	Internal service hostname.
port	integer	Exposed after provisioning	PostgreSQL port.
dsn	string	Exposed after provisioning	Connection string returned by the provider.
ready	boolean	Exposed after provisioning	Indicates whether the instance is ready for connections.

Plan

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

Parameter	Type	Default	Minimum	Maximum	Static	Notes
cpu	number	1	-	-	Yes	Priced as cpu-tenths.
memory	integer	128	-	-	Yes	Priced as ram-mib.
storage	integer	1024	512	-	No	Priced as storage-mib.

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}-{resourceGroupId}-landscape-{workspaceId}-{serviceName}.rg-{resourceGroupId}

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

Older services use the ms-postgres suffix instead, for example ms-postgres-v1-42-landscape-100-db.ms-postgres.

This change is not breaking: new services are also reachable through an alias with the ms-postgres suffix, so existing configurations and scripts that use .ms-postgres hostnames keep working.

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

Provider	Version	Resource Group ID	Workspace ID	Service Name	Hostname
postgres	v1	42	100	db	ms-postgres-v1-42-landscape-100-db.rg-42

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.rg-$TEAM_ID: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-${{ resourceGroup.id }}-landscape-${{ workspace.id }}-db.rg-${{ resourceGroup.id }}
      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).

info

Before connecting, make sure the service is synchronized and running: in the service settings, ready must be true.

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"
      }
    }
  }'

warning

The endpointUrl must be the regional S3 endpoint, not a bucket-specific hostname. For example, https://s3.eu-central-1.amazonaws.com instead of https://my-bucket.s3.eu-central-1.amazonaws.com.

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 '{
    "resourceGroupId": 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 '{
    "resourceGroupId": 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 storage and WAL archiving

Backups write two kinds of data to the backup store:

• Base backups are full copies of the database, taken every intervalH hours.
• WAL (write-ahead log) is archived continuously, starting with the first base backup.

Both are compressed with gzip. Disabling backups stops both base backups and WAL archiving. Existing backups stay in the bucket.

Sizing the backup store

Backups older than deleteRetentionDays are deleted, together with the WAL that only they needed. Plan the bucket for roughly:

compressed base backup size × (deleteRetentionDays × 24 / intervalH)
+ compressed WAL written during deleteRetentionDays

Each base backup is roughly the size of your database, reduced by compression.

warning

Use a separate bucket and quota for each managed service. If several databases share one quota, one write-heavy database can fill it and stop backups for all of them.

If your backup store is a Codesphere Object Storage service, its limits (maxSizeKb, maxObjects) apply to all of its buckets together. Separate buckets in the same service still share one quota, so create one Object Storage service per database.

What happens when the backup store is full

PostgreSQL keeps WAL on the database's disk until it is archived. If archiving fails, for example because the bucket is full, WAL builds up on the disk. When the disk is full, PostgreSQL stops and the service becomes unavailable.

To prevent this, keep enough free space in the bucket. If archiving fails, free up space or lower deleteRetentionDays. Archiving then catches up and the WAL is removed from the disk.

Storage only grows

You can increase a service's storage, but you can't decrease it.

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

Extension	Version	Description
address_standardizer	3.6.0	Parse an address into constituent elements, generally used to support geocoding address normalization.
address_standardizer_data_us	3.6.0	Address Standardizer US dataset example.
amcheck	1.4	Functions for verifying relation integrity.
autoinc	1.0	Functions for autoincrementing fields.
bloom	1.0	Bloom access method — signature file based index.
btree_gin	1.3	Support for indexing common datatypes in GIN.
btree_gist	1.7	Support for indexing common datatypes in GiST.
citext	1.6	Data type for case-insensitive character strings.
cube	1.5	Data type for multidimensional cubes.
dblink	1.2	Connect to other PostgreSQL databases from within a database.
dict_int	1.0	Text search dictionary template for integers.
dict_xsyn	1.0	Text search dictionary template for extended synonym processing.
earthdistance	1.2	Calculate great-circle distances on the surface of the Earth.
file_fdw	1.0	Foreign-data wrapper for flat file access.
fuzzystrmatch	1.2	Determine similarities and distance between strings.
hstore	1.8	Data type for storing sets of (key, value) pairs.
insert_username	1.0	Functions for tracking who changed a table.
intagg	1.1	Integer aggregator and enumerator (obsolete).
intarray	1.5	Functions, operators, and index support for 1-D arrays of integers.
isn	1.2	Data types for international product numbering standards.
lo	1.1	Large Object maintenance.
ltree	1.3	Data type for hierarchical tree-like structures.
moddatetime	1.0	Functions for tracking last modification time.
pageinspect	1.12	Inspect the contents of database pages at a low level.
pg_buffercache	1.5	Examine the shared buffer cache.
pg_freespacemap	1.2	Examine the free space map (FSM).
pg_prewarm	1.2	Prewarm relation data.
pg_stat_statements	1.11	Track planning and execution statistics of all SQL statements executed.
pg_surgery	1.0	Extension to perform surgery on a damaged relation.
pg_trgm	1.6	Text similarity measurement and index searching based on trigrams.
pg_visibility	1.2	Examine the visibility map (VM) and page-level visibility info.
pg_walinspect	1.1	Functions to inspect contents of the PostgreSQL Write-Ahead Log.
pgaudit	17.1	Provides auditing functionality.
pgcrypto	1.3	Cryptographic functions.
pgrowlocks	1.2	Show row-level locking information.
pgstattuple	1.5	Show tuple-level statistics.
plpgsql	1.0	PL/pgSQL procedural language. Installed by default.
postgis	3.6.0	PostGIS geometry and geography spatial types and functions.
postgis_raster	3.6.0	PostGIS raster types and functions.
postgis_sfcgal	3.6.0	PostGIS SFCGAL functions.
postgis_tiger_geocoder	3.6.0	PostGIS TIGER geocoder and reverse geocoder.
postgis_topology	3.6.0	PostGIS topology spatial types and functions.
postgres_fdw	1.1	Foreign-data wrapper for remote PostgreSQL servers.
refint	1.0	Functions for implementing referential integrity (obsolete).
seg	1.4	Data type for representing line segments or floating-point intervals.
sslinfo	1.2	Information about SSL certificates.
tablefunc	1.0	Functions that manipulate whole tables, including crosstab.
tcn	1.0	Triggered change notifications.
tsm_system_rows	1.0	TABLESAMPLE method which accepts number of rows as a limit.
tsm_system_time	1.0	TABLESAMPLE method which accepts time in milliseconds as a limit.
unaccent	1.1	Text search dictionary that removes accents.
uuid-ossp	1.1	Generate universally unique identifiers (UUIDs).
vector	0.8.1	Vector data type and ivfflat and hnsw access methods (pgvector).
xml2	1.1	XPath querying and XSLT.

Shutdown behavior

The database is shut down and restarted from time to time, for example for configuration changes, image updates, or platform updates. How the PostgreSQL service behaves depends on its number of instances.

Single instance

With a single instance, the database stops and starts again. Your data stays on the persistent volume and is not affected by the restart.

The downtime depends on whether clients keep their connections open when the shutdown starts. The database stops accepting new connections right away and gives existing connections up to 180 seconds to close on their own. After that, the remaining connections are closed.

Situation	Behavior	Approximate downtime
All clients disconnect shortly after the shutdown starts	The database stops cleanly without waiting.	About 1 minute
At least one client keeps its connection open	The database waits 180 seconds, then closes the remaining connections.	About 4 minutes

New connections are refused from the moment the shutdown starts. This means the database is unavailable for new clients for the whole duration, even while existing connections are still working.

Applications with a connection pool usually keep long-lived connections and do not disconnect on their own. Expect the longer case for these applications.

High availability (three instances)

With three instances, the service runs one primary and two replicas. The instances are spread across nodes where possible.

When the primary is shut down, the service switches over to a replica. The old primary is stopped right away without waiting for open connections, and a replica takes over as the new primary.

Situation	Behavior	Approximate downtime
The primary is shut down	A replica takes over as primary. Existing connections are closed right away.	About 10 to 20 seconds

Clients must reconnect after the switchover. Applications that retry failed connections recover on their own.

Data safety

• Committed transactions are safe.
• Transactions that are still open when connections are closed are rolled back. Clients receive a connection error and must retry.
• With three instances, the old primary is stopped cleanly before a replica is promoted, so the new primary has all committed data.