Käytä PostgreSQL-tietokantaasi

Katso palomuureja käsittelevä sivu, jolta löydät ohjeet tietokantasi käyttöoikeuden avaamiseen.

Graafinen käyttöliittymä

Suosittu työkalu PostgreSQL-tietokantojen käyttöön on pgAdmin, joka löytyy täältä. Sen voi asentaa työpöytäsovelluksena tai palvelimelle käytettäväksi sen selainkäyttöliittymän kautta. Huomaa, että sitä ei voi asentaa Pukki-tietokantaesiintymään, eikä Pukki-tiimi tarjoa sille tukea, koska käytämme mieluummin komentorivityökaluja.

Komentorivi

Asenna postgresql-komentorivityökalu. Huomaa, että jotkin Linux-jakelut saattavat tarjota siitä oletuksena hyvin vanhoja versioita. Katso tältä sivulta yksityiskohtaiset asennusohjeet.
Etsi tietokantaesiintymäsi julkinen IP-osoite selainkäyttöliittymän Overview-välilehdeltä tai komennolla openstack database instance list.
Käytä seuraavia komentoja PostgreSQL-esiintymäsi käyttämiseen komentoriviltä:
```
psql --user ${USERNAME} --host ${PUBLIC_IP} ${DATABASE_NAME}
```

Jos sovelluksesi käyttää asetustiedostoa, syntaksin pitäisi näyttää suunnilleen tältä:

postgresql://${USERNAME}:${PASSWORD}@{PUBLIC_IP}:5432/${DATABASE_NAME}

Voit käyttää myös jälkimmäistä syntaksia tietokannan käyttämiseen psql:llä, mutta silloin kyselyvastauksista jätetään sarakenimet pois.

Yleisiä ongelmia CLI-yhteyksissä

Jos yhteys jää odottamaan ja aikakatkaistaan ilman PostgreSQL-kehotetta tai saat virheen kuten:
```
psql: error: connection to server at "195.148.30.38", port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
```
se tarkoittaa, että joko IP-osoite on väärä tai tietokantaesiintymän palomuuri estää yhteyden.

Jos saat virheen kuten:

psql: error: connection to server at "$IP_ADDRESS", port 5432 failed: FATAL:  database "$DATABASE" does not exist

se tarkoittaa, että yrität käyttää väärää tietokantaa.

Jos psql pyytää salasanaa mutta ei hyväksy sitä, varmista että kirjoitit sen oikein, ja tarkista että tietokannan käyttäjä on olemassa joko selainkäyttöliittymän Users-välilehdellä tai komennolla openstack database user list ${DATABASE_ID}.

Pukki PostgreSQL -tietokannan käyttö Roihusta

Info

Seuraavat ohjeet koskevat myös Puhtia, mutta se poistuu käytöstä kesällä 2026. Käytä sen sijaan Roihua.

Varmista ensin, että tietokantasi palomuuri sallii liikenteen Roihusta.
Kirjaudu Roihuun.
Jotta voit käyttää psql-komentorivityökalua, sinun täytyy ensin ladata moduuli:
```
module load psql
```
Jotta voit käyttää PostgreSQL:ää eräajossa, sinun täytyy tallentaa tietokantasi salasana tiedostoon kotihakemistossasi:
1. Luo tiedosto ~/.pgpass, jonka sisältö on seuraava (muokkaa paikkamerkkimuuttujat vastaavasti):
```
$PUBLIC_IP:5432:*:$USERNAME:$PASSWORD
```
  - $PUBLIC_IP tulee olla esiintymäsi julkinen IP-osoite.
  - 5432 on käytettävä portti (Pukissa se on aina 5432).
  - * tarkoittaa, että kaikkien tietokantaesiintymäsi tietokantojen tulee käyttää samoja tunnistetietoja.
  - $USERNAME ja $PASSWORD ovat tietokannan käyttäjänimesi ja salasanasi.
2. Päivitä tiedoston oikeudet komennolla chmod 600 ~/.pgpass, jotta tunnistetietosi pysyvät turvassa.
Nyt voit varmistaa, että pääset tietokantaasi ilman salasanan syöttämistä:
```
psql --user ${USERNAME} --host ${PUBLIC_IP} ${DATABASE_NAME}
```

Yksinkertainen esimerkki Roihun eräajosta käyttäen psql:ää

Tämä edellyttää, että olet määrittänyt ~/.pgpass-tiedoston oikein edellisessä osiossa.

Luo tiedosto nimeltä my-first-psql-batch-job.bash:

my-first-psql-batch-job.bash

#!/bin/bash -l
#SBATCH --job-name=psql_job
#SBATCH --output=output_%j.txt
#SBATCH --error=errors_%j.txt
#SBATCH --time=00:01:00
#SBATCH --account=$PROJECT_NUMBER
#SBATCH --ntasks=1
#SBATCH --partition=test
#SBATCH --mem-per-cpu=1024

module load psql
psql --user $DB_USER_NAME --host $DB_IP_ADDRESS $DATABASE_NAME -c 'SELECT 1' >> psql-results.txt

Varmista, että olet päivittänyt seuraavat muuttujat:

$PROJECT_NUMBER – CSC-projektitunnuksesi (esim. project_2001234)
$DB_USER_NAME – tietokannan käyttäjänimesi (sama kuin ~/.pgpass-tiedostossa)
$DB_IP_ADDRESS – tietokantasi julkinen IP-osoite
$DATABASE_NAME – tietokantasi nimi

Kun olet tyytyväinen eräajoskriptiin, voit lähettää työn suorittamalla:
```
sbatch my-first-psql-batch-job.bash
```

Hyödyllisiä SQL-komentoja

Listaa tietokannat

\l

Listaa taulut

\d

Näytä taulujen kuvaukset

\d $TABLE_NAME

Vaihda tietokantaa

\c $DATABASE_NAME

Huomaa, että tämä on sama komento kuin uuden tietokannan luomiseen, jos sitä ei ole olemassa (ja olet antanut itsellesi root-oikeudet).

Esimerkkikysely

SELECT row1, row2 FROM table1 ORDER_BY row3 DESC LIMIT 2;

Näytä kaikki tietokanta-asetukset

SHOW ALL;

Näytä kaikki käyttäjät

select * from pg_user;

Tämä näkyy myös selainkäyttöliittymässä tai OpenStackin CLI:ssä. Huomaa, että PostgreSQL-käyttäjä on palvelukäyttäjä eli käyttäjä, jota Pukki käyttää kommunikoidakseen tietokantasi kanssa.

Laajennettu näyttö

Tämä näyttää tietueen jokaisen sarakkeen omalla rivillään. Tämä on erityisen hyödyllistä, kun haluat tarkastella yksittäistä tietuetta.

SELECT * FROM table1 LIMIT 1 \gx