Managed PostgreSQL

Users can create and configure PostgreSQL if this capability has been enabled in the DKP cluster by the administrator.

The user sets the required configuration using a Postgres object that points to a specific service class (PostgresClass), which defines the available parameters and limits. The PostgresClass is created and configured by the cluster administrator.

This guide uses two examples:

• app-postgres: the main example for creating and operating PostgreSQL — resources, Cluster mode, replication, users, databases, PostgreSQL parameters, TLS, and observability;
• snapshot-pg: a separate example for creating and restoring snapshots, since it requires a StorageClass with CSI snapshot support.

Check available resources

Before creating Postgres, check the available resources of the worker nodes. This lets you choose CPU and memory values for the example based on the actual load on the cluster.

First, list the nodes:

d8 k get nodes -o wide

The example uses two available worker nodes.

Example output:

NAME       STATUS   ROLES    AGE   VERSION
worker-1   Ready    worker   25d   v1.34.9
worker-2   Ready    worker   43m   v1.34.9

Check the resources allocated on the first worker node:

d8 k describe node worker-1 | grep -A 5 "Allocated resources"

Example output:

Allocated resources:
  (Total limits may be over 100 percent, i.e., overcommitted.)
  Resource           Requests          Limits
  --------           --------          ------
  cpu                1104m (28%)       500m (12%)
  memory             4096854330 (53%)  390Mi (5%)

Check the second worker node:

d8 k describe node worker-2 | grep -A 5 "Allocated resources"

Example output:

Allocated resources:
  (Total limits may be over 100 percent, i.e., overcommitted.)
  Resource           Requests      Limits
  --------           --------      ------
  cpu                472m (12%)    500m (12%)
  memory             1004Mi (13%)  256Mi (3%)

Check storage

Before creating Postgres, check the available StorageClass options and choose the storage class where the PostgreSQL data will be placed:

d8 k get storageclass

Example output from the test bench:

NAME                   PROVISIONER            RECLAIMPOLICY   VOLUMEBINDINGMODE      ALLOWVOLUMEEXPANSION
local                  csi.dvp.deckhouse.io   Delete          WaitForFirstConsumer   true
replicated (default)   csi.dvp.deckhouse.io   Delete          WaitForFirstConsumer   true

Main example of creating Postgres

Create a namespace:

d8 k create namespace my-postgres

To create a Postgres object, complete the following steps:

• Select PostgresClass;
• Configure resources;
• Select deployment mode;
• Configure topology and replication mode;
• Create logical database and user;
• Configure PostgreSQL parameters;
• Configure TLS;
• Configure observability.

The following example shows an app-postgres Postgres manifest. You can apply it as is and then customize it using the steps below.

apiVersion: managed-services.deckhouse.io/v1alpha1
kind: Postgres
metadata:
  name: app-postgres
  namespace: my-postgres
spec:
  postgresClassName: default

  configuration:
    maxConnections: 120

  instance:
    cpu:
      cores: 1
      coreFraction: 50
    memory:
      size: 1Gi
    persistentVolumeClaim:
      size: 10Gi
      storageClassName: replicated

  type: Cluster
  cluster:
    topology: Ignored
    replication: Consistency

  users:
    - name: app-rw
      role: rw
      storeCredsToSecret: app-postgres-rw

  databases:
    - name: app

  tls:
    mode: K8s

  observability: Enabled

Save the manifest to postgres.yaml and apply it:

d8 k apply -f postgres.yaml

Check the status of the created Postgres:

d8 k get postgres app-postgres -n my-postgres -o wide

Once the rollout is complete, the main conditions (status.conditions) should change to True. For details about each condition, see Check status.

Example output:

NAME           AVAILABLE   CONFIGURATIONVALID   LASTVALIDCONFIGURATIONAPPLIED   SCALEDTOLASTVALIDCONFIGURATION   DATABASESSYNCED   USERSSYNCED
app-postgres   True        True                 True                            True                             True              True

To change a parameter of app-postgres, edit the corresponding fragment of postgres.yaml and reapply the file.

Select PostgresClass

The spec.postgresClassName parameter defines the PostgresClass that sets the available parameters and limits for Postgres. To list the PostgresClass resources available in the cluster, run:

d8 k get postgresclass

Example output:

NAME      AGE
default   13d

This example uses the default PostgresClass with standard limits. If a different PostgresClass is selected, you can view its limits in the configuration:

d8 k get postgresclass <CLASS_NAME> -o yaml

Where <CLASS_NAME> is the name of the selected PostgresClass.

The PostgresClass settings and limits are described in Limit CPU and memory resources, Manage fault tolerance across availability zones, and Automatically validate PostgreSQL settings.

Placement restrictions

A PostgresClass can also define PostgreSQL instance placement rules using nodeSelector, nodeAffinity, and tolerations. These rules apply automatically once the class is selected and aren’t specified in the Postgres object.

Configure resources

The spec.instance parameter is used to configure the resources of each PostgreSQL instance — the number of CPUs, the guaranteed CPU share, and the amount of memory.

In the example, this fragment is responsible for resources and storage:

spec:
  instance:
    cpu:
      cores: 1
      coreFraction: 50
    memory:
      size: 1Gi

In the example, the instance is allocated one CPU core and 1Gi of memory. The coreFraction parameter defines the ratio of the CPU request to the CPU limit. For cores: 1 and coreFraction: 50, the module produced:

