From: Tom Hughes <tom@compton.nu>
Date: Thu, 28 Sep 2023 17:56:39 +0000 (+0100)
Subject: Run a yearly reindex of the larger database tables
X-Git-Url: https://git.openstreetmap.org./chef.git/commitdiff_plain/53d4fbfe0a07e734177860a78159f5d8944ae52c?ds=sidebyside;hp=2f19f1b5184c6ed91c67fe8152b20f00d9378b22

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.
---

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