How to Set Up pgAdmin for CS Teaching Labs

If you’re taking a database course at the University of Toronto and want to use pgAdmin instead of the command line to interact with the CS Teaching Labs PostgreSQL server, this guide will walk you through the entire process.

---

Step 1: Install pgAdmin

pgAdmin is a graphical user interface (GUI) for PostgreSQL that allows you to manage and query databases easily. Follow these steps to install it on your machine:

• Visit pgAdmin’s official website and download the latest version for your operating system (Windows, macOS, or Linux).

• Run the downloaded installer and follow the on-screen instructions. Once installed, launch pgAdmin.

---

Step 2: Connect to the CS Teaching Labs PostgreSQL Server

The CS Teaching Labs PostgreSQL database is only accessible from within the CS Teaching Labs network. Since you're working on your personal computer, you need to use SSH tunneling to connect pgAdmin to the database securely.

1. Open a Terminal and Create an SSH Tunnel

• Run the following command in your terminal (Mac/Linux) or Command Prompt (Windows with OpenSSH installed):

ssh -L 5432:localhost:5432 <your_cs_teaching_lab_username>@dbsrv1.teach.cs.toronto.edu

• -L 5432:localhost:5432 tells SSH to forward your local port 5432 to the PostgreSQL server’s 5432 port.

• Replace <your_cs_teaching_lab_username> with your actual CS Teaching Labs username.

• You will be prompted to enter your CS Teaching Labs password.

  🚀 Important: Keep this terminal open while using pgAdmin, or the connection will close!

2. Configure pgAdmin to Connect to the Server

1. Open pgAdmin.

2. Click “Register” → “Server”.

3. Fill in the details:

   • General Tab:

     • Name: CS Teaching Labs Database (Actually, you can choose anything you like)

   • Connection Tab:

     • Host name/address: localhost

     • Port: 5432

     • Maintenance database: postgres

     • Username: <your_cs_teaching_lab_username>

     • Password: (leave blank if authentication is via SSH)

4. Click Save, then Connect.

   🎉 You are now connected to the CS Teaching Labs PostgreSQL database via pgAdmin!

---

Step 3: Import Data into Your Database

If your professor has provided starter files with SQL scripts and CSV files, follow these steps to import them into your database.

1. Running SQL Files (e.g., Schema and Sample Data)

1. In pgAdmin, select your database in the left panel.

2. Click “Query Tool”.

3. Click the 📂 Open File icon, select populate_data.sql(or q1.sql, q2.sql, …), and click Execute (▶️ button).

2. Importing CSV Files

If your dataset includes CSV files (e.g., Seat.csv, Route.csv), follow these steps and repeat them for all necessary tables:

1. In the left panel, navigate to Schemas → Tables.

2. Right-click the target table (e.g., Seat) and select “Import/Export Data”.

3. In the pop-up window:

   • Select Import.

   • Choose the CSV file.

   • Ensure column mappings match.

   • Click OK to start importing.

---

Step 4: Running Queries for Your Assignment

Once your database is set up, you can start running queries using the provided SQL files (q1.sql, q2.sql, etc.).

1. Open Query Tool in pgAdmin.

2. Click 📂 Open File, select q1.sql, and execute it.

3. Review the results in the Data Output section.

4. Repeat for other queries (q2.sql, q3.sql, etc.).

🎯 Tip: If you need to test edge cases, modify the SQL scripts and run them on small test datasets before submission.

---

Final Thoughts

By following this guide, you can successfully set up pgAdmin to work with the CS Teaching Labs PostgreSQL database. This allows you to run queries more conveniently while avoiding command-line complexity.

Now you’re ready to work on your SQL assignments efficiently! Let me know if you have any issues. Happy querying!