> For the complete documentation index, see [llms.txt](https://docs.cybus.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.cybus.io/2-7-0/cybus-helm-charts/connectware-helm-chart/external-postgresql.md).

# Using an External PostgreSQL Database

Run Connectware on Kubernetes against a PostgreSQL server that you operate, such as Amazon RDS or Azure Database for PostgreSQL, instead of the bundled database.

By default, the `connectware` Helm chart deploys its own PostgreSQL instance in your cluster. With an external database, Connectware keeps its users, roles, and installed services on a PostgreSQL server that you operate, where your own backup and high-availability setup applies.

If you already run Connectware with the bundled database, see [Move an Existing Installation to an External Database](#move-an-existing-installation-to-an-external-database) to keep your data.

## Prerequisites

To use an external PostgreSQL database, you need the following:

* Connectware 2.7.0 or later.
* Everything listed in the [prerequisites for installing Connectware on Kubernetes](https://docs.cybus.io/2-7-0/deployment/installing-connectware/on-kubernetes.md#prerequisites).
* A PostgreSQL server that meets the [database requirements](#database-requirements).
* An administrator account on that server and a SQL client such as `psql`, to configure the server and to create the database and the database user for Connectware.
* Network access to the PostgreSQL server from the auth-server, service-manager, and topic-explorer pods and from the computer that runs your SQL client, for example through a firewall rule or a private endpoint.

## Database Requirements

| Requirement    | Details                                                                                                                                           |
| -------------- | ------------------------------------------------------------------------------------------------------------------------------------------------- |
| Version        | PostgreSQL 17 or 18.                                                                                                                              |
| Extensions     | `uuid-ossp` and `pg_trgm` must be available on the server. On Azure Database for PostgreSQL, add them to the `azure.extensions` server parameter. |
| ICU collations | The server must support ICU collations. Amazon RDS and Azure Database for PostgreSQL support them.                                                |
| Endpoint       | One host name for the primary server, such as the writer endpoint of a cluster. Connectware sends all reads and writes to this host.              |
| Location       | Place the database close to your cluster, for example in the same cloud region.                                                                   |
| Authentication | User name and password.                                                                                                                           |
| Connections    | `max_connections` of at least 130. See [Server Connection Limit](#server-connection-limit).                                                       |

### Server Connection Limit

With the default Helm values, Connectware needs a `max_connections` value of at least 130 on your server, plus the connections of any other application that uses the same server. This value includes the extra pods that Kubernetes starts while it replaces pods, for example when you apply changed Helm values.

Each Connectware pod opens up to the following number of connections:

| Component       | Connections per pod | Default replicas |
| --------------- | ------------------- | ---------------- |
| auth-server     | 21                  | 2                |
| service-manager | 20                  | 1                |
| topic-explorer  | 10                  | 2                |

If you [scale auth-server or topic-explorer horizontally](https://docs.cybus.io/2-7-0/cybus-helm-charts/connectware-helm-chart/scaling-connectware-components-horizontally.md), add its connections per pod to the 130 for each additional replica.

You check and change the value in [Configure the PostgreSQL Server](#configure-the-postgresql-server).

## Securing the Database Connection

By default, Connectware encrypts its connection to the PostgreSQL server with TLS and checks the certificate of the server. The certificate must be issued by a certificate authority (CA) in a CA chain that you provide, and it must contain the host name that Connectware connects to. This check makes sure that Connectware connects to your server and not to another server that intercepts the connection.

The TLS mode in the Helm value `postgresql.external.tls.sslMode` sets which of these protections Connectware uses. The following modes are available:

| Mode                    | Encrypts the connection | Checks the server certificate |
| ----------------------- | ----------------------- | ----------------------------- |
| `verify-full` (default) | Yes                     | Yes                           |
| `require`               | Yes                     | No                            |
| `disable`               | No                      | No                            |

With `verify-full`, keep the following in mind:

* Set the host to one of the DNS names in the server certificate. Connectware checks the name that you set, not the name that an alias such as a CNAME or a Kubernetes ExternalName Service points to. An IP address works only if the certificate lists it.
* Provide the complete CA chain, including the root CA certificate. Connectware trusts only the certificates in this file, not the CA certificates of the operating system.

Use `require` only if you cannot provide the CA chain or if the host name does not match the server certificate.

Amazon RDS and Azure Database for PostgreSQL accept only encrypted connections by default, so use `verify-full` or `require` with them.

## Install Connectware with an External Database

{% stepper %}
{% step %}

### Configure the PostgreSQL Server

Some requirements are settings of the PostgreSQL server itself. You check them with SQL, but you change them in the console of your cloud provider or in the server configuration. A change to `max_connections` takes effect only after the server restarts, so make it now, before Connectware uses the server.

1. Connect to the `postgres` database of your server with your administrator account:

{% code lineNumbers="true" %}

```bash
psql "host=${DB_HOST} port=5432 dbname=postgres user=${DB_ADMIN_USER}"
```

{% endcode %}

Replace `${DB_HOST}` with the host name of your PostgreSQL server and `${DB_ADMIN_USER}` with the name of your administrator account.

2. Check the connection limit of the server:

{% code lineNumbers="true" %}

```sql
SHOW max_connections;
```

{% endcode %}

3. If the result is lower than the [connection limit](#server-connection-limit) that Connectware needs, raise `max_connections` in the settings of your server and restart the server.
   {% endstep %}

{% step %}

### Create the Database and the Database User

Create a database user for Connectware and a database that this user owns. The user does not need superuser rights. This page uses the user name `connectware` and the database name `cybus_connectware`. If you choose other names, use them in the statements and in your Helm values.

Do not create any tables. Connectware creates its tables when it starts for the first time.

{% tabs %}
{% tab title="Amazon RDS, Aurora, and Self-Managed" %}

1. Connect to the `postgres` database with your administrator account, as in [Configure the PostgreSQL Server](#configure-the-postgresql-server), and run the following statements:

{% code lineNumbers="true" %}

```sql
CREATE ROLE connectware LOGIN PASSWORD '${DB_PASSWORD}';
GRANT connectware TO CURRENT_USER;
CREATE DATABASE cybus_connectware OWNER connectware;
```

{% endcode %}

* Replace `${DB_PASSWORD}` with a password for the Connectware database user.
* The `GRANT` statement lets your administrator account make the new user the owner of the database. PostgreSQL requires it when the administrator account is not a superuser, as on Amazon RDS and Aurora.

2. Optional: As the owner of the database, the Connectware user creates the extensions that Connectware needs on its first start. If your security policy does not allow that, connect to the `cybus_connectware` database with your administrator account, for example with `\c cybus_connectware` in `psql`, and create them yourself:

{% code lineNumbers="true" %}

```sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_trgm;
```

{% endcode %}
{% endtab %}

{% tab title="Azure Database for PostgreSQL" %}

1. Connect to the `postgres` database with the server administrator account, as in [Configure the PostgreSQL Server](#configure-the-postgresql-server), and run the following statements:

{% code lineNumbers="true" %}

```sql
CREATE ROLE connectware LOGIN PASSWORD '${DB_PASSWORD}';
GRANT connectware TO CURRENT_USER;
CREATE DATABASE cybus_connectware OWNER connectware;
```

{% endcode %}

* Replace `${DB_PASSWORD}` with a password for the Connectware database user.
* The `GRANT` statement lets the server administrator make the new user the owner of the database.

2. Connect to the `cybus_connectware` database with the server administrator account, for example with `\c cybus_connectware` in `psql`, and allow the Connectware user to create tables in the `public` schema. On Azure, the owner of a database does not get this right automatically.

{% code lineNumbers="true" %}

```sql
GRANT CREATE ON SCHEMA public TO connectware;
```

{% endcode %}

3. Optional: The Connectware user creates the extensions on its first start. If your security policy does not allow that, create them yourself with the server administrator account in the `cybus_connectware` database:

{% code lineNumbers="true" %}

```sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_trgm;
```

{% endcode %}
{% endtab %}
{% endtabs %}
{% endstep %}

{% step %}

### Get the CA Certificate Chain

For the default TLS mode `verify-full`, you need the CA chain that issued the certificate of your PostgreSQL server, as one PEM file. If you use the TLS mode `require`, skip this step.

Where you get the CA chain depends on your PostgreSQL server:

* **Amazon RDS and Aurora**: Download the certificate bundle for your AWS Region or the global bundle. See [Using SSL with a PostgreSQL DB instance](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Concepts.General.SSL.html) (AWS documentation).
* **Azure Database for PostgreSQL**: Combine the root CA certificates DigiCert Global Root G2 and Microsoft RSA Root CA 2017 into one PEM file. Convert any certificate that you download in DER format to PEM first. See [Secure connectivity with TLS](https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-connect-tls-ssl) (Microsoft documentation).
* **Self-managed PostgreSQL**: Use the CA that issued your server certificate, including all intermediate certificates up to the root.
  {% endstep %}

{% step %}

### Add the Database Settings to Your Helm Values

Add a `postgresql` section to the `values.yaml` file of your installation:

{% code title="values.yaml" lineNumbers="true" %}

```yaml
postgresql:
  type: external
  external:
    host: postgresql.example.com
    port: 5432
    database: cybus_connectware
    user: connectware
    password: '${DB_PASSWORD}'
    tls:
      sslMode: verify-full
      caChain:
        cert: |
          -----BEGIN CERTIFICATE-----
          [skipped for brevity - include the whole chain]
          -----END CERTIFICATE-----
```

{% endcode %}

* Replace `postgresql.example.com` with the host name of your PostgreSQL server and `${DB_PASSWORD}` with the password of the Connectware database user.
* Set `user` and `database` to the names you created in [Create the Database and the Database User](#create-the-database-and-the-database-user).
* Paste the content of your CA chain PEM file under `cert`, indented as in the example. If you use the TLS mode `require`, set `sslMode: require` and leave out `caChain`.

The following Helm values are available:

| Helm value                                          | Default             | Description                                                                                                                  |
| --------------------------------------------------- | ------------------- | ---------------------------------------------------------------------------------------------------------------------------- |
| `postgresql.type`                                   | `internal`          | `internal` deploys the bundled database. `external` connects Connectware to your server.                                     |
| `postgresql.external.host`                          | none                | Host name of your PostgreSQL server. Required.                                                                               |
| `postgresql.external.port`                          | `5432`              | Port of your PostgreSQL server.                                                                                              |
| `postgresql.external.database`                      | `cybus_connectware` | Name of the database.                                                                                                        |
| `postgresql.external.user`                          | `cybus-admin`       | Name of the database user.                                                                                                   |
| `postgresql.external.password`                      | none                | Password of the database user.                                                                                               |
| `postgresql.external.existingSecret`                | none                | Name of an existing Secret that holds the password under the key `password`. Takes priority over `password`.                 |
| `postgresql.external.tls.sslMode`                   | `verify-full`       | TLS mode: `verify-full`, `require`, or `disable`. See [Securing the Database Connection](#securing-the-database-connection). |
| `postgresql.external.tls.caChain.cert`              | none                | CA chain in PEM format.                                                                                                      |
| `postgresql.external.tls.caChain.existingConfigMap` | none                | Name of an existing ConfigMap that holds the CA chain in a file called `ca.crt`. Takes priority over `cert`.                 |

Helm stops the installation with an error if `host` is empty, or if neither `password` nor `existingSecret` is set.

#### Using an Existing Secret and ConfigMap

To keep the password and the CA chain out of your `values.yaml` file, create a Secret and a ConfigMap in the Connectware namespace before you apply your Helm values. If the namespace does not exist yet, create it first with `kubectl create namespace ${NAMESPACE}`.

{% code lineNumbers="true" %}

```bash
kubectl create secret generic ${DB_SECRET_NAME} \
  --from-literal=password=${DB_PASSWORD} \
  -n ${NAMESPACE}
kubectl create configmap ${DB_CA_CONFIGMAP_NAME} \
  --from-file=ca.crt=${CA_CHAIN_FILE} \
  -n ${NAMESPACE}
```

{% endcode %}

* Replace `${DB_SECRET_NAME}` and `${DB_CA_CONFIGMAP_NAME}` with names of your choice, `${CA_CHAIN_FILE}` with the path to your CA chain PEM file, and `${NAMESPACE}` with your Connectware namespace.

If you use the TLS mode `require`, create only the Secret, and leave out `caChain` in the following example.

Then reference them in the `values.yaml` file instead of `password` and `cert`:

{% code title="values.yaml" lineNumbers="true" %}

```yaml
postgresql:
  type: external
  external:
    host: postgresql.example.com
    database: cybus_connectware
    user: connectware
    existingSecret: ${DB_SECRET_NAME}
    tls:
      sslMode: verify-full
      caChain:
        existingConfigMap: ${DB_CA_CONFIGMAP_NAME}
```

{% endcode %}
{% endstep %}

{% step %}

### Install Connectware

Install Connectware with your `values.yaml` file as described in [Installing Connectware (Kubernetes)](https://docs.cybus.io/2-7-0/deployment/installing-connectware/on-kubernetes.md#installing-connectware).

When it starts, Connectware checks the database server against the [database requirements](#database-requirements). On its first start, it then creates its tables.
{% endstep %}
{% endstepper %}

## Verification

1. List the pods in your Connectware namespace:

{% code lineNumbers="true" %}

```bash
kubectl get pods -n ${NAMESPACE}
```

{% endcode %}

All pods reach the `Running` status with all containers ready. The list contains no `postgresql-0` pod, because the Helm chart does not deploy the bundled database.

2. Log in to the [Admin UI](https://docs.cybus.io/2-7-0/access/admin-ui.md).

**Result:** Connectware runs with your external PostgreSQL database.

## Move an Existing Installation to an External Database

The Helm chart does not copy data from the bundled database. To keep your users, roles, and installed services, copy the bundled database into the external database before you switch Connectware over. Connectware is not available during the move, so plan a maintenance window.

In addition to the [prerequisites](#prerequisites), you need the following:

* Connectware 2.7.0 or later, running with the bundled database. If you run an older version, upgrade Connectware first.
* A computer with `kubectl` access to your cluster and network access to the PostgreSQL server. You run all commands of this procedure on this computer.
* A `psql` client that supports the `\restrict` command, such as the one of PostgreSQL 17.6 or later. Older clients stop with `invalid command \restrict`.

{% stepper %}
{% step %}

### Prepare the External Database

Configure the server, create the database and the database user, and get the CA chain, as described in [Configure the PostgreSQL Server](#configure-the-postgresql-server), [Create the Database and the Database User](#create-the-database-and-the-database-user), and [Get the CA Certificate Chain](#get-the-ca-certificate-chain). The restore in a later step creates the tables and the extensions.
{% endstep %}

{% step %}

### Stop the Components That Use the Database

Replace `${NAMESPACE}` with your Connectware namespace and scale auth-server, service-manager, and topic-explorer down, so that no changes reach the bundled database after you create the dump:

{% code lineNumbers="true" %}

```bash
kubectl scale -n ${NAMESPACE} deployment/auth-server deployment/service-manager deployment/topic-explorer --replicas=0
```

{% endcode %}

When you switch Connectware to the external database, Helm starts these components again. If you stop the move before that step, apply your unchanged `values.yaml` file as described in [Applying Helm Configuration Changes](https://docs.cybus.io/2-7-0/cybus-helm-charts/working-with-cybus-helm-charts.md#applying-helm-configuration-changes) to start them.
{% endstep %}

{% step %}

### Dump the Bundled Database

Run the following command:

{% code lineNumbers="true" %}

```bash
kubectl exec -n ${NAMESPACE} postgresql-0 -- \
    bash -c "pg_dump -U cybus-admin --no-owner --no-privileges --no-comments cybus_connectware" \
    > connectware_database.sql
```

{% endcode %}

The options leave out the owners, privileges, and comments of the bundled database, so that the Connectware database user owns everything that you restore.

The `connectware_database.sql` file contains credentials, such as the password hashes, multi-factor authentication secrets, and access tokens of your users. Store it securely and delete it once the move is complete.
{% endstep %}

{% step %}

### Restore the Dump into the External Database

Run the following command:

{% code lineNumbers="true" %}

```bash
psql "host=${DB_HOST} port=5432 dbname=cybus_connectware user=connectware sslmode=verify-full sslrootcert=${CA_CHAIN_FILE}" \
    --single-transaction -v ON_ERROR_STOP=1 -f connectware_database.sql
```

{% endcode %}

* Replace `${DB_HOST}` with the host name of your PostgreSQL server and `${CA_CHAIN_FILE}` with the path to your CA chain PEM file.
* If you chose other values in [Create the Database and the Database User](#create-the-database-and-the-database-user), adjust `port`, `dbname`, and `user`.
* If you use the TLS mode `require`, set `sslmode=require` and leave out `sslrootcert`.

`psql` asks for the password of the Connectware database user. If a statement fails, `psql` stops and rolls back the whole restore. The database then stays empty, and you can run the command again after you fix the cause.
{% endstep %}

{% step %}

### Switch Connectware to the External Database

Add the database settings to the `postgresql` section of your `values.yaml` file as described in [Add the Database Settings to Your Helm Values](#add-the-database-settings-to-your-helm-values). Then apply the file as described in [Applying Helm Configuration Changes](https://docs.cybus.io/2-7-0/cybus-helm-charts/working-with-cybus-helm-charts.md#applying-helm-configuration-changes).

Helm removes the bundled database and restarts the Connectware pods with the external database.
{% endstep %}

{% step %}

### Check the Moved Data

1. Check that all pods are running, as described in [Verification](#verification).
2. Log in to the Admin UI with your existing credentials and check that your users, roles, and services are present.
   {% endstep %}

{% step %}

### Delete the Bundled Database Volume (Optional)

The PersistentVolumeClaim of the bundled database stays in the cluster after the switch and keeps the data of the bundled database. Deleting it removes that data permanently. Once you no longer need it, delete it:

{% code lineNumbers="true" %}

```bash
kubectl -n ${NAMESPACE} delete pvc postgresql-postgresql-0
```

{% endcode %}
{% endstep %}
{% endstepper %}

## Troubleshooting

If Connectware cannot use the database, auth-server stops at startup and Kubernetes restarts the pod. Service-manager and topic-explorer can also restart, because they use the same database. Auth-server checks the database server at every start and names the problem it finds, so read its log first. If the pod is restarting, add `--previous` to read the log of the last attempt.

{% code lineNumbers="true" %}

```bash
kubectl logs -n ${NAMESPACE} deployment/auth-server
```

{% endcode %}

The reason follows `PostgreSQL preflight failed, auth-server will exit:`. With `sslMode` set to `verify-full` or `require`, the message names a failed TLS handshake whenever the server rejects the connection, even if the cause is not TLS, such as a wrong password. Look for the cause after the TLS mode in the message:

| The message contains                                                                       | Solution                                                                                                                                |
| ------------------------------------------------------------------------------------------ | --------------------------------------------------------------------------------------------------------------------------------------- |
| `Unsupported PostgreSQL server version`                                                    | Use PostgreSQL 17 or 18.                                                                                                                |
| `is not reachable`                                                                         | Check the host, the port, and the network path from the cluster to the server, including firewall rules.                                |
| `password authentication failed`                                                           | Check that `user` and the password in your Helm values or your Secret match the database user.                                          |
| `no encryption`                                                                            | The server accepts only encrypted connections. Set `sslMode` to `verify-full` or `require`.                                             |
| `does not match certificate's altnames`                                                    | Set `host` to one of the DNS names that the message lists.                                                                              |
| `self-signed certificate in certificate chain` or `unable to verify the first certificate` | Provide the complete CA chain of the server certificate, as described in [Get the CA Certificate Chain](#get-the-ca-certificate-chain). |
| `is not available on the server`                                                           | Make the extension available on the server. On Azure, add it to the `azure.extensions` server parameter.                                |
| `cannot create it`                                                                         | Run the `CREATE EXTENSION` statement from the message with your administrator account in the Connectware database.                      |
| `cannot create tables in schema`                                                           | Run the `GRANT CREATE ON SCHEMA` statement from the message with your administrator account in the Connectware database.                |
| `has no ICU collation support`                                                             | Use a PostgreSQL server that supports ICU collations.                                                                                   |

Auth-server repeats the check each time Kubernetes restarts the pod, so a fix on the database server takes effect without further action. To change a Helm value, apply your `values.yaml` file as described in [Applying Helm Configuration Changes](https://docs.cybus.io/2-7-0/cybus-helm-charts/working-with-cybus-helm-charts.md#applying-helm-configuration-changes).

## Restarts, Failover, and Backups

When the database restarts or fails over to another server, Connectware reconnects on its own, without a restart. While the database is unreachable, the affected pods report that they are not ready, so Kubernetes routes requests only to ready pods. Once the pods are ready again, the Admin UI and the APIs respond as usual.

The procedure in [Backing Up the Connectware Database](https://docs.cybus.io/2-7-0/deployment/upgrading-connectware/backing-up-the-connectware-database.md) works with the bundled database only. Back up an external database with the tools of your PostgreSQL server or your cloud provider.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.cybus.io/2-7-0/cybus-helm-charts/connectware-helm-chart/external-postgresql.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

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.
