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!
Top comments (0)