Example output:

limits.cpu:   1
requests.cpu: 500m

For more information, see Limit CPU and memory resources.

Change resources of an existing Postgres

You can change Postgres resources by reapplying the manifest, provided the new values are allowed by the selected PostgresClass. First, check the current resource values:

d8 k get pods -n my-postgres \
  -l managed-services.deckhouse.io/managed-service-name=app-postgres \
  -o custom-columns='NAME:.metadata.name,CPU_REQUEST:.spec.containers[0].resources.requests.cpu,MEMORY_REQUEST:.spec.containers[0].resources.requests.memory'

Example output:

NAME                       CPU_REQUEST   MEMORY_REQUEST
d8ms-pg-app-postgres-1     500m          1Gi
d8ms-pg-app-postgres-2     500m          1Gi

You can apply changes to both memory and CPU at once, but for clarity, first increase memory from 1Gi to 2Gi:

spec:
  instance:
    memory:
      size: 2Gi

Apply the updated manifest:

d8 k apply -f postgres.yaml

After the update completes, the CPU request stays at 500m, and the memory request of the instances changes to 2Gi.

Example output:

NAME                       CPU_REQUEST   MEMORY_REQUEST
d8ms-pg-app-postgres-1     500m          2Gi
d8ms-pg-app-postgres-2     500m          2Gi

Next, change coreFraction from 50 to 100, leaving cores: 1:

spec:
  instance:
    cpu:
      cores: 1
      coreFraction: 100

Reapply the manifest:

d8 k apply -f postgres.yaml

After the update completes, the CPU request of the instances changes from 500m to 1, and the memory request stays at 2Gi.

Example output:

NAME                       CPU_REQUEST   MEMORY_REQUEST
d8ms-pg-app-postgres-1     1             2Gi
d8ms-pg-app-postgres-2     1             2Gi

For a running Postgres, you can change memory and coreFraction within the limits allowed by the selected PostgresClass.

Check CPU and memory limits via PostgresClass

CPU and memory values must comply with the limits of the selected PostgresClass. If the specified resources or their combination don’t match the allowed values, the API rejects the configuration.

The default PostgresClass isn’t well suited for a clear demonstration of these limits. So this example uses a separate check PostgresClass, which allows memory from 512Mi to 2Gi in 512Mi steps for 1–2 CPUs.

With cores: 1 and coreFraction: 50, a memory value of 700Mi doesn’t match the configured step, so the manifest is rejected:

spec:
  postgresClassName: check
  instance:
    cpu:
      cores: 1
      coreFraction: 50
    memory:
      size: 700Mi

Apply the manifest:

d8 k apply -f postgres.yaml

The API rejects the request. Example output:

spec.instance.memory.size: Invalid value: 734003200: memory setting does not fit Step 536870912 of the selected PostgresClass

Select deployment mode

The set of PostgreSQL instances depends on the selected deployment mode: Cluster creates a primary instance and replicas, whose composition depends on the selected replication mode; Standalone creates a single PostgreSQL instance without replicas.

Cluster mode

To work with a primary instance and replicas, use Cluster mode, set by the spec.type parameter.

spec:
  type: Cluster
  cluster:
    topology: Ignored
    replication: Consistency

The replication mode and its parameters are configured separately. The available modes and usage examples are described in Configure replication.

Standalone mode

Standalone mode runs PostgreSQL as a single instance without replication. Unlike Cluster mode, it doesn’t use topology or replication parameters.

To use this mode, specify:

spec:
  type: Standalone

After Postgres is created, a single PostgreSQL instance starts. Check the created PostgreSQL instances:

d8 k get pods -n my-postgres \
  -l managed-services.deckhouse.io/managed-service-name=app-postgres \
  -o wide

Example output:

NAME                     STATUS    NODE
d8ms-pg-app-postgres-1   Running   worker-1

Check the Services created for connecting to PostgreSQL:

d8 k get svc -n my-postgres | grep app-postgres

Example output:

d8ms-pg-app-postgres-r    ClusterIP   10.223.234.52    <none>   5432/TCP
d8ms-pg-app-postgres-ro   ClusterIP   10.223.70.248    <none>   5432/TCP
d8ms-pg-app-postgres-rw   ClusterIP   10.223.120.250   <none>   5432/TCP

Check which instances the Services point to, via endpoints:

d8 k get endpoints -n my-postgres | grep app-postgres

Example output:

d8ms-pg-app-postgres-r    10.112.2.31:5432   42h
d8ms-pg-app-postgres-ro   <none>             42h
d8ms-pg-app-postgres-rw   10.112.2.31:5432   42h

The Services with the -r and -rw suffixes route connections to the only instance. The Service with the -ro suffix is also created but has no endpoint, since Standalone mode has no replicas.

Configure topology and replication mode

In Cluster mode, you can manage the fault tolerance of PostgreSQL instances by setting their placement across nodes and availability zones in the spec.cluster.topology parameter.

Configure topology

The following values are supported:

• Ignored: placement follows the standard Kubernetes scheduling rules, spreading instances across different nodes;
• Zonal: instances are placed within one of the allowed zones;
• TransZonal: instances are placed across different availability zones.

The available topology values and zones are determined by the selected PostgresClass. For Zonal and TransZonal, the cluster infrastructure must provide the corresponding availability zones. For more information, see Manage fault tolerance across availability zones.

Placement without zone selection

