kosmos/chef
8
3
Fork 0
Code Issues 37 Pull Requests Projects 2 Activity

Migrate PostgreSQL cluster to PG14 #625

Merged
raucao merged 19 commits from feature/postgresql_migration into master 2026-04-12 14:16:22 +00:00
Conversation 4 Commits 19 Files Changed 46
+964 -300
raucao commented 2026-04-10 04:31:21 +00:00
Owner
Copy Link
Copy Source

Adds a bunch of management scripts and other changes to aid with cluster migration by replication.

Since physical replication is not supported between major versions, we set up a new standalone primary/cluster, import a dump from the current cluster, and then use logical replication to keep data in sync until the switchover.

See doc/postgres/migration.md for the full migration runbook and usage of scripts.

closes #504

Databases

  • akkounts
  • akkounts_queue
  • mastodon
  • gitea
  • drone
  • nbxplorer
  • btcpayserver
  • lndhub
  • ejabberd
  • ejabberd_5apps
Adds a bunch of management scripts and other changes to aid with cluster migration by replication. Since physical replication is not supported between major versions, we set up a new standalone primary/cluster, import a dump from the current cluster, and then use logical replication to keep data in sync until the switchover. See `doc/postgres/migration.md` for the full migration runbook and usage of scripts. closes #504 ## Databases * [x] akkounts * [x] akkounts_queue * [x] mastodon * [x] gitea * [x] drone * [x] nbxplorer * [x] btcpayserver * [x] lndhub * [x] ejabberd * [x] ejabberd_5apps
raucao added the kredits-3ops labels 2026-04-10 04:31:21 +00:00
raucao added 3 commits 2026-04-10 04:31:22 +00:00
Refactor postgres server recipes/resource 290af8177a
Upgrade WAL config for PG14 6583cd7010
WIP Prepare postgres for migration by replication bc3f291bd2
raucao self-assigned this 2026-04-10 04:31:28 +00:00
greg was assigned by raucao 2026-04-10 04:31:28 +00:00
raucao added 1 commit 2026-04-10 04:37:02 +00:00
Move doc 09412f69e8
raucao added 1 commit 2026-04-10 04:38:22 +00:00
Drone: Make database details configurable 0020677ab2
raucao added 1 commit 2026-04-10 08:49:45 +00:00
Update PG migration runbook 8e11df4544
raucao added 1 commit 2026-04-10 08:58:19 +00:00
Ignore default db for migration/management fddcd4899e
raucao marked the pull request as work in progress 2026-04-10 13:43:33 +00:00
fsmanuel reviewed 2026-04-11 09:28:25 +00:00
site-cookbooks/kosmos_postgresql/files/dump_all_databases.sh Outdated
@@ -0,0 +1,9 @@
#!/bin/bash
cd /tmp && \
(pg_dumpall --globals-only > globals.sql) && \
psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN (''template0'',''postgres'')" | \
fsmanuel commented 2026-04-11 09:28:25 +00:00
Member
Copy Link
Copy Source

Why is it template0 instead of template1 like in all the other places?

Why is it `template0` instead of `template1` like in all the other places?
raucao commented 2026-04-11 10:44:25 +00:00
Author
Owner
Copy Link
Copy Source

Ohai @fsmanuel, (very) long time no see!

That's a good catch, it should be template1, because template0 is automatically excluded by the preceding condition (it doesn't allow connections).

Thanks! 🙏

