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).
7. For Zone Availability, click Single and keep the primary zone as Any.
8. For database version, select PostgreSQL 13.
9. Click on Configuration options
10. Expand the Connectivity option and uncheck Public IP and check Private IP.
11. Expand the Backups, recovery and high availability option and uncheck Enable point-in-time recovery.
12. Don't make any other changes.
13. Click the Create button to create your Postgres instance.
14. While your instance is coming up, go to Vertex AI and start up your Jupyter notebook instance.
15. In your notebook, open a terminal window and run the following commands:
16. sudo apt update
17. sudo apt -y install gnupg2
18. wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
19. echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" |sudo tee /etc/apt/sources.list.d/pgdg.list
20. sudo apt update
21. sudo apt -y install postgresql-client-13
22. Create a .bash_profile file on Jupyter Notebook (similar to creating the cnf 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 your output looks like this, your Postgres setup is complete.

jupyter@python:~$ psql
psql (13.5 (Debian 13.5-1.pgdg100+1), server 13.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

Type "help" for help.

postgres=>

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