WIP Prepare postgres for migration by replication

doc/postgres/migration.md

@@ -1,287 +0,0 @@
-# Migrating PostgreSQL cluster to a new major version
-
-## Summary
-
-1. Dump from a replica
-2. Restore to fresh VM running new major version
-3. Add logical replication for delta sync from current/old primary
-4. Switch primary to new server
-5. Remove logical replication on new server
-
-## Runbook
-
-* Primary host: `PRIMARY_HOST`
-* Replica host: `REPLICA_HOST`
-* New PG14 host: `NEW_HOST`
-* PostgreSQL superuser: `postgres`
-* Running locally on each machine via `sudo -u postgres`
-
-Adjust hostnames/IPs/etc. where needed.
-
----
-
-### 🟢 0. PRIMARY — Pre-checks
-
-```bash
-sudo -u postgres psql -c "SHOW wal_level;"
-sudo -u postgres psql -c "SHOW max_replication_slots;"
-```
-
-If needed, edit config:
-
-```bash
-sudo -u postgres vi $PGDATA/postgresql.conf
-```
-
-Ensure:
-
-```conf
-wal_level = logical
-max_replication_slots = 10
-```
-
-Restart if changed:
-
-```bash
-sudo systemctl restart postgresql
-```
-
----
-
-### 🔵🟡 3. Create keypair for syncing dump later
-
-🔵 On NEW_HOST:
-
-```bash
-sudo mkdir -p /home/postgres/.ssh && \
-sudo chown -R postgres:postgres /home/postgres && \
-sudo chmod 700 /home/postgres/.ssh && \
-sudo -u postgres bash -c 'ssh-keygen -t ecdsa -b 256 -f /home/postgres/.ssh/id_ecdsa -N "" -C "postgres@$(hostname)"' && \
-sudo cat /home/postgres/.ssh/id_ecdsa.pub
-```
-
-Copy the public key from the above output
-
-🟡 On replica:
-
-```bash
-sudo mkdir -p /home/postgres/.ssh && \
-sudo chown -R postgres:postgres /home/postgres && \
-sudo chmod 700 /home/postgres/.ssh && \
-echo [public_key] | sudo tee /home/postgres/.ssh/authorized_keys > /dev/null && \
-sudo chmod 700 /home/postgres/.ssh
-```
-
----
-
-### 🟢 1. PRIMARY — Create publication and replication slots
-
-```bash
-sudo -u postgres pg_create_replication_publications
-```
-
-or
-
-```bash
-sudo -u postgres pg_create_replication_publication [db_name]
-```
-
-Listing publications and slots:
-
-```bash
-sudo -u postgres pg_list_replication_publications
-sudo -u postgres pg_list_replication_slots
-```
-
----
-
-### 🟡 3. REPLICA — Pause replication
-
-```bash
-sudo -u postgres psql -c "SELECT pg_wal_replay_pause();"
-```
-
-Verify:
-
-```bash
-sudo -u postgres psql -c "SELECT pg_is_wal_replay_paused();"
-```
-
----
-
-### 🟡 4. REPLICA — Run dump
-
-```bash
-sudo -u postgres pg_dump_all_databases
-```
-
-or
-
-```bash
-sudo -u postgres bash -c "pg_dumpall --globals-only > /tmp/globals.sql"
-sudo -u postgres pg_dump_database [db_name]
-```
-
----
-
-### 🟡 5. REPLICA — Resume replication
-
-```bash
-sudo -u postgres psql -c "SELECT pg_wal_replay_resume();"
-```
-
----
-
-### 🔵 6. COPY dumps to NEW HOST
-
-From NEW_HOST:
-
-```bash
-export REPLICA_HOST=[private_ip] && \
-cd /tmp && \
-sudo -u postgres scp "postgres@$REPLICA_HOST:/tmp/globals.sql" . && \
-sudo -u postgres scp "postgres@$REPLICA_HOST:/tmp/dump_*.tar.zst" .
-```
-
----
-
-### 🔵 7. NEW HOST (PostgreSQL 14) — Restore
-
-#### 7.1 Restore globals
-
-```bash
-sudo -u postgres psql -f /tmp/globals.sql
-```
-
----
-
-#### 7.2 Create databases
-
-```bash
-sudo -u postgres psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN ('template1')" | \
-xargs -I{} sudo -u postgres createdb {}
-```
-
-or
-
-```bash
-sudo -u postgres createdb [db_name]
-```
-
----
-
-#### 7.3 Restore each database
-
-```bash
-sudo -u postgres pg_restore_all_databases
-```
-
-or
-
-```bash
-sudo -u postgres pg_restore_database [db_name]
-```
-
----
-
-### 🔵 8. NEW HOST — Create subscriptions
-
-```bash
-sudo -u postgres pg_create_replication_subscriptions
-```
-
-or
-
-```bash
-sudo -u postgres pg_create_replication_subscription [db_name]
-```
-
----
-
-### 🔵 9. NEW HOST — Monitor replication
-
-```bash
-sudo -u postgres pg_list_replication_subscriptions
-```
-
----
-
-### 🔴 11. CUTOVER
-
-#### 11.1 Stop writes on old primary
-
-Put app(s) in maintenance mode, stop the app/daemons.
-
----
-
-#### 11.2 Wait for replication to catch up
-
-TODO: not the best way to check, since WAL LSNs keep increasing
-
-```bash
-sudo -u postgres psql -d [db_name] -c "SELECT * FROM pg_stat_subscription;"
-```
-
----
-
-#### 11.3 Fix sequences
-
-Run per DB:
-
-```bash
-sudo -u postgres pg_fix_sequences_in_all_databases
-```
-
-or
-
-```bash
-sudo -u postgres pg_fix_sequences [db_name]
-```
-
----
-
-#### 11.4 Point app to NEW_HOST
-
-1. Update `pg.kosmos.local` in `/etc/hosts` on app server(s). For example:
-
-   ```bash
-   export NEW_PG_PRIMARY=[private_ip]
-   bundle exec knife ssh roles:ejabberd -a knife_zero.host "sudo sed -r \"s/^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+\s(pg.kosmos.local)/$NEW_PG_PRIMARY\t\1/\" -i /etc/hosts"
-   ```
-
-   Or override node attribute(s) if necessary and/or approporiate.
-
-2. Start the app/daemons, and deactivate maintenance mode.
-
----
-
-### 🧹 12. CLEANUP NEW_HOST
-
-```bash
-sudo -u postgres pg_drop_replication_subscriptions
-```
-
----
-
-### 🧹 13. CLEANUP PRIMARY
-
-TODO: Looks like slots are dropped automatically, when subscriptions are dropped
-
-```bash
-sudo -u postgres pg_drop_replication_publications
-```
-
----
-
-### 🧹 13. CLEANUP Chef
-
-Once all apps/databases are migrated, update the role in the node
-config of the new primary to 'postgres_primary' and converge it.
-
-Also delete the old primary node config from the Chef repo.
-
----
-
-### ✅ DONE
-
----

