replication for nominatim servers

diff --git a/cookbooks/nominatim/recipes/default.rb b/cookbooks/nominatim/recipes/default.rb
index e8ca87f9c242526ebadf0bf0138dc069f809dc3c..b8bfca2a168ff0e4e925edf2c83af8044fc8ee21 100644 (file)
--- a/cookbooks/nominatim/recipes/default.rb
+++ b/cookbooks/nominatim/recipes/default.rb
@@ -32,6 +32,7 @@ apache_module "rewrite"
 apache_module "proxy"
 apache_module "proxy_fcgi"
 
+passwords = data_bag_item("nominatim", "passwords")
 home_directory = data_bag_item("accounts", "nominatim")["home"]
 source_directory = "#{home_directory}/nominatim"
 email_errors = data_bag_item("accounts", "lonvia")["email"]
@@ -69,28 +70,23 @@ node[:nominatim][:fpm_pools].each do |name, data|
   end
 end
 
-postgresql_user "tomh" do
-  cluster database_cluster
-  superuser true
-end
-
-postgresql_user "lonvia" do
-  cluster database_cluster
-  superuser true
-end
+superusers = %w(tomh lonvia twain nominatim)
 
-postgresql_user "twain" do
-  cluster database_cluster
-  superuser true
+superusers.each do |user|
+  postgresql_user user do
+    cluster database_cluster
+    superuser true
+  end
 end
 
-postgresql_user "nominatim" do
+postgresql_user "www-data" do
   cluster database_cluster
-  superuser true
 end
 
-postgresql_user "www-data" do
+postgresql_user "replication" do
   cluster database_cluster
+  password passwords["replication"]
+  replication true
 end
 
 postgresql_munin "nominatim" do
@@ -248,35 +244,31 @@ munin_plugin "nominatim_throttled_ips" do
   target "#{source_directory}/munin/nominatim_throttled_ips"
 end
 
-remote_file "#{source_directory}/data/wikipedia_article.sql.bin" do
-  action :create_if_missing
-  source "http://www.nominatim.org/data/wikipedia_article.sql.bin"
-  owner "nominatim"
-  group "nominatim"
-  mode 0644
-end
+external_data = [
+  "wikipedia_article.sql.bin",
+  "wikipedia_redirect.sql.bin",
+  "gb_postcode_data.sql.gz"
+]
 
-remote_file "#{source_directory}/data/wikipedia_redirect.sql.bin" do
-  action :create_if_missing
-  source "http://www.nominatim.org/data/wikipedia_redirect.sql.bin"
-  owner "nominatim"
-  group "nominatim"
-  mode 0644
+external_data.each do |fname|
+  remote_file "#{source_directory}/data/#{fname}" do
+    action :create_if_missing
+    source "http://www.nominatim.org/data/#{fname}"
+    owner "nominatim"
+    group "nominatim"
+    mode 0644
+  end
 end
 
-remote_file "#{source_directory}/data/gb_postcode_data.sql.gz" do
-  action :create_if_missing
-  source "http://www.nominatim.org/data/gb_postcode_data.sql.gz"
-  owner "nominatim"
-  group "nominatim"
-  mode 0644
-end
+additional_scripts = %w(backup-nominatim clean-db-nominatim)
 
-template "/usr/local/bin/backup-nominatim" do
-  source "backup-nominatim.erb"
-  owner "root"
-  group "root"
-  mode 0755
+additional_scripts.each do |fname|
+  template "/usr/local/bin/#{fname}" do
+    source "#{fname}.erb"
+    owner "root"
+    group "root"
+    mode 0755
+  end
 end
 
 directory File.dirname(node[:nominatim][:flatnode_file]) do

diff --git a/cookbooks/nominatim/templates/default/clean-db-nominatim.erb b/cookbooks/nominatim/templates/default/clean-db-nominatim.erb
new file mode 100644 (file)
index 0000000..573624b
--- /dev/null
+++ b/cookbooks/nominatim/templates/default/clean-db-nominatim.erb
@@ -0,0 +1,20 @@
+#!/bin/bash
+#
+# DO NOT EDIT - This file is being maintained by Chef
+
+
+# Vaccum all tables with indices on integer arrays.
+# Agressive vacuuming seems to help against index bloat.
+psql -q -d nominatim -c 'VACUUM ANALYSE search_name'
+psql -q -d nominatim -c 'VACUUM ANALYSE search_name_country'
+
+for i in `seq 0 246`; do
+  psql -q -d nominatim -c "VACUUM ANALYSE search_name_${i}"
+done
+
+<% if node[:postgres][:settings][:archive_mode] == "on" -%>
+
+# Cleanup archive directory
+find -L /data/postgresql-archive -mtime +3 -print0 | xargs -0r rm
+
+<% end -%>

