add centos db initialization script

3 files changed, 326 insertions, 4 deletions

diff --git a/experiment/scripts/database-centos.sh b/experiment/scripts/database-centos.sh
new file mode 100755
index 0000000..cd8e22a
--- /dev/null
+++ b/experiment/scripts/database-centos.sh
@@ -0,0 +1,321 @@
+#!/bin/bash
+INFO_MSG="
+Setup the database node (start postgresql)
+"
+OPT_MSG="
+init:
+  Initialize and start the taler database
+"
+
+set -eux
+source ~/scripts/helpers.sh
+
+# move to tmp to prevent change directory errors
+cd /tmp 
+
+function setup_disks() {
+  if [ -b /dev/disk1 ]; then
+    echo 'start=2048, type=83' | sfdisk /dev/disk1 || true
+    yes | mkfs.ext4 -m 0 /dev/disk1 || true
+    mkdir /mnt/disk || true
+    mount /dev/disk1 /mnt/disk || true
+  fi
+}
+
+# Setup the postgresql configuration
+function setup_config() {
+
+  restart_rsyslog
+ 
+  PGSETUP_INITDB_OPTIONS="-D /tmp/postgresql/13/data"
+
+  if [ -d /mnt/disk ]; then
+    mkdir /mnt/disk/pg_wal
+    chown -R postgres:postgres /mnt/disk/pg_wal
+    PGSETUP_INITDB_OPTIONS="${PGSETUP_INITDB_OPTIONS} -X /mnt/disk/pg_wal"
+  fi
+
+  if [ ! -d /tmp/postgresql/13/data ]; then
+    mkdir -p /tmp/postgresql/13/data
+    chown -R postgres:postgres /tmp/postgresql/
+  fi
+
+  postgresql-${POSTGRES_VERSION}-setup initdb
+  
+  # Get hardware info to tune in postgresql.conf
+  SHARED_MEM=$(($(awk '/MemTotal/ {print $2}' /proc/meminfo) / 3 ))
+  CACHE_SIZE=$(($(awk '/MemTotal/ {print $2}' /proc/meminfo) * 3/4))
+  NUM_CPU=$(lscpu | grep "CPU(s)" | head -n 1 | awk '{print $2}')
+
+  # Enable huge pages
+  # Size for huge_pages =~ shared_buffers * 1.25 so that there is enough
+  VM_PEAK=$((${SHARED_MEM} * 10/8))
+
+  HUGE_PAGES_SIZE=$(grep ^Hugepagesize /proc/meminfo | awk '{print $2}')
+  NUM_PAGES=$((${VM_PEAK} / ${HUGE_PAGES_SIZE}))
+
+  if ! grep -q "vm.nr_hugepages'" /etc/sysctl.conf; then
+    echo "vm.nr_hugepages=${NUM_PAGES}" >> /etc/sysctl.conf
+    sysctl -p
+  fi
+
+  # Allow longer lines to be logged by postgres
+  sed -i '1 i\$MaxMessageSize 4k' /etc/rsyslog.conf
+
+  # Configure postgres with an additional file and include this
+  # in the main configuration
+  echo "
+  listen_addresses='*'
+
+  log_destination=syslog
+  syslog_ident='taler-database'
+
+  log_error_verbosity=terse
+  # log_min_messages=PANIC
+  # log_min_error_statement=PANIC
+  # client_min_messages=ERROR
+
+  # For pgbadger
+  # log_checkpoints=on
+  # log_connections=on
+  # log_disconnections=on
+  # log_lock_waits=on
+  # log_temp_files=0
+  # log_autovacuum_min_duration=0
+  # log_error_verbosity=default
+  # log_duration=on
+  # log_statement=all
+
+  # For explain.py
+  # syslog_split_messages=off
+  # log_statement=all
+  # log_error_verbosity=default
+
+  log_min_duration_statement=${DB_MIN_DURATION_STATEMENT}
+
+  auto_explain.log_min_duration='${DB_MIN_DURATION_STATEMENT}ms'
+  auto_explain.log_verbose=true
+  auto_explain.log_nested_statements=true
+  auto_explain.log_analyze=true
+  auto_explain.log_buffers=true
+  auto_explain.log_wal=true
+
+  shared_preload_libraries='pg_stat_statements,auto_explain'
+
+  # Should be set locally
+  join_collapse_limit=1
+
+  # Large tables perform bad with the default settings
+  # However, they could also be set on each table indiviudally
+  # (NOTE: on partitions!)
+  # ALTER TABLE known_coins_default
+  #  SET (autovacuum_vacuum_scale_factor = 0.0, autovacuum_vacuum_threshold = 1000);
+  log_autovacuum_min_duration=0
+  default_statistics_target=300
+  autovacuum_vacuum_cost_limit=400
+  autovacuum_vacuum_scale_factor=0.1
+  autovacuum_vacuum_threshold=1000
+
+  # Default 50, 0.1
+  autovacuum_analyze_threshold=50
+  autovacuum_analyze_scale_factor=0.1
+
+  # use 25% of the available memory 
+  # (https://www.postgresql.org/docs/13/runtime-config-resource.html)
+ # shared_buffers=${SHARED_MEM}kB
+ # effective_cache_size=${CACHE_SIZE}kB
+  shared_buffers=24551953kB
+  effective_cache_size=75491895kB
+
+  huge_pages=on
+  
+  # (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-MAX-WAL-SIZE)
+  min_wal_size=20GB
+  max_wal_size=200GB
+  wal_buffers=1GB
+
+  checkpoint_completion_target=0.9
+  checkpoint_timeout = 15min
+  checkpoint_flush_after = 2MB
+  random_page_cost=1.1
+  
+  # Default 2kB
+  bgwriter_flush_after = 2MB
+
+  # Default 0
+  # https://www.cybertec-postgresql.com/en/the-mysterious-backend_flush_after-configuration-setting/
+  backend_flush_after = 2MB
+
+  # Too much results in CPU load
+  # https://www.postgresql.org/docs/13/runtime-config-resource.html#GUC-EFFECTIVE-IO-CONCURRENCY
+  effective_io_concurrency = 200
+
+  # Bad when turned off - Recovering db may not be possible
+  # https://www.postgresql.org/docs/13/runtime-config-wal.html#GUC-FSYNC
+  fsync = on
+
+  # Not so bad as when turning off fsync, but single transactions might get lost on crash - but
+  # like they would have aborted cleanly
+  # https://www.postgresql.org/docs/13/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT
+  # When having replication, this one can be changed (in local only on and off are of use)
+  # https://www.postgresql.org/docs/13/runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES
+  # on causes us to get 100% IO load
+  synchronous_commit = off
+
+  # Default off
+  wal_compression = off
+
+  wal_sync_method = fsync
+
+  # Bad to turn off, may lead to inconcistency
+  # https://www.postgresql.org/docs/13/runtime-config-wal.html#GUC-FULL-PAGE-WRITES
+  # Write full pages to WAL while partial are written to disk, helpful in case of crash, then
+  # partially written pages can be recovered.
+  # Can be disabled if FS does not support partial written pages (such as ZFS)
+  full_page_writes = on
+
+  max_worker_processes=${NUM_CPU}
+  max_parallel_workers=${NUM_CPU}
+  max_parallel_workers_per_gather=10
+  max_connections=500
+
+  max_parallel_maintenance_workers=12
+  
+  # out of shared memory
+  max_locks_per_transaction=85
+  
+  # (max used =~ work_mem * max_connections)
+  # NOTE: This formula is not completely correct 
+  work_mem=2GB
+  maintenance_work_mem=4GB
+  # 1 min
+  idle_in_transaction_session_timeout=60000
+
+  # Disable sequential scans
+  # enable_seqscan=off
+
+  " > /tmp/postgresql/${POSTGRES_VERSION}/data/exchange.conf
+
+  cp /tmp/postgresql/${POSTGRES_VERSION}/data/exchange.conf ${LOG_DIR}/postgresql.conf
+
+  if ! grep -q "include = 'exchange.conf'" \
+	  /tmp/postgresql/${POSTGRES_VERSION}/data/postgresql.conf; then
+    echo "include = 'exchange.conf'" >> \
+          /tmp/postgresql/${POSTGRES_VERSION}/data/postgresql.conf
+  fi
+
+  if ! grep -q "host all taler-exchange-httpd 127.16.0.0/12 trust" \
+    /tmp/postgresql/${POSTGRES_VERSION}/data/pg_hba.conf; then
+    echo "
+    # we need this in centos to initialize the db from remote (taler not installed)
+    host all taler-exchange-httpd 172.16.0.0/12 trust
+    host all postgres 172.16.0.0/12 trust
+    " >> /tmp/postgresql/${POSTGRES_VERSION}/data/pg_hba.conf
+  fi
+
+  echo "
+  [Service]
+  Environment=PGDATA=/tmp/postgresql/13/data
+  " >> /etc/systemd/system/postgresql-13.service.d/override.conf
+  systemctl daemon-reload
+
+  systemctl restart postgresql-${POSTGRES_VERSION}
+}
+
+function enable_remote_access() {
+  # Enable password for taler since this is the case in real world deployments
+  # For the postgres user do not enable authentication (used in metrics)
+  if ! grep -q "host all ${DB_USER} 127.16.0.0/12 md5" \
+    /tmp/postgresql/${POSTGRES_VERSION}/data/pg_hba.conf; then
+    echo "
+    host all ${DB_USER} 172.16.0.0/12 md5
+    " >> /tmp/postgresql/${POSTGRES_VERSION}/data/pg_hba.conf
+  fi
+}
+
+function configure_shard_access() {
+
+  for i in $(seq $NUM_SHARDS); do
+    su postgres << EOF
+psql -d "${DB_NAME}" -tAc "CREATE USER MAPPING IF NOT EXISTS FOR \"${DB_USER}\"
+                           SERVER \"${i}\"
+                           OPTIONS (user '${DB_USER}', password '${DB_PASSWORD}');"
+EOF
+  done
+
+}
+
+function remote_init_db() {
+
+  sed -i "s\<DB_URL_HERE>\postgresql://taler-exchange-httpd@db.${DNS_ZONE}:${DB_PORT}/${DB_NAME}\g" \
+  	/etc/taler/secrets/exchange-db.secret.conf
+
+  sudo -u taler-exchange-httpd taler-exchange-dbinit -r || true
+  sudo -u taler-exchange-httpd taler-exchange-dbinit -s || true
+
+  if [ "${SHARD_DB}" = "true" ]; then
+    sudo -u postgres taler-exchange-dbinit -F ${NUM_SHARDS}
+  elif [ "${PARTITION_DB}" = "true" ]; then
+    sudo -u taler-exchange-httpd taler-exchange-dbinit -P ${NUM_PARTITIONS}
+  else
+    sudo -u taler-exchange-httpd taler-exchange-dbinit
+  fi
+
+}
+
+# Initialize the database for taler exchange
+function init_db() {
+
+  # Create the role taler-exchange-httpd and the database
+  su postgres << EOF
+psql postgres -tAc "DROP DATABASE IF EXISTS \"${DB_NAME}\";"
+psql postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='taler-exchange-httpd'" | \
+  grep -q 1 || \
+  createuser taler-exchange-httpd
+psql -tAc "SELECT 1 FROM pg_database WHERE datname='${DB_NAME}'" | \
+  grep -q 1 || \
+  createdb -O taler-exchange-httpd "${DB_NAME}"
+psql -tAc "CREATE EXTENSION IF NOT EXISTS pg_stat_statements"
+EOF
+ 
+  ssh -o StrictHostKeyChecking=no monitor.${DNS_ZONE} './scripts/database-centos.sh remote-init'
+
+  if [ "${SHARD_DB}" = "true" ]; then
+    configure_shard_access
+  fi
+
+  # Create the remote user "$DB_USER" and load pg_stat_statements for metrics
+  su postgres << EOF
+psql postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='${DB_USER}'" | \
+  grep -q 1 || \
+  psql << END
+    CREATE USER "${DB_USER}" with encrypted password '${DB_PASSWORD}';
+END
+EOF
+
+  # Grant access to the databse to the remote user
+  su taler-exchange-httpd -s /bin/bash << EOF
+psql -d "${DB_NAME}"
+GRANT SELECT,INSERT,UPDATE ON ALL TABLES IN SCHEMA public TO "${DB_USER}";
+GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO "${DB_USER}";
+EOF
+
+  enable_remote_access
+  systemctl restart postgresql
+}
+
+case ${1} in 
+  init)
+    setup_disks
+    setup_config
+    restart_rsyslog
+    ;;
+  remote-init)
+    remote_init_db
+    ;;
+  *)
+    taler_perf_help $0 "$INFO_MSG" "$OPT_MSG"
+    ;;
+esac
+
+exit 0
diff --git a/experiment/scripts/run.sh b/experiment/scripts/run.sh
index 11d298b..8ea1adb 100644
--- a/experiment/scripts/run.sh
+++ b/experiment/scripts/run.sh
@@ -28,7 +28,11 @@ elif [[ "${HOSTNAME}" =~ ${DB_HOSTS} ]]; then
     PROMTAIL_LOG_PORT=1515
     setup_log
     enable_logrotate
-    exec ~/scripts/database.sh init
+    if grep -q "Red Hat" /proc/version; then 
+      exec ~/scripts/database-centos.sh init
+    else
+      exec ~/scripts/database.sh init
+    fi
 elif [[ "${HOSTNAME}" =~ ${SHARD_HOSTS} ]]; then 
     setup_log
     enable_logrotate
diff --git a/notes.txt b/notes.txt
index 99dd902..e69de29 100644
--- a/notes.txt
+++ b/notes.txt
@@ -1,3 +0,0 @@
-dahu-26.grenoble.grid5000.fr|dahu-2.grenoble.grid5000.fr|dahu-24.grenoble.grid5000.fr|dahu-28.grenoble.grid5000.fr
-
-case ${HOSTNAME} in ${SHARD_HOSTS}) echo yes;; *) echo no;; esac