GCP AlloyDB Blog Series Part 2 : Install AlloyDB Omni

Posted on by

7

In the Part 1 of AlloyDB Blog Series, I discussed the Introduction of AlloyDB for PostgresSQL. Today in Part 2, I will discuss how to do the installation of AlloyDB Omni in a GCE VM.

AlloyDB Omni

Google has a nice page detailing how to install AlloyDB Omni and Install AlloyDB Omni on a VM. It provides detail about how to install and configure AlloyDB. My approach is slightly different than the above two links. But the basic of the installation is the same. The purpose of this post is to execute the commands below with a few environment variable changes.

Step 1 : Create GCE VM

Set environment variables. Make sure to replace the values in your environment. There are a few changes I made differently what Google recommends

  • CPU : Ideally you want to have minimum of 16 CPU for large data volume. For my environment, 4 CPU is enough for my testing and also save cost as well.
  • Disk Storage : The recommended size of disk is at least 100GB. But I decided to use 50 GB instead.
  • OS : Google use Debian based OS. I personally prefer Centos or Redhat as this is the popular linux OS my customers usually prefer. In this example, I use Centos OS.
  • OS Version : Initially I tried Centos 7 and it failed during Omnidb installation due to filesystem tmpfs. Then I moved up to Centos 8, it still caused issue in CGroup V2. I could manually perform the changes for CGroup V2. For simplicity, I just moved up again to Centos 9, which finally fixed all of my issues.
  • Private IP address : I don’t like create VM with public IP. So I add no-address flag to create private VM. Of course, OS Login needs to be enabled as well.
  • SSH Connection : There are a few ways to connect the private VM. My preference is using IAP using SSH Tunnel.
export VM_NAME=omnidb1
export VM_DISK_SIZE=50
export VM_MACHINE_TYPE=n2-highmem-4
export VM_OS_IMAGE=projects/centos-cloud/global/images/centos-stream-9-v20230711
export GCP_PROJECT=<put your project id here>
export GCP_ZONE=us-west4-b 
export GCP_SUBNET=<put your subnet here>
export GCP_SERVICE_ACCOUNT=<put your GCP Service account here>

Run the following script to create VM

gcloud compute instances create $VM_NAME \
    --project=$GCP_PROJECT \
    --zone=$GCP_ZONE \
    --machine-type=$VM_MACHINE_TYPE \
    --network-interface=network-tier=PREMIUM,subnet=$GCP_SUBNET,no-address \
    --metadata=enable-os-login=true \
    --no-address \
    --maintenance-policy=MIGRATE \
    --provisioning-model=STANDARD \
    --service-account=$GCP_SERVICE_ACCOUNT \
    --scopes=https://www.googleapis.com/auth/devstorage.read_write,https://www.googleapis.com/auth/logging.write,https://www.googleapis.com/auth/monitoring.write,https://www.googleapis.com/auth/servicecontrol,https://www.googleapis.com/auth/service.management.readonly,https://www.googleapis.com/auth/trace.append \
    --create-disk=auto-delete=yes,boot=yes,device-name=instance-1,image=$VM_OS_IMAGE,mode=rw,size=$VM_DISK_SIZE,type=projects/alloydb-omni/zones/us-central1-a/diskTypes/pd-ssd

Once the VM is created, using the following command to connect to VM:

gcloud compute ssh --zone $GCP_ZONE $VM_NAME --tunnel-through-iap --project $GCP_PROJECT

Step 2 : Install required packages

There are a few notes for my installation of packages:

  • AlloyDB Omni requires Docker installation. So I include Docker installation.
  • Because I will need to use TPCH to generate testing data. My prefer way is to generate the data by my self using TPCH’s dbgen script. This script requires the compilation. So I include the installation of make and “Development Tools”
sudo yum install -y yum-utils wget unzip make
sudo yum group install -y "Development Tools"

Step 3 : Install and Configure Docker

The following steps show how to install, configure and test Docker in the VM.

sudo yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo
sudo yum install -y docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin

sudo yum install -y docker
sudo systemctl status docker
sudo systemctl start docker
sudo systemctl enable docker
sudo systemctl status docker

sudo docker run hello-world

Step 4 : Create a user for db activities

