How to create AlloyDB — Database with Google Cloud CLI and Google Cloud Console.

• How to create a cluster and instance.
• How to create tables and insert data in your database
• How to use the Google Cloud CLI with AlloyDB.

Architecture AlloyDB — Database

Step 1 Create a cluster and instance

1. First create an AlloyDB cluster. On the Cloud Console Navigation menu ( under Databases click AlloyDB for PostgreSQL then Clusters.
2. Then click Create cluster at the top of the page.
3. Choose Highly Available from the list of options for the cluster type to start with step of the wizard.
4. Click Continue under the list of cluster options.
5. In the Configure your cluster section, fill in the following fields. Leave the others at their default value.

Cluster ID- — — — — — — — Name Cluster

Password _-___________Put Password

Select Network — — — — — — peering-network

Click Continue to proceed.

7. Under Configure your primary instance, set the instance ID as lab-instance.

8. Select 2 vCPU, 16 GB as your machine type.

9. Scroll to the bottom of the page and click Create Cluster.

Clusters are groups of instances of virtual machines that can include a primary instance and multiple read pool instances. All cluster resources share a storage layer, which scales as needed.

Cluster IDdemocluster

VersionPostgreSQL 14 compatible

TypeHighly available

Location

us-central1 (Iowa)

Low CO2

Total storage used automatically scales

Network projects/186756110282/global/networks/peering-network

Step.2 Create tables and insert data in your database

1. A VM named, alloydb-client, containing the PostgreSQL client was provisioned for you at the start of the lab.
2. On the Navigation menu under Compute Engine click VM instances.
3. For the instance named alloydb-client, in the Connect column, click SSH to open a terminal window.
4. Set the following environment variable, replacing ALLOYDB_ADDRESS with the Private IP address of the AlloyDB instance.

export ALLOYDB=ALLOYDB_ADDRESS

1. Run the following command to store the Private IP address of the AlloyDB instance on the AlloyDB client VM so that it will persist throughout the lab.

echo $ALLOYDB  > alloydbip.txt

1. Use the following command to launch the PostgreSQL (psql) client. You will be prompted to provide the postgres user’s password (Change3Me) which you entered when you created the cluster.

psql -h $ALLOYDB -U postgres

1. Input and run the following SQL command to create a new table named regions.

CREATE TABLE regions (
    region_id bigint NOT NULL,
    region_name varchar(25)
) ;
ALTER TABLE regions ADD PRIMARY KEY (region_id);

1. Next add several rows of data to the regions table. Input and run the following SQL command.

INSERT INTO regions VALUES ( 1, 'Europe' );
INSERT INTO regions VALUES ( 2, 'Americas' );
INSERT INTO regions VALUES ( 3, 'Asia' );
INSERT INTO regions VALUES ( 4, 'Middle East and Africa' );

1. Run the following simple query to verify that you inserted the records.

SELECT region_id, region_name from regions;

1. Type \q to exit the psql client.
2. Another option to create tables and/or load data is by using a SQL file (.sql). A SQL file can contain DDL, DML or any supported SQL syntax. You will download and run a file containing DDL and DML, run that file, and then verify the load.
3. Run the following command to download a file containing DDL and DML for three tables: countries, departments, and jobs.

gsutil cp gs://cloud-training/OCBL403/hrm_load.sql hrm_load.sql

1. Reconnect to the PostgreSQL (psql) client. You will be prompted to provide the postgres user’s password (Change3Me).

psql -h $ALLOYDB -U postgres

Run the following command to process the sql file.

\i hrm_load.sql

1. Run the following command to see the tables that are loaded into your database.

\dt

1. Run a spot check query to examine the data in one of the tables you just created and loaded.

select job_title, max_salary 
from jobs 
order by max_salary desc;

Step 3. Use the Google Cloud CLI with AlloyDB

1. Creating an AlloyDB cluster instance via gcloud is very simple.
2. In the Cloud Shell, create a new AlloyDB cluster using the command below.

gcloud beta alloydb clusters create gcloud-lab-cluster \
    --password=Change3Me \
    --network=peering-network \
    --region=us-central1 \
    --project=qwiklabs-gcp-03-fb26f6cf4fc0

1. Once the cluster is created, run the following command to create the Primary instance.

gcloud beta alloydb instances create gcloud-lab-instance\
    --instance-type=PRIMARY \
    --cpu-count=2 \
    --region=us-central1  \
    --cluster=gcloud-lab-cluster \
    --project=qwiklabs-gcp-03-fb26f6cf4fc0

1. After the process completes, you can run the following command to list the AlloyDB clusters instances available in your project. The earlier instance you created, lab-cluster, and the one you just created, gcloud-lab-cluster, are returned in the listing.

gcloud beta alloydb clusters list

Deleting a cluster

gcloud beta alloydb clusters delete gcloud-lab-cluster \
    --force \
    --region=us-central1 \
    --project=qwiklabs-gcp-03-fb26f6cf4fc0

1. To confirm that gcloud-lab-cluster was deleted run the following command:

gcloud beta alloydb clusters list

Create Backup