Postgres Setup Guide

Follow this guide to set up your Postgres environment for this class.

1. Go to the GCP console.
2. From the nav menu, click on SQL and then click on Create Instance.
3. From the create an instance screen, click on Choose PostgreSQL.
4. Name your Postgres instance. For example, pg.
5. Set a root password. Make sure you remember it!
6. For region, choose us-central1 (Iowa) and Zone, Any.
7. For database version, select PostgreSQL 9.6.
8. Click on Configuration options
9. Expand the Connectivity option and uncheck Public IP and check Private IP.
10. Expand the Backups, recovery and high availability option and uncheck Enable point-in-time recovery.
11. Don't make any other changes.
12. Click the Create button to create your Postgres instance.
13. While your instance is coming up, go to AI Platform Notebooks and start your Jupyter notebook instance.
14. Open a terminal window and run sudo apt-get install postgresql-client to install the Postgres client libraries.
15. Create a .bash_profile file with the following lines:

export PGDATABASE=postgres
export PGHOST={private IP}
export PGPORT=5432
export PGUSER=postgres
export PGPASSWORD={password}

16. Run source .bash_profile to set the environment variables or just open a new terminal window.
17. Run psql to bring up the postgres client. If you get the following output, your Postgres setup is complete.

(base) jupyter@python-notebook:~$ psql
psql (9.6.17, server 9.6.18)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.

postgres=>

18. Once you have tested connectivity, stop your Postgres instance and Jupyter instance. If you forget to stop those resources, you will quickly run out of GCP credits!