Migration from PostgresCluster API

The PGAdmin API is the new way to deploy pgAdmin with Crunchy Postgres for Kubernetes (CPK). In this guide, we walk through how to migrate your pgAdmin deployment from the PostgresCluster API to the PGAdmin API.

Why migrate?

Deploying pgAdmin through the PostgresCluster API limits your configuration options in quite a few ways:

• pgAdmin deployments are only compatible with PostgreSQL 14 and below.
• pgAdmin users and their passwords must be the same as specific Postgres users defined in the PostgresCluster.
• pgAdmin usernames will always have the @pgo suffix.

Besides the limitations just mentioned, the PGAdmin API has significant improvements over the PostgresCluster version:

• Added support for new versions of pgAdmin that include feature enhancements, bug fixes, and security patches
• New features like:
  • declarative user passwords
  • better configuration options
  • server discovery
  • connectivity options

In this section we will walk through how to configure your PGAdmin manifest to replicate your PostgresCluster API-based deployment. Some of these fields can be copied directly from your PostgresCluster manifest while others are either new or need to be configured differently. Before we talk about how to migrate your PostgresCluster API-based pgAdmin, let's walk through how the PGAdmin API is different.

How does the PGAdmin API compare?

What hasn't changed?

Configuration

Configuration of pgAdmin settings and mounting of files is the same between both APIs. These options are still configured through a config section of your manifest. You can define pgAdmin settings using settings and mount files to the pgAdmin Pod using files. If you have your pgAdmin connected to an LDAP server, you can user ldapBindPassword to securely provide your credential. You can copy these fields directly over from your PostgresCluster manifest. Your mounted files and settings will be applied to your new pgAdmin deployment in the same way as your existing deployment.

Generic Kubernetes Options

Some of the configuration options in the PostgresCluster API are basic Kubernetes configuration options that aren't specific to pgAdmin. For example, the dataVolumeClaimSpec is a standard Kubernetes field that defines the size of your pgAdmin PVC. Other examples are the metadata, resources, and affinity fields. These fields can also be copied from your PostgresCluster manifest and defined in the PGAdmin manifest.

What has changed?

User management

User management with pgAdmin has changed significantly with the PGAdmin API. With the PostgresCluster API, users and passwords were created based on the Postgres users that you defined in your manifest. With PGAdmin, users are still defined in the users section of your manifest but are unrelated to Postgres. This allows you to update users and rotate passwords separately from Postgres.

pgAdmin requires usernames to be in the email format. With the PostgresCluster API, this condition was met by adding the @pgo suffix to your user. Now you have the ability to provide your own email as the username, meaning you can have <user>@my.company.com.

You can also declaratively define and rotate your password by providing a reference to a Kubernetes Secret. Before you create your PGAdmin resource, you will need to create this Secret.

Service Creation

The PostgresCluster API for pgAdmin contains a copy of the Kubernetes Service spec, configurable through the service field. This field allows you to effectively pass a Service definition through to Kubernetes. With the PGAdmin API, we have decided against providing this type of pass-through configuration of the pgAdmin Service. Instead, the serviceName field of the PGAdmin API produces a simple ClusterIP service.

Services and connections to your pgAdmin deployment will vary depending on your Kubernetes environment. We go into more detail about connecting to your pgAdmin and creating Services in our connectivity documentation.

What's new?

Server Discovery

Any pgAdmin deployments created with the PGAdmin API are not tied to a specific PostgresCluster. This provides the flexibility to create a single pgAdmin that can manage multiple PostgresClusters. The PGAdmin API can be configured to discover servers in your Kubernetes namespace using the serverGroups field. More information about this can be found in the server discovery documentation.

However, you can still easily create a pgAdmin deployment that can only access a single PostgresCluster. This is done by providing your PostgresCluster deployment name through the PGAdmin API. You even have the ability to define your own server group name in the pgAdmin interface!

Migrating to the PGAdmin API

What does a PostgresCluster pgAdmin manifest look like?

Consider a PostgresCluster with the following pgAdmin fields:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo
spec:
  users:
    - name: rhino
      databases:
        - zoo
  userInterface:
    pgAdmin:
      config:
        settings:
          SHOW_GRAVATAR_IMAGE: False
        files:
          - configMap:
              name: myconfigmap
              optional: false
      dataVolumeClaimSpec:
        accessModes:
          - 'ReadWriteOnce'
        resources:
          requests:
            storage: 1Gi
...
Copy

First, notice that this is not a complete PostgresCluster manifest. These are only fields that relate to pgAdmin in some way. Any other fields will be left in your PostgresCluster.

