Quickly set up a PostgreSQL environment on GCP

This quick-start guide is part of a series that shows how to set up databases on Google Cloud Platform, for developing and testing purposes.

This guide will show you how to create a PostgreSQL environment running inside your Google Cloud Project. This make sense when you want to have more control over your PostgreSQL instance, otherwise you can just use the GCP managed version, Cloud SQL: https://cloud.google.com/sql/docs/postgres/

Create a Compute Engine VM

Using Cloud Shell:

# Create the PostgreSQL GCE instance
gcloud compute instances create postgresql \
  --zone=us-central1-c \
  --machine-type=n1-standard-1 \
  --image-project=debian-cloud --boot-disk-size=10GB \
  --image=debian-9-stretch-v20190916 \
  --boot-disk-type=pd-standard \
  --boot-disk-device-name=postgresql \
  --scopes=cloud-platform 

Configure your VM with PostgreSQL

Using Cloud Shell:

# Connect to the PostgreSQL VM
gcloud compute ssh --zone=us-central1-c postgresql

# Login as super user
sudo -s

# Install Docker
curl -sSL https://get.docker.com/ | sh

# Install postgresl client
apt-get install postgresql-client

# Build and run
mkdir -p /tmp/pgdata/
docker run -d -p 5432:5432 --name postgresql -v /tmp/pgdata:/var/lib/postgresql/data postgres:9.6

Load your PostgreSQL database with data

Using Cloud Shell:

# Verify PG is installed and started up successfully
docker ps -a
docker logs --follow <PG_CONTAINDER_ID>

# Wait for the message to appear:
# LOG:  database system is ready to accept connections

# Leave the logs command, by pressing CTRL + C

# Log into the PG container
docker ps
docker exec -it <PG_CONTAINDER_ID> bash

# Log as the postgres user
su postgres

# Connect to postgres database
psql postgres

# Create tables and populate with Data
CREATE TABLE Users (uid INTEGER PRIMARY KEY,
                    name VARCHAR);
INSERT INTO Users
  SELECT generate_series, random()
  FROM generate_series(1, 100000);

CREATE TABLE Messages (mid INTEGER PRIMARY KEY,
 uid INTEGER REFERENCES Users(uid),
 ptime DATE, message VARCHAR);

INSERT INTO Messages
   SELECT generate_series,
          round(random()*100000),
          date(now() - '1 hour'::INTERVAL * round(random()*24*30)),
          random()::text
   FROM generate_series(1, 100000);

And that's it!

If you have difficulties, don’t hesitate reaching out. I would love to help you!