Administrative Tasks

Manually Restarting PostgreSQL

There are times when you might need to manually restart PostgreSQL. This can be done by adding or updating a custom annotation to the cluster’s spec.metadata.annotations section. PGO will notice the change and perform a rolling restart.

For example, if you have a cluster named hippo in the namespace postgres-operator, all you need to do is patch the hippo postgrescluster with the following:

kubectl patch postgrescluster/hippo -n postgres-operator --type merge \
  --patch '{"spec":{"metadata":{"annotations":{"restarted":"'"$(date)"'"}}}}'

Watch your hippo cluster: you will see the rolling update has been triggered and the restart has begun.

Rotating TLS Certificates

Credentials should be invalidated and replaced (rotated) as often as possible to minimize the risk of their misuse. Unlike passwords, every TLS certificate has an expiration, so replacing them is inevitable. When you use your own TLS certificates with PGO, you are responsible for replacing them appropriately. Here’s how.

PGO automatically detects and loads changes to the contents of PostgreSQL server and replication Secrets without downtime. You or your certificate manager need only replace the values in the Secret referenced by spec.customTLSSecret.

If instead you change spec.customTLSSecret to refer to a new Secret or new fields, PGO will perform a rolling restart.

When changing the PostgreSQL certificate authority, make sure to update customReplicationTLSSecret as well.

PgBouncer needs to be restarted after its certificates change. There are a few ways to do it:

  1. Store the new certificates in a new Secret. Edit the PostgresCluster object to refer to the new Secret, and PGO will perform a rolling restart of PgBouncer.

    spec:
     proxy:
       pgBouncer:
         customTLSSecret:
           name: hippo.pgbouncer.new.tls

    or

    1. Replace the old certificates in the current Secret. PGO doesn’t notice when the contents of your Secret change, so you need to trigger a rolling restart of PgBouncer. Edit the PostgresCluster object to add a unique annotation. The name and value are up to you, so long as the value differs from the previous value. yaml spec: proxy: pgBouncer: metadata: annotations: restarted: Q1-certs

This kubectl patch command uses your local date and time:

   kubectl patch postgrescluster/hippo --type merge \
     --patch '{"spec":{"proxy":{"pgBouncer":{"metadata":{"annotations":{"restarted":"'"$(date)"'"}}}}}}'

Next Steps

We’ve covered a lot in terms of building, maintaining, scaling, customizing, restarting, and expanding our Postgres cluster. However, there may come a time where we need to delete our Postgres cluster. How do we do that?