From 361fe7b2d59cd38452162f1c66f961f9f71ec377 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Sun, 14 Jun 2015 22:46:58 +0200 Subject: [PATCH] replication for nominatim servers --- cookbooks/nominatim/recipes/default.rb | 72 +++++++++---------- .../templates/default/clean-db-nominatim.erb | 20 ++++++ .../nominatim/templates/default/cron.erb | 5 +- roles/nominatim-master.rb | 44 ++++++++++++ roles/nominatim.rb | 2 +- roles/poldi.rb | 5 +- roles/pummelzacken.rb | 6 +- 7 files changed, 104 insertions(+), 50 deletions(-) create mode 100644 cookbooks/nominatim/templates/default/clean-db-nominatim.erb create mode 100644 roles/nominatim-master.rb diff --git a/cookbooks/nominatim/recipes/default.rb b/cookbooks/nominatim/recipes/default.rb index e8ca87f9c..b8bfca2a1 100644 --- 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 index 000000000..573624b58 --- /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 9ad9c1ff4..8b1ad0d69 100644 --- 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 index 000000000..0fa032677 --- /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 86a2fe9cd..e1a2a3ddc 100644 --- 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 e121bf345..fc71d1fa7 100644 --- 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 eeaadfce9..495f2a612 100644 --- 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]" ) -- 2.39.5