Service: PostgreSQL - EyevinnOSC/community GitHub Wiki

PostgreSQL

PostgreSQL (serviceId birme-osc-postgresql) is an open source relational database. In Eyevinn Open Source Cloud (OSC) it is a long-running service instance with up to 20 GiB of storage per instance. License: MIT (OSC image). Source: birme/osc-postgresql.

What it can do

  • Act as the database backend for your applications and analytics tools.
  • Act as the relational database for OSC services that need one (for example Umami, Ghost, Formbricks, PostgREST).
  • Run custom initialization on first start (PostgresInitDbArgs, PostgresInitDbSql).

Creating an instance

PostgreSQL has no dependencies on other OSC services. Store the password as a service secret first: create-service-secret with serviceId: "birme-osc-postgresql", name postgrespwd, and a strong password as value. Then call create-service-instance with serviceId: "birme-osc-postgresql":

{
  "name": "mydb",
  "PostgresPassword": "{{secrets.postgrespwd}}",
  "PostgresUser": "postgres",
  "PostgresDb": "myapp"
}

Verify with wait-for-service-ready or get-instance-status. describe-service-instance returns the IP address and port. Connect with any PostgreSQL client:

psql "postgres://postgres:<password>@<IP>:<PORT>/postgres"

or in an application:

DATABASE_URL=postgres://postgres:<password>@<IP>:<PORT>/postgres

Options

Option Required Type Meaning
name Yes string Name of the instance. Alphanumeric/underscore only (^\w+$), at most 20 characters.
PostgresPassword Yes string, sensitive Password of the PostgreSQL superuser. Store it as a secret and pass {{secrets.NAME}}. Applies only when the database is first initialized (see "Changing the password").
PostgresUser No string Username of the superuser account. Defaults to postgres.
PostgresDb No string Name of the default database created on first start. Defaults to the same name as the user if not set.
PostgresInitDbArgs No string Extra command-line arguments passed to initdb when the database cluster is initialized, for example for encoding, locale or authentication methods.
PostgresInitDbSql No string SQL commands or script content run during initialization, for example to create additional users or tables.

{{secrets.NAME}} only works as the entire field value.

Using the instance

Many OSC services (Umami, Ghost, Formbricks, PostgREST and others) accept a PostgreSQL connection URL. After creating the instance, store the full connection URL as a service secret on the target service and reference it with {{secrets.NAME}}. Create the PostgreSQL instance first and confirm it is running before creating the dependent instance.

OSC CLI equivalent:

osc create birme-osc-postgresql mydb \
  -o PostgresPassword="{{secrets.postgrespwd}}" \
  -o PostgresUser="postgres" \
  -o PostgresDb="myapp"

Changing the password

PostgresPassword sets the superuser password only when the instance first creates its database. After that the password lives in the database. Changing the service secret, or restarting the instance, does not change it. This follows the upstream image behavior: the Docker Hub postgres page states that "the Docker specific variables will only have an effect if you start the container with a data directory that is empty".

Updating a service secret does not restart or reconfigure running instances either. A service reads its secret values when it starts.

To rotate the password:

  1. Connect with the current password.
  2. Run \password postgres in psql, using your PostgresUser instead of postgres if you set one. psql prompts for the new password twice and sends it as a hash, so the new password is not written to the server log.
  3. Immediately update the service secret (for example postgrespwd) to the same new value. The PostgreSQL instance needs no restart.

The secret must be updated too because backups connect with the current value of the secret that PostgresPassword references. When it differs from the real password, backups fail with an authentication error.

Any other service secret that embeds this password, such as connection URLs for dependent services, must be updated as well, and that service restarted so it reads the new value.

A secret name used by several instances is shared, so changing it changes what backups of every instance referencing it use. Use one secret per instance, or rotate all of them together.

Stored secret values cannot be read back. Keep the old value until the rotation is finished.

Limitations

  • Storage is limited to 20 GiB per instance.
  • The superuser password cannot be changed by editing the secret alone; see "Changing the password".

Resources

⚠️ **GitHub.com Fallback** ⚠️