Managed Postgres#

In this lab you’ll see how to connect your Django pod to an managed Postgres instance. Managed instances have some advantages, they are robust and stable and backed by the expertise of your cloud vendor. They usually have automatic backups and are easily made highly available with almost no configuration. The downside of managed services is that they’re expensive and they require you to do some “classic” IT admin work.

Introduction#

We are going to do the following tasks in this lab:

  1. Create a manged Postgres instance in GCP.

  2. Login to our DMBS and perform setup actions.

  3. Connect our Django pod to the external database.

Create a Managed Postgres Instance#

In class I will record the process of doing this through Google Cloud Console. Here are the details:

Item Value
Instance ID managed-postgres
Password test
Database version PostgreSQL 14
Region us-central1 (or wherever your cluster is)
Zonal availability Single zone (saves $$)
Machine type Lightweight 1vCPU, 3.75 GB (lowest $$)
Storage HDD, 10 GB (lowest $$)
Instance IP assignment Select Private IP, default network (You will have to enable Private services access)
Instance IP assignment Also select Public IP (needed for Cloud Shell connections)
Backups Disable Backups

Create a Postgres User and Schema#

When using the Postgres container or Helm chart the container has a setup script that will automatically create one user and database. This is a huge timesaver and makes it really easy to get going. A managed database works like a traditional “big” DBMS: An admin has to create accounts.

You must do this in Cloud Shell. It may be possible in a dev environment but not if your machine has an IPv6 address. Log in to your SQL database:

Warning

The first try will fail and tell you to enable the Cloud SQL Admin API

$ gcloud sql connect managed-postgres --user=postgres --quiet

Once at the psql prompt enter the following SQL commands:

create user django password 'django';
grant django to postgres;
create database django owner django;

Make a note of the username, password and database name, they will be added to your pod specification.

Connect Your Pod#

Update the environment variables in your pod to point to the managed database:

Note

Get the internal IP address of your DB from Cloud Console

Here’s a configuration fragment:

    env:
    - name: DB_ENGINE
      value: "postgres"
    - name: DB_NAME
      value: "django"
    - name: DB_USER
      value: "django"
    - name: DB_PASSWORD
      value: "django"
    - name: DB_HOST
      value: "put-the-ip-address-here"

Editing environment variables and re-applying the YAML will not take effect unless you restart the pod manually. You can also just delete the pod and re-apply:

$ kubectl delete pod/mysite 
$ kubectl apply -f deployment/mysite-pod.yaml

Verify the new pod starts:

$ kubectl get all 

Verify that the pod is able to initialize the new database:

$ kubectl logs -f pod/mysite
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, polls, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying auth.0012_alter_user_first_name_max_length... OK
  Applying polls.0001_initial... OK
  Applying sessions.0001_initial... OK
Superuser created successfully.

Cleanup#

If you have this working you can leave it running but be aware that it will eat your credit very quickly. To clean up remove the managed database in the Cloud Console.