Käytä PostgreSQL-tietokantaasi

Katso palomuurisivu, jolta löydät ohjeet tietokantasi käyttöoikeuksien avaamiseen.

Varmista, että tietokantayhteytesi on salattu

Pukin PostgreSQL-tietokantainstanssit tukevat salattuja yhteyksiä itse allekirjoitetulla varmenteella, ja useimpien asiakkaiden pitäisi oletusarvoisesti käyttää salattua yhteyttä. Jos haluat varmistaa, että nykyinen yhteytesi on salattu, voit tarkistaa asian seuraavalla kyselyllä:

SELECT a.pid, a.usename, a.client_addr, a.application_name, s.ssl
FROM pg_stat_activity a
JOIN pg_stat_ssl s USING (pid);

Viimeinen sarake, ssl, näyttää arvon t salatuille yhteyksille ja f salaamattomille yhteyksille. Huomaa, että se näyttää kaikki yhteydet vain, jos kysely suoritetaan root-käyttäjänä.

Tapa pakottaa salattu yhteys vaihtelee asiakasohjelmittain, mutta yleinen tapa on lisätä yhteysmerkkijonoon muuttuja sslmode=require.

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 Pukin tietokantainstanssiin, eikä Pukki-tiimi tarjoa sille tukea, koska käytämme mieluummin komentorivityökaluja.

Komentorivi

Asenna komentorivityökalu postgresql. Huomaa, että jotkin Linux-jakelut saattavat tarjota siitä oletuksena hyvin vanhoja versioita. Katso yksityiskohtaiset asennusohjeet tältä sivulta.
Etsi 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}?sslmode=require

Voit käyttää myös jälkimmäistä syntaksia tietokantaan yhdistämiseen komennolla psql, 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ä tietokantakäyttäjä on olemassa joko selainkäyttöliittymän Users-välilehdellä tai komennolla openstack database user list ${DATABASE_ID}.

Pukki PostgreSQL -tietokantasi käyttäminen Roihusta

Varmista ensin, että tietokantasi palomuuri sallii liikenteen Roihusta.
Kirjaudu Roihuun.
Jotta voit käyttää komentorivityökalua psql, sinun täytyy ensin ladata moduuli:
```
module load psql
```
Jos haluat käyttää PostgreSQL:ää eräajossa, sinun täytyy tallentaa tietokantasalasanasi 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 tietokantasi käyttäjätunnus ja salasana.
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 – tietokantasi käyttäjätunnus (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 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