diff --git a/cookbooks/nominatim/templates/default/cron.erb b/cookbooks/nominatim/templates/default/cron.erb
index 9ad9c1ff4c215612014ac6e29a3fc541ccdd2fd2..8b1ad0d697fe5b59c3c8952e742eb975e5701520 100644 (file)
--- a/cookbooks/nominatim/templates/default/cron.erb
+++ b/cookbooks/nominatim/templates/default/cron.erb
@@ -3,6 +3,7 @@
 MAILTO=<%= @mailto %>
 
 * * * * * lonvia <%= @bin_directory %>/cron_ipanalyse.py /var/log/apache2/nominatim.openstreetmap.org-access.log
-00 2 * * * nominatim <%= @bin_directory %>/cron_vacuum.sh
+<% if node[:nominatim][:backup_enabled] -%>
 00 3 1 * * nominatim /usr/local/bin/backup-nominatim
-
+<% end -%>
+30 1 * * * postgres /usr/local/bin/clean-db-nominatim

diff --git a/roles/nominatim-master.rb b/roles/nominatim-master.rb
new file mode 100644 (file)
index 0000000..0fa0326
--- /dev/null
+++ b/roles/nominatim-master.rb
@@ -0,0 +1,44 @@
+name "nominatim-master"
+description "Role applied to the master nominatim server"
+
+default_attributes(
+  :postgresql => {
+    :versions => ["9.3"],
+    :settings => {
+      :defaults => {
+        :listen_addresses => "*",
+        :wal_level => "hot_standby",
+        :archive_mode => "on",
+        :archive_command => "/bin/cp %p /data/postgresql-archive/%f",
+        :max_wal_senders => "1",
+        :late_authentication_rules => [
+          { :database => "replication", :user => "replication", :address => "146.179.159.164/32" }
+        ]
+      }
+    }
+  },
+  :nominatim => {
+    :enable_backup => true
+  },
+  :rsyncd => {
+    :modules => {
+      :archive => {
+        :comment => "WAL Archive",
+        :path => "/data/postgresql-archive",
+        :read_only => true,
+        :write_only => false,
+        :list => false,
+        :uid => "postgres",
+        :gid => "postgres",
+        :transfer_logging => false,
+        :hosts_allow => [
+          "146.179.159.164"
+        ]
+      }
+    }
+  }
+)
+
+run_list(
+  "role[nominatim]"
+)

diff --git a/roles/nominatim.rb b/roles/nominatim.rb
index 86a2fe9cd23552bcf98081c71e3d45009ac39fc1..e1a2a3ddc2463570b4d5a402e9dc20cb8bea6aeb 100644 (file)
--- a/roles/nominatim.rb
+++ b/roles/nominatim.rb
@@ -27,7 +27,7 @@ default_attributes(
       :defaults => {
         :max_connections => "450",
         :synchronous_commit => "off",
-        :checkpoint_segments => "50",
+        :checkpoint_segments => "32",
         :checkpoint_timeout => "10min",
         :checkpoint_completion_target => "0.9",
         :autovacuum_max_workers => "1"

diff --git a/roles/poldi.rb b/roles/poldi.rb
index e121bf345d678275ca1ab09344c3d48bdf69d44e..fc71d1fa746cd9b61cb541fc9e1451c29f46e91c 100644 (file)
--- a/roles/poldi.rb
+++ b/roles/poldi.rb
@@ -57,7 +57,6 @@ default_attributes(
     }
   },
   :postgresql => {
-    :versions => ["9.3"],
     :settings => {
       :defaults => {
         :shared_buffers => "10GB",
@@ -70,7 +69,7 @@ default_attributes(
     }
   },
   :nominatim => {
-    :enabled => false,
+    :enabled => true,
     :flatnode_file => "/ssd-old/nominatim/nodes.store",
     :database => {
       :cluster => "9.3/main",
@@ -95,5 +94,5 @@ default_attributes(
 run_list(
   "role[ic]",
   "role[tyan-s7010]",
-  "role[nominatim]"
+  "role[nominatim-slave]"
 )

diff --git a/roles/pummelzacken.rb b/roles/pummelzacken.rb
index eeaadfce9bb32bd237924fdec5f6135ee86967c3..495f2a612e64daf5f84462aa6b17dd3a5b5da7da 100644 (file)
--- a/roles/pummelzacken.rb
+++ b/roles/pummelzacken.rb
@@ -19,15 +19,13 @@ default_attributes(
     }
   },
   :postgresql => {
-    :versions => ["9.3"],
     :settings => {
       :defaults => {
         :shared_buffers => "10GB",
         :work_mem => "160MB",
         :maintenance_work_mem => "10GB",
         :random_page_cost => "1.5",
-        :effective_cache_size => "60GB",
-        :fsync => "on"
+        :effective_cache_size => "60GB"
       }
     }
   },
@@ -56,5 +54,5 @@ default_attributes(
 
 run_list(
   "role[ucl-wolfson]",
-  "role[nominatim]"
+  "role[nominatim-master]"
 )