From 53d4fbfe0a07e734177860a78159f5d8944ae52c Mon Sep 17 00:00:00 2001
From: Tom Hughes <tom@compton.nu>
Date: Thu, 28 Sep 2023 18:56:39 +0100
Subject: [PATCH 1/1] Run a yearly reindex of the larger database tables

The reindex starts at 0200 on the second Friday in January to
avoid the monthly reindex that runs on the first Sunday of the
month and sit between two weekly backups.
---
 cookbooks/db/files/default/yearly-reindex.sql | 16 +++++++++++++
 cookbooks/db/recipes/master.rb                | 23 +++++++++++++++++++
 2 files changed, 39 insertions(+)
 create mode 100644 cookbooks/db/files/default/yearly-reindex.sql

diff --git a/cookbooks/db/files/default/yearly-reindex.sql b/cookbooks/db/files/default/yearly-reindex.sql
new file mode 100644
index 000000000..5f41cb2a1
--- /dev/null
+++ b/cookbooks/db/files/default/yearly-reindex.sql
@@ -0,0 +1,16 @@
+REINDEX (VERBOSE) TABLE CONCURRENTLY current_node_tags;
+REINDEX (VERBOSE) TABLE CONCURRENTLY current_nodes;
+REINDEX (VERBOSE) TABLE CONCURRENTLY current_relation_tags;
+REINDEX (VERBOSE) TABLE CONCURRENTLY current_relations;
+REINDEX (VERBOSE) TABLE CONCURRENTLY current_way_nodes;
+REINDEX (VERBOSE) TABLE CONCURRENTLY current_way_tags;
+REINDEX (VERBOSE) TABLE CONCURRENTLY current_ways;
+REINDEX (VERBOSE) TABLE CONCURRENTLY gps_points;
+REINDEX (VERBOSE) TABLE CONCURRENTLY node_tags;
+REINDEX (VERBOSE) TABLE CONCURRENTLY nodes;
+REINDEX (VERBOSE) TABLE CONCURRENTLY relation_members;
+REINDEX (VERBOSE) TABLE CONCURRENTLY relation_tags;
+REINDEX (VERBOSE) TABLE CONCURRENTLY relations;
+REINDEX (VERBOSE) TABLE CONCURRENTLY way_nodes;
+REINDEX (VERBOSE) TABLE CONCURRENTLY way_tags;
+REINDEX (VERBOSE) TABLE CONCURRENTLY ways;
diff --git a/cookbooks/db/recipes/master.rb b/cookbooks/db/recipes/master.rb
index 905d7a221..3a2b30a4e 100644
--- a/cookbooks/db/recipes/master.rb
+++ b/cookbooks/db/recipes/master.rb
@@ -106,3 +106,26 @@ end
 service "monthly-reindex.timer" do
   action [:enable, :start]
 end
+
+cookbook_file "/usr/local/share/yearly-reindex.sql" do
+  owner "root"
+  group "root"
+  mode "644"
+end
+
+systemd_service "yearly-reindex" do
+  description "Yearly database reindex"
+  exec_start "/usr/bin/psql -f /usr/local/share/yearly-reindex.sql openstreetmap"
+  user "postgres"
+  sandbox true
+  restrict_address_families "AF_UNIX"
+end
+
+systemd_timer "yearly-reindex" do
+  description "Yearly database reindex"
+  on_calendar "Fri *-1-8..14 02:00"
+end
+
+service "yearly-reindex.timer" do
+  action [:enable, :start]
+end
-- 
2.39.5