Google Cloud SQL / PostgreSQL

Info

Postgres 15 has changed the security model around the public schema and it is no longer world writeable. If you and your team make use of the public schema for interactive sessions and experimentation you will have to create separate schemas for separate users and share these role or user grants. Normal app usage will function normally.

PostgreSQL is a relational database service that is provided by Google Cloud Platform. It is a good choice for storing data that is relational in nature.

You can provision and configure Postgres through your application manifest.

The database is provisioned into the teams own project in GCP. Here the team has full access to view logs, create and restore backups and other administrative database tasks.

When you deploy your application with database config, NAIS will ensure the database exists in a Google Cloud SQL instance with the specified Postgres version, and configure the application with means to connect to it.

The Database instance takes a few minutes to be created, so your app will not be able to connect to right away. This only applies to the first time deploy.

Below is an example of the minimal configuration needed. See all configuration options in the application manifest reference.

...
apiVersion: nais.io/v1alpha1
kind: Application
metadata:
  name: myapp
...
spec:
  ...
  gcp:
    sqlInstances:
      - type: POSTGRES_14
        databases:
          - name: mydb

Choosing the right tier for production

By default, the database server is db-f1-micro which has 1 vCPU, 614 MB RAM and 10GB of SSD storage with no automatic storage increase. Shared CPU machine types (db-f1-micro and db-g1-small) are NOT covered by the Cloud SQL SLA. Consider changing to the db-custom-CPU-RAM tier for your production databases. Please also note that exhausting disk and/or CPU with automatic increase disabled is not covered by the SLA.

Configuration

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

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.

description	environment variable	example
ip	NAIS_DATABASE_MYAPP_MYDB_HOST	100.10.1.0
port	NAIS_DATABASE_MYAPP_MYDB_PORT	5432
database name	NAIS_DATABASE_MYAPP_MYDB_DATABASE	.spec.gcp.sqlInstances[].databases[].name
database user	NAIS_DATABASE_MYAPP_MYDB_USERNAME	.spec.gcp.sqlInstances[].name
database password	NAIS_DATABASE_MYAPP_MYDB_PASSWORD	(randomly generated)
database url with credentials	NAIS_DATABASE_MYAPP_MYDB_URL	postgresql://username:password@100.10.1.0:5432/mydb?sslcert=...
Below variables only available for instances with private IP
jdbc url with credentials	NAIS_DATABASE_MYAPP_MYDB_JDBC_URL	jdbc:postgresql://100.10.1.0:5432/mydb?password=...&user=...&sslcert=...
path to root cert	NAIS_DATABASE_MYAPP_MYDB_SSLROOTCERT	/var/run/secrets/nais.io/sqlcertificate/root-cert.pem
path to client cert	NAIS_DATABASE_MYAPP_MYDB_SSLCERT	/var/run/secrets/nais.io/sqlcertificate/cert.pem
path to client key	NAIS_DATABASE_MYAPP_MYDB_SSLKEY	/var/run/secrets/nais.io/sqlcertificate/key.pem
path to client key in DER format	NAIS_DATABASE_MYAPP_MYDB_SSLKEY_PK8	/var/run/secrets/nais.io/sqlcertificate/key.pk8
ssl mode	NAIS_DATABASE_MYAPP_MYDB_SSLMODE	verify-ca

Info

The application is the only application that can access the database instance. Other applications can not connect. It is not, for instance, possible to have two applications (e.g. producer and consumer) connecting directly to the database.

Info

Note that if you change your application name, database name or envVarPrefix, and then change it later, you have to manually reset database credentials.

Database flags

Info

Use database flags with caution, they will alter the behaviour of your postgres instance.

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.

Info

The value is always required to be a string in nais.yaml.

Example of setting database flags:

...
kind: Application
metadata:
  name: myapp
spec:
  gcp:
    sqlInstances:
      - type: POSTGRES_14
        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

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

Info

Data is available for seven days, increasing this will incur extra cost.

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.

Info

This feature is not enabled by default. When enabled the Postgres instance will be restarted.

Warning

