From aeb19df4222269c585de55be5568326222df879d Mon Sep 17 00:00:00 2001 From: grm Date: Sun, 13 Sep 2026 23:52:41 +0300 Subject: Give every blog its own Postgres database A blog is now a database of its own (blog_) on the same server: one pg_dump is a complete backup of a blog, one psql restores it, and nothing a blog's queries do can reach another blog's rows. The control database (DATABASE_URL) keeps only users and the blog registry (id, owner, subdomain, db_name); title, tagline and theme move into a one-row settings table next to the content so the dump really is everything. db.Cluster holds the control pool plus small, lazily opened per-blog pools. store.Store (control) hands out a store.BlogStore per blog; every blog_id parameter and column is gone, the database is the scope. Handlers reach it through blogStore(r), which resolveBlog puts in the context next to the blog. Existing data is moved in place by control migration 00006, a Go migration that runs inside the control transaction: it creates and migrates each blog database, copies the rows preserving ids, and marks the registry; 00007 then drops the old tables. Either every blog is moved or the control database is untouched. /media/{id} now serves the host's blog only, so dashboard previews on the root domain use /b/{sub}/media/{id}. Subdomains are capped at 58 chars so "blog_" + name fits a Postgres identifier. Deleting a user drops their database. Store.Open resets a blog's pool and retries once so a database restored under a running app (dropdb --force, createdb, psql) just works. Co-Authored-By: Claude Opus 5 Claude-Session: https://claude.ai/code/session_01Sd8UPWrvyYCLj97JexNw3A --- cmd/blogspace/main.go | 31 +++++++++++++++++++++++++------ cmd/blogspace/seed.go | 30 ++++++++++++++++++------------ 2 files changed, 43 insertions(+), 18 deletions(-) (limited to 'cmd') diff --git a/cmd/blogspace/main.go b/cmd/blogspace/main.go index cf240a8..20e6ffe 100644 --- a/cmd/blogspace/main.go +++ b/cmd/blogspace/main.go @@ -45,15 +45,18 @@ func run(ctx context.Context, cfg *config.Config, cmd string) error { if err := waitForDB(ctx, cfg.DatabaseURL); err != nil { return err } - if err := db.Migrate(ctx, cfg.DatabaseURL); err != nil { + if err := db.MigrateControl(ctx, cfg.DatabaseURL); err != nil { return fmt.Errorf("migrate: %w", err) } - pool, err := db.Open(ctx, cfg.DatabaseURL) + cluster, err := db.Open(ctx, cfg.DatabaseURL) if err != nil { return err } - defer pool.Close() - st := store.New(pool) + defer cluster.Close() + st := store.New(cluster) + if err := migrateBlogs(ctx, cluster, st); err != nil { + return err + } switch cmd { case "migrate": @@ -74,13 +77,29 @@ func run(ctx context.Context, cfg *config.Config, cmd string) error { } } +// migrateBlogs brings every registered blog database up to the current +// schema. A blog whose database is missing (a restore gone wrong) is logged +// and skipped so the other blogs still come up; its requests fail until fixed. +func migrateBlogs(ctx context.Context, cluster *db.Cluster, st *store.Store) error { + blogs, err := st.ListBlogs(ctx) + if err != nil { + return err + } + for _, b := range blogs { + if err := db.MigrateBlog(ctx, cluster.BlogURL(b.DBName)); err != nil { + log.Printf("blog %q: database %s: %v", b.Subdomain, b.DBName, err) + } + } + return nil +} + // waitForDB retries the connection so `docker compose up` ordering doesn't matter. func waitForDB(ctx context.Context, url string) error { deadline := time.Now().Add(60 * time.Second) for { - pool, err := db.Open(ctx, url) + c, err := db.Open(ctx, url) if err == nil { - pool.Close() + c.Close() return nil } if time.Now().After(deadline) { diff --git a/cmd/blogspace/seed.go b/cmd/blogspace/seed.go index 7862363..a476486 100644 --- a/cmd/blogspace/seed.go +++ b/cmd/blogspace/seed.go @@ -41,10 +41,14 @@ func seed(ctx context.Context, cfg *config.Config, st *store.Store) error { if err != nil { return err } - if err := st.UpdateBlogSettings(ctx, blog.ID, blog.Title, "Notes on gardening, code and everything in between"); err != nil { + bs, err := st.Open(ctx, blog) + if err != nil { + return err + } + if err := bs.UpdateSettings(ctx, blog.Title, "Notes on gardening, code and everything in between"); err != nil { return err } - home, err := st.HomePage(ctx, blog.ID) + home, err := bs.HomePage(ctx) if err != nil { return err } @@ -54,9 +58,8 @@ func seed(ctx context.Context, cfg *config.Config, st *store.Store) error { {Slug: "news", Title: "News", IntroMD: "Short updates, newest first.", ShowInNav: true}, {Slug: "contact", Title: "Contact", IntroMD: "Email: alice@example.com\n\nOr find me at the Saturday market, stall 12.", ShowInNav: true}, } { - p.BlogID = blog.ID p.IntroHTML = markdown.Render(p.IntroMD) - created, err := st.CreatePage(ctx, &p) + created, err := bs.CreatePage(ctx, &p) if err != nil { return err } @@ -74,14 +77,14 @@ func seed(ctx context.Context, cfg *config.Config, st *store.Store) error { if err := png.Encode(&buf, img); err != nil { return err } - bg, err := st.CreateImage(ctx, blog.ID, "gradient.png", "image/png", buf.Bytes()) + bg, err := bs.CreateImage(ctx, "gradient.png", "image/png", buf.Bytes()) if err != nil { return err } theme := web.DefaultTheme() theme.BgImage = bg.ID.String() theme.BgMode = "tile" - if err := st.UpdateBlogTheme(ctx, blog.ID, theme.JSON()); err != nil { + if err := bs.UpdateTheme(ctx, theme.JSON()); err != nil { return err } // a footer line and a right column, so the layout options have something to show @@ -90,8 +93,7 @@ func seed(ctx context.Context, cfg *config.Config, st *store.Store) error { {Area: "right", Kind: "recent", Title: "Recent posts", Count: 5}, {Area: "right", Kind: "archive", Title: "Archive"}, } { - m.BlogID = blog.ID - if _, err := st.CreateModule(ctx, &m); err != nil { + if _, err := bs.CreateModule(ctx, &m); err != nil { return err } } @@ -100,7 +102,7 @@ func seed(ctx context.Context, cfg *config.Config, st *store.Store) error { for i := 0; i < 10; i++ { body := fmt.Sprintf("Filler post number %d, here to demonstrate pagination.", i+1) post := &store.Post{PageID: home.ID, Title: fmt.Sprintf("Filler post %d", i+1), Slug: fmt.Sprintf("filler-%d", i+1), BodyMD: body, BodyHTML: markdown.Render(body), Published: true} - if _, err := st.CreatePost(ctx, post); err != nil { + if _, err := bs.CreatePost(ctx, post); err != nil { return err } } @@ -112,7 +114,7 @@ func seed(ctx context.Context, cfg *config.Config, st *store.Store) error { } for _, p := range posts { post := &store.Post{PageID: pages[p.page].ID, Title: p.title, Slug: slug.Make(p.title), BodyMD: p.body, BodyHTML: markdown.Render(p.body), Published: true} - if _, err := st.CreatePost(ctx, post); err != nil { + if _, err := bs.CreatePost(ctx, post); err != nil { return err } } @@ -121,12 +123,16 @@ func seed(ctx context.Context, cfg *config.Config, st *store.Store) error { if err != nil { return err } - rootHome, err := st.HomePage(ctx, root.ID) + rs, err := st.Open(ctx, root) + if err != nil { + return err + } + rootHome, err := rs.HomePage(ctx) if err != nil { return err } welcome := "This is the blog on the **root domain**, owned by the superadmin. It works like every other blog.\n\nOther blogs live on subdomains, for example [alice](" + cfg.BlogURL("alice") + ")." - if _, err := st.CreatePost(ctx, &store.Post{PageID: rootHome.ID, Title: "Welcome to Blogspace", Slug: "welcome", BodyMD: welcome, BodyHTML: markdown.Render(welcome), Published: true}); err != nil { + if _, err := rs.CreatePost(ctx, &store.Post{PageID: rootHome.ID, Title: "Welcome to Blogspace", Slug: "welcome", BodyMD: welcome, BodyHTML: markdown.Render(welcome), Published: true}); err != nil { return err } log.Printf("seed: superadmin %q (password %q in dev), blogger alice / alicealice at %s", cfg.SuperadminUsername, "admin", cfg.BlogURL("alice")) -- cgit v1.2.3