Database Server Documentation

General configuration options:

• hostname: McNairDBServ
• user full name: McNair Center
• username: mcnair
• don't encrypt home directory
• manual partitioning (see below for RAID 10 configuration)
• no automatic updates
• software: OpenSSH server, PostgreSQL database, and Samba file server

Database server drive partition specs:

• RAID 10 the four drives
• 1 MB out of each drive for bootloader
• use all of M.2 drive as swap space
• PCIe express drive format as linux partition (for PostgreSQL data directory)

Installing Ubuntu (2/19/2016)

First try at installation didn't work when the installer got to the partition disks step, because the SSD and M.2 drives weren't being detected for the partitioner. I suspected that there was a driver missing, but I don't see how we'd be able to install a driver without a proper bash shell (the installer does come with a shell, but it's very limited).

Kranthi and I took another look at the PCIe slots on the mobo and it turns out that the two PCIe slots that we had plugged the M.2 drive and SSD into were linked to CPU2, whereas the database server was only configured with one CPU, which was put in CPU1. From pages 2-19 and 2-20 of the mobo datasheeet, we put the M.2 drive in PCIE1 and the SSD in PCIE3, where are both linked to CPU1 and tried the installation process again.

This time, two new drives showed up in the partitioner (/dev/nvme0n1 and /dev/nvme1n1), but they're both listed as unknown (their capacities, however, are displayed as 400.1 GB and 512.1 GB respectively, which technically identifies the first as the SSD and the second as the M.2 drive). Going back to section 2.5 of the mobo datasheet suggests that we should assign each device in the expansion slot an IRQ (from my understanding, an IRQ is a number from 0 to 15 that marks the priority level of the device). To do so, we have to use the BIOS menu, so I aborted the installation and pressed the 'Delete' key during the boot up to get to the BIOS menu.

But through the entire tree of menus, I was only able to find an IRQ for Serial Port 1 and Serial Port 2, under NCT6779D Super IO Configuration. Wikipedia's article on interrupt requests notes that, "PCI Express does not have physical interrupt lines at all, and uses MSI exclusively." So I guess IRQs are no longer a big deal (they "went the way of the dodo," as Ed says). With no other way to label the PCIe drives from the BIOS menu making itself clear, I exited and rebooted the computer to proceed with the installation.

Interesting side note: the mobo datasheet notes that the motherboard comes with utilities to set up RAID arrays. But there doesn't seem to be any benefit of doing it on the motherboard over doing it in the Ubuntu partitioner.

In the partitioner, I deleted the partitions that were automatically generated and started each drive off with a new partition table. Then I partitioned the drives as follows:

• /dev/nvme0n1 (aka the 400 GB Intel SSD)
  • entire space (~400 GB) as primary partition. use as ext4 filesystem, mount point: /, bootable flag off.
• /dev/nvme1n1 (aka the 512 GB M.2 drive)
  • half (~256 GB) as primary partition. use as swap space (bootable flag off).
  • the other half (~256 GB) as primary partition. use as ext4 filesystem, mount: /var/postgresql (choose "Enter it manually" in the mount point selection menu), bootable flag off.
• for each of the four 3.0 TB hard drives (aka /dev/sda, /dev/sdb, /dev/sdc, and /dev/sdd)
  • 10 MB primary partition, use as "reserved BIOS boot area" (bootable flag can't be changed, so leave it set to off)
  • the rest of the space as a primary partition, use as ext4 filesystem, mount to /bulk (choose "Enter it manually" in the mount point selection menu), bootable flag off

Then choose "configure software RAID" to set up the software RAID device. Confirm the partitions. Then wait for the disks to be partitioned.

Choose "Create MD device" and then "RAID10" to begin. Use 2 active devices and 2 spare devices. For the two active devices, choose /dev/sda2 and /dev/sdb2. For the two spare devices, choose /dev/sdc2 and /dev/sdd2. Confirm the partitions that will be changed. Then wait for the device to be created. When you get back to the software RAID configuration menu, choose "Delete MD device" and then choose the software RAID device that was created (there should only be one, and it should be named something like "md0_raid10"). Note the device name (in this case, it is /dev/md0) and check that the component devices are correct. If they're correct, choose "No" (to not delete the software RAID device). This will take you back to the software RAID configuration menu, where you can choose "Finish" to set up the RAID device, which should now appear at the top of the partitioner.

Choose the 3.0 TB partition in the RAID device and configure it to use as ext4 filesystem and mount point /bulk (choose "Enter it manually" in the mount point selection menu). Then choose "Finish partitioning and write changes to disk" at the bottom of the partitioner menu. Confirm that the partition changes to be made are correct. Then wait for the partitions to be formatted.

After the system is installed, the installer will prompt for software selection. Choose OpenSSH server, PostgreSQL database, and Samba file server for installation. After it finishes, it will prompt if you want to install the GRUB bootloader onto the master boot record. From my experience with setting up the webserver, choose "No" and on the next screen, install the bootloader to "/dev/sda /dev/sdb /dev/sdc /dev/sdd" and then if all goes well, the installation will finish. Take the CD out and then choose "Continue" and the system will reboot, at which point you should be able to boot into Ubuntu!

User management

Adding Root Accounts to the box

Assuming that you have root, you can create user accounts on the box and give them root too. This isn't necessary for regular users - they just need a Postgres user account (see below). To add users to the box, the process is:

First create the users group, checking the last group number (5xx is the next one):

cat /etc/group
/usr/sbin/groupadd -g 5xx username 

Then add the user (it doesn't matter what you put for -p, it is going to be overwritten):

/usr/sbin/useradd -g username -G root -s /bin/bash -p xxxx -d /home/username -m username
      where g is the primary group, G is other groups, p sets a password, 
      d declares a home directory and m makes the directory

Change the user's password:

passwd username

And add the user to the sudoers file

echo 'username ALL=(ALL) ALL' >> /etc/sudoers

Deleting a user

To delete a user:

/usr/sbin/userdel -r roger 
where r removes the home directory

And to remove their group

/usr/sbin/groupdel username

And remove their entry from the sudoers file too if they had root.

Setting up the Samba Server

Based on this Ubuntu page, but this guide is also helpful. Make a backup copy of the smb.conf configuration file before editing it:

$ sudo cp /etc/samba/smb.conf /etc/samba/smb.conf_backup
$ sudo vi /etc/samba/smb.conf

Change the workgroup parameter (under the [global] section) to MCNAIRDB. (Note that the security parameter was removed in Samba version 4.0.0).

Then go to the bottom of the configuration file and add a new section, [bulk]:

[bulk]
   comment = McNair File Server Share
   path = /bulk
   browseable = yes
   guest ok = no
   read only = no
   create mask = 0755

Once you're done editing the configuration file, test the file with testparm:

$ testparm /etc/samba/smb.conf

The /bulk directory should already exist from installation, so change its permissions:

$ sudo chmod 770 /bulk
$ groupadd smbusers
$ chown :smbusers /bulk

You also need to add a user to the Samba database. I used the username "alexjiang" for the commands below, which can be replaced with whatever username needs to be added:

$ smbpasswd -a alexjiang
$ usermod -G smbusers alexjiang

Then restart the samba services:

$ sudo restart smbd
$ sudo restart nmbd

Then you can try using any Windows Explorer window to check if the file server is set up correctly.

Adding accounts for McNair Center Researchers

Add a user group:

cat /etc/group
/usr/sbin/groupadd -g 5xx username 

Then add the user (it doesn't matter what you put for -p, it is going to be overwritten):

/usr/sbin/useradd -g username -G smbusers -s /bin/bash -p xxxx -d /home/username -m username
      where g is the primary group, G is other groups, p sets a password, 
      d declares a home directory and m makes the directory
passwd username

Add the user's samba password:

$ smbpasswd -a alexjiang

Then restart the samba services:

$ sudo restart smbd
$ sudo restart nmbd

Mapping a drive to the Dbase server's bulk directory

In Windows:

Go to my computer -> Map Network Drive
Connect to:
 \\128.42.44.181\bulk
With username:
 MCNAIRDB\username

In Mac:

Go to Finder and hit CMD-k
Enter server address: 
 smb://128.42.44.181/bulk
With username:
 MCNAIRDB\username

Adding a dbase user and creating a dbase

Log on to the box as root then:

groupadd -g 112 postgres 
useradd -g postgres -s /bin/bash -p xxxx -d /home/postgres -m postgres
passwd postgres
mkdir /var/postgresql/data
chown postgres /var/postgresql/data

Change to postgres and initialize the dbase

su postgres
cd /usr/lib/postgresql/9.5/bin/
./initdb -D /var/postgresql/data

/etc/init.d/postgresql stop
/usr/lib/postgresql/9.5/bin/postgres -D /var/postgresql/data > logile 2>&1 &

To work as the postgres account.

Now add the user using:

createuser --interactive joe
(Answer y or n to whether you want the new role to be superuser, etc.)

If the user doesn't have an account on the box, then you'll need to add a database for them (use DBname of 'firstname_data' as a default):

createdb -O username DBname

Drop the dbase and the user with:

dropdb DBname
dropuser username

The 'research' user is created as follows:

createuser -P -s research

Change /etc/postgresql/9.3/main/pg_hba.conf from

# "local" is for Unix domain socket connections only
local   all             all           peer

To:

local   all             all           trust

Restart postgres to reload the configuration:

/etc/init.d/postgresql restart

McNair Center Users

Use the 'research' account to create dbases and log into them using psql

createdb -O research Test

postgres@McNairDBServ:~$ pg_ctl restart -D /var/lib/postgres-xc/DN2 -Z datanode
pg_ctl: could not open PID file "/var/lib/postgres-xc/DN2/postmaster.pid": Permission denied

The issue here seems to be that we have installed Postgres-XC and an entire database cluster...

installing