roles/postgresql_replica_logical.rb

@@ -1,8 +1,7 @@
 name "postgresql_replica_logical"
 
-run_list [
-  "kosmos_postgresql::hostsfile",
-  "kosmos_postgresql::replica_logical",
-  "kosmos_postgresql::firewall",
-  "kosmos_postgresql::management_scripts"
-]
+run_list %w(
+  kosmos_postgresql::hostsfile
+  kosmos_postgresql::replica_logical
+  kosmos_postgresql::firewall
+)

site-cookbooks/kosmos_drone/attributes/default.rb

@@ -1,6 +1,2 @@
 node.default["kosmos_drone"]["domain"] = "drone.kosmos.org"
 node.default["kosmos_drone"]["upstream_port"] = 80
-node.default["kosmos_drone"]["pg_host"] = "pg.kosmos.local"
-node.default["kosmos_drone"]["pg_port"] = 5432
-node.default["kosmos_drone"]["pg_db"] = "drone"
-node.default["kosmos_drone"]["pg_user"] = "drone"

site-cookbooks/kosmos_drone/recipes/default.rb

@@ -9,11 +9,11 @@ credentials = data_bag_item("credentials", "drone")
 drone_credentials = data_bag_item('credentials', 'drone')
 
 postgres_config = {
-  host: node["kosmos_drone"]["pg_host"],
-  port: node["kosmos_drone"]["pg_port"],
-  database: node["kosmos_drone"]["pg_db"],
-  username: node["kosmos_drone"]["pg_user"],
-  password: drone_credentials["postgresql_password"]
+  username: "drone",
+  password: drone_credentials["postgresql_password"],
+  host: "pg.kosmos.local",
+  port: 5432,
+  database: "drone"
 }
 
 directory deploy_path do

