DEV Community

Marcelo Costa
Marcelo Costa

Posted on • Edited on

Quickly set up a PostgreSQL environment on GCP

#postgres #gcp #docker #database

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

And that's it!

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

Top comments (0)