# Postgres Store Bindplane stores organizations, accounts, collector metadata, configurations and more in Postgres when configured to use Postgres as the primary datastore. Using Postgres is a prerequisite for operating Bindplane in [High Availability](/production-checklist/bindplane/high-availability.md). This guide will cover the deployment of Bindplane and Postgres 16 on Linux (Debian 12) and Kubernetes. ### Prerequisites You must have a Bindplane license key before following this guide. If you do not have a license, you can request one on the [Download](https://bindplane.com/download) page. If deploying Bindplane to Kubernetes, you must have [Helm](https://helm.sh/) installed. ### Linux #### 1. Architecture This guide will reference two virtual machines, one for the Bindplane control-plane (`bindplane`) and one for the Postgres installation (`bindplane-postgres`). It is best practice to deploy Postgres to a dedicated machine, allowing multiple Bindplane instances to make use of it if you decide to use High Availability. The network in this example contains the required DNS entries to support reaching the machines by their short hostname`bindplane` and `bindplane-postgres`. If you do not have DNS in your environment, use IP addresses instead of hostnames when configuring Bindplane to connect to Postgres. #### 2. Postgres Installation and Configuration Start by installing Postgres. This guide is using Debian 12, but you can use your preferred distribution,\ just know that the commands to install and manage Postgres may differ. Configure the Postgres apt repository. ```bash sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - ``` Install Postgres 16 from the Postgres repository. ```bash sudo apt-get update sudo apt-get install postgresql-16 ``` Enable and start the Postgres service. ```bash sudo systemctl start postgresql.service sudo systemctl enable --now postgresql.service ``` Configure Postgres to listen on all interfaces. Edit the Postgres configuration file and find `listen_addresses`. ```bash sudo vim /etc/postgresql/16/main/postgresql.conf ``` Uncomment `listen_addresses` and set the value to `0.0.0.0`. It should look like this: ```txt listen_addresses = '0.0.0.0' ``` {% hint style="info" %} **NOTE** If your system has `iptables`or `firewalld`enabled, make sure to allow port `5432/tcp`. {% endhint %} Next we need to update the Authentication configuration. Configure Postgres to allow remote connections. ```bash sudo vim /etc/postgresql/16/main/pg_hba.conf ``` Find the lines that looks like this: ```txt # IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 # IPv6 local connections: host all all ::1/128 scram-sha-256 ``` Update the configuration by replacing `127.0.0.1/32` and `::1/128`. It should look like this: ```txt # IPv4 all connections: host all all 0.0.0.0/0 scram-sha-256 # IPv6 all connections: host all all ::/0 scram-sha-256 ``` User setup and Database creation Connect to the Postgres installation by switching to the `postgres` user and running the `psql`client command. ```bash sudo su - postgres psql ``` Execute the setup queries found in the [Postgres Configuration](/deployment/virtual-machine/bindplane/postgresql/postgres-configuration.md) docs. Restart the service. ```bash sudo systemctl restart postgresql.service ``` With Postgres installed and configured, you can move onto installing and configuring Bindplane. #### 3. Bindplane Installation and Configuration Install Bindplane by following the instructions on the [Download](https://bindplane.com/download) page. ```bash curl \ -fsSlL https://storage.googleapis.com/bindplane-op-releases/bindplane/latest/install-linux.sh \ -o install-linux.sh bash install-linux.sh \ --version 1.72.1 \ --init && rm install-linux.sh ``` Once the package is installed, select `y` to initialize the configuration. 1. Input your license key 2. Server Host: `0.0.0.0` 3. Server Port: `3001` 4. Remote URL: `http://bindplane:3001`, the remote URL should match your hostname or IP address. 5. Authentication Method: `Single User` 6. Username: `admin` 7. Password: Your secure password 8. Storage Type: `postgres` 9. Postgres Host: `bindplane-postgres`, this value should match your Postgres server's hostname or IP address. 10. Postgres Port: `5432` 11. Postgres Database Name: `bindplane` 12. Postgres SSL Mode: `disable`, see [Postgres TLS](/how-to-guides/infrastructure-and-operations/postgres/postgres-tls.md) for TLS configuration, as a follow up to this guide. 13. Maximum Number of Database Connections: `100` 14. Postgres Username: `bindplane` 15. Postgres Password: Your password 16. Event Bus Type: Local 17. Automatically restart: `y` **Securing Credentials** The initialization wizard writes the Bindplane login password and Postgres password to the configuration file in plain text. To avoid storing sensitive values on disk, remove those passwords from the configuration file and supply them with environment variables instead. Remove the passwords from `/etc/bindplane/config.yaml`. ```yaml auth: type: system username: admin # password removed store: type: postgres postgres: host: bindplane-postgres port: 5432 database: bindplane username: bindplane # password removed ``` Set the passwords with a systemd override. ```bash sudo systemctl edit bindplane ``` ```ini [Service] Environment="BINDPLANE_PASSWORD=your-secure-bindplane-password" Environment="BINDPLANE_POSTGRES_PASSWORD=your-secure-password" ``` Restart Bindplane to apply the change. ```bash sudo systemctl restart bindplane ``` {% hint style="info" %} **TIP** Any documented Postgres field can be set with an environment variable. The Bindplane login password can also be set with `BINDPLANE_PASSWORD`. See [Using Environment Variables](#using-environment-variables) for the reference list. {% endhint %} Watch the Bindplane log file for any issues: ```bash sudo tail -F /var/log/bindplane/bindplane.log ``` Bindplane will log the following lines which indicate Postgres is configured and working. ```json {"level":"info","timestamp":"2024-09-18T00:34:22.670Z","message":"Using postgres store"} {"level":"info","timestamp":"2024-09-18T00:34:23.091Z","message":"Starting rollout updater"} {"level":"info","timestamp":"2024-09-18T00:34:23.093Z","message":"Metrics provider is NOP"} ``` If the `Using postgres store` log is not immediately followed by an error log, Postgres is configured correctly. #### 4. Verification Log into the Bindplane web interface at `http://bindplane:3001`. Replace `bindplane` with your hostname or IP address. If you can create a configuration successfully, Postgres is working as intended. ### Kubernetes #### 1. Architecture This guide will use `minikube` to deploy Postgres and Bindplane using high availability. In production, it is recommended to deploy Postgres to a virtual machine, a SaaS provider (CloudSQL, RDS, etc) or to use a Postgres operator such as [zalando/postgres-operator](https://github.com/zalando/postgres-operator). Start by configuring `minikube` or your Kubernetes provider of choice. ```bash minikube start \ --nodes 1 \ --cpus 4 \ --memory 12g ``` #### 2. Postgres Installation and Configuration {% hint style="danger" %} **WARNING** The Postgres YAML manifest provided in this guide is not production ready. It does not use secure authentication. It does not provide volume persistence, meaning data will be lost when the Postgres pod is updated or replaced. {% endhint %} Begin by deploying the Postgres deployment to Kubernetes. You can inspect the YAML manifest [here](https://github.com/observIQ/bindplane-op-helm/tree/main/test/helper/postgres). ```bash kubectl apply -f \ https://raw.githubusercontent.com/observIQ/bindplane-op-helm/main/test/helper/postgres/postgres.yaml ``` If not using the provided Postgres example deployment, make sure to follow the [Postgres Configuration](/deployment/virtual-machine/bindplane/postgresql/postgres-configuration.md) docs when provisioning your database host. Once the pod is deployed, the `postgres` namespace will look like this: ```bash $ kubectl -n postgres get all NAME READY STATUS RESTARTS AGE pod/postgres-0 1/1 Running 0 23s NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE service/postgres ClusterIP 10.100.109.54 5432/TCP 23s NAME READY AGE statefulset.apps/postgres 1/1 23s ``` The service `postgres` will route traffic to the pod `postgres-0`. Postgres is accessible using the\ username `postgres` and password `password`. #### 3. Bindplane Installation and Configuration Setup your Helm client to support deploying the [Bindplane Helm Chart](https://github.com/observIQ/bindplane-op-helm) ```bash helm repo add bindplane \ https://observiq.github.io/bindplane-op-helm helm repo update helm search repo ``` Create the Bindplane license secret, where `$BINDPLANE_LICENSE` is your Bindplane license key. ```bash kubectl create secret generic bindplane \ --from-literal=license=$BINDPLANE_LICENSE ``` Create a Helm `values.yaml` file. ```yaml config: username: admin sessions_secret: 4484766F-5016-4077-B8E0-0DE1D637854B server_url: http://bindplane.local:80 licenseUseSecret: true backend: type: postgres postgres: host: postgres.postgres.svc.cluster.local database: bindplane credentialSecret: name: bindplane-postgres usernameKey: username passwordKey: password maxConnections: 20 eventbus: type: nats replicas: 2 resources: requests: memory: 100Mi cpu: 100m limits: memory: 100Mi nats: resources: requests: memory: 100Mi cpu: 100m limits: memory: 100Mi ``` **Securing Credentials** Avoid storing the Bindplane login password and Postgres credentials in `values.yaml`. Use a Kubernetes secret with `credentialSecret` for Postgres credentials, and use `extraEnv` for the Bindplane login password. 1. Create the Postgres credential secret. ```bash kubectl create secret generic bindplane-postgres \ --namespace=default \ --from-literal=username=postgres \ --from-literal=password=your-secure-password ``` 2. Create the Bindplane auth secret. ```bash kubectl create secret generic bindplane-auth \ --namespace=default \ --from-literal=password=your-secure-bindplane-password ``` Reference the secrets in `values.yaml`. ```yaml backend: type: postgres postgres: host: postgres.postgres.svc.cluster.local database: bindplane credentialSecret: name: bindplane-postgres usernameKey: username passwordKey: password maxConnections: 20 extraEnv: - name: BINDPLANE_PASSWORD valueFrom: secretKeyRef: name: bindplane-auth key: password ``` {% hint style="info" %} **TIP** For Helm deployments, prefer `credentialSecret` for Postgres credentials. Use `extraEnv` for the Bindplane login password and for other Postgres fields you want to supply with environment variables. See [Using Environment Variables](#using-environment-variables) for the reference list. {% endhint %} This configuration will deploy Bindplane with two replicas, configured to connect to Postgres using the clusterIP service at `postgres.postgres.svc.cluster.local`. In this configuration, Bindplane is not exposed by ingress, but can be reached using port forwarding. Deploy Bindplane High Availability. ```bash helm upgrade \ --install bindplane-ha \ bindplane/bindplane \ --values values.yaml ``` Once the chart is deployed, the following pods will be present: * bindplane-ha * Web interface * API * Agent connections * bindplane-ha-jobs * Manages the database initialization and migrations * Periodic jobs, such as cleaning up disconnected Kubernetes agents. * bindplane-ha-nats * For supporting Bindplane [High Availability Event Bus](/production-checklist/bindplane/high-availability/event-bus.md). * bindplane-ha-prometheus * Acts as the storage for collector throughput measurement data * Contains the required configuration or supporting Bindplane * bindplane-ha-transform-agent * For [Live Preview](/feature-guides/data-management/live-preview.md) processing ```bash $ kubectl -n default get all NAME READY STATUS RESTARTS AGE pod/bindplane-ha-54cb7b5d97-q4x48 1/1 Running 0 5m pod/bindplane-ha-54cb7b5d97-wwt6j 1/1 Running 0 5m pod/bindplane-ha-jobs-55576897c-glncr 1/1 Running 0 5m pod/bindplane-ha-nats-0 1/1 Running 0 5m pod/bindplane-ha-nats-1 1/1 Running 0 5m pod/bindplane-ha-nats-2 1/1 Running 0 5m pod/bindplane-ha-prometheus-0 1/1 Running 0 5m pod/bindplane-ha-transform-agent-9fbf44f95-lwmsw 1/1 Running 0 5m NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE service/bindplane-ha ClusterIP 10.97.69.212 3001/TCP 5m service/bindplane-ha-nats-cluster-headless ClusterIP None 6222/TCP 5m service/bindplane-ha-nats-headless ClusterIP None 4222/TCP 5m service/bindplane-ha-prometheus ClusterIP 10.106.131.157 9090/TCP 5m service/bindplane-ha-transform-agent ClusterIP 10.100.49.83 4568/TCP 5m service/kubernetes ClusterIP 10.96.0.1 443/TCP 25h NAME READY UP-TO-DATE AVAILABLE AGE deployment.apps/bindplane-ha 2/2 2 2 5m deployment.apps/bindplane-ha-jobs 1/1 1 1 5m deployment.apps/bindplane-ha-transform-agent 1/1 1 1 5m NAME DESIRED CURRENT READY AGE replicaset.apps/bindplane-ha-54cb7b5d97 2 2 2 5m replicaset.apps/bindplane-ha-jobs-55576897c 1 1 1 5m replicaset.apps/bindplane-ha-transform-agent-9fbf44f95 1 1 1 5m NAME READY AGE statefulset.apps/bindplane-ha-nats 3/3 5m statefulset.apps/bindplane-ha-prometheus 1/1 5m ``` #### 4. Verification Access Bindplane over port forwarding. ```bash kubectl -n default port-forward service/bindplane-ha 3001:3001 ``` Once the tunnel is running, you can reach Bindplane at `http://localhost:3001`. If you can successfully create a configuration, Postgres is configured and working correctly. ### Using Environment Variables Bindplane can read Postgres settings and selected Bindplane settings from environment variables. This lets you keep secrets out of configuration files and Helm values. #### Bindplane credentials | Config field | Environment variable | | ------------ | -------------------- | | `password` | `BINDPLANE_PASSWORD` | #### Postgres settings | Config field | Environment variable | | -------------------- | ----------------------------------------- | | `host` | `BINDPLANE_POSTGRES_HOST` | | `port` | `BINDPLANE_POSTGRES_PORT` | | `connectTimeout` | `BINDPLANE_POSTGRES_CONNECT_TIMEOUT` | | `database` | `BINDPLANE_POSTGRES_DATABASE` | | `username` | `BINDPLANE_POSTGRES_USERNAME` | | `password` | `BINDPLANE_POSTGRES_PASSWORD` | | `maxConnections` | `BINDPLANE_POSTGRES_MAX_CONNECTIONS` | | `maxIdleConnections` | `BINDPLANE_POSTGRES_MAX_IDLE_CONNECTIONS` | | `maxIdleTime` | `BINDPLANE_POSTGRES_MAX_IDLE_TIME` | | `maxLifetime` | `BINDPLANE_POSTGRES_MAX_LIFETIME` | | `statementTimeout` | `BINDPLANE_POSTGRES_STATEMENT_TIMEOUT` | | `schema` | `BINDPLANE_POSTGRES_SCHEMA` | | `sslMode` | `BINDPLANE_POSTGRES_SSL_MODE` | | `sslRootCert` | `BINDPLANE_POSTGRES_SSL_ROOT_CERT` | | `sslCert` | `BINDPLANE_POSTGRES_SSL_CERT` | | `sslKey` | `BINDPLANE_POSTGRES_SSL_KEY` | On Linux, set these in a systemd override for the `bindplane` service. On Kubernetes, set them with `extraEnv` and a Secret reference. ### Commonly Asked Questions #### Migration from legacy Bbolt Store If you are using bolt store and would like to switch to Postgres, reference the following documentation: * [Linux](/configuration/bindplane/migration/multi-project-migration.md) * [Kubernetes](/how-to-guides/cloud-and-platform-integrations/kubernetes/kubernetes-postgres-migration.md) #### Does Bindplane work with SaaS hosted Postgres? Yes, Bindplane supports the popular cloud providers such as Google Cloud CloudSQL, AWS RDS,\ and Azure Database. As long as the cloud provider is exposing a Postgres server, Bindplane can use it. Bindplane does not officially support Postgres like systems, such as [AlloyDB](https://cloud.google.com/alloydb?hl=en) or CockroachDB. #### Does Bindplane support Transport Layer Security (TLS)? Yes, Bindplane supports TLS and mutual TLS when connecting to Postgres. After following this guide,\ reference the [Postgres TLS](/how-to-guides/infrastructure-and-operations/postgres/postgres-tls.md) guide. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.bindplane.com/how-to-guides/infrastructure-and-operations/postgres/postgres-store.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.