This step is optional. You can use whatever user id you ssh to the VM and perform the rest steps below. But personally, I prefer to create a separate user as a better way to manage the activities down in the road. I also set the time zone to the right time zone as well.

export ALLOYDB_USER=alloyuser
sudo useradd -g adm -G docker,google-sudoers ${ALLOYDB_USER}
groups
id ${ALLOYDB_USER}
sudo timedatectl set-timezone America/Chicago
ls -l /etc/localtime
sudo su ${ALLOYDB_USER}
cd
pwd

Step 5 : Install AlloyDB Omni

Execute the following code as it is, it will install the AlloyDB Omni database if successful. 

docker pull gcr.io/alloydb-omni/pg-service:latest
docker pull gcr.io/alloydb-omni/memory-agent:latest
docker image ls

cd
mkdir install
cd install
gsutil ls gs://alloydb-omni-install
gsutil cp -r gs://alloydb-omni-install/$(gsutil cat gs://alloydb-omni-install/latest) .
ls -l
cd $(gsutil cat gs://alloydb-omni-install/latest)

ls -l
tar -xzf alloydb_omni_installer.tar.gz && cd installer
sudo bash install_alloydb.sh

If successful, the result will be something as follows:

[alloyuser@omnidb weidong.zhou]$ docker pull gcr.io/alloydb-omni/pg-service:latest
latest: Pulling from alloydb-omni/pg-service
267f288e9329: Pull complete 
0b57a5d95214: Pull complete 
7d920f8a053e: Pull complete 
afcae9374567: Pull complete 
849d91eb7ea9: Pull complete 
Digest: sha256:1376b0faa50a9fac95a1c5777eafca3e38bc2b9d8e1883cf8857fba7474cdb1e
Status: Downloaded newer image for gcr.io/alloydb-omni/pg-service:latest
gcr.io/alloydb-omni/pg-service:latest
[alloyuser@omnidb weidong.zhou]$ docker pull gcr.io/alloydb-omni/memory-agent:latest
latest: Pulling from alloydb-omni/memory-agent
267f288e9329: Already exists 
0b57a5d95214: Already exists 
aff0e617551a: Pull complete 
Digest: sha256:8c60aa4dacd6d2e3ab7d19255cd3e0c0205dd011838e9f3c9e020301a5c12f93
Status: Downloaded newer image for gcr.io/alloydb-omni/memory-agent:latest
gcr.io/alloydb-omni/memory-agent:latest
[alloyuser@omnidb weidong.zhou]$ docker image ls
REPOSITORY                         TAG       IMAGE ID       CREATED        SIZE
gcr.io/alloydb-omni/pg-service     latest    390a2d7a12a8   8 days ago     1.89GB
gcr.io/alloydb-omni/memory-agent   latest    5f4bedeb8ff3   8 days ago     204MB
hello-world                        latest    9c7a54a9a43c   3 months ago   13.3kB
[alloyuser@omnidb weidong.zhou]$ cd
[alloyuser@omnidb ~]$ mkdir install
[alloyuser@omnidb ~]$ cd install
[alloyuser@omnidb install]$ gsutil ls gs://alloydb-omni-install
gs://alloydb-omni-install/latest
gs://alloydb-omni-install/alloydb-omni-0.1.0-preview-postgresql-14.4/
gs://alloydb-omni-install/alloydb-omni-0.2.0-preview-postgresql-14.4/
gs://alloydb-omni-install/alloydb-omni-0.3.0-preview-postgresql-15.2/
[alloyuser@omnidb install]$ gsutil cp -r gs://alloydb-omni-install/$(gsutil cat gs://alloydb-omni-install/latest) .
Copying gs://alloydb-omni-install/alloydb-omni-0.3.0-preview-postgresql-15.2/alloydb_omni_installer.tar.gz...
/ [1 files][ 12.6 KiB/ 12.6 KiB]                                                
Operation completed over 1 objects/12.6 KiB.                                     
[alloyuser@omnidb install]$ ls -l
total 0
drwxr-xr-x. 2 alloyuser adm 43 Aug 16 07:34 alloydb-omni-0.3.0-preview-postgresql-15.2
[alloyuser@omnidb install]$ cd $(gsutil cat gs://alloydb-omni-install/latest)
[alloyuser@omnidb alloydb-omni-0.3.0-preview-postgresql-15.2]$ ls -l
total 16
-rw-r--r--. 1 alloyuser adm 12958 Aug 16 07:34 alloydb_omni_installer.tar.gz
[alloyuser@omnidb alloydb-omni-0.3.0-preview-postgresql-15.2]$ tar -xzf alloydb_omni_installer.tar.gz && cd installer
[alloyuser@omnidb installer]$ sudo bash install_alloydb.sh
2023-08-16 07:34:46.007 CDT: [install_alloydb.sh:59] Starting installation of AlloyDB Omni...
2023-08-16 07:34:46.009 CDT: [install_alloydb.sh:61] Checking hardware requirements for AlloyDB Omni...
2023-08-16 07:34:46.015 CDT: [install_alloydb.sh:64] Checking software requirements for AlloyDB Omni...
2023-08-16 07:34:46.090 CDT: [install_alloydb.sh:67] Configuring installation files...
2023-08-16 07:34:46.105 CDT: [install_alloydb.sh:70] Setting up postgres user...
2023-08-16 07:34:46.172 CDT: [install_alloydb.sh:73] Configuring systemd services...
Created symlink /etc/systemd/system/multi-user.target.wants/alloydb-setup-env.service → /etc/systemd/system/alloydb-setup-env.service.
Created symlink /etc/systemd/system/multi-user.target.wants/alloydb-dataplane.service → /etc/systemd/system/alloydb-dataplane.service.
2023-08-16 07:34:46.548 CDT: [install_alloydb.sh:76] Copying configuration files...
2023-08-16 07:34:46.558 CDT: [install_alloydb.sh:79] Creating internal directories...
2023-08-16 07:34:46.562 CDT: [install_alloydb.sh:82] Finished installing AlloyDB Omni

Please note: you may run into the error like “FATAL: AlloyDB Omni requires cgroups V2 to run.” There are some manual steps to configure cgroups for Centos 7 or 8. But the easiest way is to use Centos 9.

Step 6 : Post installation of AlloyDB Omni

I need to change certain configuration in the AlloyDB Omni. In the code below, I also show the difference where the configuration changes.

sudo cp -p /var/alloydb/config/dataplane.conf /var/alloydb/config/dataplane.conf.orig
sudo sed -i "s|^\(DATADIR_PATH=\).*|\1/home/$USER/alloydb-data|" \
/var/alloydb/config/dataplane.conf
sudo diff /var/alloydb/config/dataplane.conf /var/alloydb/config/dataplane.conf.orig

cat /var/alloydb/config/postgresql.conf > newflags.conf

Make sure to run the following lines in one command

echo "
google_columnar_engine.enabled=on
google_columnar_engine.enable_auto_columnarization=on
google_columnar_engine.enable_auto_columnarization_schedule='EVERY 7 DAYS'
google_columnar_engine.memory_size_in_mb = 3000
work_mem = 64MB
 " >> newflags.conf

sudo cp newflags.conf /var/alloydb/config/postgresql.conf

Starts the AlloyDB Omni’s dataplane service

sudo systemctl start alloydb-dataplane
sudo systemctl status alloydb-dataplane
sudo systemctl enable alloydb-dataplane
sudo docker ps

Verify the AlloyDB Omni

sudo docker exec -it pg-service psql -h localhost -U postgres
\conninfo
SHOW server_version;
\q

The followings are the result from command execution above:


The followings are the result from command execution above:

[alloyuser@omnidb installer]$ sudo cp -p /var/alloydb/config/dataplane.conf /var/alloydb/config/dataplane.conf.orig
[alloyuser@omnidb installer]$ sudo sed -i "s|^\(DATADIR_PATH=\).*|\1/home/$USER/alloydb-data|" \
/var/alloydb/config/dataplane.conf
[alloyuser@omnidb installer]$ sudo diff /var/alloydb/config/dataplane.conf /var/alloydb/config/dataplane.conf.orig
4c4
< DATADIR_PATH=/home/alloyuser/alloydb-data
---
> DATADIR_PATH="/path/to/alloydb/omni/data"
[alloyuser@omnidb installer]$ cat /var/alloydb/config/postgresql.conf > newflags.conf
[alloyuser@omnidb installer]$ echo "
google_columnar_engine.enabled=on
google_columnar_engine.enable_auto_columnarization=on
google_columnar_engine.enable_auto_columnarization_schedule='EVERY 7 DAYS'
google_columnar_engine.memory_size_in_mb = 3000
work_mem = 64MB
 " >> newflags.conf
[alloyuser@omnidb installer]$ sudo cp newflags.conf /var/alloydb/config/postgresql.conf
[alloyuser@omnidb installer]$ sudo systemctl start alloydb-dataplane
[alloyuser@omnidb installer]$ sudo systemctl status alloydb-dataplane
● alloydb-dataplane.service - AlloyDB Data plane
     Loaded: loaded (/etc/systemd/system/alloydb-dataplane.service; enabled; preset: disabled)
     Active: active (exited) since Wed 2023-08-16 07:40:22 CDT; 10s ago
    Process: 94951 ExecStart=/bin/bash /opt/alloydb/scripts/start_alloydb.sh (code=exited, status=0/SUCCESS)
   Main PID: 94951 (code=exited, status=0/SUCCESS)
        CPU: 749ms

Aug 16 07:40:21 omnidb.us-west4-b.c.dbtest2023-230806.internal bash[95056]: 2023-08-16 07:40:21.028 CDT: [start_alloydb.sh:73] Configu>
Aug 16 07:40:21 omnidb.us-west4-b.c.dbtest2023-230806.internal bash[95058]: Setting up swapspace version 1, size = 10 GiB (10737414144>
Aug 16 07:40:21 omnidb.us-west4-b.c.dbtest2023-230806.internal bash[95058]: no label, UUID=fd064f49-0da9-4187-b9aa-7271c0b540f8
Aug 16 07:40:21 omnidb.us-west4-b.c.dbtest2023-230806.internal bash[95062]: 2023-08-16 07:40:21.040 CDT: [start_alloydb.sh:83] Enablin>
Aug 16 07:40:21 omnidb.us-west4-b.c.dbtest2023-230806.internal bash[95066]: 2023-08-16 07:40:21.058 CDT: [start_alloydb.sh:93] Startin>
Aug 16 07:40:21 omnidb.us-west4-b.c.dbtest2023-230806.internal bash[95086]: 2023-08-16 07:40:21.784 CDT: [start_alloydb.sh:116] Comput>
Aug 16 07:40:21 omnidb.us-west4-b.c.dbtest2023-230806.internal bash[95093]: f960e164c3995ffe8570644f5702cd7bf83e190218b7bce07189ab7a6b>
Aug 16 07:40:22 omnidb.us-west4-b.c.dbtest2023-230806.internal bash[95142]: 8fbea67b597a64f1e9857e43ab15fb249168edd0690fcb5332660d5185>
Aug 16 07:40:22 omnidb.us-west4-b.c.dbtest2023-230806.internal bash[95198]: 2023-08-16 07:40:22.235 CDT: [start_alloydb.sh:153] Succes>
Aug 16 07:40:22 omnidb.us-west4-b.c.dbtest2023-230806.internal systemd[1]: Finished AlloyDB Data plane.
[alloyuser@omnidb installer]$ sudo systemctl enable alloydb-dataplane
[alloyuser@omnidb installer]$ docker ps
CONTAINER ID   IMAGE                                     COMMAND                  CREATED          STATUS          PORTS     NAMES
8fbea67b597a   gcr.io/alloydb-omni/memory-agent:latest   "/cgmon --logtostder…"   26 seconds ago   Up 25 seconds             memory-agent
f960e164c399   gcr.io/alloydb-omni/pg-service:latest     "/bin/bash /smurf-sc…"   27 seconds ago   Up 25 seconds             pg-service
[alloyuser@omnidb installer]$ docker exec -it pg-service psql -h localhost -U postgres
psql (15.2)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5432".
postgres=# SHOW server_version;
 server_version 
----------------
 15.2
(1 row)

postgres=# \q

All good here. In the next blog, I will discuss how to connect to AlloyDB Omni using psql and pgAdmin, especially connect from laptop.