With topology: Ignored, instance placement is managed by the Kubernetes scheduler. This mode spreads instances across different nodes without any additional configuration from the user. The main example uses this mode:

spec:
  cluster:
    topology: Ignored

Check instance placement:

d8 k get pods -n my-postgres \
  -l managed-services.deckhouse.io/managed-service-name=app-postgres \
  -o wide

The instances should be on different nodes.

Example output:

NAME                       STATUS    NODE
d8ms-pg-app-postgres-1     Running   worker-1
d8ms-pg-app-postgres-2     Running   worker-2

Placement within a single zone

With topology: Zonal, one of the zones allowed by the selected PostgresClass is chosen for placing Postgres. All cluster instances are placed within this zone.

spec:
  cluster:
    topology: Zonal

To use Zonal, nodes must have the topology.kubernetes.io/zone label set to the corresponding zone value. The zone must be allowed by the selected PostgresClass.

For example, if two available nodes belong to the default zone:

Example output:

NAME       ZONE
worker-1   default
worker-2   default

The instances can be placed as follows.

Example output:

NAME                       STATUS    NODE
d8ms-pg-app-postgres-1     Running   worker-1
d8ms-pg-app-postgres-2     Running   worker-2

In this example, both instances are placed in the default zone.

Configure replication

In Cluster mode, replication transfers data from the primary PostgreSQL instance to the replicas. The replication mode is set in spec.cluster.replication.

The following modes are supported:

• Availability: a primary instance and one asynchronous replica;
• Consistency: a primary instance and one synchronous replica;
• ConsistencyAndAvailability: a primary instance, one synchronous replica, and one asynchronous replica.

Check replication mode

Replication status is checked through the pg_stat_replication view on the primary instance. Use this procedure for any Cluster mode, including after changing spec.cluster.replication.

First, identify the current primary instance:

PRIMARY="$(d8 k get clusters.cnpg.internal.managed.deckhouse.io d8ms-pg-app-postgres \
  -n my-postgres \
  -o jsonpath='{.status.targetPrimary}')"

Then run the query:

d8 k exec -n my-postgres "$PRIMARY" -- \
  psql -U postgres -d postgres -c \
  "SELECT application_name, state, sync_state FROM pg_stat_replication;"

The expected sync_state values depend on the mode:

A state: streaming value means the replica is receiving changes from the primary instance. Instance roles can change while the mode is being switched, so always identify the primary instance again via status.targetPrimary rather than by Pod number.

Availability mode

Availability mode creates a primary PostgreSQL instance and one asynchronous replica.

To use this mode, specify:

spec:
  type: Cluster
  cluster:
    topology: Zonal
    replication: Availability

After Postgres is created, two PostgreSQL instances start:

d8 k get pods -n my-postgres \
  -l managed-services.deckhouse.io/managed-service-name=app-postgres \
  -o wide

Example output:

NAME                     READY   STATUS
d8ms-pg-app-postgres-1   1/1     Running
d8ms-pg-app-postgres-2   1/1     Running

Check the replication mode as described in Check replication mode. For Availability, expect one replica with sync_state = async:

     application_name      |   state   | sync_state
---------------------------+-----------+------------
 d8ms-pg-app-postgres-2    | streaming | async
(1 row)

You can check how Service traffic is distributed between the primary instance and the replica through EndpointSlice:

d8 k get endpointslice -n my-postgres | grep app-postgres

Example output:

d8ms-pg-app-postgres-r-v8kcv    IPv4   5432   10.112.2.249,10.112.2.155
d8ms-pg-app-postgres-ro-696bp   IPv4   5432   10.112.2.155
d8ms-pg-app-postgres-rw-8nx8s   IPv4   5432   10.112.2.249

The -rw Service routes connections to the primary instance, -ro to the replica, and -r to both instances.

Consistency mode

Consistency mode, used in the main example, creates a primary PostgreSQL instance and one synchronous replica:

spec:
  type: Cluster
  cluster:
    topology: Ignored
    replication: Consistency

After Postgres is created, two PostgreSQL instances start:

d8 k get pods -n my-postgres \
  -l managed-services.deckhouse.io/managed-service-name=app-postgres \
  -o wide

Example output:

NAME                       STATUS    NODE
d8ms-pg-app-postgres-1     Running   worker-1
d8ms-pg-app-postgres-2     Running   worker-2

Check the replication mode as described in Check replication mode. For Consistency, expect one replica with sync_state = quorum:

      application_name      |   state   | sync_state
----------------------------+-----------+------------
 d8ms-pg-app-postgres-2     | streaming | quorum
(1 row)

You can additionally verify synchronous replication by checking the actual data transfer. Identify the primary instance the same way as in Check replication mode:

PRIMARY="$(d8 k get clusters.cnpg.internal.managed.deckhouse.io d8ms-pg-app-postgres \
  -n my-postgres \
  -o jsonpath='{.status.targetPrimary}')"

Create a check table on the primary instance and insert a row:

d8 k exec -n my-postgres "$PRIMARY" -- \
  psql -U postgres -d postgres -c "
    CREATE TABLE consistency_check (
      id integer PRIMARY KEY,
      value text
    );
    INSERT INTO consistency_check VALUES (1, 'replicated');
  "

Identify the replica:

REPLICA="$(d8 k get pods -n my-postgres \
  -l managed-services.deckhouse.io/managed-service-name=app-postgres \
  -o jsonpath='{range .items[*]}{.metadata.name}{"\n"}{end}' | \
  grep -v "^${PRIMARY}$" | head -n1)"

