Adding the Gateway Database (One Computer Configuration)

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

  1. 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

  1. 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):

  1. 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.

  1. 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-10B6E0AD572D

Device Start End Sectors Size Type
/dev/sdb1 2048 468862094 468860047 223.6G Linux filesystem

  1. 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

  1. 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, 23887872

Allocating 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.

  1. 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.

  1. 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
  1. 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

  1. Next mount the disk
    $ sudo mount -v -a
/                        : ignored
/boot/efi                : already mounted
none                     : ignored
/mnt/ts_gateway          : successfully mounted
  1. 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

  1. 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

  1. 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).

  1. Create a database user with the username cmix, which is used to access the database.
    $ sudo -u postgres createuser --createdb --pwprompt cmix

  2. Change to the postgres user account
    $ sudo su postgres

  3. 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 #.

  1. 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

  1. Quit the postgres client software.
    # \q

  2. Exit the postgres user account.
    $ exit

  3. Create the database.
    $ sudo -u postgres createdb -D ts_gateway -O cmix cmix_gateway

  4. 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)
  1. 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)
  1. Quit the postgres client software.
    # \q

  2. Exit the postgres user account.
    $ exit

  3. Modify the gateway.yaml file.
    $ nano /opt/xxnetwork/gateway.yaml

  4. 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"
  1. 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

8 Likes

Hello.
Shouldn’t it be “/ mnt / ts_gateway” instead of “/ home / ubuntu / ts_gateway”?

Yes. I’ve corrected the mistake.

:+1:

Great post!
One question. Should I restart something to make changes happen?
Also, on last step dbAddress: “0.0.0.0:5432” ← I had to change my node and gateway ports, but this one is related to the database and not to any of these services right?
On the node.yaml file it’s the same.

Very good manual! THX! :ok_hand: :+1:

You do not need to make any changes to the dbAddress: “0.0.0.0:5432”

That is the correct setting.

Very nice manual! I completed the steps sometime after round [80338600] today and restarted the node+gateway server, and I see that several rounds after that are successful.

How do I know if the upgrade has been successful? Do I have to wait until March 25th, or can I find out already.

I would suggest some kind of step in the manual to say what needs to be done to confirm that the upgrade has worked.

1 Like

/dev/sdb1 /mnt/ts_gateway ext4 defaults 0 0

“The only thing you may need to change is /dev/sdb1 Change it to the appropriate SSD partition’s name, for example /dev/nvme0n1” - if we mount a new partition, then by analogy, we probably need to use nvme1n1 and not nvme0n1?

As an example my setup:

nvme0n1 931,5G live
├─nvme0n1p1 512M vfat
├─nvme0n1p2 1M
└─nvme0n1p3 931G ext4 /
nvme1n1 465,8G live
└─nvme1n1p1 465,8G ext4 /mnt/ts_gateway

~~> /dev/nvme1n1p1 /mnt/ts_gateway ext4 defaults 0 0

1 Like

You’ve just readied the database. No upgrade has happened yet. The binaries will be updated via the wrapper script on 3/18.

If you want to confirm the database is there and the password you stored in the gateway.yaml is correct, you can do …

$ psql -h 0.0.0.0 -U cmix -d cmix_gateway
Password for user cmix: 
psql (10.16 (Ubuntu 10.16-0ubuntu0.18.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

cmix_gateway=> \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)

cmix_gateway=> \q

Your output may be different but as long as you can connect to the gateway database, it’s a good indication you set everything up correctly.

2 Likes

I want to write a small addition.
I was the first to encounter this, and after I solved the problem, I helped a few more guys from discord and from the telegram chat.

The fact is that if you use an nvme disk (m2) for your computer as a new disk for the gate, then you take it one of the PCI interfaces. Therefore, after adding it, you may have problems with your network card and internet connection. The network card also occupies one of the PCI interfaces, and after adding a new disk, the interface number will change.

therefore, if you have problems with your internet connection, you need to do the following

ls -la /etc/netplan/

most likely, you will see a file in this folder with the following name " 00-installer-config.yaml"

sudo nano /etc/netplan/00-installer-config.yaml

we will see the contents of the file

network:
  ethernets:
    enp5s0:
      dhcp4: true
  version: 2

change it enp5s0 on enp6s0. (if you have a different number, then just increase by one)

network:
  ethernets:
    enp6s0:
      dhcp4: true
  version: 2

save file and reboot system

check in internet

more information can be found here Network - Configuration | Ubuntu

4 Likes

Thank you for the answer. I have confirmed that the database is there and can connect to the gateway database.

1 Like

Thanks for a great post.
Need to open and forward ports 5432?

No, do not do that.

thank

thx! :+1:

Выражаю благодарность за этот гайд очень помог ! еще раз спасибо огромное! предлагаю поставить согласовать на премию!
I express my gratitude for this guide that helped a lot ! thank you again very much! I suggest you put it on the bonus!

1 Like