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 (file)
index 0000000..5f41cb2
--- /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 905d7a221ed8ed8f4d9fbc08bf1f649ded476cb5..3a2b30a4ede6a3e12e5836288e8fcee21653f750 100644 (file)
--- 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