Ohai @fsmanuel, (very) long time no see! That's a good catch, it should be `template1`, because `template0` is automatically excluded by the preceding condition (it doesn't allow connections). Thanks! 🙏
👍 1
raucao added 8 commits 2026-04-11 11:24:19 +00:00
Improve postgres management scripts a89db454d0
Fix method undefined in heredoc c92f9157a5
Improve RAM usage allowance db9177c9c6 
Queries can spawn more processed and then use more RAM than
`shared_buffers`
Update postgres roles 64d5d34d85 
Reset to normal/final
Update doc 9de37cde96
Fix akkounts systemd unit stop command 061880536b 
Typo (not using pumactl), but we don't need to specify it to do the
right thing anyway. systemd can just send sigterm on its own.
Add new postgres cluster, remove old one d5e3d62522
Fix Java/Homebrew cookbook resource failing with recent Chef ac4fb0c9ca 
Required by our Mastodon cookbook
raucao marked the pull request as ready for review 2026-04-11 11:24:37 +00:00
raucao requested review from fsmanuel 2026-04-11 11:24:43 +00:00
raucao commented 2026-04-11 11:25:49 +00:00
Author
Owner
Copy Link
Copy Source

Added more changes and fixes, as well as the new cluster node configs. I think we're ready to merge...

Added more changes and fixes, as well as the new cluster node configs. I think we're ready to merge...
raucao added 1 commit 2026-04-11 11:35:07 +00:00
Update vendored cookbooks f0314e0b99
greg reviewed 2026-04-12 11:40:39 +00:00
site-cookbooks/kosmos_postgresql/recipes/replica_logical.rb Outdated
@@ -0,0 +7,4 @@
role "replica_logical"
end
# primary = postgresql_primary
greg commented 2026-04-12 11:40:39 +00:00
Owner
Copy Link
Copy Source

Were these lines left here intentionally?

Were these lines left here intentionally?
raucao commented 2026-04-12 12:12:18 +00:00
Author
Owner
Copy Link
Copy Source

Not really, just didn't care I guess. I removed them.

Not really, just didn't care I guess. I removed them.
raucao added 2 commits 2026-04-12 12:11:50 +00:00
Fix default apt keyring dir not existing on older Ubuntu a3be57afbc 
Recent Chef client versions use it
Remove commented lines 1f7a1d0909
greg approved these changes 2026-04-12 13:54:10 +00:00
greg left a comment
Owner
Copy Link
Copy Source

👍

👍
raucao added 1 commit 2026-04-12 14:15:27 +00:00
Merge branch 'master' into feature/postgresql_migration b24a6107d2
raucao merged commit be0d7105d3 into master 2026-04-12 14:16:22 +00:00
raucao deleted branch feature/postgresql_migration 2026-04-12 14:16:23 +00:00
Sign in to join this conversation.
Reviewers
No Reviewers
fsmanuel
greg
Labels
Clear labels
service
discourse
Kosmos Community Forums
monitoring
Infrastructure metrics, alerts, notifications, etc.
service
accounts
Kosmos Accounts
service
drone-ci
Kosmos Drone CI
service
email
mail.kosmos.org
service
garage
S3-compatible object storage
service
gitea
Kosmos Gitea
service
ipfs
Kosmos IPFS
service
mastodon
kosmos.social
service
nostr
Relays, Blossom server, etc.
service
postgres
Database cluster
service
remotestorage
Portable data storage for the Web
service
wiki
Kosmos Wiki
service
xmpp
Kosmos Chat
bug
Something is not working
 design
Graphic/visual design
 dev environment
Config, builds, CI, deployment, etc.
 docs
Documentation
 duplicate
This issue or pull request already exists
 enhancement
Improving existing functionality
 feature
New functionality
 good first issue
Dive in, and start contributing
 idea
Something to consider
 invalid
Not a bug
 kredits-1
Small contribution
 kredits-2
Medium contribution
 kredits-3
Large contribution
 on hold
Currently not actionable
 ops
Manual IT ops activities
 question
Looking for an answer
release
major
release
minor
release
patch
security
All your base are belong to us
 ui/ux
User interface, process design, etc.
 wontfix
This won't be fixed
No labels kredits-3 ops
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
bkero (bkero) bumi (bumi) fsmanuel (fsmanuel) galfert (galfert) greg (Greg) hueso (hueso) raucao (Râu Cao) slvrbckt (Nick Jennings)
No Assignees raucao greg
3 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: kosmos/chef#625