Subject: Access a Postgres storage with two independent instances

From: first last <[email-masked]>
Date: Tue, 7 May 2024 00:47:40 +0530

I tried to deploy Postgres deployment with Kubernetes, having three
replicas that are accessing the same storage(PVC). Here is the configuration
```
apiVersion: v1
kind: PersistentVolume
metadata:
name: postgres-volume
labels:
type: local
spec:
persistentVolumeReclaimPolicy: Delete
capacity:
storage: 10Gi
accessModes:
- ReadWriteOnce
hostPath:
path: /data/postgresql
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
name: postgres-volume-claim
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 10Gi
---
apiVersion: apps/v1
kind: Deployment
metadata:
name: postgres
spec:
replicas: 3
selector:
matchLabels:
app: postgres
template:
metadata:
labels:
app: postgres
spec:
containers:
- name: postgres
image: 'postgres:14'
env:
- name: POSTGRES_DB
value: db
- name: POSTGRES_USER
value: user
- name: POSTGRES_PASSWORD
value: pwd
volumeMounts:
- name: postgresdata
mountPath: /var/lib/postgresql/data
volumes:
- name: postgresdata
persistentVolumeClaim:
claimName: postgres-volume-claim
---
apiVersion: v1
kind: Service
metadata:
name: postgres
labels:
app: postgres
spec:
type: NodePort
ports:
- port: 5432
protocol: TCP
targetPort: 5432
nodePort: 32001
selector:
app: postgres

```

When you apply this configuration file `kubectl apply -f file_name.yaml`,
you can create three pods. Here I have given the configuration to create
the pods that use a single storage as a data store. But what actually
happening is, that it uses separate storage. I have tested all the replicas
manually by creating a db and table and see, if other database stores are
affected or not. Anyway, it is not affected. And then I tried to apply the
same config file change the image name and the env variables suit for MySQL
and tried to deploy it. It works as expected. Anyway, it can not use single
storage(
https://stackoverflow.com/questions/78436945/access-a-mysql-storage-with-two-independent-instances
).

Finally, my question is, why the scenario is different for MySQL and
Postgres?  Could you please help me to figure out the issue? This is for my
Final year project testing Kubernetes using fuzzing.

---

From: Alan Hodgson <[email-masked]>
Date: Mon, 06 May 2024 13:36:59 -0700

On Tue, 2024-05-07 at 00:47 +0530, first last wrote:
> . Anyway, it can not use single
> storage(
> Finally, my question is, why the scenario is different for MySQL
> and Postgres?  Could you please help me to figure out the issue?
> This is for my Final year project testing Kubernetes using fuzzing.

Not sure what you're trying to accomplish, but PostgreSQL can
definitely not share storage between active postmasters. Nor can
MySQL, afaik.

---

From: first last <[email-masked]>
Date: Tue, 7 May 2024 09:56:50 +0530

Actually what I want is multiple Postgres instances to have read and write
access to the same data storage directory. Anyway, that can not be possible
as what you say. But, when I deploy MySQL/MongoDB deployment with multiple
replicas to access the same storage, the all pods except one become
CrashLoopBackOff state. This is happening for MySQL/Mongo. When it comes to
Postgres, I deployed it with multiple pods, all the pods became healthy and
there is no such CrashLoopBackOff state occurred. But those pods access
different storages for every pod. Why this totally different from MySQL and
Mongo?

On Tue, May 7, 2024 at 2:34 AM Alan Hodgson <[email-masked]>
wrote:

> . Anyway, it can not use single storage(
> Not sure what you're trying to accomplish, but PostgreSQL can definitely
> not share storage between active postmasters. Nor can MySQL, afaik.
>

On Tue, May 7, 2024 at 2:34 AM Alan Hodgson <[email-masked]>
wrote:

> . Anyway, it can not use single storage(
> Not sure what you're trying to accomplish, but PostgreSQL can definitely
> not share storage between active postmasters. Nor can MySQL, afaik.
>

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 07 May 2024 13:17:35 +0200

On Tue, 2024-05-07 at 09:56 +0530, first last wrote:
> But, when I deploy MySQL/MongoDB deployment with multiple replicas to
> access the same storage, the all pods except one become CrashLoopBackOff
> state. This is happening for MySQL/Mongo. When it comes to Postgres, I
> deployed it with multiple pods, all the pods became healthy and there is
> no such CrashLoopBackOff state occurred. But those pods access different
> storages for every pod. Why this totally different from MySQL and Mongo?

Because it is different software?

"Why" questions are notoriously hard to answer (as anyone knows who has
ever had children).  Shared-storage architectures are not great, because
they have a single point of failure and only scale very moderately.  So
there are no efforts in PostgreSQL to make that work.

Yours,
Laurenz Albe

---

From: Alan Hodgson <[email-masked]>
Date: Tue, 07 May 2024 09:34:39 -0700

On Tue, 2024-05-07 at 09:56 +0530, first last wrote:
> Actually what I want is multiple Postgres instances to have read
> and write access to the same data storage directory. Anyway, that
> can not be possible as what you say. But, when I deploy
> MySQL/MongoDB deployment with multiple replicas to access the same
> storage, the all pods except one become CrashLoopBackOff state.
> This is happening for MySQL/Mongo. When it comes to Postgres, I
> deployed it with multiple pods, all the pods became healthy and
> there is no such CrashLoopBackOff state occurred. But those pods
> access different storages for every pod. Why this totally different
> from MySQL and Mongo?

No idea, but this doesn't appear to have anything to do with
PostgreSQL. You could load any image in that container and then see
what the storage looks like. Probably better asked on a kubernetes
list somewhere.

Random thought, though, if you actually have multiple nodes in your
k8s cluster, you might just be seeing an artifact of the
ReadWriteOnce access mode. For a real shared volume you probably want
ReadWriteMany, which only a few storage backends support (and which,
again, PostgreSQL would not work on).