site-cookbooks/kosmos_postgresql/files/create_publication.sh

@@ -1,31 +0,0 @@
-#!/bin/bash
-set -euo pipefail
-
-DB_NAME="${1:?Usage: $0 <database_name>}"
-
-echo "== Processing DB: $DB_NAME =="
-
-# Create publication (idempotent)
-psql -d "$DB_NAME" -v ON_ERROR_STOP=1 <<'SQL'
-DO $$
-BEGIN
-  IF NOT EXISTS (
-    SELECT 1 FROM pg_publication WHERE pubname = 'migrate_pub'
-  ) THEN
-    CREATE PUBLICATION migrate_pub FOR ALL TABLES;
-  END IF;
-END
-$$;
-SQL
-
-# Create logical replication slot (idempotent-ish)
-SLOT="migrate_slot_${DB_NAME}"
-
-if ! psql -d "$DB_NAME" -Atqc "SELECT 1 FROM pg_replication_slots WHERE slot_name = '$SLOT'" | grep -q 1; then
-  echo "  Creating slot: $SLOT"
-  psql -d "$DB_NAME" -c "SELECT pg_create_logical_replication_slot('$SLOT', 'pgoutput');"
-else
-  echo "  Slot already exists: $SLOT"
-fi
-
-echo "== Done =="

site-cookbooks/kosmos_postgresql/files/create_publications.sh

@@ -3,7 +3,7 @@ set -e
 
 echo "== Creating publication in each database =="
 
