infra_wiki

Operate Postgres

Find, connect to, inspect and restart the CloudNativePG Postgres databases with the kubectl cnpg plugin, and check which ones have a backup.

Use this runbook to find, connect to, inspect or restart a Postgres database, and to check its backup. To change the instances, storage or Postgres version of a database, change its manifest, as Apply a Kubernetes change describes. Each database is a CloudNativePG Cluster object. CloudNativePG is the Kubernetes controller that runs Postgres, and kubectl cnpg is its kubectl plugin. The cnpg commands find the primary pod and container for you.

warning

This runbook restarts databases and runs psql by hand. It is an exception to the GitOps rule because git cannot hold a restart or a query.

Before you start#

  • Get kubectl access, as Get cluster admin access describes.
  • Install kubectl-cnpg. The owner's dotfiles install it with mise, and the Rowbutt sandbox image carries it at the operator's version.
  • Put --context <site> after the cnpg command. <site> is folly or offsite.

Find a database#

Declared inChange it through
clusters/folly/apps/tronbyt/04-database.yamlGit
clusters/offsite/apps/mate/database.yamlGit
packages/charts/*/templates/database.yaml, in each chart that has oneGit, in the chart values of the HelmRelease
The kthx-datastores namespacekthx. Each is the database that kthx makes for a built app.

clusters/base/platform/cloudnative-pg/ installs the controller on both clusters.

  1. List the databases of a cluster.

    kubectl get cluster.postgresql.cnpg.io -A --context <site>

    Result: A line for each database, with its INSTANCES, STATUS and PRIMARY.

Connect to a database#

  1. Open a psql session in the app database.

    kubectl cnpg psql <name> -n <namespace> --context <site> -- -d <database>

    Result: The <database>=# prompt. Without -d, psql opens the postgres database.

  2. To run one statement, give psql its flags after --.

    kubectl cnpg psql <name> -n <namespace> --context <site> -- -d <database> -At -c 'select 1'

    Result: 1.

Inspect a database#

  1. Show the health, primary and backup state.

    kubectl cnpg status <name> -n <namespace> --context <site>

    Result: Status: Cluster in healthy state and the Primary instance.

  2. Read the logs of all instances.

    kubectl cnpg logs cluster <name> -n <namespace> --context <site> --tail 50

    Result: JSON log lines from each instance.

Restart a database#

note

folly's tally-db and tronbyt each run two instances on two nodes, so a node can go down while the other instance serves. Every other live Cluster has one instance and no standby.

caution

On a one-instance Cluster, a restart stops the database until its pod is ready again. On a two-instance Cluster, CNPG restarts the standby, switches over, then restarts the old primary.

  1. Restart the database.

    kubectl cnpg restart <name> -n <namespace> --context <site>

    Result: <name> restarted.

  2. Make sure that kubectl cnpg status shows Cluster in healthy state.

Check the backups#

Each database except mate-db has a barman ObjectStore named garage in its namespace, a ScheduledBackup, and WAL archiving through the barman-cloud plugin. The store is the cnpg bucket of the site's Garage, under <namespace>/. A chart's backup value sets the endpoint and the 1Password item. Backups describes the schedule, the copy to GCS and the retention. To restore a database, use Restore a database.

A Postgres Datastore in kthx-datastores archives to that namespace's garage store, which clusters/base/platform/kthx-engine-target/backup/ declares. The kthx engine owns each Datastore's Cluster and ScheduledBackup. It turns the plugin on when it provisions a Datastore, and on an older one at the hourly check of an attached Datastore. The first check after the Cluster is ready with the plugin adds the ScheduledBackup. A Datastore attached to no App is not checked.

DatabaseExtra backup
kthx (kthx-db)The CronJob kthx-db-backup writes a pg_dumpall to gs://bluenose-kthx/backups/pg/ each night. The bucket deletes a dump after 30 days.
mate (mate-db)Its only backup. The CronJob mate-db-backup writes a pg_dump to gs://homelab-ng-mate/backups/pg/ each night, without the ChatGPT sign-in. The bucket deletes a dump after 30 days.
The engine database (kthx-engine-db)None. keepOnDelete keeps the Cluster and its data if the release is deleted.
Prowler (prowler-db), tronbyt (tronbyt, on folly), the DatastoresNone.

warning

A database with neither an ObjectStore nor a dump has no backup. If it loses its volume, its data is lost.

  1. Read the backup lines in the kubectl cnpg status output of the database.

    Result: Continuous Backup status with the first and last point of recoverability.

  2. List the backups of the database.

    kubectl get backup -n <namespace> --context <site>

    Result: A row for each backup, with the phase completed and the newest less than a day old.

If something goes wrong#

SymptomCauseAction
flags cannot be placed before plugin name: --context--context is before cnpg.Put --context after the cnpg command.
unknown command "cnpg" for "kubectl"The plugin is not installed.Run mise install github:cloudnative-pg/cloudnative-pg.
cnpg status shows no primary.The Cluster is in bootstrap or in a restart.If no primary shows after 5 minutes, read kubectl cnpg logs cluster.
BackupJobStale or BackupJobFailed fires.The backup-push CronJob has not succeeded in its window.Read the logs of each container of the last Job of the CronJob in the alert.
CNPGBackupStale fires.A database has no backup newer than 36 hours. An immediate backup can run before a recovering Cluster has a pod.Run kubectl get scheduledbackup,backup -n <namespace>, then read kubectl cnpg status. If the Cluster is ready without a completed base backup, run kubectl cnpg backup as in Recover a re-created database.
KthxBackupFailing fires.No kthx dump has succeeded for 36 hours.Read the logs of the last kthx-db-backup Job.
MateDatabaseBackupFailing fires.No mate dump has succeeded for 36 hours.Read the logs of the last mate-db-backup Job. A 403 from upload is the grant in terraform/gcp/projects/homelab-ng/mate.tf.

Edit on GitHub

Linked from 10
  • kthxThe lab's hosting product on the offsite cluster, which the owner and agents use to serve a directory at <name>.kthx.dev or to build a repository and deploy it to a cluster or a cloud.
  • Quick sitesA kthx quick site is a directory served at https://<name>.kthx.dev from a bucket, with its own database, websocket, visitor ID, file store, model endpoint and MCP server.
  • RowbuttA chat bot, code name mate, that answers the owner in Discord and Slack threads with a coding agent whose tools run in a sandbox on the offsite cluster.
  • KubernetesThe folly and offsite Kubernetes clusters, how Flux applies clusters/ from git, and the sandbox runtimes and storage the clusters offer.
  • WorkloadsWhat runs on the folly and offsite Kubernetes clusters, the files that list it, and the workloads that have their own page.
  • RunbooksStep-by-step procedures for the hosts, clusters, network, secrets and apps, grouped by area.
  • Install kthxInstall the kthx engine on the offsite cluster, from the OpenTofu roots to the first passkey.
  • Recover a re-created databaseBring a Postgres database back from its barman backups when its CloudNativePG Cluster is created again under the same name, such as after a Kubernetes cluster rebuild.
  • Restore a databaseRecover a Postgres database from its barman backups in Garage into a scratch CloudNativePG cluster, copy the data back, and run the restore drill.
  • Sign Rowbutt in to ChatGPTSign mate in to the owner's ChatGPT subscription from chat, check the sign-in, and sign it out.

↑↓ move ↵ open esc close