Skip to main content

PostgreSQL

What is PostgreSQL?​

PostgreSQL is a powerful, object-relational database management system (ORDBMS) known for its stability, scalability and extensibility. It's open source and supports a wide variety of SQL features, including complex queries, transactions, indexing and JSON data.

PostgreSQL is suitable for both small applications and large, data-intensive systems and is used worldwide by companies and developers.

Creating a PostgreSQL database​

You can deploy a PostgreSQL database in your mittwald hosting environment using containers. There are several main approaches:

The most convenient way to deploy a PostgreSQL database is using Terraform. The following example shows how to use a PostgreSQL container in your own Terraform deployment:

variable "postgres_password" {
  type      = string
  sensitive = true
}

data "mittwald_container_image" "postgres" {
  image = "postgres:17"
}

resource "mittwald_container_stack" "postgresql" {
  project_id    = mittwald_project.example.id
  default_stack = true

  containers = {
    postgresql = {
      description = "PostgreSQL Database Server"
      image       = data.mittwald_container_image.postgres.image
      entrypoint  = data.mittwald_container_image.postgres.entrypoint
      command     = data.mittwald_container_image.postgres.command

      environment = {
        POSTGRES_DB       = "mydatabase"
        POSTGRES_USER     = "myuser"
        POSTGRES_PASSWORD = var.postgres_password
      }

      volumes = [
        {
          volume = "postgresql_data"
          mount_path = "/var/lib/postgresql/data"
        },
        {
          project_path = "/files/postgresql-backups"
          mount_path = "/mnt"
        }
      ]

      ports = [
        {
          container_port = 5432
          public_port    = 5432
          protocol       = "tcp"
        }
      ]
    }
  }

  volumes = {
    postgresql_data = {}
  }
}

You can also use the random_password resource to generate a password dynamically. Note that in this case you should also store this password securely in a secret manager, as it will not be retrievable afterwards.

resource "random_password" "postgres_password" {
  length = 24
}

data "mittwald_container_image" "postgres" {
  image = "postgres:17"
}

resource "mittwald_container_stack" "postgresql" {
  project_id    = mittwald_project.example.id
  default_stack = true

  containers = {
    postgresql = {
      description = "PostgreSQL Database Server"
      image       = data.mittwald_container_image.postgres.image
      entrypoint  = data.mittwald_container_image.postgres.entrypoint
      command     = data.mittwald_container_image.postgres.command

      environment = {
        POSTGRES_DB       = "mydatabase"
        POSTGRES_USER     = "myuser"
        POSTGRES_PASSWORD = random_password.postgres_password.result
      }

      volumes = [
        {
          volume = "postgresql_data"
          mount_path = "/var/lib/postgresql/data"
        },
        {
          project_path = "/files/postgresql-backups"
          mount_path = "/mnt"
        }
      ]

      ports = [
        {
          container_port = 5432
          public_port    = 5432
          protocol       = "tcp"
        }
      ]
    }
  }

  volumes = {
    postgresql_data = {}
  }
}

After creating the resource, you can access the PostgreSQL database from within your hosting environment via the URL postgresql://postgresql:5432.

Using the mStudio UI​

Alternatively, you can set up a PostgreSQL container manually:

  1. Go to the Container menu item in your project in mStudio and create a new container. You can choose any name.

  2. Enter the image postgres:17. You can keep the entrypoint and command as suggested.

Volumes​

To persistently store your PostgreSQL database data, define volumes under Volumes as follows:

  • Create new volume, on Path in Container (Mount Point): /var/lib/postgresql/data (Stores the database data)
  • Project path /files/postgresql-backups, on Path in Container (Mount Point): /mnt (This path is used to store database dumps or backups)

Environment Variables​

Set the following environment variables for the container:

POSTGRES_PASSWORD=mypassword
POSTGRES_USER=myuser # optional; default is "postgres"
POSTGRES_DB=mydatabase # optional; default is "postgres"

Ports​

Accept the suggested default port 5432.

Using CLI with mw container run​

You can also deploy a PostgreSQL container with the mittwald CLI and the mw container run command:

mw container run \
  --name postgresql \
  --env POSTGRES_DB=mydatabase \
  --env POSTGRES_USER=myuser \
  --env POSTGRES_PASSWORD=mypassword \
  --volume postgresql-data:/var/lib/postgresql/data \
  --volume /files/postgresql-backups:/mnt \
  --publish 5432:5432/tcp \
  --create-volumes \
  postgres:17

This command creates a new container named "postgresql" with the PostgreSQL image, sets all necessary environment variables and mounts volumes for persistent data storage.

Using CLI with mw stack deploy​

If you prefer Docker Compose, you can create a docker-compose.yml file and deploy it with the mw stack deploy command:

  1. Create a docker-compose.yml file with the following content:

    version: "3"
    services:
      postgresql:
        image: postgres:17
        environment:
          - POSTGRES_DB=mydatabase
          - POSTGRES_USER=myuser
          - POSTGRES_PASSWORD=mypassword
        ports:
          - "5432:5432"
        volumes:
          - postgresql_data:/var/lib/postgresql/data
          - /files/postgresql-backups:/mnt
    volumes:
      postgresql_data:

  2. Deploy the stack with the CLI:

    mw stack deploy

This approach is particularly useful when you want to deploy multiple containers that work together.

Accessing your container within your hosting environment​

Once the container is running, you can verify that the PostgreSQL instance is available. There are two main options:

Option 1: Direct access via container shell​

mw container ssh postgresql
psql -U myuser -d mydatabase

Option 2: Port forwarding for local access​

mw container port-forward postgresql 5432:5432

In a separate terminal, you can then connect locally:

psql -h localhost -p 5432 -U myuser -d mydatabase

With both options, you should be redirected to the PostgreSQL prompt where you can execute SQL commands.

Operations​

Your PostgreSQL data is backed up as part of regular project backups. However, for databases it is strongly recommended to create additional dumps to ensure consistency and recoverability.

A dump can be created directly in the container. Example command for a manual backup:

mw container exec postgresql "pg_dump -U myuser mydatabase -f /mnt/mydatabase.sql"

The backup file will then be available in the project under /files/postgresql-backups/.

Automation is easily possible via cronjobs.