Run a Postgres instance for cheap in Google Cloud
Tags: googlecloud, gcp, postgres, computeengine
Published: 2020-05-25 10:42:55

---
title: Run a Postgres instance for cheap in Google Cloud
description: Deploy a Postgres container in Compute Engine in the easiest and cheapest way possible.
published: true
tags:
  - googlecloud
  - gcp
  - postgres
  - computeengine
canonical_url: https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/
cover_image: https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/meta.jpg
---

This post was originally posted on [JonCloudGeek](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/).


## Table of Contents
<!-- MEDIUM_LIST_PRESERVE -->
  1. [Scenario](#scenario)
  1. [Get started](#get-started)
  1. [Create a compute instance running a Postgres container](#create-a-compute-instance-running-a-postgres-container)
  1. [Add a Firewall rule to connect to this instance](#add-a-firewall-rule-to-connect-to-this-instance)
  1. [Migrate data to new DB](#migrate-data-to-new-db)
  1. [Summary](#summary)
  1. [Disclaimer](#disclaimer)

![An elephant](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/meta.jpg "Postgres in a container")<figcaption>Postgres in a container</figcaption>

In this blog post, I will explore running a Postgres instance in a container within Compute Engine for a fraction of what it costs to run in Cloud SQL.

## Scenario

[Cloud SQL](https://cloud.google.com/sql) is kind of expensive:

* `f1-micro` gives you a weak instance with only 0.25 vCPU burstable to 1 vCPU, from $9.37.
* `g1-small` is still 0.5 vCPU burstable to 1 vCPU, but the price jumps to from $27.25.
* Let's not even talk about the non-shared vCPU standard instances.

For toy project or production projects that just don't need that power, Cloud SQL is overkill, at least the price is overkill.

This blog post is for smaller projects that need Postgres but without Cloud SQL, and without the maintenance hassle of running commands to manually install Postgres.

## Get started

<!-- MEDIUM_LIST_PRESERVE -->
1. Sign up for [Google Cloud](https://console.cloud.google.com/). Free trial gives you $300 credits lasting one year.
1. [Create a project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#creating_a_project) to house your related resources.
1. Navigate to [Compute Engine](https://console.cloud.google.com/compute/instances).

## Create a compute instance running a Postgres container

1. In the Compute Engine instances page in the Cloud Console, click on **Create**.

    ![Click on Create](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/click-create.png "Click Create")<figcaption>Click Create</figcaption>

2. Select the E2 series and the `e2-micro` machine type. E2 series is a cost-optimized series of compute engine instances. Read more about it in the [launch blog post](https://cloud.google.com/blog/products/compute/google-compute-engine-gets-new-e2-vm-machine-types). `e2-micro` is like `f1-micro`, but with **2 shared vCPU**, which is good enough for almost any small project, but not compromising like the weak `f1-micro` (which hangs / throttles when you try to do too much CPU work).

    The `e2-micro` starts from **$6.11/month**.

    ![Select e2-micro](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/select-e2.png "Select e2-micro")<figcaption>Select e2-micro</figcaption>

3. Check **Deploy a container image to this VM instance**. This will cause Compute Engine to only run the given image (next step) in the compute instance. Each compute instance can only run a single container using this method, and since we have sized our instance to only have just enough resources, this is totally fine.

    ![Check Deploy a container image to this VM instance](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/check-container.png "Check this to deploy a container image to our Compute Engine instance")<figcaption>Check this to deploy a container image to our Compute Engine instance</figcaption>

4. Choose a Postgres image from the Marketplace.

    1. Search for Postgres in Marketplace:

        ![Search for Postgres in Marketplace](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/marketplace-postgres-search.png "Search for Postgres in Marketplace")<figcaption>Search for Postgres in Marketplace</figcaption>

    1. Select a Postgres version:

        ![Choose your desired Postgres version](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/marketplace-postgres-results.png "Choose your desired Postgres version")<figcaption>Choose your desired Postgres version</figcaption>

    1. Click on **Show Pull Command** to retrieve the image URL:

        ![Click on Show Pull Command](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/show-pull-command.png "Click on Show Pull Command")<figcaption>Click on Show Pull Command</figcaption>

        If you click on **Get Started with Postgresql 11** it will bring you to a Github page with more (important) information.

        Notably you want to take note of the list of [Environment Variables](https://github.com/GoogleCloudPlatform/postgresql-docker/blob/master/11/README.md#environment-variables) understood by the container image.


    1. Copy the image URL:

        ![Copy the image url](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/copy-image-url.png "Copy the image url")<figcaption>Copy the image url</figcaption>

5. Paste the image URL into previous Compute Engine step. Set the minimum necessary environment variables. The default user is **postgres**. POSTGRES_DB is arguably unnecessary, you can already create it manually after instance creation.

    ![Set the image and environment variables](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/set-image-env-var.png "Set the image and environment variables")<figcaption>Set the image and environment variables</figcaption>

6. **[VERY IMPORTANT]** Mount a directory and point it at where the container stores data. **IF YOU DO NOT DO THIS, WHEN YOUR INSTANCE REBOOTS OR STOPS FOR WHATEVER REASON, ALL DATA DISAPPEARS.**

    ![Mount a volume at the Postgres data directory](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/mount-volume.png "Mount a volume at the Postgres data directory")<figcaption>Mount a volume at the Postgres data directory</figcaption>

    It is somewhat container intuitive, usually in the command line we state the host path first, and then the mount path. Here **Mount path** (the container directory) comes first, and **Host path** (the OS directory) comes second.

    Click **Done**.

7. Some optional settings to configure:

    ![Turn on Secure Boot](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/secure-boot.png "Turn on Secure Boot")<figcaption>Turn on Secure Boot</figcaption>

    ![Uncheck Delete boot disk when instance is deleted](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/uncheck-delete-boot-disk.png "Uncheck Delete boot disk when instance is deleted")<figcaption>Uncheck Delete boot disk when instance is deleted</figcaption>

8. Click **Create**.

## Add a Firewall rule to connect to this instance

By default, post 5432 is blocked in a Google Cloud project. To allow connections from your local machine, do the following:

1. Go to **Firewall rules**.

    ![Search for firewall and click on Firewall rules (VPC network)](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/search-firewall.png "Search for firewall and click on Firewall rules (VPC network)")<figcaption>Search for firewall and click on Firewall rules (VPC network)</figcaption>

1. Select **Create Firewall Rule**.
1. Name, **allow-postgres** (or anything you like)
1. In **Target tags**, add `allow-postgres`.
1. In **Source IP ranges**, add `0.0.0.0/0`. Or Google "my ip" and paste in the result (safer but cumbersome, IP changes frequently).
1. In **Specific protocols and ports**, add `5432` in **tcp**.
1. Click **Create**.
1. Go back to the DB instance, click **Edit**.
1. Add the `allow-postgres` network tag:

    ![Add the allow-postgres network tag](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/add-network-tag.png "Add the allow-postgres network tag")<figcaption>Add the allow-postgres network tag</figcaption>

1. Click **Save**. Your instance is now accessible from your local machine.

## Migrate data to new DB

1. Dump your current DB data:

    ```bash
    pg_dump -d mydb -h db.example.com -U myuser --format=plain --no-owner --no-acl  \
      | sed -E 's/(DROP|CREATE|COMMENT ON) EXTENSION/-- \1 EXTENSION/g' > mydb-dump.sql
    ```

1. Get the external IP of our new DB:

    ![Copy the external IP of the new instance](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/get-external-ip.png "Copy the external IP of the new instance")<figcaption>Copy the external IP of the new instance</figcaption>

1. Use the external IP to `psql` to the instance. When prompted, paste the DB password from `POSTGRES_PASSWORD` earlier. The following command restores the dump back into the new DB instance:

    ```bash
    psql -h [EXTERNAL_IP] -U postgres mydb < mydb-dump.sql
    ```

1. Your DB is now ready. When creating the DB, note the internal hostname for this instance:

    ![Note the instance internal hostname](https://joncloudgeek.com/blog/deploy-postgres-container-to-compute-engine/note-hostname.png "Note the instance internal hostname")<figcaption>Note the instance internal hostname</figcaption>

    You can use this internal hostname to talk to this DB from within your VPC (another Compute Engine instance, Cloud Run, GKE, etc.). If that fails, then you can fallback to the **Internal IP** (see screenshot in Step 2).

1. **[HIGHLY RECOMMENDED]** Stop (shut down) your DB instance and start it again. Connect to your instance via `psql` (note that the External IP will likely change). Check that all your data is intact.

1. Remove `allow-postgres` from your instance **Network tags** (Edit, remove, Save). Your instance is no longer publicly accessible. By default, all internal network ports are open in Firewall rules so your DB instance remains accessible from within your VPC.

## Summary

In this blog post, we have successfully created a Postgres instance from a container image in Compute Engine. We configured a firewall rule to connect to that instance from our local machine using a network tag, and we removed that network tag to lock up access to that instance. We also used the instance External IP to connect to it and restore dumped SQL data from our old instance (with the firewall rule in place).

If you are confident about the needs and performance of your application, you can choose to downgrade the instance to `f1-micro` to save another $2/month. Eventually GCP will come along and tell you that your instance is over-utilized (you can ignore or reject the warning). Note that all the risk is yours if your DB instance hangs because it is CPU-starved. There is an increased risk to cheap in the cloud.

Alternatively, there is a Postgres [**Google Click to Deploy**](https://console.cloud.google.com/marketplace/details/click-to-deploy-images/postgresql) option in the Marketplace. This will run Postgres in Debian OS, not a container. And you also cannot run it in an E2 instance (only N1 is supported). But it is probably more production ready, I presume.

Also highly recommended is that you take [**scheduled snapshots**](https://cloud.google.com/compute/docs/disks/scheduled-snapshots) of the boot disk of the DB instance. This is important for data recovery and backups. Usually Cloud SQL takes care of this for you, but we need to handle it ourselves since we are DIY-ing here.

## Disclaimer

The information provided in this blog post is provided as is, without warranty of any kind, express or implied. By following the steps outlined in this blog post I do not guarantee that your database will be free from any sort of failures or data losses.

## My GCP Books

If you found this blog post helpful, kindly check out my [books on GCP topics](https://joncloudgeek.com/books/)!