Data Platform/Systems/PostgreSQL/Operations

This page lists standard operations that we may need to carry out on PostgreSQL clusters that are deployed to the Data platform.

Finding which instance is the current PG master

brouberol@deploy2002:~$ kubectl get cluster postgresql-test -ojson | jq -r .status.currentPrimary
postgresql-test-2

Increasing the storage size of a cluster

As we're storing the data on Ceph, we can resize the volume in which PG stores its data on the fly. To do this, simply adjust thecluster.storage.size cluster configuration value.

Assuming the cluster was deployed with the following configuration

cluster:
  storage:
    size: 10Gi

then change it to

cluster:
  storage:
    size: 15Gi

and redeploy the cluster. The volumes should get automatically upsized within minutes.

Bear in mind that volumes cannot be downsized.

Increasing the WAL storage size of a cluster

Follow the same instructions than in Data Platform/Systems/CloudnativePG/Clusters#Increasing the storage size of a cluster but adjust the cluster.walStorage.size parameter instead.

Increasing the max number of connections on PostgreSQL

PostgreSQL server configuration tunables can be set and tweaked via the cluster.postgresql.parameters YAML dictionary (except these ones). For example, if you'd like to increase the max_connectionssetting to 300 in a cluster using all default configuration values, change the cluster configuration to

cluster:
  postgresql:
    parameters:
      max_connections: 300

As each connection opens a UNIX process on the PG server, keep an eye on resource consumption and don't hesitate to increase its amount of CPU and RAM if needed.

Increasing the max number of client connections on PGBouncer

A subset of PGBouncer configuration options can be tweaked from the release YAML values themselves. For example, if PGBouncer is getting near its client connection limit, you can increase it by tweaking the pooler.parameters dictionary entries. For example:

pooler:
  parameters:
    max_client_conn: 200

All PGBouncer configuration options are available here: https://www.pgbouncer.org/config.html