07 – Setup and Explore Chado Schema

Session Overview

Hands-on lab: participants restore a PostgreSQL dump of the training Chado database and then explore its structure using psql meta-commands and SQL queries to confirm the schema matches what was described in the lecture.

Related Files

Download these before starting the lab:

📄 scripts/install-db.sh 📄 scripts/restore.sh 📄 data/chado_dump.sql

Step 0 — Install PostgreSQL

All training computers run the same OS image. Run install-db.sh once to install PostgreSQL 16. If PostgreSQL is already installed it will skip automatically.

ⓘ Note: install-db.sh is custom-tailored to the C100 training workstations running Ubuntu 22.04.1 LTS. Do not run it on a different OS or machine configuration without reviewing the script first.

Training Environment

uname -a

Linux administrator-Altos-P10-F8 6.8.0-111-generic #111~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Tue Apr 14 17:13:45 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux

OS: Ubuntu 22.04.1 LTS · Arch: x86_64 · Kernel: 6.8.0-111-generic

Check permissions

bash

ls -l install-db.sh

Look for an x in the permissions column (e.g. -rwxr-xr-x). If missing:

bash

chmod +x install-db.sh

Run the installer

bash

bash install-db.sh

ⓘ The script uses sudo — you will be prompted for your password. Once done, continue below to restore the database.

Connection Details

Use these credentials to connect to the training database — from psql, a script, or a GUI tool such as pgAdmin or DBeaver.

Host	`localhost`
Port	`5432`
Database	`chado_training`
User	`postgres`
Password	`training2026`

ⓘ The password is set automatically by restore.sh when the database is created.

Troubleshooting — Password Authentication Failed

If you see "password authentication failed for user postgres", the password may not have been set yet. Run this command to set it manually:

bash

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'training2026';"

If the error persists, PostgreSQL may be using scram-sha-256 which some clients do not support. Switch to md5 and restart:

bash

sudo sed -i 's/scram-sha-256/md5/g' /etc/postgresql/16/main/pg_hba.conf
sudo systemctl restart postgresql

Option A — Automated via restore.sh

Using restore.sh

restore.sh creates the training database and loads the Chado schema in one command. It does not install PostgreSQL — run install-db.sh first if you have not already.

Step 1: Check permissions

bash

ls -l restore.sh

If the x flag is missing:

bash

chmod +x restore.sh

Step 2: Place the files

Make sure both files are in the same working directory:

bash

ls restore.sh chado_dump.sql

Step 3: Run the script

bash

bash restore.sh chado_training chado_dump.sql

The script will:

Create the database chado_training (skips if it already exists)
Restore the Chado schema from chado_dump.sql
Verify the schema is present
Print the connect command when done

Step 4: Connect to the database

bash

sudo -u postgres psql -d chado_training

ⓘ Once connected, continue with the Explore the Schema section below.

Option B — Manual Steps

Manually Restoring the Database

Follow these steps if you prefer to run each command yourself.

Step 1: Create the Database

bash

sudo -u postgres createdb chado_training

Step 2: Restore the Chado Schema

bash

sudo -u postgres psql -d chado_training -f chado_dump.sql

Step 3: Connect to the Database

bash

sudo -u postgres psql -d chado_training

ⓘ Once connected, continue with the Explore the Schema section below.

Explore the Schema

Run these commands inside psql after connecting — they apply whether you used Option A or Option B.

Step 1: List Tables

psql

\dt
\dt *snp*
\dt *stock*

Step 2: View Table Structure

psql

\d cvterm
\d snp_feature
\d variantset

Step 3: Explore Relationships

psql

SELECT o.genus, o.species, s.name AS stock_name
FROM organism o
JOIN stock s ON s.organism_id = o.organism_id;