2.2.1 Database Server

This section provides instructions on how to set up the Sensys DB. These instructions are meant to be executed on the server that will contain the database. At the moment, Sensys provides support for PostgreSQL, so the following sections will provide instructions on how to set up the database using this DBMS.

To complete the following steps, the following file is needed: “orcmdb_psql.sql” (found in the Sensys repository in the “contrib/database” directory).

NOTE: the following instructions will use the following settings as reference, but the database administrator may choose to use different settings:

• Database instance name: orcmdb
• Database user: orcmuser

####2.2.1.1 Software Requirements

#####2.2.1.1.1 Requirements for PostgreSQL

Package	Version	Req.	Notes
PostgreSQL Server	9.3 or higher	Yes	Required on database server
PostgreSQL Client	9.3 or higher	Yes	Required on database server

NOTE: Client may be installed on any machine for administrative tasks: testing the database connection, data and schema management, etc.

####2.2.1.2 Installation Overview At a high level, installing the database requires the following steps:

1. Installing the DBMS
2. Performing some configuration tasks (e.g. enabling remote access to the database)
3. Creating the database
4. Performing basic DBA tasks: creating users and roles

####2.2.1.3 Notes On User Privileges For simplicity, the following steps provide instructions for creating a single database user with all the privileges. However, it’s recommended to create roles and set privileges appropriately. It’s up to the DBA to decide this and it will depend on the number of users that need to be managed and on organization policies.

General recommendations regarding users and privileges:

• A separate administrative user should be created and it should be used to create the database.
• Roles should be used to manage user privileges. Administrative users should have all privileges on the database while regular users should be restricted (depending on the data they need to access for their tasks).
• The standard Sensys user should have the following privileges:
  • Select, insert, update and delete privileges on all tables
  • Execute privileges on all stored procedures

####2.2.1.4 Preparing the Server

#####2.2.1.4.1 PostgreSQL installation

1. Install the PostgreSQL server and client
   • Please refer to the PostgreSQL documentation for installation instructions
   • PostgreSQL installation wiki
2. Verify the installation by starting the postgresql service

   service postgresql start
   

   • NOTE:
     • Depending on the version, before being able to start the service it may be necessary to execute:

       service postgresql initdb`
       

     • If desired, the service may be configured to start automatically:

       chkconfig postgresql on
       

     • The actual name of the service may vary (e.g. “postgresql-9.3”)
     • These commands need to be run with administrative privileges
3. Enable external TCP connections to the postgresql service
   • Make sure the firewall is configured to allow incoming connections to the postgresql service port (5432 by default)
   • Enable client authentication
     • Edit the “pg_hba.conf” configuration file
       • The file location may vary depending on the installation package used
       • For example:
         • “/etc/postgresql/9.3/main”
         • “/var/lib/pgsql/9.3/data/”
     • The file contains detailed instructions on how to add authentication configuration options
     • At the very least, external connections should be allowed to the orcmdb database
     • Recommendation: start with basic password authentication and try more secure configurations once this is working
   • Enable networking for PostgreSQL
     • Edit the “postgresql.conf” configuration file
     • Edit the following line to specify what IP addresses to listen on:

       listen_addresses = <comma-separated list of addresses>
       

       • NOTE: use '*’ to specify all
4. Create orcmuser
   • Use the createuser command as the default postgres user:

     % sudo –u postgres createuser –P orcmuser
     

   • NOTE:
     • This command will prompt the user for a password. Please choose a strong password.
     • To verify if the user was created successfully, execute '\du' from a psql session.
     • Depending on the authentication configuration in “pg_hba.conf” for local connections, the orcmuser may not be allowed to execute this command. An alternative for handling this is to enable password authentication for local connections (at least temporarily)
5. Create the orcmdb database. NOTE: this requires code from the Sensys repository under the "contrib/database" directory.

   1. Create the database:

      % sudo –u postgres createdb --owner orcmuser orcmdb
      

   2. Use Alembic tool to setup the database schema:

      1. Install SQLAlchemy version 0.9.2 or higher.

         % sudo pip install --upgrade SQLAlchemy
         

      2. Install Alembic version 0.8.2 or higher.

         % sudo pip install --upgrade Alembic
         

      3. Set the database connection info in PG_DB_URL environment variable. Alternative this can be set it in the schema_migration.ini file or pass to the "alembic" command using "-x db_url" option.

         % export PG_DB_URL=postgresql://[username[:password]]@host[:port]/database
         

      4. Run the migration code to bring the database to the current schema version

         % cd contrib/database
         % python setup_orcmdb.py --alembic-ini=schema_migration.ini
         

6. Verify the installation
   • Make sure the database server is listening on a port

     netstat -plane |grep postmaster
     

   • Connect to the database from a remote machine:

     psql –-host=<hostname or IP address> -–username=orcmuser –-dbname=orcmdb --password
     

   • List the database’s tables:

     \dt
     

     • Here are some of the tables that will be listed
       • data_item
       • data_sample
       • event
       • event_type
       • fru
       • fru_type
       • job
       • job_node
       • maintenance_record
       • node
     • See 4.3 Sensys DB Schema for the DB schema diagrams