path: root/experiment/scripts/database-centos.sh

#!/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