[HA supabase][Try 1] Highly available PostgreSQL in Supabase with Stolon

 Some good for Highly available PostgreSQL:

1. spilo - Highly available elephant herd: HA PostgreSQL cluster using Docker

https://github.com/zalando/spilo

Spilo is a Docker image that provides PostgreSQL and Patroni bundled together. Patroni is a template for PostgreSQL HA. Multiple Spilos can create a resilient High Available PostgreSQL cluster. For this, you'll need to start all participating Spilos with identical etcd addresses and cluster names.

2. pgagroal - High-performance connection pool for PostgreSQL

https://github.com/agroal/pgagroal

pgagroal is a high-performance protocol-native connection pool for PostgreSQL.

3. Mastering PostgreSQL Connection Pooling with Odyssey

https://medium.com/@jayantanand00/mastering-postgresql-connection-pooling-with-odyssey-53008942978a

Odyssey’s use of both multithreading and coroutines results in a superior performance compared to other solutions.

4. stolon

https://github.com/sorintlab/stolon

PostgreSQL high availability in Golang

Practice:

Ubuntu server 1: diepnn@192.168.43.163

+ Install supabase self-hosted docker

+ Docker eviroment:

POSTGRES_PASSWORD=your-super-secret-and-long-postgres-password

POSTGRES_HOST=db

POSTGRES_DB=postgres

POSTGRES_PORT=5432

+ Check postgres replication status

# select usename from pg_stat_replication;

"supabase_admin"

Ubuntu server 2: vc@192.168.43.164

+ Install Install PostgreSQL

1. sudo apt install postgresql postgresql-contrib

+ Install Install Etcd

1. sudo apt install etcd

Note: keep status service as this

Install Brew, stolon:

https://brew.sh/

1. /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

1. brew install stolon

Go: 

1. Init with store etcdv3 (http://127.0.0.1:2379)

stolonctl --cluster-name supabase-cluster --store-backend=etcdv3 init

2. Terminal window 1:

stolon-sentinel --cluster-name supabase-cluster --store-backend=etcdv3

3. Terminal window 2:

stolon-keeper --cluster-name supabase-cluster --store-backend=etcdv3 --uid supa1 --data-dir data/supa1 --pg-su-username=supabase_admin --pg-su-password='your-super-secret-and-long-postgres-password' --pg-repl-username=supabase_admin --pg-repl-password='your-super-secret-and-long-postgres-password' --pg-bin-path /usr/lib/postgresql/14/bin --pg-advertise-port 5432 --pg-advertise-address 192.168.43.163 --pg-listen-address=127.0.0.1 --pg-port 5432

4. Terminal window 3:

stolon-proxy --cluster-name supabase-cluster --store-backend=etcdv3 --listen-address 192.168.43.164 --port 25432

5. Test

"C:\Users\ADMIN\AppData\Local\Programs\pgAdmin 4\runtime\psql.exe" -h 192.168.43.164 -p 25432 -U supabase_admin -d postgres

"C:\Users\ADMIN\AppData\Local\Programs\pgAdmin 4\runtime\psql.exe" -h 192.168.43.163 -p 5432 -U supabase_admin -d postgres

6. BUT

The problems occur when I run another keeper

stolon-keeper --cluster-name supabase-cluster --store-backend=etcdv3 --uid supa2 --data-dir data/supa2 --pg-su-username=supabase_admin --pg-su-password='your-super-secret-and-long-postgres-password' --pg-repl-username=rep_user --pg-repl-password='P@ssword321' --pg-bin-path /usr/lib/postgresql/14/bin --pg-advertise-port 5432 --pg-advertise-address 192.168.43.163 --pg-listen-address=127.0.0.1 --pg-port 5435

...

2024-07-04T17:10:13.948+0700    INFO    cmd/keeper.go:1141      current db UID

different than cluster data db UID       {"db": "", "cdDB": "a499f61a"}

... pg_basebackup: error: could not send replication command

"START_REPLICATION": ERROR: replication slot "stolon_a499f61a" does not exist

I am try:

docker exec -u postgres -it supabase-db /bin/bash

apt update

apt install nano

nano /etc/postgresql/pg_hba.conf

    host replication rep_user 192.168.0.0/16 md5

/usr/lib/postgresql/15/bin/pg_ctl reload

run sql with supabase_admin (super user)

CREATE USER rep_user REPLICATION LOGIN PASSWORD 'P@ssword321';

select pg_create_physical_replication_slot('stolon_a499f61a');

-- select pg_drop_replication_slot('stolon_a499f61a'); -- when need delete, stop test

select * FROM pg_replication_slots;

Change version postgresql to 15 (in 192.168.43.164)

https://askubuntu.com/questions/1456014/how-to-upgrade-postgresql-from-14-to-15-on-ubuntu-22-04

and Things still don't get better

7. While Supabase utilizes PostgreSQL under the hood, Supabase itself doesn't directly support configuring Stolon for high availability. Stolon is typically used in self-hosted deployments where you have more control over the underlying infrastructure.

However, there are alternative approaches to achieve high availability for your PostgreSQL database in Supabase:

1. Supabase Edge Functions: Supabase offers Postgres extensions that can be deployed as Edge Functions. These functions can be used for logic like automatic failover mechanisms for read replicas.

2. External High Availability Solutions: You can explore deploying Stolon alongside your Supabase instance on a platform like Kubernetes. This would involve managing both Supabase and Stolon yourself.

8. NEXT

Stolon is typically used in self-hosted deployments where you have more control over the underlying infrastructure.

https://github.com/sorintlab/stolon/blob/master/doc/twonodes.md