Project

General

Profile

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

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