SUPABASE SELF HOSTING

https://supabase.com/docs/guides/self-hosting/docker

<aside> 💡

After cloning, make sure about changing the all passwords in .env file.

</aside>

# If you already initialized:
docker compose down -v
rm -rf volumes/db/data/ # Be careful! This will remove the database 
docker compose up -d

Install and Open **DBeaver:**

image.png

With Docker

# connect to database with docker
docker exec -it supabase-db psql -U postgres -d postgres

# Inside psql, you can execute commands.

# Show roles
\\pset pager off
\\du+

Get Backup

sudo apt install postgresql-client postgresql
pg_dump -h 10.69.3.10 -U postgres -p 5432 -d postgres -n hefesto -F c -f hefesto.backup
## Then enter the given password

Load Backup

# Inside #root, run:
docker cp hefesto.backup supabase-db:/tmp/hefesto.backup
# Enter to container
docker exec -it supabase-db bash
psql -U postgres -d postgres -f /tmp/hefesto.backup
# or if it's a custom format backup run
pg_restore -U postgres -d postgres -n hefesto /tmp/hefesto.backup
# if it gives version error, check:
pg_restore --version

# if it doesn't work because of version issues, exit docker bash and, use on pc:
pg_restore -h 127.0.0.1 -p 5432 -U postgres.hefesto -d postgres -n hefesto hefesto.backup

Create a user with read-access

-- Create a new role for the client application with only read access to the hefesto schema
CREATE ROLE client_user WITH LOGIN PASSWORD 'hefesto';

GRANT USAGE ON SCHEMA hefesto TO client_user;
GRANT SELECT ON ALL TABLES IN SCHEMA hefesto TO client_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA hefesto TO client_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA hefesto GRANT SELECT ON TABLES TO client_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA hefesto GRANT SELECT ON SEQUENCES TO client_user;

See all users

select rolname from pg_roles;

Doing a Checkup

  1. Check PostgreSQL running on that machine

    SSH or log in to the machine with IP 10.69.3.109 and run:

    sudo systemctl status postgresql

    or

    ps aux | grep postgres

  2. Check PostgreSQL listening on the right IP and port

    Check in postgresql.conf (usually in /etc/postgresql/<version>/main/ or /var/lib/pgsql/data/):