CDB-Server¶

Introduction¶

This page provides the information for installing a CDB-Server. There are two of these machines, they both host a web service interface to the Configuration DB along with an instance of the database. The two instances of the database are set up as primary and standby. A server in the MICE control room hosts the primary copy of the database. The web service interface on this server gives read and write access to the control room systems. The second server is in PPD, this hosts the standby copy of the database. The Public Interface has read only access to the standby, hence isolating the primary from any public access.

Overview¶

install and configure Tomcat
install and configure Configuration DB war file and configuration files
install and configure Postgresql

Detailed Instructions¶

• install and configure as per the Public Interface except do not change the Tomcat port

• install Postgresql repo
  

  wget http://www.pgrpms.org/9.1/redhat/rhel-5-x86_64/pgdg-sl91-9.1-6.noarch.rpm
  rpm -Uvh pgdg-sl91-9.1-6.noarch.rpm

  
  or for SL6:
  

  http://yum.postgresql.org/9.1/redhat/rhel-6-x86_64/pgdg-sl91-9.1-6.noarch.rpm
  

• bug in repo
  

  emacs /etc/yum.repos.d/pgdg-91-sl.repo -nw
      # change $releasever to 5

• install Postgresql
  

  yum install postgresql91-server postgresql91-jdbc check_postgres.noarch

Setting up the Database¶

• create pgsql-cdb and conf file
  

  ln -s postgresql-9.1 /etc/init.d/pgsql-cdb
  cat > /etc/sysconfig/pgsql/pgsql-cdb << EOF
  PGDATA=/var/lib/pgsql/data
  PGLOG=/var/lib/pgsql/pgstartup.log
  EOF

• need to mess around as raid has already been set up so /var/lib/pgsql/data is not empty
  

  mv /var/lib/pgsql/data /var/lib/pgsql/dataZ
  /sbin/service pgsql-cdb initdb
  mv /var/lib/pgsql/data/* /var/lib/pgsql/dataZ/
  mv /var/lib/pgsql/data/pg_xlog/* /var/lib/pgsql/dataZ/pg_xlog/
  rm -rf /var/lib/pgsql/data
  mv  /var/lib/pgsql/dataZ /var/lib/pgsql/data

Access Control¶

• create password file, replacing password with the database root password
  

  cat > /var/lib/pgsql/.pgpass << EOF
  localhost:*:*:postgres:password
  EOF

• change mode of password file
  

  chmod 0600 /var/lib/pgsql/.pgpass

Primary Server¶

• restrict access to database
  

  emacs /var/lib/pgsql/data/pg_hba.conf -nw
      # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
      local   cdb         mice,supermouse                   md5     
      local   cdb         all                               reject
  
      host    cdb         mice,supermouse 127.0.0.1/32      md5     
      host    cdb         all         0.0.0.0/0             reject

• allow access to the MICE control room
  

  /sbin/iptables -I INPUT -s MICE_control_room -i eth0 -d 0.0.0.0/0  -p TCP --dport 8080 -j ACCEPT
  /sbin/iptables save

Standby Server¶

• restrict access to database
  

  emacs /var/lib/pgsql/data/pg_hba.conf -nw
      # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
      local   cdb         mice,supermouse                   md5     
      local   cdb         all                               reject
  
      host    cdb         mice,supermouse 127.0.0.1/32      md5     
      host    cdb         mice        130.246.44.152/32     md5
      host    cdb         all         0.0.0.0/0             reject

• allow access via the public interface
  

  emacs /var/lib/pgsql/data/postgresql.conf -nw
      listen_addresses = '130.246.44.152'

• allow access to the MICE control room and the public interface
  

  /sbin/iptables -I INPUT -s MICE_control_room -i eth0 -d 0.0.0.0/0  -p TCP --dport 8080 -j ACCEPT
  /sbin/iptables -I INPUT -s Public_interface -i eth0 -d 0.0.0.0/0  -p TCP --dport 5432 -j ACCEPT
  /sbin/iptables save

Archiving¶

• in order to turn on logging and keep backups follow the instructions at Archiving

Replication¶

• in order to set up replication follow the instructions at Database Replication

Ensure All Files Belongs to postgres¶

• change permissions, backupbot should be in the postgres group and needs access so it can back up the logs
  

  chown -R postgres:postgres /var/lib/pgsql/
  chmod 750 /var/lib/pgsql
  chmod 750 /var/lib/pgsql/data
  chmod 750 /var/lib/pgsql/data/pg_xlog

Nagios¶

• some notes on Nagios can be found at Nagios

Start Her Up¶

• start postgresql
  

  /sbin/chkconfig pgsql-cdb on
  /sbin/service pgsql-cdb start