Use this feature with automatic storage increase enabled.

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.

Cloud SQL credentials

Cloud SQL uses ConfigConnector/CNRM to create and manage all relevant resources (sqldatabase, sqlinstance, sqluser, credentials) for postgreSQL. When creating an application via your nais.yaml the database in your google project, along with other necessary resources, are created. The creation of the database takes about ten minutes, and the credential settings will be updated after the database is ready for use.

Warning

If you delete and recreate your app, new credentials will be created and a synchronization is needed. This process can take up to ten minutes. Using the workaround described below you can avoid this synchronization period.

Workaround for password synchronization issues

We recommend using nais-cli for rotating password for your Postgres database user.

nais postgres password rotate appname

Manually

Retrieve the password from the secret google-sql-MYAPP in your namespace (the password is base64 encoded):

kubectl get secret google-sql-<MYAPP> -o jsonpath="{ .data['<YOUR PASSWORD VARIABLE>'] }" | base64 -d

Log in to the Google Cloud Console and set the password manually for the application user in the sql instance: SQL -> DB_INSTANCE -> Users -> USERNAME -> Change password

Reset database credentials

Info

If you have multiple sql users their names will be on the format: <MYAPP>-<MYDB>-<SQLUSERNAME> instead of <MYAPP>

To reset the database credentials for your application (if application name, database name or envVarPrefix has been changed), you need to first delete the secret and sqluser for the database:

$ kubectl delete secret google-sql-<MYAPP>
$ kubectl delete sqluser <MYAPP>

Then either redeploy your application or force a synchronization of your application:

kubectl patch application <MYAPP> -p '[{"op": "remove", "path": "/status/synchronizationHash"}]' --type=json

Cloud SQL Proxy (instances created before 2024-04-18)

The application will connect to the database using Cloud SQL Proxy, ensuring that the database communication happens in secure tunnel, authenticated with automatically rotated credentials.

NAIS will add and configure the proxy client container as a sidecar in the pod, making it available on localhost for the application. The application will then connect to the proxy using standard database protocol just as if it was the actual database.

For more detailed information, check out the Cloud SQL Proxy documentation

Additional user(s) database(s)

You can add users to your database by setting database configuration option: .spec.gcp.sqlInstances[].databases[].users[].name. Additional users needs to manually be given access to the database and table. Either directly or with Flyway or other database migration tools.

Names added must match regex: ^[_a-zA-Z][_a-zA-Z0-9]+$. Secrets is generated and mounted for each user.

With .spec.gcp.sqlInstances[].databases[].envVarPrefix set to DB and additional username to _user2 you will get environment variables in format DB_USER2_MYDB_USERNAME etc.

Info

If you've deployed your application with an additional users, and then change name or remove the user from configuration, you need to manually delete the google-sql-<MYAPP>-<USER> secret:

$ kubectl delete secret google-sql-<MYAPP>-<USER>

Personal database access

Databases should always be accessed using a personal account, and the access should ideally be temporary.

Info

Personal database access can also be configured using the nais-cli.

Prerequisites

Step 1. Install local binaries

This guide assumes that you have the following installed on your local machine:

• nais-cli
• kubectl
• (Optionally for cli access) psql binary

We will use the nais postgres command from the CLI to set up the database access.

Step 2. Allow your user to edit Cloud SQL resources for your project

Ensure that you have authenticated gcloud by running

nais login

Step 3. Select the context and namespace of your application

If you have installed kubectx you can use the following command to select the context and namespace of your application:

kubectx <CLUSTER>
kubens <TEAM>

If you do not have kubectx installed, you can use the following command to select the context and namespace of your application:

kubectl config use-context <CLUSTER>
kubectl config set-context --current --namespace=<TEAM>

Step 3. One-time setup of privileges to SQL IAM users

This is only required once per database instance.

Once the database instance is created, we need to grant the IAM users access to the public schema.

nais postgres prepare <MYAPP>

Prepare will prepare the postgres instance by connecting using the application credentials and modify the permissions on the public schema. All IAM users with correct permissions in your GCP project will be able to connect to the instance.

