Last reviewed: 2026-09-03

Working with Postgres

Introduction

This guide shows how to add a PostgreSQL database to your application using the idp-postgresql chart. Databases run on an IDP-managed Aurora Serverless v2 cluster and are connected to your application through Kubernetes secrets.

Each database is named after your namespace ([namespace]-[database]), and its roles can only access that one database.

Table of contents

Define your Database

In your apps repository, add a subfolder for the database under the relevant namespace folder. If you are deploying both an application and a database together, two layouts work well:

App and database as siblings inside a service folder:

apps/team-dev/my-service/
├── app/
│   ├── application.yaml
│   └── values.yaml
└── db/
    ├── application.yaml
    └── values.yaml

App and database as separate top-level folders:

apps/team-dev/my-service/
├── application.yaml
└── values.yaml
apps/team-dev/my-service-db/
├── application.yaml
└── values.yaml

Both patterns are equally valid — pick whichever fits your team’s folder conventions.

Now define a values.yaml file describing the database you want to provision:

postgresqlDatabases:
  - name: my-db
    mode: standard

Mode can be either standard or protected. Using mode protected enables deletion protection. Deletion protection should be enabled for production databases. Enabling deletion protection will ensure the database in the Aurora cluster is not deleted if the database definition in the apps repository is deleted. If a protected database is accidentally deleted from the apps repo, follow the guide in backup and restore. For a list of all possible configuration options in values.yaml see README.

Next you need to create an application.yaml in the same subfolder in order to instruct ArgoCD how to create your app and deploy the chart:

apiVersion: v2
name: my-db
description: my-db
version: 0.1.0
slackChannel: my-slack-channel
helm:
  chart: helm/idp-postgresql
  chartVersion: "1.0.4" # Check https://github.com/jppol-idp/helm-idp/tree/main/charts/idp-postgresql for the latest version

Once this is committed, ArgoCD will automatically deploy the database. Expect the database to be fully ready in 1-3 minutes.

Database roles

In addition to the database, multiple other resources are created. Namely 3 different roles:

  • [namespace]-[database]-read
  • [namespace]-[database]-write
  • [namespace]-[database]-admin

The admin role is the owner of the public schema and is able to create more schemas in the database. However, when creating schemas besides public, the read and write users are not automatically granted privileges to the schemas.

The read role is granted USAGE on the public schema and read privileges (SELECT) on all tables and other objects the admin user creates in that schema.

The write role is granted USAGE on the public schema and write privileges (SELECT, INSERT, UPDATE, DELETE, TRUNCATE) on all tables and other objects the admin user creates in that schema.

The credentials for the roles are stored as AWS secrets along with the connection details. The secrets are stored as customer/[namespace]/[role].

Connect to Postgres

You can connect to Postgres in several ways: directly from your application in the Kubernetes cluster, through a managed pgAdmin instance, by proxying through RDS proxy from your local machine, or through a read-only Grafana datasource for dashboards.

Which one to use depends on what you are doing:

You want to… Use
Run the database from your deployed application Your application
Build dashboards, or run aggregated and filtered queries, over data your application accumulates in Postgres over time - application logs, request or event history, usage and metering records Grafana read-only datasource
Explore ad-hoc, scan whole tables, pull large or unbounded result sets, export CSV, or pull a full extract to hand off or analyse elsewhere RDS proxy with DataGrip, DBeaver, or psql, or pgAdmin

Grafana loads the full result of a query into memory before it can render it, so it is built for bounded dashboard queries, not bulk data extraction. Large or unfiltered queries are truncated at the row limit or fail to render.

Connect your Application

Once the database is created, configure your application to use it. Consider what level of privileges your app requires and select a role accordingly. Update your app’s values.yaml to load these as environment variables:

env:
  - name: DB_NAME
    value: [namespace]-[database]
  - name: DB_USER
    valueFrom:
      secretKeyRef:
        name: [namespace]-[database]-[read/write/admin]
        key: username
  - name: DB_PASSWORD
    valueFrom:
      secretKeyRef:
        name: [namespace]-[database]-[read/write/admin]
        key: password
  - name: DB_HOST
    valueFrom:
      secretKeyRef:
        name: [namespace]-[database]-[read/write/admin]
        key: endpoint
  - name: DB_PORT
    valueFrom:
      secretKeyRef:
        name: [namespace]-[database]-[read/write/admin]
        key: port

For example, when deploying my-db to the idp-dev namespace:

env:
  - name: DB_NAME
    value: idp-dev-my-db
  - name: DB_USER
    valueFrom:
      secretKeyRef:
        name: idp-dev-my-db-write
        key: username
(...)

Connect via pgAdmin

You can use the shared pgAdmin deployment if you prefer a web-based interface.

Query via Grafana

The IDP team can add your database to your cluster’s Grafana as a read-only datasource. Once it is added, it appears in the datasource picker when you build panels and in Explore. It connects as the [namespace]-[database]-read role, so it is SELECT-only and limited to that one database.

What it is for. Building dashboards and running bounded queries over data your application accumulates in Postgres over time - application logs, request or event history, usage and metering records. Think aggregates, filtered lookups, and time-series panels, not table dumps. For large result sets or full extracts, use RDS proxy or pgAdmin.

Limits that apply:

  • Results are capped at 200,000 rows. A query that would return more is silently truncated to the first 200,000 rows, so filter and aggregate in SQL rather than relying on the cap.
  • Grafana holds the whole result set in memory while it renders, and your browser then has to load all of it. A wide table, or one with large text columns such as a logs table, becomes unusable well before the row cap. Do not run SELECT * against a large table.

How to request it:

  1. Contact the IDP team in your onboarding Slack channel.
  2. Tell them the cluster, the database name, and that you want a read-only Grafana datasource for dashboards and exploration.
  3. The IDP team adds a datasource named Postgres [database] to that cluster’s Grafana.
  4. It is then available to everyone with access to that Grafana instance.

To change or remove the datasource, contact the IDP team.

Connect via local development environment or database tool

The IDP platform provides a shared RDS proxy that allows you to connect to your database from your local machine. This is useful for:

  • Running your application locally against a real database
  • Using database tools like pgAdmin, DBeaver, or DataGrip
  • Pulling large result sets or exporting whole tables, for example ad-hoc data extraction for an audit - work that is too big for the Grafana datasource

Start by following this guide to get kubectl access. Then port-forward the rds-proxy to your local machine:

kubectl port-forward -n rds-proxy svc/rds-proxy-idp-rds-proxy 5432:5432

Let the terminal run to keep the connection alive.

Retrieve the username and password for your database role from the corresponding secret in aws secretsmanager.

Connect your local application or database tool using:

  • Host: localhost
  • Port: 5432
  • Database: NAMESPACE-DATABASE (e.g., idp-dev-my-db)
  • Username/Password: from the secret above

To export a large table or query result, run \copy <table> TO 'out.csv' CSV HEADER from psql, or use pgAdmin’s Query Tool -> Download as CSV.

Monitoring

Prometheus Postgres Exporter collects Postgres specific metrics, while CloudWatch metrics provide health and performance metrics from the shared Aurora serverless v2 cluster. Metrics from both sources can be viewed in Grafana. For an example on how to use the metrics, see the PostgreSQL database example dashboard.

To query the data in your database itself (rather than its metrics) from Grafana, see Query via Grafana.

Backup and Restore

Snapshot retention

Automated snapshots are taken daily by the Aurora cluster between 02:00 and 04:00 CEST:

  • Production/Staging: 35 days of snapshots
  • Test/Dev: 7 days of snapshots

Disaster recovery from snapshot using pgAdmin

KOA has documented their restore experience in a playbook that is a useful reference: Disaster db restore IDP.