Check that the row exists directly on the replica:

d8 k exec -n my-postgres "$REPLICA" -- \
  psql -U postgres -d postgres -c \
  "SELECT pg_is_in_recovery(), * FROM consistency_check;"

Example output:

 pg_is_in_recovery | id |   value
-------------------+----+------------
 t                 |  1 | replicated
(1 row)

A pg_is_in_recovery() = t value shows that the query ran on the replica. The presence of the replicated row confirms that data was transferred from the primary instance to the synchronous replica.

ConsistencyAndAvailability mode

ConsistencyAndAvailability mode creates a primary PostgreSQL instance, one synchronous replica, and one asynchronous replica.

To use this mode, specify:

spec:
  type: Cluster
  cluster:
    topology: Zonal
    replication: ConsistencyAndAvailability

After Postgres is created, three PostgreSQL instances start:

d8 k get pods -n my-postgres \
  -l managed-services.deckhouse.io/managed-service-name=app-postgres \
  -o wide

Example output:

NAME                     READY   STATUS
d8ms-pg-app-postgres-1   1/1     Running
d8ms-pg-app-postgres-2   1/1     Running
d8ms-pg-app-postgres-3   1/1     Running

Check the replication mode as described in Check replication mode. For ConsistencyAndAvailability, expect two replicas — with sync_state = quorum and sync_state = async:

     application_name      |   state   | sync_state
---------------------------+-----------+------------
 d8ms-pg-app-postgres-2    | streaming | quorum
 d8ms-pg-app-postgres-3    | streaming | async
(2 rows)

Change replication mode of an existing cluster

You can change the replication mode of an existing Postgres in Cluster mode. To do this, change spec.cluster.replication in the app-postgres manifest and reapply it.

For example, to switch from Availability to Consistency, specify:

spec:
  cluster:
    replication: Consistency

Apply the changes:

d8 k apply -f postgres.yaml

During the update, ScaledToLastValidConfiguration may temporarily switch to False. After the update completes, the object’s conditions (status.conditions) should return to True.

Check the new mode as described in Check replication mode. After switching to Consistency, the replica should work in synchronous mode:

d8ms-pg-app-postgres-1 | streaming | quorum

When switching back to Availability, the same check should show asynchronous replication:

d8ms-pg-app-postgres-2 | streaming | async

When switching to ConsistencyAndAvailability, the number of instances increases from two to three. Check the running instances:

d8 k get pods -n my-postgres \
  -l managed-services.deckhouse.io/managed-service-name=app-postgres \
  -o wide

After the update completes, the pg_stat_replication check should show a synchronous and an asynchronous replica:

     application_name      |   state   | sync_state
---------------------------+-----------+------------
 d8ms-pg-app-postgres-3    | streaming | async
 d8ms-pg-app-postgres-2    | streaming | quorum
(2 rows)

When switching back from ConsistencyAndAvailability to Consistency, the number of instances decreases from three to two, and the remaining replica works in streaming | quorum mode.

Create logical database and user

The main example creates the app-rw user and the app logical database:

spec:
  users:
    - name: app-rw
      role: rw
      storeCredsToSecret: app-postgres-rw

  databases:
    - name: app

After applying the manifest, wait for the users and databases to synchronize. The USERSSYNCED and DATABASESSYNCED conditions should be True:

d8 k get postgres app-postgres -n my-postgres -o wide

PostgreSQL user

User credentials are stored in the Secret specified in storeCredsToSecret.

Check the created Secret:

d8 k get secret app-postgres-rw -n my-postgres

Example output:

NAME              TYPE                       DATA
app-postgres-rw   kubernetes.io/basic-auth   4

The Secret contains the parameters required for connecting:

app-dsn
host
password
username

Get the connection parameters as follows:

echo "host: $(d8 k get secret app-postgres-rw -n my-postgres -o jsonpath='{.data.host}' | base64 --decode)"
echo "username: $(d8 k get secret app-postgres-rw -n my-postgres -o jsonpath='{.data.username}' | base64 --decode)"
echo "password: $(d8 k get secret app-postgres-rw -n my-postgres -o jsonpath='{.data.password}' | base64 --decode)"
echo "app-dsn: $(d8 k get secret app-postgres-rw -n my-postgres -o jsonpath='{.data.app-dsn}' | base64 --decode)"

Example output:

host: d8ms-pg-app-postgres-rw
username: app-rw
password: <PASSWORD>
app-dsn: postgresql://app-rw:<PASSWORD>@d8ms-pg-app-postgres-rw:5432/app

Where <PASSWORD> is the user’s password from the Secret.

You can use the values from the Secret to configure your application or PostgreSQL client connection.

Declarative user management

The list of users in spec.users describes the required PostgreSQL state. When the list changes, the module synchronizes user roles and their related Secrets.

For example, remove the app-rw user from the manifest:

spec:
  users: []

Apply the updated manifest:

d8 k apply -f postgres.yaml

After synchronization completes, the USERSSYNCED condition should return to True:

d8 k get postgres app-postgres -n my-postgres -o wide

Check that the role is gone directly in PostgreSQL:

PRIMARY="$(d8 k get clusters.cnpg.internal.managed.deckhouse.io d8ms-pg-app-postgres \
  -n my-postgres \
  -o jsonpath='{.status.targetPrimary}')"

