Käytä PostgreSQL-tietokantaasi

Katso ohjeet tietokantasi käyttöoikeuksien avaamiseen palomuureja käsittelevältä sivulta.

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-tietokanta-instanssiin, 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 yksityiskohtaiset asennusohjeet tältä sivulta.
Selvitä tietokanta-instanssisi 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ää jumiin 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 tietokanta-instanssin 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ö Puhdissa

Varmista ensin, että tietokantasi palomuuri sallii liikenteen Puhdista.
Kirjaudu Puhtiin.
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 tietokanta-instanssisi 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 Puhdin 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-projektisi tunnus (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 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