In the near future we will be releasing an update that requires operators to add a database on the gateway computer.
Currently, the handbook only covers the setup of the node database. We will be revising the handbook once the update has been released but in the meantime, I wanted to get a head start so operators have time to setup the gateway database and get help if they run into any problems.
There are two hardware configurations listed in the hand handbook:
- Two Computers: The node and gateway processes are run on different computers.
- One Computer: The node and gateway processes are run on the same computer.
If you run the node and gateway software on two different computers, please refer to Adding the Gateway Database (Two Computers)
This post is for operators who run the node and gateway software on the same computer and have a second SSD for the gateway database data.
PART ONE: PREPARE THE SECOND DISK FOR THE DATABASE
TL;DR PART ONE will walk you through how to identify, partition, mount and create a directory for the gateway database data on a second disk. If you have already mounted the second disk in the computer, you can move down to PART TWO: SETUP THE DATABASE
PREFACE
It is typical that when installing Ubuntu Server, the root, boot and swap partitions are located on the first physical disk. Often the second physical disk is not partitioned nor mounted during the OS installation.
Before we get started, some basic information about how Linux handles physical disks, partitions and mounting. Linux doesn’t name physical disks and partitions like Windows, where disks auto-mount and are named C, D, etc. on all Windows computers.
Physical disks can be identified, modified and mounted by using a few programs. I will be using programs named lsblk, fdisk, mkfs.ext4 and mount.
- lsblk will be used to show disk and partition information.
- fdisk will be used to create a partition.
- mkfs.ext4 will be used to format a partition.
- mount will be used to mount a partition.
I have two SATA SSDs in the NodeLab node. Therefore Linux named the disks sda and sdb. If they were NVMe SSDs they would be named nvme0n1 and nvme1n1. I’ll explain.
After I installed Ubuntu Server on the NodeLab computer, the following was shown when I run …
$ lsblk --output NAME,SIZE,FSTYPE,MOUNTPOINT,STATE
NAME SIZE FSTYPE MOUNTPOINT STATE
sda 953.9G running
├─sda1 512M vfat /boot/efi
└─sda2 953.4G ext4 /
sdb 223.6G running
└─sdb1 223.6G
The NAME column shows 2 physical disks, sda and sdb.
sda had two partitions, sda1 and sda2.
├─sda1 is the boot partition (/boot/efi).
├─sda2 is the root partition (/).
sdb had no partitions nor was it mounted. This is where I want to store the gateway database data.
If these were NVMe SSDs, it would be similar but the 2 physical disks would be named, nvme0n1 and nvme1n1.
nvme0n1 would have two partitions, nvme0n1p1 and nvme0n1p2.
├─nvme0n1p1 would be the boot partition (/boot/efi).
├─nvme0n1p2 would be the root partition (/).
nvme1n1 would have no partitions nor be mounted.
INSPECT YOUR DISKS
- Not all computers are the same so look at the SSDs. Please run the following …
$ lsblk --output NAME,SIZE,FSTYPE,MOUNTPOINT,STATE
NOTE: In the commands that follow I use /dev/sdb or dev/sdb1. sdb was the disk with no partitions. Again, this is where I want to store the gateway database data. If the output of the previous command shows a differently named empty SSD, you must make changes to accommodate the name of the SSD on your computer. For example, if the output shows nvme1n1 is the disk with no partitions, then you must use /dev/nvme1n1 or /dev/nvme1n1p1 respectively as you proceed. I will keep reminding you!
CREATE A NEW PARTITION
- To create a new partition on sdb I used fdisk. I ran the following …
$ sudo fdisk /dev/sdb
This starts the fdisk program. As previously noted, if you have an NVMe SSD, then you should change /dev/sdb to the correct device, for example /dev/nvme1n1
You will see the following …
Welcome to fdisk (util-linux 2.31.1).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.Command (m for help):
- I pressed nEnter to create a new partition. You will be prompted for user input a few times, just press Enter to all questions to accept the defaults. It looked like this …
Command (m for help): n
Partition type
p primary (0 primary, 0 extended, 4 free)
e extended (container for logical partitions)
Select (default p):
Using default response p.
Partition number (1-4, default 1):
First sector (34-468862094, default 2048):
Last sector, +sectors or +size{K,M,G,T,P} (2048-468862094, default 468862094):Created a new partition 1 of type ‘Linux filesystem’ and of size 223.6 GiB.
- Check the newly created partition, sdb1, by entering pEnter
Command (m for help): p
Disk /dev/sdb: 223.6 GiB, 240057409536 bytes, 468862128 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: gpt
Disk identifier: 8B2E5263-BC7A-4F9E-B8C1-10B6E0AD572DDevice Start End Sectors Size Type
/dev/sdb1 2048 468862094 468860047 223.6G Linux filesystem
- Last, write the new partition scheme by entering wEnter
Command (m for help): w
The partition table has been altered.
Calling ioctl() to re-read partition table.
Syncing disks.
That completes creating a new partition.
FORMATTING THE NEW PARTITION
As previously noted, if you have an NVMe SSD, then you should change /dev/sdb1 to the correct device, for example /dev/nvme1n1p1
- Format the new partition with …
$ sudo mkfs.ext4 /dev/sdb1
mke2fs 1.44.1 (24-Mar-2018)
Discarding device blocks: done
Creating filesystem with 58607505 4k blocks and 14655488 inodes
Filesystem UUID: bd0f520b-ade5-4988-9fa5-b757462b2b22
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424, 20480000, 23887872Allocating group tables: done
Writing inode tables: done
Creating journal (262144 blocks): done
Writing superblocks and filesystem accounting information: done
That completes formatting the new partition.
By default PostgreSQL stores database data in a subdirectory of /var/lib/postgresql
, which is located on the first SDD (sda) in the root partition, sda2 (/). Since we’re going to store the database data on the second physical disk, sdb, I created a new partition on sdb and formatted the new partition (sdb1).
PERMANENTLY MOUNTING THE NEW PARTITION
Create a new mount point (/mnt/ts_gateway), mount the partition permanently, and change the ownership of the ts_gateway directory so PostgreSQL can write to it.
- To create the mount point I ran…
$ sudo mkdir -v /mnt/ts_gateway
mkdir: created directory ‘/mnt/ts_gateway’
Modify the file that determines which partitions are mounted when the computer boots up. It is /etc/fstab.
You should always create a backup before you modify any files in /etc.
- Create a backup of the fstab by running …
$ sudo cp -v /etc/fstab /etc/fstab.bak
‘/etc/fstab’ → ‘/etc/fstab.bak’
My computer’s /etc/fstab is as follows and yours will be different so don’t worry if it is, we’re interested in what needs to be added …
/dev/disk/by-uuid/69f714d4-7e02-4d72-bba4-4b166ba3147d / ext4 defaults 0 0 /dev/disk/by-uuid/441E-EF61 /boot/efi vfat defaults 0 0 /swap.img none swap sw 0 0
- Use nano to edit the file …
$ sudo nano /etc/fstab
And add the following…
/dev/sdb1 /mnt/ts_gateway ext4 defaults 0 0
Note
- The only thing you may need to change is /dev/sdb1 Change it to the appropriate SSD partition’s name, for example /dev/nvme1n1p1.
- It is customary to use a Tab between each item, not Space.
- The last two items are zeros.
Save the file by pressing Ctrl+s then Ctrl+x
- Next mount the disk
$ sudo mount -v -a
/ : ignored /boot/efi : already mounted none : ignored /mnt/ts_gateway : successfully mounted
- Inspect the disks again and see what you have …
$ lsblk --output NAME,SIZE,FSTYPE,MOUNTPOINT,STATE
sdb1 is mounted at /mnt/ts_gateway
NAME SIZE FSTYPE MOUNTPOINT STATE sda 953.9G running ├─sda1 512M vfat /boot/efi └─sda2 953.4G ext4 / sdb 223.6G running └─sdb1 223.6G ext4 /mnt/ts_gateway
CHANGING OWNERSHIP
- Change ownership of the ts_gateway directory so database data can be written to it.
$ sudo chown -v postgres:postgres /mnt/ts_gateway/
changed ownership of ‘/mnt/ts_gateway/’ from root:root to postgres:postgres
- Now we can see it is owned by postgres
$ ls -la /mnt/
total 12 drwxr-xr-x 3 root root 4096 Mar 3 18:25 . drwxr-xr-x 24 root root 4096 Mar 3 16:02 .. drwx------ 4 postgres postgres 4096 Mar 3 18:51 ts_gateway
The second SSD should be ready to store the database data in /mnt/ts_gateway
PART TWO: SETUP THE DATABASE
In Part One we created a directory where PostgreSQL will store the gateway database data (/mnt/ts_gateway).
-
Create a database user with the username
cmix
, which is used to access the database.
$ sudo -u postgres createuser --createdb --pwprompt cmix
-
Change to the postgres user account
$ sudo su postgres
-
Run the postgres client software
$ psql
psql (10.14 (Ubuntu 10.14-0ubuntu0.18.04.1)) Type "help" for help. postgres=#
NOTE: The command prompt has changed from $ to #
Just as you don’t type the $, don’t type the #.
- Create the tablespace and point it to the directory on the second disk drive. You
# CREATE TABLESPACE ts_gateway LOCATION '/mnt/ts_gateway';
CREATE TABLESPACE
-
Quit the postgres client software.
# \q
-
Exit the postgres user account.
$ exit
-
Create the database.
$ sudo -u postgres createdb -D ts_gateway -O cmix cmix_gateway
-
Verify that the computer now has both a nodedb and cmix_gateway. Run the following three commands one after the other.
$ sudo su postgres
$ psql
# \l
List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges --------------+----------+----------+---------+---------+----------------------- cmix_gateway | cmix | UTF8 | C.UTF-8 | C.UTF-8 | nodedb | node | UTF8 | C.UTF-8 | C.UTF-8 | postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (5 rows)
- Verify the tablespace points to the directory on the second disk drive.
# \db
List of tablespaces Name | Owner | Location ------------+----------+------------------------- pg_default | postgres | pg_global | postgres | ts_gateway | postgres | /mnt/ts_gateway (3 rows)
-
Quit the postgres client software.
# \q
-
Exit the postgres user account.
$ exit
-
Modify the gateway.yaml file.
$ nano /opt/xxnetwork/gateway.yaml
-
Add the following code to the bottom of the gateway.yaml file.
# Database connection information dbUsername: "cmix" dbPassword: "PASSWORD" dbName: "cmix_gateway" dbAddress: "0.0.0.0:5432"
- Save and exit nano by pressing Ctrl+s then Ctrl+x
If you need any help, please feel free to DM me on the xx network Discord channel. My ID is Keith aka LordVetinari#9085 It might take me a few hours to get back to you as I am on Korean Standard Time.
Don’t panic, take a step back from the computer and I’ll be with you ASAP.
Thanks for running a node!
Keith