d8 k exec -n my-postgres "$PRIMARY" -- \
  psql -U postgres -d postgres -Atc \
  "SELECT rolname FROM pg_roles WHERE rolname = 'app-rw';"

The command shouldn’t return a role name.

When running d8 k exec, a message about the selected container may appear:

Defaulted container "postgres" out of: postgres, bootstrap-controller (init)

The app logical database, which remains in spec.databases, isn’t deleted when the user is removed. Check that it still exists:

d8 k exec -n my-postgres "$PRIMARY" -- \
  psql -U postgres -d postgres -Atc \
  "SELECT datname FROM pg_database WHERE datname = 'app';"

Expected output:

app

To recreate the user, add it back to spec.users:

spec:
  users:
    - name: app-rw
      role: rw
      storeCredsToSecret: app-postgres-rw

After you reapply the manifest, the module recreates the PostgreSQL role and the Secret.

Logical database

The logical databases that the module must create and maintain are defined in spec.databases:

spec:
  databases:
    - name: app

After the database is created, the DATABASESSYNCED condition should be True.

Connect to PostgreSQL

After Postgres is created, the module creates the -r, -ro, and -rw Services, used to connect to the PostgreSQL instances depending on their role:

• -rw: the primary instance;
• -ro: the replicas;
• -r: all available instances.

For clarity, the app-postgres example creates the following Services:

NAME                      TYPE        PORT(S)
d8ms-pg-app-postgres-r    ClusterIP   5432/TCP
d8ms-pg-app-postgres-ro   ClusterIP   5432/TCP
d8ms-pg-app-postgres-rw   ClusterIP   5432/TCP

By default, these Services have type ClusterIP and are available inside the cluster. The user’s connection credentials and parameters are stored in the Secret specified in storeCredsToSecret.

You can connect to PostgreSQL both from applications inside the cluster and from an external network. For an external connection, the corresponding Service needs to be published separately.

Connect from within the cluster

For a connection from within the cluster, use the corresponding Service and the credentials from the user’s Secret. In the main example, an application with write access connects to the d8ms-pg-app-postgres-rw Service as the app-rw user to the app database.

To test the connection, you don’t need to install psql on a control plane node. You can use a temporary client Pod instead:

d8 k run postgres-client \
  -n my-postgres \
  --rm -it \
  --restart=Never \
  --image=postgres:17 \
  --env="PGPASSWORD=$(d8 k get secret app-postgres-rw -n my-postgres -o jsonpath='{.data.password}' | base64 --decode)" \
  -- \
  psql \
    -h d8ms-pg-app-postgres-rw \
    -U app-rw \
    -d app \
    -c 'SELECT current_database(), session_user, current_user;'

Example output:

 current_database | session_user | current_user
------------------+--------------+--------------
 app              | app-rw       | rw
(1 row)

session_user shows the user the connection was made as (app-rw), and current_user shows the current access role (rw).

External connection to PostgreSQL

To work with PostgreSQL from an external network, you can use graphical clients and other applications that support connecting to PostgreSQL. For this, the Service through which PostgreSQL is published must be reachable from outside the cluster, and the client needs to specify the server address, port, database, and user credentials.

In this example, the connection is made to the previously created app database as the app-rw user in the app-postgres Postgres.

Publish PostgreSQL for external access

The publishing method depends on the cluster’s network infrastructure. In this example, an external load balancer accepts connections on <EXTERNAL_IP>:5432 and forwards them to NodePort 30001 on a cluster node. A separate Service routes this traffic to the primary PostgreSQL instance.

Don’t modify the d8ms-pg-app-postgres-rw Service created by the module. Create a separate Service for external access:

apiVersion: v1
kind: Service
metadata:
  name: app-postgres-external
  namespace: my-postgres
spec:
  type: NodePort
  selector:
    cnpg.internal.managed.deckhouse.io/cluster: d8ms-pg-app-postgres
    cnpg.internal.managed.deckhouse.io/instanceRole: primary
  ports:
    - name: postgres
      protocol: TCP
      port: 5432
      targetPort: 5432
      nodePort: 30001

Apply the manifest:

d8 k apply -f app-postgres-external.yaml

Check the created Service:

d8 k get svc app-postgres-external -n my-postgres -o wide

Example output:

NAME                    TYPE       CLUSTER-IP      EXTERNAL-IP   PORT(S)          AGE   SELECTOR
app-postgres-external   NodePort   10.223.111.45   <none>        5432:30001/TCP   4s    cnpg.internal.managed.deckhouse.io/cluster=d8ms-pg-app-postgres,cnpg.internal.managed.deckhouse.io/instanceRole=primary

On the external load balancer, configure it to accept TCP connections on port 5432 and forward them to NodePort 30001 on the cluster node. In this example, the following chain is set up:

<EXTERNAL_IP>:5432
        |
external load balancer
        |
<NODE_IP>:30001
        |
NodePort
        |
primary PostgreSQL :5432

Before testing the external connection, you can confirm that the created NodePort routes traffic to the primary PostgreSQL instance. To do this, get the user’s password:

PGPASSWORD="$(d8 k get secret app-postgres-rw -n my-postgres \
  -o jsonpath='{.data.password}' | base64 --decode)"

Start a temporary client Pod and connect via the node IP and NodePort:

