Database setup

local

After you have created the Postgres instance, set up the database, role, schema, and extensions.

Log into Postgres as a superuser:

pgcli -u postgres -d postgres

Then run:

\c postgres
CREATE DATABASE cooker WITH OWNER postgres;
CREATE ROLE app_user WITH LOGIN PASSWORD '123'; -- use SCRAM in prod

-- The following needs to be executed while connected to the cooker database.
\c cooker
-- Revoke the ability for all users (PUBLIC) to create anything in the public schema.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Create new schemas.
CREATE SCHEMA app AUTHORIZATION app_user;

-- Create extensions in the "public" schema.
CREATE EXTENSION IF NOT EXISTS "pg_uuidv7" WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS "btree_gist" WITH SCHEMA public; -- req. app.booking
-- CREATE EXTENSION IF NOT EXISTS "btree_gin" WITH SCHEMA public;
-- CREATE EXTENSION IF NOT EXISTS "pg_trgm" WITH SCHEMA public;
-- CREATE EXTENSION IF NOT EXISTS "vector" WITH SCHEMA public;

production

We use NeonDB for production.

Extensions are not created by migrations — the migration role is not a superuser on Neon — so each one has to be enabled on the database before a migration that depends on it is deployed. btree_gist is on Neon’s supported list; run it once, ahead of the booking migration:

CREATE EXTENSION IF NOT EXISTS "btree_gist";

The same applies to every database that already exists, local ones included: just localsetup only starts Postgres, so the block above helps fresh setups only and an existing cooker database needs the statement run by hand before goose up.