-for db in $(psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN ('template1','postgres')"); do
+for db in $(psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN ('template0','template1')"); do
   echo "Processing DB: $db"
 
   # Create publication (idempotent)

site-cookbooks/kosmos_postgresql/files/drop_publications.sh

@@ -1,9 +1,8 @@
-#!/bin/bash
 set -e
 
-echo "== Dropping subscriptions slots and publications =="
+echo "== Dropping subscriptions slots and publications on PRIMARY =="
 
-for db in $(psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN ('template1','postgres')"); do
+for db in $(psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN ('template0','template1')"); do
   echo "Processing DB: $db"
 
   SLOT="migrate_slot_${db}"

site-cookbooks/kosmos_postgresql/files/drop_subscriptions.sh

@@ -1,28 +1,27 @@
-#!/usr/bin/env bash
 set -e
 
-echo "== Dropping subscriptions =="
+echo "== Dropping subscriptions on PG14 =="
 
-for db in $(psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN ('template1','postgres')"); do
+for db in $(psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN ('template0,'template1'')"); do
   echo "Processing DB: $db"
 
   SUB="migrate_sub_${db}"
 
-  # Check if subscription exists
-  EXISTS=$(psql -d "$db" -Atqc "SELECT 1 FROM pg_subscription WHERE subname = '$SUB'")
+  # Disable first (important)
+  psql -d "$db" -c "ALTER SUBSCRIPTION $SUB DISABLE;" 2>/dev/null || true
 
-  if [ "$EXISTS" = "1" ]; then
-    echo "  Found subscription: $SUB"
-
-    # Disable first (good practice)
-    psql -d "$db" -c "ALTER SUBSCRIPTION $SUB DISABLE;"
-
-    # Drop it (must be top-level)
-    psql -d "$db" -c "DROP SUBSCRIPTION $SUB;"
-
-  else
-    echo "  No subscription: $SUB"
-  fi
+  # Drop subscription if exists
+  psql -d "$db" -v ON_ERROR_STOP=1 <<SQL
+DO \$\$
+BEGIN
+  IF EXISTS (
+    SELECT 1 FROM pg_subscription WHERE subname = '$SUB'
+  ) THEN
+    DROP SUBSCRIPTION $SUB;
+  END IF;
+END
+\$\$;
+SQL
 
 done
 

site-cookbooks/kosmos_postgresql/files/dump_all_databases.sh

@@ -1,7 +1,7 @@
 #!/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'')" | \
+psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN (''template0'')" | \
 xargs -I{} -P4 sh -c "
   pg_dump -Fd -j 4 -d \"{}\" -f dump_{} &&
   tar -cf - dump_{} | zstd -19 -T0 > dump_{}.tar.zst &&

site-cookbooks/kosmos_postgresql/files/dump_database.sh

@@ -1,10 +0,0 @@
-#!/bin/bash
-set -euo pipefail
-
-DB_NAME="${1:?Usage: $0 <database_name>}"
-
-cd /tmp
-
-pg_dump -Fd -j 4 -d "$DB_NAME" -f "dump_${DB_NAME}"
-tar -cf - "dump_${DB_NAME}" | zstd -19 -T0 > "dump_${DB_NAME}.tar.zst"
-rm -rf "dump_${DB_NAME}"

site-cookbooks/kosmos_postgresql/files/fix_sequences.sh

@@ -1,35 +0,0 @@
-#!/bin/bash
-set -e
-
-DB="$1"
-
-if [ -z "$DB" ]; then
-  echo "Usage: $0 <database>"
-  exit 1
-fi
-
-echo "== Fixing sequences in database: $DB =="
-
-SQL=$(psql -d "$DB" -Atqc "
-  SELECT
-    'SELECT setval(' ||
-    quote_literal(pg_get_serial_sequence(quote_ident(n.nspname)||'.'||quote_ident(c.relname), a.attname)) ||
-    ', COALESCE(MAX(' || quote_ident(a.attname) || '), 0) + 1, false) FROM ' ||
-    quote_ident(n.nspname)||'.'||quote_ident(c.relname) || ';'
-  FROM pg_class c
-  JOIN pg_namespace n ON n.oid = c.relnamespace
-  JOIN pg_attribute a ON a.attrelid = c.oid
-  WHERE c.relkind = 'r'
-    AND a.attnum > 0
-    AND NOT a.attisdropped
-    AND pg_get_serial_sequence(quote_ident(n.nspname)||'.'||quote_ident(c.relname), a.attname) IS NOT NULL;
-")
-
-if [ -z "$SQL" ]; then
-  echo "No sequences found in $DB"
-  exit 0
-fi
-
-echo "$SQL" | psql -d "$DB"
-
-echo "== Done =="

site-cookbooks/kosmos_postgresql/files/fix_sequences_in_all_databases.sh

@@ -1,38 +0,0 @@
-#!/bin/bash
-set -e
-
-echo "== Fixing sequences across all databases =="
-
-for db in $(psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN ('template1','postgres')"); do
-  echo "---- DB: $db ----"
-
-  # Generate fix statements
-  SQL=$(psql -d "$db" -Atqc "
-    SELECT
-      'SELECT setval(' ||
-      quote_literal(pg_get_serial_sequence(quote_ident(n.nspname)||'.'||quote_ident(c.relname), a.attname)) ||
-      ', COALESCE(MAX(' || quote_ident(a.attname) || '), 0) + 1, false) FROM ' ||
-      quote_ident(n.nspname)||'.'||quote_ident(c.relname) || ';'
-    FROM pg_class c
-    JOIN pg_namespace n ON n.oid = c.relnamespace
-    JOIN pg_attribute a ON a.attrelid = c.oid
-    WHERE c.relkind = 'r'
-      AND a.attnum > 0
-      AND NOT a.attisdropped
-      AND pg_get_serial_sequence(quote_ident(n.nspname)||'.'||quote_ident(c.relname), a.attname) IS NOT NULL;
-  ")
-
-  if [ -z "$SQL" ]; then
-    echo "No sequences found in $db"
-    continue
-  fi
-
-  echo "Fixing sequences in $db..."
-
-  # Execute generated statements
-  echo "$SQL" | psql -d "$db"
-
-done
-
-echo "== Done fixing sequences =="
-

site-cookbooks/kosmos_postgresql/files/list_publications.sh

@@ -1,5 +1,5 @@
 #!/bin/bash
-for db in $(psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN ('template1','postgres')"); do
+for db in $(psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn"); do
   echo "DB: $db"
   psql -d "$db" -Atqc "SELECT pubname FROM pg_publication;"
 done

site-cookbooks/kosmos_postgresql/files/list_subscriptions.sh

@@ -1,5 +1,5 @@
 #!/bin/bash
-for db in $(psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN ('template1','postgres')"); do
+for db in $(psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN ('template0','template1')"); do
   echo "==== DB: $db ===="
   psql -d "$db" -c "SELECT * FROM pg_stat_subscription;"
 done

site-cookbooks/kosmos_postgresql/files/restore_all_databases.sh

@@ -1,12 +1,8 @@
 #!/bin/bash
-set -euo pipefail
-
 cd /tmp
-
 for f in dump_*.tar.zst; do
   db=$(echo $f | sed "s/dump_\(.*\)\.tar\.zst/\1/")
   echo "Restoring $db"
   zstd -d "$f" -c | tar -xf -
   pg_restore -j 4 -d "$db" dump_$db
-  rm -rf "dump_$db"
 done

site-cookbooks/kosmos_postgresql/files/restore_database.sh

@@ -1,14 +0,0 @@
-#!/bin/bash
-set -euo pipefail
-
-DB_NAME="${1:?Usage: $0 <database_name>}"
-
-cd /tmp
-
-FILE="dump_${DB_NAME}.tar.zst"
-DIR="dump_${DB_NAME}"
-
-echo "Restoring $DB_NAME"
-zstd -d "$FILE" -c | tar -xf -
-pg_restore -j 4 -d "$DB_NAME" "$DIR"
-rm -rf "$DIR"

site-cookbooks/kosmos_postgresql/recipes/management_scripts.rb

@@ -1,121 +0,0 @@
-#
-# Cookbook:: kosmos_postgresql
-# Recipe:: management_scripts
-#
-
-credentials = data_bag_item('credentials', 'postgresql')
-
-cookbook_file "/usr/local/bin/pg_dump_all_databases" do
-  source "dump_all_databases.sh"
-  user "postgres"
-  group "postgres"
-  mode "0744"
-end
-
-cookbook_file "/usr/local/bin/pg_dump_database" do
-  source "dump_database.sh"
-  user "postgres"
-  group "postgres"
-  mode "0744"
-end
-
-cookbook_file "/usr/local/bin/pg_restore_all_databases" do
-  source "restore_all_databases.sh"
-  user "postgres"
-  group "postgres"
-  mode "0744"
-end
-
-cookbook_file "/usr/local/bin/pg_restore_database" do
-  source "restore_database.sh"
-  user "postgres"
-  group "postgres"
-  mode "0744"
-end
-
-cookbook_file "/usr/local/bin/pg_create_replication_publications" do
-  source "create_publications.sh"
-  user "postgres"
-  group "postgres"
-  mode "0744"
-end
-
-cookbook_file "/usr/local/bin/pg_create_replication_publication" do
-  source "create_publication.sh"
-  user "postgres"
-  group "postgres"
-  mode "0744"
-end
-
-cookbook_file "/usr/local/bin/pg_drop_replication_publications" do
-  source "drop_publications.sh"
-  user "postgres"
-  group "postgres"
-  mode "0744"
-end
-
-cookbook_file "/usr/local/bin/pg_list_replication_publications" do
-  source "list_publications.sh"
-  user "postgres"
-  group "postgres"
-  mode "0744"
-end
-
-cookbook_file "/usr/local/bin/pg_list_replication_slots" do
-  source "list_replication_slots.sh"
-  user "postgres"
-  group "postgres"
-  mode "0744"
-end
-
-template "/usr/local/bin/pg_create_replication_subscriptions" do
-  source "create_subscriptions.sh.erb"
-  user "postgres"
-  group "postgres"
-  mode "0740"
-  variables pg_host: "pg.kosmos.local",
-            pg_port: 5432,
-            pg_user: "replication",
-            pg_pass: credentials["replication_password"]
-  sensitive true
-end
-
-template "/usr/local/bin/pg_create_replication_subscription" do
-  source "create_subscription.sh.erb"
-  user "postgres"
-  group "postgres"
-  mode "0740"
-  variables pg_host: "pg.kosmos.local",
-            pg_port: 5432,
-            pg_user: "replication",
-            pg_pass: credentials["replication_password"]
-  sensitive true
-end
-
-cookbook_file "/usr/local/bin/pg_drop_replication_subscriptions" do
-  source "drop_subscriptions.sh"
-  user "postgres"
-  group "postgres"
-  mode "0744"
-end
-
-cookbook_file "/usr/local/bin/pg_list_replication_subscriptions" do
-  source "list_subscriptions.sh"
-  user "postgres"
-  group "postgres"
-  mode "0744"
-end
-
-cookbook_file "/usr/local/bin/pg_fix_sequences_in_all_databases" do
-  source "fix_sequences.sh"
-  user "postgres"
-  group "postgres"
-  mode "0744"
-end
-
-cookbook_file "/usr/local/bin/pg_fix_sequences" do
-  source "fix_sequences.sh"
-  user "postgres"
-  group "postgres"
-  mode "0744"
-end

site-cookbooks/kosmos_postgresql/recipes/primary.rb

@@ -6,3 +6,38 @@
 postgresql_custom_server postgresql_version do
   role "primary"
 end
+
+cookbook_file "/usr/local/bin/pg_dump_all_databases" do
+  source "dump_all_databases.sh"
+  user "postgres"
+  group "postgres"
+  mode "0744"
+end
+
+cookbook_file "/usr/local/bin/pg_create_replication_publications" do
+  source "create_publications.sh"
+  user "postgres"
+  group "postgres"
+  mode "0744"
+end
+
+cookbook_file "/usr/local/bin/pg_drop_replication_publications" do
+  source "drop_publications.sh"
+  user "postgres"
+  group "postgres"
+  mode "0744"
+end
+
+cookbook_file "/usr/local/bin/pg_list_replication_publications" do
+  source "list_publications.sh"
+  user "postgres"
+  group "postgres"
+  mode "0744"
+end
+
+cookbook_file "/usr/local/bin/pg_list_replication_slots" do
+  source "list_replication_slots.sh"
+  user "postgres"
+  group "postgres"
+  mode "0744"
+end

site-cookbooks/kosmos_postgresql/recipes/replica.rb

@@ -3,6 +3,10 @@
 # Recipe:: replica
 #
 
+service postgresql_service do
+  supports restart: true, status: true, reload: true
+end
+
 postgresql_custom_server postgresql_version do
   role "replica"
 end
@@ -16,9 +20,6 @@ if primary.nil?
   return
 end
 
-# TODO Replace pg.kosmos.local with private IP once available
-# via proper node attribute
-# https://gitea.kosmos.org/kosmos/chef/issues/263
 execute "set up replication" do
   command <<-EOF
 systemctl stop #{postgresql_service}

site-cookbooks/kosmos_postgresql/recipes/replica_logical.rb

@@ -3,13 +3,48 @@
 # Recipe:: replica_logical
 #
 
+service postgresql_service do
+  supports restart: true, status: true, reload: true
+end
+
 postgresql_custom_server postgresql_version do
   role "replica_logical"
 end
 
-# primary = postgresql_primary
-#
-# if primary.nil?
-#   Chef::Log.warn("No PostgreSQL primary node found. Skipping replication setup.")
-#   return
-# end
+postgresql_data_bag_item = data_bag_item('credentials', 'postgresql')
+
+primary = postgresql_primary
+
+if primary.nil?
+  Chef::Log.warn("No PostgreSQL primary node found. Skipping replication setup.")
+  return
+end
+
+template "/usr/local/bin/pg_create_replication_subscriptions" do
+  source "create_subscriptions.sh.erb"
+  user "postgres"
+  group "postgres"
+  mode "0740"
+  sensitive true
+end
+
+cookbook_file "/usr/local/bin/pg_drop_replication_subscriptions" do
+  source "drop_subscriptions.sh"
+  user "postgres"
+  group "postgres"
+  mode "0744"
+end
+
+cookbook_file "/usr/local/bin/pg_list_replication_subscriptions" do
+  source "list_subscriptions.sh"
+  user "postgres"
+  group "postgres"
+  mode "0744"
+end
+
+cookbook_file "/usr/local/bin/pg_restore_all_databases" do
+  source "restore_all_databases.sh"
+  user "postgres"
+  group "postgres"
+  mode "0744"
+end

site-cookbooks/kosmos_postgresql/resources/server.rb

@@ -64,7 +64,7 @@ action :create do
   postgresql_server_conf "main" do
     version postgresql_version
     additional_config additional_config
-    notifies :restart, "service[#{postgresql_service}]", :delayed
+    notifies :reload, "service[#{postgresql_service}]", :delayed
   end
 
   postgresql_user "replication" do

site-cookbooks/kosmos_postgresql/templates/create_subscription.sh.erb

@@ -1,31 +0,0 @@
-#!/bin/bash
-set -euo pipefail
-
-DB_NAME="${1:?Usage: $0 <database_name>}"
-
-echo "== Processing DB: $DB_NAME =="
-
-SLOT="migrate_slot_${DB_NAME}"
-SUB="migrate_sub_${DB_NAME}"
-
-psql -d "$DB_NAME" -v ON_ERROR_STOP=1 <<SQL
-DO \$\$
-BEGIN
-  IF NOT EXISTS (
-    SELECT 1 FROM pg_subscription WHERE subname = '$SUB'
-  ) THEN
-    CREATE SUBSCRIPTION $SUB
-    CONNECTION 'host=<%= @pg_host %> port=<%= @pg_port %> dbname=$DB_NAME user=<%= @pg_user %> password=<%= @pg_pass %>'
-    PUBLICATION migrate_pub
-    WITH (
-      slot_name = '$SLOT',
-      create_slot = false,
-      copy_data = false,
-      enabled = true
-    );
-  END IF;
-END
-\$\$;
-SQL
-
-echo "== Done =="

site-cookbooks/kosmos_postgresql/templates/create_subscriptions.sh.erb

@@ -1,9 +1,8 @@
-#!/bin/bash
 set -e
 
 echo "== Creating subscriptions for all databases =="
 
-for db in $(psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN ('template1','postgres')"); do
+for db in $(psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN ('template0','template1')"); do
   echo "Processing DB: $db"
 
   SLOT="migrate_slot_${db}"