Supabase
Support for Kubernetes Postgres Operators (CNPG) #31147
Replies: 2 comments 11 replies
-
|
I'm currently working through this. I have tinkered with it before, and I duplicated supabase's postgres Dockerfile, and added in the cnpg requirements. I got it working, but in the end didnt need it. So I'm going to try a simple Dockerfile that will build on the supabase/postgres docker image and install the barman packages (the minimum requirements for cnpg) and see how it goes. I have recently found this: I guess there are 3 ways of applying the migrations.
It would be amazing to have all the migrations built into a docker container that can run them against any postgres instance, as opposed to having them included in the supabase/postgres image. I'll see where I get to with just brute-forcing barman into the supabase/postgres image. If that works easily enough, then I think working on a community supported (or officially supported?) container that centralizes all the required bootstraping migrations in something. on a side note, there is a bug in supabase/realtime that prevents it from clustering correctly. |
Beta Was this translation helpful? Give feedback.
-
|
For my clarification, what defines the 'magic' that makes the supabase postgres container what it is. If I understand correctly, it is a combination of postgres extensions like wal2json and pgjwt to build a featureful generic postgres image, and then a series of initial migrations to create tables needed for other supabase components like auth and Realtime? A sidecar init container sounds like a great idea. Package up all the migrations with a migrate script into a separate supabase/postgres-init, and then anyone could attach that to their existing postgres container to get the needed migrations. Maybe even configure the init container if you only plan to use certain parts of the supabase ecosystem, like how the current Kubernetes chart lets you enable or disable each piece. I think the second part is how to handle all the postgres plugins themselves. I was starting with this article. where they recommend using the cnpg image at the base and installing more plugins on top. So, maybe we could use all the ansible automation from the supabase/postgres image as a way to package up supabase recommended plugins and treat it kind of like a 'standard library for postgres'. Then install on top of cnpg base. Then, that feels like it would generalize to other operators or custom images
|
Beta Was this translation helpful? Give feedback.
-
|
I agree that the magic of supabase has to be maintained! I will say that, in my experience, a "bring your own base image" isnt feasible. I think its easier to start with the supabase/postgres image, smash in barman/barman-cli/barman-cli-cloud, and figure out a way to make migrations work (sidecar, initcontainer, bootstrap from a standalone container... idk). |
Beta Was this translation helpful? Give feedback.
-
|
The following Dockerfile is bringing up a CNPG cluster for supabase. FROM supabase/postgres:15.8.1.017
# https://www.postgresql.org/download/linux/debian/
# Import the repository signing key:
RUN apt install curl ca-certificates
RUN install -d /usr/share/postgresql-common/pgdg
RUN curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
# Create the repository configuration file:
RUN sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# https://docs.pgbarman.org/release/3.12.1/user_guide/installation.html
# Install barman:
RUN apt update && apt install -y --no-install-recommends \
barman \
barman-cli \
barman-cli-cloud \
&& rm -rf /var/lib/apt/lists/*
# Change the uid of postgres to 26 - CNPG fails if this doesn't happen
RUN usermod -u 26 postgres
USER 26Make sure your image name meets the requirements: https://cloudnative-pg.io/documentation/1.24/container_images/
I'm now working on getting migrations to happen. I think I am going to build a "migrations" image with a bash script and the migrations from the various repos. The image can then run as a job and apply the migrations against the CNPG database. Only issue with this is dealing with future migrations. There is no way to check if existing migrations have been run, so it will blindly apply all the migrations (which is fine for a first run, but will probably throw loads of errors if re-run considering there are |
Beta Was this translation helpful? Give feedback.
-
|
Working on the migrations atm, but I'm getting tired. I'm using dbmates latest container here. # it seems like supabase uses dbmate?
# https://github.com/supabase/postgres/tree/develop/migrations
# https://github.com/amacneil/dbmate
FROM ghcr.io/amacneil/dbmate:2.24.2@sha256:6e245cce5580567747cf440d84a7507121793c81564772fdd75f8ee0f45bbb79
COPY ./db ./db
COPY ./migrate.sh ./migrate.sh
RUN chmod +x ./migrate.sh
ENTRYPOINT ["./migrate.sh"]the shell script #!/usr/bin/env sh
echo "--------------------"
echo "Beginning migrations"
dbmate --migrations-dir "./db" --wait up
echo " Complete"
echo "--------------------"I got to here: This is probably because of Thought I'd share some progress. Going to circle back to this in the next few days, or at least before the New Year! |
Beta Was this translation helpful? Give feedback.
-
|
Ok, so the migrations in the supabase/supabase repo use variable substitution to load env vars into the postgres db (JWT tokens mostly). The docker-compose file maps the migrations into a specific order that is not indicated by their filename. Which means those files need to be renamed to a dbmate compatible format, and organised in the correct order. Which leads onto the next problem... So, I think some manual code for these specific migrations is required. Update: looks like migrations are working. Havent tested against supabase services yet |
Beta Was this translation helpful? Give feedback.
-
|
Here is an all-in-one update of my progress so far: CNPG+Supabase Compatible Dockerfile FROM supabase/postgres:15.8.1.017
# Import the repository signing key:
RUN apt install curl ca-certificates
RUN install -d /usr/share/postgresql-common/pgdg
RUN curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
# Create the repository configuration file:
RUN sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Update the package lists:
RUN apt update && apt install -y --no-install-recommends \
barman \
barman-cli \
barman-cli-cloud \
&& rm -rf /var/lib/apt/lists/*
# Change the uid of postgres to 26
RUN usermod -u 26 postgres
USER 26Make sure you tag the image according to CNPG requirements https://cloudnative-pg.io/documentation/1.24/container_images/ Example cluster manifest: apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: testing
spec:
instances: 3
imageName: <<<<IMAGE NAME>>>>
imagePullPolicy: Always
storage:
size: 5Gi
enableSuperuserAccess: true
postgresql:
pg_hba:
# ripped from supabase/posgres/ansible/files/postgresql_config/pg_hba.conf
- local all supabase_admin scram-sha-256
- local all all peer map=supabase_map
- host all all 127.0.0.1/32 trust
- host all all ::1/128 trust
- host all all 10.0.0.0/8 scram-sha-256
- host all all 172.16.0.0/12 scram-sha-256
- host all all 192.168.0.0/16 scram-sha-256
- host all all 0.0.0.0/0 scram-sha-256
- host all all ::0/0 scram-sha-256
pg_ident:
# ripped from supabase/posgres/ansible/files/postgresql_config/pg_ident.conf
- supabase_map postgres postgres
- supabase_map gotrue supabase_auth_admin
- supabase_map postgrest authenticator
- supabase_map adminapi postgres
# ripped from supabase/posgres/ansible/files/postgresql_config/postgresql.conf
shared_preload_libraries:
[
pg_stat_statements,
pg_stat_monitor,
pgaudit,
plpgsql,
plpgsql_check,
pg_cron,
pg_net,
timescaledb,
auto_explain,
pg_tle,
supautils,
# pgsodium # pgsodium crashes cnpg db pods
]
bootstrap:
initdb:
database: supabase
# I think this has to be supabase_admin (due to migration hard coded strings), however I will investigate further
owner: supabase_adminMigrations: NOTE: comment out the contents of Dockerfile: # it seems like supabase uses dbmate?
# https://github.com/supabase/postgres/tree/develop/migrations
# https://github.com/amacneil/dbmate
FROM ghcr.io/amacneil/dbmate:2.24.2@sha256:6e245cce5580567747cf440d84a7507121793c81564772fdd75f8ee0f45bbb79
COPY ./db ./db
COPY ./migrate.sh ./migrate.sh
RUN chmod +x ./migrate.sh
# Install
# gettext for the envsubst bin
# moreutils for the sponge bin (allows reading/writing from the same file for in-situ envsubst)
RUN apk add --no-cache gettext moreutils
ENTRYPOINT ["./migrate.sh"]migrate.sh: #!/usr/bin/env sh
echo "--------------------"
echo "Beginning migrations"
# in-situ env_var substitution
echo "Variable Substitution"
for migration in ./db/final/*.sql; do
envsubst '$POSTGRES_USER, $POSTGRES_PASSWORD, $POSTGRES_DB, $JWT_SECRET, $JWT_EXP' < $migration | sponge $migration
done
# build from parts, as the CNPG superuser targets "*" database.
# Superuser credentials, and the App DB
db_uri="postgresql://${SUPERUSER_USER}:${SUPERUSER_PASSWORD}@${SUPERUSER_HOST}:${SUPERUSER_PORT}/${POSTGRES_DB}"
echo "Running base migrations"
# MIGRATION_SCHEMA as first search_path so the dbmate migrations table gets created in a seperate schema (not polluting the public schema, and not interfering with Realtime's schema_migrations table)
dbmate_uri="${db_uri}?search_path=${MIGRATION_SCHEMA},public"
# https://github.com/amacneil/dbmate
dbmate --url $dbmate_uri --migrations-dir "./db/base" --wait --no-dump-schema up
echo "Running final migrations"
echo "_supabase.sql"
psql -d $db_uri -f "./db/final/_supabase.sql"
echo "webhooks.sql"
psql -d $db_uri -f "./db/final/webhooks.sql"
echo "jwt.sql"
psql -d $db_uri -f "./db/final/jwt.sql"
echo "logs.sql"
psql -d $db_uri -f "./db/final/logs.sql"
echo "realtime.sql"
psql -d $db_uri -f "./db/final/realtime.sql"
echo "poolers.sql"
psql -d $db_uri -f "./db/final/poolers.sql"
echo "roles.sql"
psql -d $db_uri -f "./db/final/roles.sql"
echo " Complete"
echo "--------------------"Job example manifest: apiVersion: batch/v1
kind: Job
metadata:
name: supabase-migration
spec:
template:
spec:
containers:
- name: supabase-migration
image: <<<<CONTAINER NAME>>>>
env:
- name: SUPERUSER_HOST
valueFrom:
secretKeyRef:
name: testing-superuser
key: host
# Use the Superuser CNPG secret for migration connection
- name: SUPERUSER_PORT
valueFrom:
secretKeyRef:
name: testing-superuser
key: port
# Use the Superuser CNPG secret for migration connection
- name: SUPERUSER_USER
valueFrom:
secretKeyRef:
name: testing-superuser
key: user
# Use the Superuser CNPG secret for migration connection
- name: SUPERUSER_PASSWORD
valueFrom:
secretKeyRef:
name: testing-superuser
key: password
# Schema where dbmate will store base migration details, so it doesnt pollute the `public` schema
- name: MIGRATION_SCHEMA
value: dbmate
# use the App secret, for variable substitution in supabase migrations
- name: POSTGRES_PASSWORD
valueFrom:
secretKeyRef:
name: testing-app
key: password
# use the App secret, for variable substitution in supabase migrations
- name: POSTGRES_USER
valueFrom:
secretKeyRef:
name: testing-app
key: user
# use the App secret, for variable substitution in supabase migrations
- name: POSTGRES_DB
valueFrom:
secretKeyRef:
name: testing-app
key: dbname
# for variable substitution in supabase migrations
- name: JWT_SECRET
valueFrom:
secretKeyRef:
name: supabase-jwt
key: secret
# for variable substitution in supabase migrations
- name: JWT_EXP
valueFrom:
secretKeyRef:
name: supabase-jwt
key: expiry
restartPolicy: Never
backoffLimit: 4the secrets Notes on migration: Any new migrations in supabase/postgres can be loaded into the Probably worth adding some sort of flag or check to disable the And finally: 24/12/27 UPDATEOk, I had some issues with migrations, schemas, connection strings etc. Changes:
I will work on getting the other services up. 04/01/25. |
Beta Was this translation helpful? Give feedback.
-
|
How did you get the individual services working? I worked on updating the now abandoned I'm raring to go so if you have any repo I can test, I've got some time to work on this starting today :-). |
Beta Was this translation helpful? Give feedback.
-
|
What services are you struggling with? I've just been writing the manifests for supabase services according to their docker-compose. The only real work I've had to do is with CNPG, which I've documented above (and will eventually repo). And with Realtime due to its environment setup nuking any chance of getting it to cluster properly (which I've documented in the linked issue, and I'm going to PR to get fixed). The supabase helm charts are a useful reference for stuff, and the docker-compose has the updated image tags and env vars. |
Beta Was this translation helpful? Give feedback.
-
|
I'm not struggling with the services, I'm struggling with getting a reasonably robust If you can confirm I should be getting a properly working db-side setup with the above elements, I'll give it a whirl (I haven't even tried a hello-world cnpg yet though, so your excerpts will no doubt make more sense once I've done that!) |
Beta Was this translation helpful? Give feedback.
-
|
With what I've documented above, I am packaging barman into the supabase/postgres-15 image. And CNPG is happy to launch a 3 replica cluster with the image and the above manifest. The only postgres config that differs from supabase (that I can find, I am NOT a database engineer) is that I guess the other concern would be that CNPG tweaks some wal/replication settings that mess with large scale realtime stuff. I haven't noticed any issues in the single-user testing I have done. I have tested it with meta/rest/realtime through studio. I'm working on storage/imgproxy. It's very much a WIP, but I don't think you are wasting your time using the above as a starting point that will get you 90% of the way there (maybe further) Update: |
Beta Was this translation helpful? Give feedback.
-
|
@Towerful Thanks for the detailed description. I tried following along but im getting an error when deploying the cluster. I think its related to this I tried removing the file or placing an empty file but that causes an error did you experience something similar? |
Beta Was this translation helpful? Give feedback.
-
I have been running my Postgres database on Kubernetes with 'Cloud-Native Postgres Operator' https://cloudnative-pg.io/ with great success on the base postgres image. I'd like to try self hosting some subset of Supabase on Kubernetes, while still using the CNPG operator to manage my database, but haven't been able to figure it out.
Would be great to have some option on how to merge the supabase postgres image with Kubernetes operators like CNPG or Zalando, so a self-hosted Supabase, or really any Kubernetes Postgres implementattion, can take advantage of both the high availability management goodies of these operators, and also the baseline extensions and other functionality of this image.
Beta Was this translation helpful? Give feedback.
All reactions