To restore a database from a snapshot using pgAdmin, follow these steps:

  1. Contact the IDP team in your onboarding Slack channel and request a restore.
  2. Find a suitable snapshot together with the IDP team.
  3. IDP initializes a restore cluster from the selected snapshot.
  4. Connect to the restore cluster using pgAdmin:
    • Get the new restore Aurora RDS cluster endpoint from the IDP team.
    • Use the web version of pgAdmin at [https://pgadmin..idp.jppol.dk/browser/](https://pgadmin..idp.jppol.dk/browser/).
  5. Take a backup of the desired database from the restore cluster:
    • In pgAdmin, right-click the database and select Backup.
    • Select Custom as the format.
    • Important: Select the correct admin role for the database (e.g. NAMESPACE-DATABASE-admin).
    • Click Backup.
  6. Connect to the primary DB cluster in pgAdmin.
  7. Restore the database on the primary cluster:
    • In pgAdmin, right-click the database and select Restore.
    • Select the backup file from step 5.
    • Under Query Options, enable “Clean before restore”.
    • Select the admin role for the database.
    • Click Restore.
  8. Notify the IDP team so they can shut down the restore cluster.

Disaster recovery without pgAdmin, using psql scripts directly from restore container

pgAdmin is likely not a suitable tool for restoring large databases. The restore procedure for large databases is a work in progress.

Protected database removed from apps repo

If a protected database is removed from the apps repo, the database, its data, and its stored credentials are all preserved. Only the Kubernetes connection secrets in your namespace are removed.

To restore it, re-add the database definition to the apps repo. The platform re-adopts the existing database automatically and rotates the role passwords while doing so, so restart your application pods once the database is healthy to pick up the new credentials.

SSL configuration

It is required that connections to your database are established using SSL. As a minimum, you should set sslmode to require in your connection string / connection settings. The different SSL modes are documented in the Postgres docs. When setting sslmode to require, data sent over the connection is encrypted but the identity of the server is not verified.

It is recommended, but not required, that connections to production databases are established using sslmode equals verify-full. This encrypts data sent over the connection, and the identity of the server is cryptographically verified. This does however require that you include and configure some extra certs in your container. Note that the certificates might already be baked into your container image or installed via a package manager. A guide to installing AWS’ certs can be found on the following AWS docs page: Using SSL with a PostgreSQL DB instance

Database engine updates

The IDP team manages all PostgreSQL engine updates on the shared Aurora cluster. Routine maintenance runs nightly between 02:00–04:00 UTC.

OS patches and minor version updates are applied during the maintenance window using zero-downtime patching. OS patches take under 5 minutes; minor version upgrades typically complete in a few seconds. A very small number of active connections may be dropped during the cutover.

Major version updates are performed using blue-green deployments outside the regular maintenance window. A fully replicated copy of the cluster is created, verified, and then switched over with a cutover that takes under 5 minutes. The IDP team will announce the window in advance. During the replication phase, DDL changes (schema changes, CREATE/DROP TABLE, etc.) must not be applied — they will block the switchover. The IDP team will coordinate with you on timing.

If you have questions about an upcoming update, reach out in your onboarding Slack channel.

Migrations

Data migration

If you have an existing PostgreSQL database outside the IDP platform and want to move it onto the IDP-managed Aurora cluster, the general approach is to provision a new IDP database, migrate the data, and then update your application to point to the new connection details.

KOA has documented their migration experience in a playbook that is a useful reference: Postgres DB IDP Migration Playbook.

Schema migration

The platform does not enforce a specific tool or workflow. We recommend applying schema migrations from your application at startup rather than as a separate CI step. Make sure your migration tool supports locking, so multiple replicas starting at once don’t apply migrations concurrently.

Common errors

no pg_hba.conf entry for host

If you get an error saying:

no pg_hba.conf entry for host (...)

Then you need to set sslmode to require in your connection string / connection settings. See section on SSL configuration for a more in detail explanation.