Cloud SQL for Postgres

Runway supports provisioning of Cloud SQL for Postgres instances and will configure your workloads with the required environment variables to connect to Postgres.

Features

Cloud SQL Auth Proxy

Runway will deploy a sidecar on your workload enabling secure connectivity to your Cloud SQL instance.

Pre-defined instance types

Simply specify an instance type from a pre-defined list (search for instance_type_mapping).

For example: instead of db-custom-2-7680, you would specify xsmall.

Per-environment configuration

There are a few settings you can configure per-environment. Currently:

• instance_type
• disk_size_gb
• backup_configuration

See the schema documentation for more information on all the available configuration options.

Backups always on

Backups cannot be disabled. Certain settings (e.g., number of retained backups) can be configured on a per-environment basis.

Restore validation

Backups will be validated periodically and a validation query can be performed against the restored instance to ensure that the backup is not stale.

Maintenance window

There is a maintenance window set for all Cloud SQL instances of Sunday 06:00 UTC. Currently, it is not user-configurable, however we encourage you to leave feedback if this is something you would like to be able to configure for your use case.

Support for additional users

You can configure your Postgres instance to have additional users, which will be created using random passwords stored in Vault and made available to your workloads via environment variables.

Steps

Update provisioner

You need to create the postgres instance then link it to your workloads.

Create Postgres instance

In the provisioner repository, you will need to add your new Postgres instance to the postgres_instances key in the inventory.yml. For example:

postgres_instances:
  ...
  - name: runway-db-foo  # name must start with "runway-db-"
    identifier: FOO      # must be unique
    provider: GCP
    db_version: "POSTGRES_16"
    region: us-east1
    zone: us-east1-b
    database_configuration:
      databases:
        - your-db-name
    env_configuration:
      staging:
        instance_type: 2xsmall
        disk_size_gb: 10
      production:
        instance_type: xsmall
        disk_size_gb: 10
    labels:
      owner_email_handle: scalability-team
      department: eng-infra
      department_group: eng-infra-scalability
      product_category: scalability

Link Postgres instance to workloads

In the inventory section, you need to use the name defined above to link your workloads to the Postgres instance. Based on the example in the previous section, you would link a workload to the Postgres instance as follows:

inventory:
  ...
  - name: your-workload
    project_id: 123456
    postgres_instances:
      - runway-db-foo

Runtimes

Cloud Run

Update your runway.yml

Once the Cloud SQL instance has been provisioned, you need to configure your runway.yml for your workload(s) similar to the following:

apiVersion: runway/v1
kind: RunwayService # or RunwayJob
metadata:
  ...
spec:
  ...
  cloud_providers:
    gcp:
      cloudsql_instances:
        - instance_connection_name: gitlab-runway-<env>:<region>:<db-name>

Connecting to Cloud SQL

Runway assigns two ports for each instance listed in spec.cloud_providers.gcp.cloudsql_instances in your runway.yml:

• Cloud SQL Proxy - Proxy Port
• Cloud SQL Proxy - Admin Port

The proxy port starts at 5000 and the admin port starts at 5010:

• The first instance will use ports 5000 (proxy) and 5010 (admin)
• The second instance will use ports 5001 (proxy) and 5011 (admin)
• …

For this example, let’s assume you only have a single Cloud SQL instance. Your code will need to use the following details to connect:

• Host: localhost
• Port: 5000
• Username/Password: (see credentials section)

Credentials

You can connect to your instance either using the root user (postgres) or with any of the users you have defined in additional_users. Runway will always create random passwords for both the root user (postgres) and any additional users. The passwords are stored the same way as any other secret (see secrets management) and made available to any linked workloads through environment variables.

Connect using the postgres user

With a Postgres instance identifier value of FOO, you would have the following environment variable available to your workload:

RUNWAY_PG_USER_POSTGRES_PASSWORD_FOO

Connect using additional users

You can connect using any of your additional_users by using the following environment variable pattern:

RUNWAY_PG_USER_<NAME>_PASSWORD_<IDENTIFIER>

For example: say you define the following in the provisioner’s inventory.yml:

postgres_instances:
  ...
  - name: runway-db-foo
    identifier: FOO
    provider: GCP
    db_version: "POSTGRES_16"
    region: us-east1
    zone: us-east1-b
    additional_users:
      - name: app

Once you link this Postgres instance to your workload, you would have the following environment variable available:

RUNWAY_PG_USER_APP_PASSWORD_FOO

Using Cloud SQL from a RunwayJob

Problem

Jobs are different to RunwayService in that when jobs are triggered, the container is supposed to run until it has finished doing its work and then exit. Because we have a sidecar container running the Cloud SQL Proxy process as a daemon, it will never exit so the Runway Job will keep running until it eventually times out and the job is marked as failed.

Solution

You need to tell Cloud SQL Proxy to exit once your code has finished running. This is done by sending a HTTP GET request to the Cloud SQL Proxy admin port:

http://localhost:5010/quitquitquit

In this example, 5010 refers to the first Cloud SQL instance so adjust accordingly if you have more than one instance defined.

Restore validation

The provisioner creates a GitLab project on ops.gitlab.net for every Postgres instance (like the runway-db-example instance). Each project has a scheduled pipeline that restores the latest backup to another Cloud SQL instance and, optionally, runs a validation query against the restored database.

For production Cloud SQL instances, an alert will be fired if a restore validation pipeline was not triggered in the last 24 hours or if a pipeline has started but not completed within 2 hours.

The configuration is found in the env_configuration.<env>.backup_configuration.restore_validation field. The query should return 1 row of at least 2 columns to pass validation. The two columns can be data of any kind and will be printed in the CI job. Users can generate summary statistics and expose it through the two columns (see example below). The query should return no rows if the data is invalid. An alert will be fired if the validation query fails on production.

postgres_instances:
  ...
  - name: runway-db-foo
    ...
    env_configuration:
      staging:
        backup_configuration:
            restore_validation:
              database: runway
              user: postgres
              query: "SELECT COUNT(*) AS row_count, MAX(timestamp_column) AS most_recent_timestamp FROM your_table WHERE timestamp_column >= NOW() - INTERVAL 1 DAY LIMIT 1;"

Observability

Runway supports observability for managed Cloud SQL Postgres instances. By integrating with Runbooks, Runway provides default SLIs/SLOs, saturation monitoring/capacity planning, and common graphs for dashboards.

Right now, prerequisite for observability is service catalog entry. Follow these steps:

1. Create new entry in service catalog that uses same instance name defined in inventory: e.g. runway-db-example
2. Create new entry in metrics catalog: e.g.

   local postgresArchetype = import 'service-archetypes/runway-postgres-archetype.libsonnet';
   local metricsCatalog = import 'servicemetrics/metrics.libsonnet';
   
   metricsCatalog.serviceDefinition(
     postgresArchetype(
       type='runway-db-example',
       descriptiveName='Example Postgres managed by Runway'
     )
   )

3. Run make generate and commit autogenerated content

After approval and merging, a CI/CD pipeline will deploy your new dashboard similar to this one.

Feedback

We welcome your feedback!

Tell us what works well, what could be improved, what features are missing for your use case(s), etc. Thank you!