d8 k run nodeport-test \
  -n my-postgres \
  --rm -i \
  --restart=Never \
  --image=postgres:17 \
  --env="PGPASSWORD=$PGPASSWORD" \
  -- \
  psql \
    -h <NODE_IP> \
    -p 30001 \
    -U app-rw \
    -d app \
    -c "SELECT current_database(), pg_is_in_recovery(), inet_server_addr();"

Example of a successful result:

 current_database | pg_is_in_recovery | inet_server_addr
------------------+-------------------+------------------
 app              | f                 | <POD_IP>
(1 row)

A pg_is_in_recovery = f value confirms that the connection is directed to the primary PostgreSQL instance.

Connect by IP address

You can connect directly by IP address, for example, to verify external access to PostgreSQL. For a permanent connection, it’s recommended to use a DNS name and TLS with server certificate verification, as described below.

Get the user’s password:

d8 k get secret app-postgres-rw -n my-postgres \
  -o jsonpath='{.data.password}' | base64 --decode; echo

In DBeaver, create a PostgreSQL connection and specify:

Host:     <EXTERNAL_IP>
Port:     5432
Database: app
Username: app-rw
Password: <PASSWORD>

Where <PASSWORD> is the password from the app-postgres-rw Secret.

After connecting, open the SQL Editor and run:

SELECT
    current_database(),
    session_user,
    inet_server_addr(),
    inet_server_port(),
    pg_is_in_recovery();

On the tested bench, the query returned:

 current_database | session_user | inet_server_addr | inet_server_port | pg_is_in_recovery
------------------+--------------+------------------+------------------+-------------------
 app              | app-rw       | <POD_IP>         |             5432 | f

A pg_is_in_recovery = f value confirms the connection to the primary PostgreSQL instance.

Connect with TLS verification

For a permanent external connection, it’s recommended to use TLS with server certificate verification.

In the example, app-postgres uses K8s mode, so PostgreSQL TLS certificates are issued automatically. The server certificate is signed by cluster-selfsigned-ca.

Save the automatically created server certificate to a file to determine the DNS name from the SAN:

d8 k get secret d8ms-pg-app-postgres-server-cert \
  -n my-postgres \
  -o jsonpath='{.data.tls\.crt}' | \
  base64 --decode > /tmp/app-postgres-server.crt

View the certificate details and its Subject Alternative Name (SAN):

openssl x509 \
  -in /tmp/app-postgres-server.crt \
  -noout \
  -subject -issuer -dates -ext subjectAltName

For app-postgres, the certificate contains the DNS name of the -rw Service:

d8ms-pg-app-postgres-postgres-rw.<EXTERNAL_IP>.sslip.io

With verify-full mode, the client checks that the server name matches the certificate, so use the DNS name from the SAN for the connection.

Get the CA certificate:

d8 k get secret selfsigned-ca-key-pair \
  -n d8-cert-manager \
  -o jsonpath='{.data.tls\.crt}' | \
  base64 --decode > /tmp/app-postgres-ca.crt

Verify the trust chain:

openssl verify \
  -CAfile /tmp/app-postgres-ca.crt \
  /tmp/app-postgres-server.crt

Example of a successful result:

/tmp/app-postgres-server.crt: OK

Transfer the CA certificate to the machine you’re connecting from. For example, if the cluster node is reachable via SSH, copy the certificate using scp:

scp user@<NODE_IP>:/tmp/app-postgres-ca.crt ~/app-postgres-ca.crt

In DBeaver, specify the connection parameters:

Host:     d8ms-pg-app-postgres-postgres-rw.<EXTERNAL_IP>.sslip.io
Port:     5432
Database: app
Username: app-rw
Password: <PASSWORD>

Where <PASSWORD> is the password from the app-postgres-rw Secret.

In the SSL settings, specify the CA certificate and verify-full mode:

CA Certificate: <CA_CERT_PATH>
SSL mode:       verify-full

Where <CA_CERT_PATH> is the path to the app-postgres-ca.crt file.

After connecting, run:

SELECT
    current_database(),
    session_user,
    inet_server_addr(),
    inet_server_port(),
    pg_is_in_recovery();

A successful query execution and a pg_is_in_recovery = f value confirm the connection to the primary PostgreSQL instance.

If you use verify-full and specify the <EXTERNAL_IP> address instead of the DNS name from the SAN, server name verification fails:

The hostname <EXTERNAL_IP> could not be verified by hostnameverifier PgjdbcHostnameVerifier.

When using verify-full, connect using a DNS name listed in the server certificate’s SAN.

Configure PostgreSQL parameters

PostgreSQL parameters can be changed via spec.configuration, if the selected PostgresClass allows overriding them.

Whether a parameter can be changed is determined by the PostgresClass settings:

• the parameter must be allowed to be overridden;
• the parameter value must comply with the configured validation rules.

If a parameter cannot be overridden or its value is outside the allowed limits, the API rejects the request.

Change an allowed parameter

In the main example, app-postgres uses the default PostgresClass, which allows changing the maxConnections parameter.

Change the value:

spec:
  configuration:
    maxConnections: 100

Apply the changes:

d8 k apply -f postgres.yaml

After the update completes, check the applied value directly in PostgreSQL:

PRIMARY="$(d8 k get clusters.cnpg.internal.managed.deckhouse.io d8ms-pg-app-postgres \
  -n my-postgres \
  -o jsonpath='{.status.targetPrimary}')"

