Postgres reference

Configuration¶

The full configuration options for the Postgres instance can be found in the application spec reference.

Database connnection¶

To connect your application to the database, use information from the environment variables below.

Naming conventions¶

The prefix NAIS_DATABASE_MYAPP_MYDB is automatically generated from the instance name myapp (defaults to application name) and mydb (from database spec).

You can customize these environment variable names by setting .spec.gcp.sqlInstances[].databases[].envVarPrefix.

For instance, setting this to DB will give you DB_HOST, DB_USERNAME, etc. Note that changing or adding envVarPrefix requires you to manually delete the google-sql-<MYAPP> secret and SQLUser with the same name as the application, see below.

If you have hyphens (-) in your application name or database name, they will be converted to underscores (_). E.g. my-awesome-app = MY_AWESOME_APP.

Database flags¶

Postgres in gcp supports setting database flags to alter the database instance performance and functionality, the flags available are listed here: Google Cloud SQL supported flags. This listing specifies what value types are expected, which ranges are allowed and if a restart is required.

Example of setting database flags:

...
kind: Application
metadata:
  name: myapp
spec:
  gcp:
    sqlInstances:
      - type: POSTGRES_14
        tier: db-f1-micro
        databases:
          - name: mydb
        flags:
        - name: autovacuum_max_workers
          value: "10"                           #integer in google spec, requires restart
        - name: autovacuum
          value: "on"                           #boolean in google spec
        - name: autovacuum_analyze_scale_factor
          value: "2"                            #float in google spec

Database max connections¶

The default maximum number of connections is dependent on the memory size of the instance and can be configured with the max_connections flag. The smallest instance size has a default of 25 maximum connections.

Default connection limits for different memory sizes can be found on Google Cloud SQL Configure database flags.

Database metrics¶

Postgres instances automatically collect metrics that can be viewed in the following locations:

• Google Cloud Console, under the Cloud SQL section.
• NAIS Console, under the Postgres section.
• Grafana, using the Google CLoud Monitoring datasource.

A list of all avaialbe metrics can be found on Cloud SQL metrics.

Learn how to view Postgres metrics in the Google Cloud Console, Grafana, and NAIS Console

Query insights¶

Query insights are now enabled by default in GCP. This feature provides query overview and analysis. The data is available in the Google cloud console.

For further reading see Google Cloud SQL Query Insights

Maintenance window¶

Google will automatically perform upgrades, fix bugs and apply security patches to prevent exploits. Your application should be able to handle occasional downtime as this maintenance is performed. Read more on maintenance windows. NAIS does not configure the maintenance window, but this can be set up in the application spec: nais.yaml. If you wish to be notified about upcoming maintenance, you can opt-in for this on the Communications page in the GCP console.

Automated backup¶

The database is backed up nightly at 3 AM (GMT+1) by default, but can be overridden in nais.yaml by setting spec.gcp.sqlInstances[].autoBackupTime. By default, seven backups will be kept. More info about Cloud SQL backups.

The backups can be found in the Google Cloud SQL instance dashboard.

Point-in-time recovery¶

Point-in-time recovery can be enabled by configuring this in the sql instance for your application spec. This feature allows you to recover your database to a specific point in time.

See application spec reference. For further reading see google Cloud SQL PIT recovery

Disaster backup¶

In case of catastrophic failure in GCP we are running a daily complete backup of the postgresql databases in GCP to an on-prem location. This backup currently runs at 5 am. This is in addition to the regular backups in GCP.

Postgres version¶

The Postgres version can be configured in the application spec. The version is defined by the type field in the nais.yaml file.

spec:
  gcp:
    sqlInstances:
      - type: POSTGRES_16

The full list of supported versions can be found in the application spec reference.

Learn how to safely upgrade the Postgres version

Server size¶

The server instance size can be configured in the application spec. The instance size is defined by the tier field in the nais.yaml file.

spec:
  gcp:
    sqlInstances:
      - type: POSTGRES_16
        tier: db-custom-2-5120

Learn how to change the server instance tier

The tier can be set to one of the following values:

The custom tiers can be customized to fit your application's requirements, following this format:

• Replace the <CPU> placeholder with the number of CPUs
• Replace the <RAM> placeholder with the amount of memory.

When selecting the number of CPUs and amount of memory, there are some restrictions on the configuration you choose:

• CPU must be either 1 or an even number between 2 and 96.
• Memory must be:
  • 0.9 to 6.5 GB per vCPU
  • A multiple of 256 MB
  • At least 3.75 GB (3840 MB)