GSP395.md

GSP395 - Create and Manage AlloyDB Databases - Challenge Lab

Task 1. Create a cluster and instance

1. Create Cluster
   • Cluster ID : lab-cluster
   • Password : Change3Me
   • Network : peering-network
   • Instance ID : lab-instance
   • Machine Type : 2 vCPU, 16 GB
2. Copy IP Address = 10.51.0.2

Task 2. Create tables in your instance

1. Navigation menu > Compute Engine > VM Instances

2. Connect SSH to the instance alloydb-instance

3. Set environment variables

   export ALLOYDB=ALLOYDB_ADDRESS

4. Simpan ke AlloyDB

   echo $ALLOYDB  > alloydbip.txt

5. Buka AlloyDB

   psql -h $ALLOYDB -U postgres

6. Create table

   CREATE TABLE regions (
       region_id bigint NOT NULL,
       region_name varchar(25)
   ) ;
   ALTER TABLE regions ADD PRIMARY KEY (region_id);
   
   CREATE TABLE countries (
       country_id char(2) NOT NULL,
       country_name varchar(40),
       region_id bigint
   ) ;
   ALTER TABLE countries ADD PRIMARY KEY (country_id);
   
   CREATE TABLE departments (
       department_id smallint NOT NULL,
       department_name varchar(30),
       manager_id integer,
       location_id smallint
   ) ;
   ALTER TABLE departments ADD PRIMARY KEY (department_id);

Task 3. Load simple datasets into tables

• Insert Data

  INSERT INTO regions
  VALUES
      (1, 'Europe' ),
      (2, 'Americas'),
      (3, 'Asia'),
      (4, 'Middle East and Africa');
  
  INSERT INTO countries
  VALUES
      ('IT', 'Italy', 1 ),
      ('JP', 'Japan', 3 ),
      ('US', 'United States of America', 2 ),
      ('CA', 'Canada', 2 ),
      ('CN', 'China', 3 ),
      ('IN', 'India', 3 ),
      ('AU', 'Australia', 3 ),
      ('ZW', 'Zimbabwe', 4 ),
      ('SG', 'Singapore', 3 );
  
  INSERT INTO departments
  VALUES
      (10, 'Administration', 200, 1700),
      (20, 'Marketing', 201, 1800),
      (30, 'Purchasing', 114, 1700),
      (40, 'Human Resources', 203, 2400),
      (50, 'Shipping', 121, 1500),
      (60, 'IT', 103, 1400);

Task 4. Create a Read Pool instance

• Add Read Pool, Fill
  • Read pool instance ID : lab-instance-rp1
  • Node count : 2
  • Machine Type : 2 vCPU, 16 GB

Task 5. Create a backup

1. Databases > AlloyDB for PostgreSQL > Backups
2. Backup ID = lab-backup