The default is to allow only SELECT statements. If you need to allow all privileges, you can use the --all-privs flag.

nais postgres prepare --all-privs <MYAPP>

Granting temporary personal access

Step 1. Create database IAM user

This is required once per user and requires that you have access to the team's GCP project.

nais postgres grant <MYAPP>

This will give you a limited time access to the database unless there's already an existing permission for your user.

Step 3. Log in with personal user

Use nais postgres proxy to create a secure tunnel to the database.

nais postgres proxy <MYAPP>

This will start a proxy client in the background and print the connection string to the database.

Authenticate using your personal Google account email as username and leave the password empty.

If you'd like to use the psql binary, you can use the following command to connect to the database:

nais postgres psql <MYAPP>

This will create a proxy on a random port and execute the psql binary with the correct connection string.

Upgrading major version

Before doing a major version upgrade, you should check the Cloud SQL docs on upgrading PostgreSQL for an instance for any preparation that needs to be done.

When you are ready, you can change type in your nais.yaml to a new major version of PostgreSQL, and redeploy, and the upgrade will start. For safe upgrades, it is recommended to only do one major version at a time.

Warning

Upgrading requires the instance to become unavailable for a period of time depending on the size of your database (expect 10 minutes or more of downtime). Be sure to schedule your upgrade when your application can be offline.

Deleting the database

The database is not automatically removed when deleting your NAIS application. Remove unused databases to avoid incurring unnecessary costs. This is done by setting cascadingDelete in your nais.yaml-specification.

Danger

When you delete an Cloud SQL instance, you cannot reuse the name of the deleted instance until one week from the deletion date.

Debugging

Check the events on the Config Connector resources

$ kubectl describe sqlinstance <myapp>
$ kubectl describe sqldatabase <mydb>
$ kubectl describe sqluser <myapp>

Check the logs of the Cloud SQL Proxy (if instance uses cloudsql-proxy)

$ kubectl logs <pod> -c cloudsql-proxy

Example with all configuration options

See full example.

FAQ

FATAL: password authentication failed for user "<user>"

Answer

The synchronization of the password to the database may have failed. See workaround for password synchronization issues.

Connect to a cloned database-instance

Answer

If you have for some reason cloned a database in the console, you need to do some manually changes on the new database to be allowed to connect to it. First you need to log in to with the old username and password, then run GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "cloned-user"; to give the new cloned user access to all the old tables. If you have objects outside of tables those also needs to be changed. Also remember to delete the google-sql-appname-secret from the cluster, so new secrets are generated for the cloned database. After this you can update your nais.yaml-file to use the instance name of the cloned database instead of the old ones. Remember to delete the old database when you are finished.

Cloud Sql Conditions messages

Invalid request: backup retention should be >= transaction log retention

Answer

This error occurs when the backup retention is set to a value lower than the transaction log retention. The backup retention should be equal to or greater than the transaction log retention. You can fix this by setting the retainedBackups to a value equal to or greater than the transaction log retention or by setting the transactionLogRetentionDays to a value equal to or less than the backup retention. This can be configured in the NAIS manifest. Read more about automated backups with cloud sql.

Cannot disable cloudsql.logical_decoding while there are logical replication slots

Answer

This error occurs when you try to disable logical decoding while there are logical replication slots. Notes and limitations on disabling logical decoding.

...immutable field(s): [Field Name: settings.0.diskSize, Got: x, Wanted: xx]...

Answer

This error occurs when you try to change the disk size of the database instance. The disk size settings of the database instance cannot be less then current size after the instance is created. You can fix this by specifying in the NAIS manifest the desired disk size of the database instance to be equal to or greater than the current size. If you want to control the disk size of the instance you should disable automatic storage increase.

Not allowed to do major version upgrade from POSTGRES_x to POSTGRES_xx

Answer

This error occurs when you try to 'upgrade' the major version of the database instance to a version that is not allowed. You cant downgrade the major version of the database instance, if you want to downgrade the version you need to create a new instance with the desired version. You can fix this by specifying in the NAIS manifest the version type to the same as the current or a higher version.