d8 k exec -n my-postgres "$PRIMARY" -- \
  psql -U postgres -d postgres -c \
  "SHOW max_connections;"

Example output:

 max_connections
-----------------
 100

The parameter was changed because it’s allowed to be overridden by the selected PostgresClass.

Restrictions on parameter overrides

A PostgresClass can restrict the list of PostgreSQL parameters a user can change via spec.configuration.

For example, if the PostgresClass only allows overriding:

overridableConfiguration:
  - maxConnections
  - sharedBuffers
  - walKeepSize

an attempt to change a parameter not on this list is rejected.

Apply, for example:

spec:
  configuration:
    workMem: 16Mi

d8 k apply -f postgres.yaml

The API returns an error. Example output:

Configuration field workmem restricted to override by administrator in selected postgresClass

In this case, the Postgres configuration doesn’t change, since the parameter is restricted by the selected PostgresClass.

Validate parameter values

In addition to specifying which parameters can be overridden, a PostgresClass can define validation rules for their values.

For example, if maxConnections has the following constraint:

configuration.maxConnections >= 100

the following change is rejected:

spec:
  configuration:
    maxConnections: 50

Apply the manifest:

d8 k apply -f postgres.yaml

The API returns an error. Example output:

Rule: configuration.maxConnections >= 100

The existing Postgres continues running with the last successfully applied configuration.

Configure TLS

The spec.tls parameter is used to manage PostgreSQL TLS certificates. The CertManager, CustomCertificate, and K8s modes are supported.

To use certificates issued by cert-manager, specify CertManager mode:

spec:
  tls:
    mode: CertManager
    certManager:
      clusterIssuerName: postgres-ca

The corresponding Issuer or ClusterIssuer must be prepared in advance. The administrative dependencies are described in Dependencies for specific features.

To use existing certificates from a Secret, select CustomCertificate mode:

spec:
  tls:
    mode: CustomCertificate
    customCertificate:
      serverCASecret: postgres-ca
      serverTLSSecret: postgres-tls

K8s mode

In K8s mode, PostgreSQL certificates are issued automatically:

spec:
  tls:
    mode: K8s

Once the object reaches the ready state, the module creates a Secret with the CA, server, and replication certificates.

Check TLS usage on the PostgreSQL side through the pg_stat_ssl view. Identify the primary instance the same way as in Check replication mode, and run the query:

PRIMARY="$(d8 k get clusters.cnpg.internal.managed.deckhouse.io d8ms-pg-app-postgres \
  -n my-postgres \
  -o jsonpath='{.status.targetPrimary}')"

d8 k exec -n my-postgres "$PRIMARY" -- \
  psql -U postgres -d postgres -c "
    SELECT
      a.pid,
      a.usename,
      a.client_addr,
      a.client_port,
      s.ssl,
      s.version,
      s.cipher
    FROM pg_stat_activity a
    LEFT JOIN pg_stat_ssl s USING (pid)
    WHERE a.usename = 'app-rw';
  "

For a TLS connection, the ssl field is t, and version and cipher show the TLS version and cipher used.

Configuring a client connection with server certificate verification is described in Connect with TLS verification.

Monitoring and alerts

For Postgres, you can enable monitoring with alerts, fully disable monitoring, or keep monitoring without alerts. The observability mode is set by the spec.observability parameter.

The main example enables monitoring and alerts:

spec:
  observability: Enabled

To fully disable monitoring, use:

spec:
  observability: Disabled

To keep monitoring but disable alerts, use:

spec:
  observability: EnabledWithoutAlerts

Check the applied mode by the Pod labels:

d8 k get pod -n my-postgres \
  -l managed-services.deckhouse.io/managed-service-name=app-postgres \
  -o json | \
  jq '.items[].metadata.labels | with_entries(select(.key | test("observability|prometheus")))'

The observability.deckhouse.io/servicemonitoring label value depends on the selected mode:

Enabled                → enabled
Disabled               → disabled
EnabledWithoutAlerts   → no-alerts

With monitoring enabled, the output also contains the label:

"prometheus.deckhouse.io/custom-target": "managed-postgres"

Backup and restore

The PostgresSnapshot object is used to create snapshots. The StorageClass where Postgres is placed must use a CSI driver with snapshot support, and a corresponding VolumeSnapshotClass must be available in the cluster.

The main example uses the replicated StorageClass, for which the provider in this configuration doesn’t support creating snapshots. So for this demonstration, a separate snapshot-local StorageClass on sds-local-volume with LVM Thin is used.

Check the available snapshot classes:

d8 k get volumesnapshotclass

The following snapshot class is available for snapshot-local:

NAME                              DRIVER                           DELETIONPOLICY
sds-local-volume-snapshot-class   local.csi.storage.deckhouse.io   Delete

Create a snapshot

To test this, create a separate snapshot-pg Postgres in the snapshot-local StorageClass:

apiVersion: managed-services.deckhouse.io/v1alpha1
kind: Postgres
metadata:
  name: snapshot-pg
  namespace: my-postgres
spec:
  postgresClassName: default
  instance:
    cpu:
      cores: 1
      coreFraction: 50
    memory:
      size: 1Gi
    persistentVolumeClaim:
      size: 2Gi
      storageClassName: snapshot-local
  type: Standalone
  users:
    - name: snapshot-rw
      role: rw
      storeCredsToSecret: snapshot-pg-rw
  databases:
    - name: snapshotdb

