Postgres¶
You can provision and configure Postgres through nais.yaml
.
Warning
If you change the postgreSQL version your data will be lost, as a new database will be created. In other words to upgrade the database version you will need to do a migration as described here: Upgrade GCP postgreSQL
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.
Info
This feature is only available in GCP clusters. If you need on-prem databases, head over to navikt/database-iac.
Below is an example of the minimal configuration needed. See all configuration options in the nais.yaml reference.
...
kind: Application
metadata:
name: myapp
spec:
gcp:
sqlInstances:
- type: POSTGRES_12
databases:
- name: mydb
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.
key | environment variable | default |
---|---|---|
hostname | NAIS_DATABASE_MYAPP_MYDB_HOST |
127.0.0.1 |
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 |
postgres://username:password@127.0.0.1:5432/mydb |
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 have deployed your application with one configuration, and then change it later, you have to manually delete the google-sql-MYAPP secret before you make a new deploy:
$ kubectl delete secret google-sql-<MYAPP>
Cloud SQL Proxy¶
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
Sizing your database¶
By default, the database server has 1 vCPU, 614 MB RAM and 10GB of SSD storage with no automatic storage increase. If you need to change the defaults you can do this in nais.yaml
.
Administration¶
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.
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
.
Default 7 backups will be kept. More info here.
The backups can be found in the Google Cloud SQL instance dashboard.
Personal database access¶
Databases should always be accessed using a personal account, and the access should ideally be temporary.
Prerequisites¶
Install local binaries¶
Grant privileges to sql IAM users¶
Info
To be able to perform the gcloud commands mentioned below you need a role with user edit permissions, e.g. roles/cloudsql.editor
To grant yourself this role for a given project: gcloud projects add-iam-policy-binding <project> --member user:<your-email> --role roles/cloudsql.editor
Info
This is only required once per database instance and should be done before DDL scripts are run in the database, in order to ensure the objects have the right permissions.
Once the database instance is created, we need to grant the IAM users access to the "public" schema. This can either be done by using the default application database user during database creation/migration with scripts (e.g. flyway), or as a one-time setup by using the default postgres user:
In order to use the postgres user, you have to set a password first:
gcloud sql users set-password postgres --instance=<INSTANCE_NAME> --prompt-for-password --project <PROJECT_ID>
Then set up the cloudsql proxy and log in to the database (you will be prompted for the password you just set):
CONNECTION_NAME=$(gcloud sql instances describe <INSTANCE_NAME> --format="get(connectionName)" --project <PROJECT_ID>);
cloud_sql_proxy -instances=${CONNECTION_NAME}=tcp:5432
psql -U postgres -h localhost <DATABASE_NAME> -W
This can be enabled for all cloudsqliamusers with the following command: (all IAM users are assigned the role cloudsqliamuser)
alter default privileges in schema public grant all on tables to cloudsqliamuser;
Or for a specific user: (the IAM user must exist in the database):
alter default privileges in schema public grant all on tables to 'user@nav.no';
Granting temporary personal access¶
Create database IAM user¶
Info
This is required once per user and requires that you have create user permission in IAM in your project, e.g. Cloud SQL Admin
gcloud beta sql users create <FIRSTNAME>.<LASTNAME>@nav.no --instance=<INSTANCE_NAME> --type=cloud_iam_user --project <PROJECT_ID>
Create a temporary IAM binding for 1 hour:¶
gcloud projects add-iam-policy-binding <PROJECT_ID> --member=user:<FIRSTNAME>.<LASTNAME>@nav.no --role=roles/cloudsql.instanceUser --condition="expression=request.time < timestamp('$(date -v '+1H' -u +'%Y-%m-%dT%H:%M:%SZ')'),title=temp_access"
Log in with personal user:¶
export PGPASSWORD=$(gcloud auth print-access-token)
psql -U <FIRSTNAME>.<LASTNAME>@nav.no -h localhost <DATABASE_NAME>
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.
Maintenance window¶
Google will automatically perform upgrades, fix bugs and apply security patches to prevent exploits. Your application should be able to handle occational downtime as this maintenance is performed. Read more on maintenance windows here. NAIS will automatically configure the maintenance window to 4 AM (GMT+1), but can be overridden in 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.
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.
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
$ kubectl logs <pod> -c cloudsql-proxy
Example with all configuration options¶
See full example.