Try Supavisor:
https://www.youtube.com/watch?v=JvBT4XBdoUE
https://www.youtube.com/watch?v=ogYNmJOFEpk
1. clone https://github.com/supabase/supavisor (only need folder "./dev")
2. mix file:
docker-compose.yml
version: "3"
services:
db:
image: supabase/postgres:15.1.0.148
container_name: supavisor-db
ports:
- "6432:5432"
volumes:
- ./dev/postgres:/docker-entrypoint-initdb.d/
# Uncomment to set MD5 authentication method on unitialized databases
# - ./dev/postgres/md5/etc/postgresql/pg_hba.conf:/etc/postgresql/pg_hba.conf
command: postgres -c config_file=/etc/postgresql/postgresql.conf
environment:
POSTGRES_HOST: /var/run/postgresql
POSTGRES_PASSWORD: postgres
# Uncomment to set MD5 authentication method on unitialized databases
# POSTGRES_INITDB_ARGS: --auth-host=md5
supavisor:
# build: .
image: supabase/supavisor:1.1.67
container_name: supavisor
ports:
- 4000:4000
- 5452:5452
- 6543:6543
environment:
PORT: 4000
PROXY_PORT_SESSION: 5452
PROXY_PORT_TRANSACTION: 6543
DATABASE_URL: "ecto://postgres:postgres@db:5432/postgres"
CLUSTER_POSTGRES: "true"
SECRET_KEY_BASE: "1234567890123456789012123456789012345678901234567890321234567
8901234567890123456789032123456789012345678901234567890323456789032"
VAULT_ENC_KEY: "12345678901234567890123456789032"
API_JWT_SECRET: "dev"
METRICS_JWT_SECRET: "dev"
REGION: "local"
ERL_AFLAGS: -proto_dist inet_tcp
command: sh -c "/app/bin/migrate && /app/bin/server"
depends_on:
- db
3. Runs twice, because postgresql initializes some data
docker compose up -d
docker compose up -d
Let's try:
depends_on:
db:
condition: service_healthy
4. https://supabase.github.io/supavisor/development/setup/
JWT secret key: dev (read docker-compose.yml)
JWT payload:
{
"iss": "supabase",
"role": "anon",
"iat": 1645192824,
"exp": 1960768824
}
Curl 1: add tenant (connect from docker to host: use ip 172.17.0.1)
curl --location --request PUT 'http://192.168.43.164:4000/api/tenants/dev_tenant' \
--header 'Accept: */*' \
--header 'Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.
eyJpc3MiOiJzdXBhYmFzZSIsInJvbGUiOiJhbm9uIiwiaWF0IjoxNjQ1MTkyODI0LCJleHAiOjE5NjA3Njg4MjR9
.M9jrxyvPLkUxWgOYSf5dNdJ8v_eRrq810ShFRT8N-6M' \
--header 'Content-Type: application/json' \
--data '{
"tenant": {
"db_host": "172.17.0.1",
"db_port": 6432,
"db_database": "postgres",
"ip_version": "auto",
"enforce_ssl": false,
"require_user": false,
"auth_query": "SELECT rolname, rolpassword FROM pg_authid WHERE rolname=$1;",
"users": [
{
"db_user": "postgres",
"db_password": "postgres",
"pool_size": 20,
"mode_type": "transaction",
"is_manager": true
}
]
}
}'
5. Psql test:
By default, Supavisor uses port 6543 for transaction mode and 5452 for session mode:
"C:\Users\ADMIN\AppData\Local\Programs\pgAdmin 4\runtime\psql.exe" -h 192.168.43.164 -p 6543 -U postgres.dev_tenant -d postgres
(password: postgres)
Working!!!
"C:\Users\ADMIN\AppData\Local\Programs\pgAdmin 4\runtime\psql.exe" -h 192.168.43.164 -p 6543 -U spam -d postgres
=> psql: error: connection to server at "192.168.43.164", port 6543 failed: FATAL: Tenant or user not found
6. supavisor is connection pooler
With docker image: supabase/postgres:15.1.0.147. Create container => supabase-db 1, 2, 3
Connect to supbase-db 1,2,3 with supavisor
Config supase-auth,kong,... (disable analytics, vector) with dbuser postfix ".dev_tenant"
#.env
POSTGRES_HOST=172.17.0.1
POSTGRES_DB=postgres
POSTGRES_PORT=6543
USER_SUPABASE_ADMIN=supabase_admin.dev_tenant
USER_AUTHENTICATOR=authenticator.dev_tenant
USER_SUPABASE_STORAGE_ADMIN=supabase_storage_admin.dev_tenant
USER_SUPABASE_AUTH_ADMIN=supabase_auth_admin.dev_tenant
#docker-compose.yml example change
name: supabase
services:
...
realtime:
container_name: realtime-dev.supabase-realtime
image: supabase/realtime:v2.25.50
healthcheck:
test:
[
"CMD",
"bash",
"-c",
"printf \\0 > /dev/tcp/localhost/4000"
]
timeout: 5s
interval: 5s
retries: 3
restart: unless-stopped
environment:
PORT: 4000
DB_HOST: ${POSTGRES_HOST}
DB_PORT: ${POSTGRES_PORT}
DB_USER: ${USER_SUPABASE_ADMIN}
DB_PASSWORD: ${POSTGRES_PASSWORD}
DB_NAME: ${POSTGRES_DB}
DB_AFTER_CONNECT_QUERY: 'SET search_path TO _realtime'
DB_ENC_KEY: supabaserealtime
API_JWT_SECRET: ${JWT_SECRET}
...
7. Next
Try config replicate PostgreSQL for container supabase-db-1,2,3 in image supabase/postgres
Maybe help:
https://gist.github.com/vadv/e55fca418d6a14da71f01a95da493fae
https://github.com/marcel-dempers/docker-development-youtube-series/blob/master/storage/databases/postgresql/3-replication/README.md (https://www.youtube.com/watch?v=FC2JMBYDcJE)
My train:
Edit container supabase-db (192.168.43.163:5432)
docker exec -u postgres -it supabase-db /bin/bash
apt update
apt install nano
nano /etc/postgresql/pg_hba.conf
-> add this
host replication postgres 0.0.0.0/0 md5
/usr/lib/postgresql/15/bin/pg_ctl reload
exit
docker commit supabase-db supabase/postgres:15.1.0.147.mix.1
# new image supabase/postgres:15.1.0.147.mix.1 for replication username: postgres
Create postgresql with 192.168.43.164:5001
#docker-compose.pg_basebackup.yml
name: supabase
services:
cli3:
stdin_open: true
tty: true
container_name: supabase-cli-3
image: postgres:15.0
volumes:
- ./volumes/db3/data:/data:Z
entrypoint:
- /bin/bash
docker compose -f docker-compose.pg_basebackup.yml up -d
docker exec -it supabase-cli3 /bin/bash
pg_basebackup -h 192.168.43.163 -p 5432 -U postgres -D /data/ -Fp -Xs -R
# Edit file: ./volumes/db3/data/postgresql.auto.conf
primary_conninfo = 'user=postgres password='your-super-secret-and-long-postgres-password' host=192.168.43.163 port=5432'
# docker-compose.rep.yml
name: supabase
services:
rep3:
stdin_open: true
tty: true
container_name: supabase-rep-3
environment:
- POSTGRES_USER=postgres
# password is of "primary" <- 'your-super-secret-and-long-postgres-password'
- POSTGRES_PASSWORD=admin123
- POSTGRES_DB=postgres
- PGDATA=/data
volumes:
- ./volumes/db3/data:/data:Z
- ./volumes/db3/config:/config:Z
- ./volumes/db3/archive:/mnt/server/archive:Z
ports:
- 5001:5432
image: postgres:15.0
command: -c 'config_file=/config/postgresql.conf'
docker compose -f docker-compose.rep.yml up -d
Test:
"C:\Users\ADMIN\AppData\Local\Programs\pgAdmin 4\runtime\psql.exe"
-h 192.168.43.163 -p 5001 -U postgres -d postgres
# password: your-super-secret-and-long-postgres-password
# edit in supabase (192.168.43.163:5432) and check in 192.168.43.154:5001
8. Wait supavisor can be load balanced across read-replicas...
2024/07/10
- Load balancing
- Queries can be load balanced across read-replicas
- Load balancing is independent of Postgres high-availability management (see below)
Now with pgcat, can read replica:
I config pgcat as connection pooler
+ read primary and replica
+ good metric http://0.0.0.0:9930/metrics
+ good with pgbench
Not use stolon for replica, use native tool "pg_basebackup"
Hope enjoy with supavisor in future!!!
NOTE 2024/07/11 pgcat:
+ Service realtime, auth need direct connect to supabase-db (5432) !
+ With pgcat, some api supabase error when call replica not read operator !!!
+ :(
NOTE 2024/07/11 supavisor, sometimes it works, sometimes it fails
+ error: prepared statement "0" already exist
+ error: An invalid response was received from the upstream server
-> my fix: Service realtime, auth, function=> direct connect to supabase-db (5432)
mix service "rest" with environment: PGRST_DB_URI
postgres://authenticator.dev_tenant:${POSTGRES_PASSWORD}@${POSTGRES_HOST}:6543,authenticator:${POSTGRES_PASSWORD}@${POSTGRES_HOST}:5432/${POSTGRES_DB}
doc from https://github.com/PostgREST/postgrest/issues/3414
# having only one pg listening on 5432 this will succeed, since libpq will try the second host
psql postgresql://user:pass@localhost:5433,localhost:5432/postgres
# this will now fail since our pg is read-write and we tell libpq to only use a read-only session
psql postgresql://user:pass@localhost:5433,localhost:5432/postgres?target_session_attrs=read-only
BUG:
catch change data with service_role_key
// need to run the code over and over again (sql query)
grant USAGE on schema btth to service_role;
grant all on all tables in schema btth to service_role;
I known cacth change design to authenticated BUT ......
Comments
Post a Comment