Skip to content

Postgres / PostgreSQL

Postgres is an open-source relational database used for a number of applications at VTTI, both third-party and in-house. It has a long history and has seen particular success and growth in the past decade due in large part to its extensibility.

Info

The debate over the name and pronunciation is long but hopefully not too contentious! Postgres, PostgreSQL, PG, PGSQL and others might be used throughout these docs.

For pronunciation, typically “POST-gres-cue-ell” and “POST-gres” are what you'll hear most often. Unless you really enjoy being in the minority, you probably don't want to pronounce it as "POST-gray" (like its French with a silent s on the end) or “post-gres-SEE-quel”1

Postgres Operator

Like many of our stateful deployments on Kubernetes, we leverage an Operator to handle many of the day-to-day tasks of our Postgres databases. This allows us to declare the basic state of the database we would like, and the operator will go make that happen. In addition to creating databases and users, operators allow us to easily setup other configuration like backups and HA (high-availability) that includes a replicated failover instance.

We currently use an open-source operator from Zalando2 to run a number of Postgres instances and clusters on Kubernetes.

Connecting

Credentials and host config (relative/internal to the Kubernetes cluster) can be found in Vault.

Note

If you use LDAP to sign into Vault you will get a "surprise" Duo push as a two-factor that needs to be approved before you can continue. Alternatively, use the OIDC method to login via our GitLab (leave Role field empty).

pgAdmin

pgAdmin is one of the more popular Postgres-specific GUI tools that offers both desktop and web-based versions. We have the web-based version deployed inside of the prod Kubernetes cluster, which provides convenient access to all of the databases deployed there:

https://pgadmin.prod.cloud.vtti.vt.edu/

Click "Login with VTTI Login" to sign in through our SSO system (AD credentials). You can then register postgres databases to connect to.

Port-forward

Port-forwarding allows you to connect to pods running in Kubernetes via the API server. To do this, you must have a local application (like kubectl) installed and configured to access Kubernetes.

Since most of our Postgres instances are setup in a failover mode with a replica, you'll need to find the correct instance to connect to. From within the cluster, we can use a Service that gets updated with the current IP address of the leader, but for various reasons, we can't port-forward to that service and instead have to go directly to the pod. There are a couple of ways you can do this:

  • Check the logs of the postgres instances like kubectl -n postgres logs it-postgres-0 --tail 5 and see whether it indicates it is the leader or secondary/follower (you want to port-forward to the leader)
  • Look at the IP address of the service kubectl -n postgres describe service it-postgres and look for the "Endpoints" value which will be the IP address of the leader instance. You can use kubectl -n postgres get pod -o wide to see the pods and their IP addresses.

Once you have the pod to connect to, use the port-forward command, specifying the local port and remote pod port: kubectl -n postgres port-forward it-postgres-1 5432:5432. From your local DB tool (e.g. DBVisualizer) you can now connect using localhost as the server.

Other methods

We're planning to provide easier, secure access in the not too distant future after some upgrades to the Kubernetes cluster.

Troubleshooting

The Postgres volume is filling up

If the Postgres volume (PVC) is filling up, there are a couple of things to determine first. If you are running an HA cluster and only seeing one of the volumes filling up, then this is likely related to a replication issue, rather than the actual data stored in the database. This can happen if the replica gets into a state where it is no longer replicating properly, in which case the WAL logs on the primary will not be removed because they have not been replicated.

You can start by looking at some logs, and then you can exec into the pod that is the master and run some patronictl commands to get things back to a working state. 

kubectl exec -it vcc-postgres-0 -- bash

# Check the size of dirs in pgdata 
$ du -h /home/postgres/pgdata/pgroot/
# If the pg_wal is really big, that's the issue (obviously)

# Check the status of the patroni cluster
$ patronictl list

+ Cluster: vcc-postgres (6883419593675641139) ---------+----+-----------+
| Member         |      Host      |   Role  |  State   | TL | Lag in MB |
+----------------+----------------+---------+----------+----+-----------+
| vcc-postgres-0 | 10.198.104.130 |  Leader | running  | 64 |           |
| vcc-postgres-1 |  10.198.0.140  | Replica | starting |    |   unknown |
+----------------+----------------+---------+----------+----+-----------+
# Cluster replica is stuck in the starting phase, logs confirm it is having issues starting

# Let's pause the automatic failover and then reinitialize the replica member
$ patronictl pause vcc-postgres
$ patronictl reinit vcc-postgres vcc-postgres-1
# This will start a reinitialization of the replica after confirmation. Check on the status after a few minutes
$ patronictl list

+ Cluster: vcc-postgres (6883419593675641139) --------+----+-----------+
| Member         |      Host      |   Role  |  State  | TL | Lag in MB |
+----------------+----------------+---------+---------+----+-----------+
| vcc-postgres-0 | 10.198.104.130 |  Leader | running | 64 |           |
| vcc-postgres-1 |  10.198.0.140  | Replica | running | 64 |         0 |
+----------------+----------------+---------+---------+----+-----------+

# Finally unpause the cluster failover
$ patronictl resume vcc-postgres

You can also confirm the size of the pg_wal dir has been reduced, and within a few minutes, this change will be picked up by the prometheus metrics and alerts.

  1. https://www.cybertec-postgresql.com/en/the-shibboleth-of-postgresql/ ↩

  2. Zalando is a huge European online fashion retailer with thousands of developers and a ton of experience running Postgres that they share with the community through a number of open-source projects ↩