Setting Up your Google Cloud Account

This page provides instructions on how to set up a Linux virtual machine and PostgreSQL client tools running on Google Cloud. We will be using Google's cloud computing infrastructure for Part 2 of Project 1. You should have received an email from Alisha with your individual Google Cloud code that will provide you with enough credit for the class. This email also included the login credentials for your PostgreSQL database account, as well as a proxy-key.json file as an attachment, which you will need to connect to the PostgreSQL server as explained below.

Caution: Running services (e.g., virtual machines) on any cloud provider costs money or credits. It can be very easy to spend more than you anticipated by leaving your services running. So please make it a habit to stop your services when not in use. If you run out of credits, there's not much that the class staff can do to help you out.

Signing Up and Setting Up the OS

As a first step, please log in with your Lionmail or Barnard account in your browser and follow link console.cloud.google.com/education. After visiting this website, confirm that you are logged in with your Lionmail or Barnard account by checking the top right of your screen. (If you enter your credit code into the wrong account, such as your personal Gmail account, you won't be able to transfer it later to your Lionmail or Barnard account.) After confirming that you are logged in with your Lionmail or Barnard account, enter the credit code ("coupon code") that we sent you by email.

Then, to set up your VM on the Google Cloud, follow these steps in a new browser tab:

1. Go to console.cloud.google.com
2. Click on the dropdown menu immediately to the right of "Google Cloud" on the top left of the page, and then on “New project” (if you have the option to select from multiple billing accounts, make sure you select the one with “Billing Account for Education” as your billing account name)
3. Enter a “Project name” (e.g., “cs4111-001”) and click “Create”
4. Go back to console.cloud.google.com and make sure that the project that you just created (e.g., “cs4111-001”) is selected on the top navigation bar, to the right of “Google Cloud”
5. Click on the menu button in the top left hand corner and select the item “Compute Engine” and then “VM instances”; if prompted, click on "Enable" and/or "Set account"; please be patient: this step takes a few moments to complete
6. Click “Create instance” to create your instance (roughly speaking, an instance is a computer that runs on the cloud)
7. Type any name (for example, “cs4111-instance”)
8. Change “Region” to “us-east1 (South Carolina)”
9. Keep the “Zone” selection as "Any"
10. Under "Series," select "N1"; then, under "Machine type," make sure that "n1-standard-1 (1 vCPU, 3.75 GB memory)" is selected
11. Click on the "OS and Storage" tab, click "Change" and then set "Operating system" to Ubuntu and "Version" to "Ubuntu 24.04 LTS" with "x86/64" boot disk's architecture; change the boot disk size to 25 GB; finally click "Select"
12. Click "Create" to create the VM

Configuring Internet Access

We will now configure your VM to be able to safely connect to the internet via Cloud NAT:

1. Go back to console.cloud.google.com. Click on the menu button in the top left hand cover and select the item "Network services." (If you don't see it, click on "View all products" and then click on "Network services" under "Networking.") Go to "Cloud NAT" on the left.
2. Click "Get started" or "Created Cloud NAT gateway"
3. Configure:
   • Gateway name: db-vm-nat
   • NAT type: Public
   • Network: default
   • Region: us-east1 (South Carolina)
4. Under "Cloud Router," click on "Create new router" and then set:
   • Name: db-vm-router
   • Leave other settings at their defaults
5. Under Cloud NAT mapping, configure:
   • Source endpoint type: VM instances, GKE nodes, Serverless
   • Source IP version: IPv4 subnet ranges
   • IPv4 subnet ranges: Custom, and then:
   • Subnets: default
   • IP ranges: Primary IP range
6. Finally, select:
   • Cloud NAT IP addresses: Automatic
   • Network Service Tier: Standard
   • Leave all other settings at their defaults and click Create

Using your VM

Background information: Each VM falls under a project on Google Cloud. To start working, you should always make sure that you are under the project that you created above for this class (e.g., “cs4111-001”). Note that when a VM is created, it is automatically started as well. So please make sure that you stop the VM (see below) if you don't need to use it, to avoid wasting credits.

1. Go to console.cloud.google.com
2. Select the correct project for the class (e.g., “cs4111-001”) at the top of the page, next to “Google Cloud”
3. Click on the menu button in the top left hand corner and select the item “Compute Engine” and then “VM instances”
4. Select the VM that you created above for the project (e.g., “cs4111-instance”) by checking the box next to its name and, if it's not running already, select “Start / Resume ” from the top menu (this process can take a minute)
5. Once the VM has started up, there are two ways to connect:
   1. On the page where you start the VM, you can connect to the VM through the browser by clicking on “SSH” in the same row as the rest of the VM’s information. This opens a new browser window that you can interact with as you would with a local Unix/Linux terminal.
   2. Alternatively, you can SSH into the VM by using the terminal, following the instructions for Option 2, Steps 1-4, available here.
6. To turn off the VM when you are done using it, go to the “Compute Engine” page, select the VM, and select “Stop” from the top menu. Make sure you turn off the VM whenever you or your partner aren’t using it to avoid wasting credits.

Adding your Teammate to the VM

1. Go to console.cloud.google.com
2. Make sure that you are in the project that the VM is associated with (see above)
3. Click on the menu button in the top left hand corner and select the item “IAM and Admin” (this item might be under "More Products")
4. Click the “Grant access” button near the top of the screen
5. Type in your teammate’s Lionmail or Barnard address in the box labeled “New principals”
6. In the dropdown menu labeled “Select a role,” select “Owner” to give your teammate full access to the resources of the project, then “Save”
   Note: if your teammate does not receive your email invitation for some reason, please ask them to use this URL template, with the right information filled in, to find and accept your invitation: https://console.cloud.google.com/invitation?project=[your project id]&account=[your email address]&memberEmail=[your teammate's email address] To find the Project ID, go to console.cloud.google.com and select your project instance via the dropdown menu. You should see the Project ID near the top of the page.
7. Once your teammate accepts the invitation, your teammate will be able to use the VM as specified in the previous instructions

Installing Additional Software in Your VM

You will need to install additional software in your VM for this project. Only one teammate needs to perform this step, as long as the other teammate is added to this VM following the instructions above. These commands install the PostgreSQL 18 client tools that we need for our project. You should run:
sudo apt-get update
sudo apt-get -y upgrade
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt-get update
sudo apt-get install -y postgresql-client-18


Setting up PostgreSQL

We will use the PostgreSQL DBMS in this class. Check that the client program works:
psql --version

If it prints something like the following, with a version 18.X, then you have the right client:

psql (PostgreSQL) 18.6 (Ubuntu 18.6-1.pgdg24.04+2)

Now, follow the following steps to enable a connection with the class's PostgreSQL server:

• Download the Cloud SQL Auth Proxy: go to this page, select the "Linux 64-bit" option, copy Steps 1 and 2 listed there, and execute them in your VM.
• Get the proxy-key.json file that you received as an attachment in the email from Alisha and upload the file to your VM in the same directory as your cloud-sql-proxy binary from the previous step.
• Launch the "proxy" in the background on your VM to be able to connect safely to the PostgreSQL server:
  nohup ./cloud-sql-proxy --credentials-file=proxy-key.json --port 5454 coms4111-db-ta:us-east1:server-4111-fall-2026 > proxy.log 2>&1 &

• Confirm that the proxy started successfully by inspecting the log file:
  tail -n 10 proxy.log You should see the line, "The proxy has started successfully and is ready for new connections!"
• With the proxy running in the background, connect to the PostgreSQL database as follows:
  psql -h 127.0.0.1 -p 5454 -U <YOUR_UNI> -d proj1part2
• Shut down the proxy when finished:
  pkill -f cloud-sql-proxy

Stopping Your Virtual Machine

While you have abundant credit for this class on Google Cloud, it is very easy to accidentally use up all of your credits. To conserve your credits --and also to avoid wasting energy-- make sure to turn off your machine whenever you are not using it, following the instructions in Step 6 under "Using your VM" above.