To clearly verify data recovery at the moment the snapshot was created, use a check table: add the BEFORE_SNAPSHOT row before creating the snapshot, and AFTER_SNAPSHOT after.

Create the check table and insert the first row:

PGPASSWORD="$(d8 k get secret snapshot-pg-rw -n my-postgres \
  -o jsonpath='{.data.password}' | base64 --decode)"

d8 k run snapshot-client \
  -n my-postgres \
  --rm -i \
  --restart=Never \
  --image=postgres:17 \
  --env="PGPASSWORD=$PGPASSWORD" \
  -- \
  psql \
    -h d8ms-pg-snapshot-pg-rw \
    -U snapshot-rw \
    -d snapshotdb \
    -c "
      CREATE TABLE snapshot_check (
        id integer PRIMARY KEY,
        value text NOT NULL
      );
      INSERT INTO snapshot_check VALUES (1, 'BEFORE_SNAPSHOT');
      SELECT * FROM snapshot_check;
    "

Example output:

 id |      value
----+-----------------
  1 | BEFORE_SNAPSHOT
(1 row)

Create the PostgresSnapshot object:

apiVersion: managed-services.deckhouse.io/v1alpha1
kind: PostgresSnapshot
metadata:
  name: snapshot-pg-backup
  namespace: my-postgres
spec:
  postgresName: snapshot-pg

Apply the manifest:

d8 k apply -f snapshot-pg-backup.yaml

Check the snapshot status:

d8 k get postgressnapshot snapshot-pg-backup -n my-postgres \
  -o jsonpath='{.status.phase}{"\n"}'

After the snapshot is created successfully, the command returns:

completed

Check the created VolumeSnapshot:

d8 k get volumesnapshot -n my-postgres

Example output:

NAME                         READYTOUSE   SOURCEPVC               RESTORESIZE   SNAPSHOTCLASS
d8ms-pg-snapshot-pg-backup   true         d8ms-pg-snapshot-pg-1   2Gi           sds-local-volume-snapshot-class

READYTOUSE=true confirms the snapshot is ready for recovery.

After the snapshot is created, add a second check row to the original database:

PGPASSWORD="$(d8 k get secret snapshot-pg-rw -n my-postgres \
  -o jsonpath='{.data.password}' | base64 --decode)"

d8 k run snapshot-client \
  -n my-postgres \
  --rm -i \
  --restart=Never \
  --image=postgres:17 \
  --env="PGPASSWORD=$PGPASSWORD" \
  -- \
  psql \
    -h d8ms-pg-snapshot-pg-rw \
    -U snapshot-rw \
    -d snapshotdb \
    -c "
      INSERT INTO snapshot_check VALUES (2, 'AFTER_SNAPSHOT');
      SELECT * FROM snapshot_check ORDER BY id;
    "

Example output:

 id |      value
----+-----------------
  1 | BEFORE_SNAPSHOT
  2 | AFTER_SNAPSHOT
(2 rows)

Restore from a PostgresSnapshot

To restore, create a new Postgres object and specify the created PostgresSnapshot in spec.dataSource.objectRef. You don’t need to delete the original Postgres:

apiVersion: managed-services.deckhouse.io/v1alpha1
kind: Postgres
metadata:
  name: snapshot-pg-restored
  namespace: my-postgres
spec:
  dataSource:
    objectRef:
      kind: PostgresSnapshot
      name: snapshot-pg-backup
  type: Standalone
  instance:
    cpu:
      cores: 1
      coreFraction: 50
    memory:
      size: 1Gi
    persistentVolumeClaim:
      size: 2Gi
      storageClassName: snapshot-local

The type and instance fields must be specified explicitly — they aren’t inherited from the original Postgres. After that, apply the manifest:

d8 k apply -f snapshot-pg-restored.yaml

Wait for the restored Postgres to become ready:

d8 k get postgres snapshot-pg-restored -n my-postgres -o wide -w

After the restored PostgreSQL starts, check the check table:

PGPASSWORD="$(d8 k get secret snapshot-pg-rw -n my-postgres \
  -o jsonpath='{.data.password}' | base64 --decode)"

d8 k run snapshot-restore-check \
  -n my-postgres \
  --rm -i \
  --restart=Never \
  --image=postgres:17 \
  --env="PGPASSWORD=$PGPASSWORD" \
  -- \
  psql \
    -h d8ms-pg-snapshot-pg-restored-rw \
    -U snapshot-rw \
    -d snapshotdb \
    -c "SELECT * FROM snapshot_check ORDER BY id;"

Example output:

 id |      value
----+-----------------
  1 | BEFORE_SNAPSHOT
(1 row)

The presence of only BEFORE_SNAPSHOT confirms that the database state was restored to the moment the snapshot was created.

Check status

The current state of Postgres is reflected in status.conditions of this object.

For a quick check, use:

d8 k get postgres app-postgres -n my-postgres -o wide

Main conditions (status.conditions):

While resources or PostgreSQL parameters are being changed, some conditions may temporarily be False, while Available stays True.

To watch the status change:

d8 k get postgres app-postgres -n my-postgres -o wide -w

To see the details:

d8 k get postgres app-postgres -n my-postgres -o yaml

If Postgres doesn’t reach the ready state, see the Frequently Asked Questions section for diagnostics.

Additional resources

• Frequently Asked Questions
• Module managed-postgres custom resources