Käytä PostgreSQL-tietokantaasi

Katso palomuureja käsittelevä sivu, jolta löydät ohjeet tietokantasi käyttöoikeuksien 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 selainkäyttöliittymän kautta. Huomaa, että sitä ei voi asentaa Pukki-tietokantainstanssiin, 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 tarkat asennusohjeet tältä sivulta.
Selvitä tietokantainstanssisi julkinen IP-osoite selainkäyttöliittymän Overview-välilehdeltä tai komennolla openstack database instance list.
Käytä seuraavia komentoja PostgreSQL-instanssiisi yhdistä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 tietokantaan yhdistämiseen psql:llä, mutta silloin kyselyvastauksista jätetään sarakkeiden nimet pois.

Yleisiä ongelmia CLI-yhteyksissä

Jos yhteys jää odottamaan ja aikakatkaistaan ilman PostgreSQL-kehotetta tai saat esimerkiksi seuraavan virheen:
```
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 tietokantainstanssin palomuuri estää yhteyden.

Jos saat esimerkiksi seuraavan virheen:

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 sen laskentapalvelut on poistettu käytöstä kesästä 2026 lähtien. 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 vastaamaan omia tietojasi):
```
$PUBLIC_IP:5432:*:$USERNAME:$PASSWORD
```
  - $PUBLIC_IP tulee olla instanssisi julkinen IP-osoite.
  - 5432 on käytettävä portti (Pukissa se on aina 5432).
  - * tarkoittaa, että kaikkien tietokantainstanssisi tietokantojen tulee käyttää samoja tunnistetietoja.
  - $USERNAME ja $PASSWORD ovat tietokannan käyttäjätunnuksesi 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ätunnuksesi (sama kuin ~/.pgpass-tiedostossa)
$DB_IP_ADDRESS – tietokantasi julkinen IP-osoite
$DATABASE_NAME – tietokantasi nimi

Kun eräajokomentosarja on valmis, 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 jos 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