The user definition for rhino creates that user in Postgres and an associated non-administrator user in pgAdmin named rhino@pgo. You will use the same password, stored in the hippo-pguser-rhino Secret, to log in to both Postgres and pgAdmin.

Under the config section of the manifest, we have configuration for files and settings. The SHOW_GRAVATAR_IMAGE setting is disabled and we are mounting the contents of myconfigmap to /etc/pgadmin/conf.d in the Pod.

We define a dataVolumeClaimSpec of size 1GiB that pgAdmin will use to store persistent data, like the SQLite DB file.

Additionally, a ClusterIP Service named hippo-pgadmin is created by default.

With the above in mind, let's look at a similar configuration using the PGAdmin API.

Replicating your PostgresCluster pgAdmin

First, the PostgresCluster manifest is simplified leaving only the users section:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo2
spec:
  users:
    - name: rhino
      databases:
        - zoo
...
Copy

The users section of the PostgresCluster manifest is unchanged. We are still creating a Postgres user rhino and database zoo. You will still need the credentials of the rhino Postgres user when connecting pgAdmin to your database, after you have successfully logged in to pgAdmin.

However, the userInterface section and all pgAdmin specific configuration is now defined in the PGAdmin manifest. Let's consider a PGAdmin manifest:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PGAdmin
metadata:
  name: hippo2-pgadmin
spec:
  users:
    - username: "rhino@example.com"
      passwordRef:
        name: pgadmin-password
        key: password-data
  config:
    settings:
      SHOW_GRAVATAR_IMAGE: False
    files:
      - configMap:
          name: myconfigmap
          optional: false
  serviceName: hippo2-pgadmin # based on the PostgresCluster name
  dataVolumeClaimSpec:
    accessModes:
    - "ReadWriteOnce"
    resources:
      requests:
        storage: 1Gi
  serverGroups:
    - name: "Crunchy PostgreSQL Operator"
      postgresClusterName: hippo2
Copy

User Creation

In the PGAdmin manifest, we still have a users field, but the definition has different fields. The username field can be any string in an email format and the passwordRef field will point to a Secret that contains your password. You can create the Secret with the following command:

kubectl create secret generic pgadmin-password --from-literal=password-data=$YOUR_PASSWORD
Copy

In the example, we create the rhino@example.com pgAdmin user and set the password to the contents of the pgadmin-password secret. Learn more about user management in our user management docs.

Configuration

Like in the PostgresCluster example, we provide configuration options through the config field. The settings and files fields look exactly the same as from our PostgresCluster manifest and can be copied directly over. Your files will still be mounted at /etc/pgadmin/conf.d and your pgAdmin settings will be set. Learn more about configuration in the configuration docs.

Service

Unlike the PostgresCluster API, the PGAdmin API will not create a Service by default. If you are using the default Service, you can replicate this behavior by setting the serviceName field in your PGAdmin manifest. If you do not need a Service, you can simply leave out the serviceName field.

The serviceName field will create a ClusterIP Service with the same naming as the default PostgresCluster pgAdmin deployment (<cluster-name>-pgadmin). In our example, we set serviceName to hippo2-pgadmin. If you need a different type of Service, consult our connectivity docs.

Data Volume

We define a dataVolumeClaimSpec of size 1GiB that pgAdmin will use to store persistent data, like the SQLite DB file. You can copy this spec from your PostgresCluster manifest directly to your PGAdmin manifest at dataVolumeClaimSpec. You can also copy over other generic Kubernetes options, like the affinity, metadata, or resources fields.

Server Discovery

Finally, you will need to tell the PGAdmin API what PostgresCluster that it should discover. Since we are replicating a PostgresCluster deployment, where PGAdmin can only see one PostgresCluster, we will select the PostgresCluster by name.

In our example, we define a serverGroup named Crunchy PostgreSQL Operator and set it to discover a single PostgresCluster named hippo2. Learn more about server discovery in the server discovery docs.

Next steps

Using the PGAdmin manifest, you can configure a pgAdmin deployment to replicate a one PostgresCluster to one pgAdmin deployment. Similar manifests can be created for other PostgresClusters or you can deploy one pgAdmin that can discover many PostgresClusters. You have the flexibility to choose!

There are some configuration options that we did not cover in this guide. For example, you might be interested in and advanced configuration like LDAP. There is also new functionality that wasn't available through a PostgresCluster API-based deployment, notably TLS configuration using Gunicorn.

If you don't see something in this guide, read through the PGAdmin API docs or feel free to reach out in Discord.