PostgreSQL:n asennus ja perusasetukset

01Mikä PostgreSQL on ja miksi se kannattaa valita?

PostgreSQL on lähes neljäkymmentä vuotta kehitetty, avoimen lähdekoodin ja ilmainen relaatiotietokanta. Transaktioturvallisuus (ACID), monipuoliset tietotyypit, JSONB-tuki ja kehittyneet indeksointivaihtoehdot tekevät siitä sopivan pienistä projekteista suuren liikenteen sovelluksiin.

Varaukset, varasto, tilaukset tai ilmoitukset ovat tietoa, jossa johdonmukaisuus on kriittistä, ja siihen PostgreSQL sopii hyvin. Esimerkiksi hotelliohjelmistossa tai verkkokauppa-alustassa, jossa useampi käyttäjä muokkaa samaa tietuetta yhtä aikaa, transaktioturvallisuus on siksi tärkeää. Jos haluat kokeilla PHP:tä ja tietokantaa nopeasti paikallisesti, XAMPP-asennus on käytännöllinen alku. Samoihin töihin käytetään usein myös MySQL:ää; jos haluat vertailla järjestelmiä, tutustu MySQL-asennusoppaaseen.

Tässä artikkelissa käymme läpi kaiken, mitä tarvitset asennuksen jälkeisenä ensimmäisenä päivänä: roolien ja tietokantojen luonti, etäyhteysasetukset, varmuuskopiointi ja yleisimmät virheilmoitukset.

02Mitä tarvitset ennen asennusta

Muutaman päätöksen tekeminen ennen asennusta vähentää myöhempiä ongelmia. Päätä heti alussa, mitä pääversiota käytät tuotantopalvelimella: siirtyminen pääversiosta toiseen vaatii varmuuskopion ja palautuksen tai pg_upgrade-työkalun.

Myös asennustavan valinta on tärkeä. Kehityskoneella Docker on helpoin tapa ajaa eri versioita rinnakkain projektien välillä ilman ristiriitoja. Tuotantopalvelimella käyttöjärjestelmän paketinhallinnalla asennettu palvelu on ennustettavampi tietoturvapäivitysten, lokien seurannan ja automaattisen käynnistyksen kannalta.

• Käyttäjä, jolla on järjestelmänvalvojan (Windows) tai sudo-oikeudet (macOS, Linux)
• Oletusportti 5432 ei ole jonkin toisen sovelluksen käytössä
• Viralliselta sivustolta löytyvä tuettu ajantasainen pääversio: postgresql.org
• Vahva pääkäyttäjän salasana, jonka tallennat turvalliseen paikkaan
• Toimiva Docker-asennus, jos asennat Dockerilla

03Asennus käyttöjärjestelmän mukaan

Jokainen alla oleva tapa asentaa ohjelman virallisista tai virallisesti suositelluista lähteistä. Korvaa komennoissa oleva versiopaikkamerkki itse valitsemallasi pääversiolla.

Windows

Lataa EDB:n valmistelema asennusohjelma postgresql.org-sivuston Windows-sivulta. Ohjattu toiminto asentaa palvelimen, pgAdminin, komentorivityökalut ja Stack Builderin yhdellä kertaa; se kysyy postgres-käyttäjän salasanan, portin ja kieliasetuksen.

Jotta voit käyttää psql-komentoa kaikissa päätteissä asennuksen jälkeen, lisää bin-kansio PATH-muuttujaan. Käynnistä-valikon SQL Shell (psql) -pikakuvake tekee saman asian.

# Kansion nimi vaihtelee asentamasi pääversion mukaan
$env:Path += ";C:\Program Files\PostgreSQL\<versio>\bin"
psql --version
psql -U postgres -h localhost

macOS (Homebrew)

Homebrewssa PostgreSQL tulee versionumeroituina paketteina. Listaa ensin saatavilla olevat paketit, asenna sitten haluamasi pääversio ja käynnistä se palveluna. Jos haluat graafisen käyttöliittymän, Postgres.app on yleinen vaihtoehto.

brew search postgresql
brew install postgresql@<versio>
brew services start postgresql@<versio>
# Versionumeroituja paketteja ei välttämättä lisätä PATHiin automaattisesti;
# suorita asennustulosteen echo 'export PATH=...' -rivi
psql --version
psql postgres

Ubuntu ja Debian

Jakelun oma pakettivarasto riittää useimpiin projekteihin. Jos tarvitset uudemman pääversion, voit asentaa sen lisäämällä postgresql.org-sivuston kuvaaman PGDG-varaston.

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl enable --now postgresql
sudo systemctl status postgresql
sudo -u postgres psql -c "SELECT version();"

Docker

Kehitysympäristöön siistein tapa on virallinen postgres-image: voit käynnistää ja sammuttaa tietokannan asentamatta koneelle pysyvää palvelua. Liitä aina nimetty volyymi (volume), jotta tiedot eivät katoa.

• Viralliset imaget versiosta 18 alkaen liittävät volyymin polkuun /var/lib/postgresql.
• Versiossa 17 ja sitä vanhemmissa imageissa liitä volyymi polkuun /var/lib/postgresql/data; tarkista asia imagen Docker Hub -sivun huomautuksesta.

docker run -d --name pg \
  -e POSTGRES_PASSWORD=GucluBirParola \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql \
  postgres:<versio>

docker exec -it pg psql -U postgres

04Käyttäjän ja tietokannan luominen

Asennuksen mukana tuleva postgres-rooli on pääkäyttäjä; sovelluksen yhdistäminen sillä ei ole tietoturvan kannalta oikein. Luo jokaiselle projektille oma rooli, jolla on oikeudet vain omaan tietokantaansa.

PostgreSQL:ssä käyttäjä ja rooli ovat sama käsite: LOGIN-oikeuden omaava rooli toimii kuin käyttäjä. Rooli, jonka teet tietokannan omistajaksi, voi luoda tauluja kyseisen tietokannan public-skeemaan.

CREATE ROLE uygulama WITH LOGIN PASSWORD 'BurayaGucluParola';
CREATE DATABASE magaza OWNER uygulama ENCODING 'UTF8';

-- Vain luku -raportointikäyttäjä
CREATE ROLE rapor WITH LOGIN PASSWORD 'BaskaGucluParola';
GRANT CONNECT ON DATABASE magaza TO rapor;
\c magaza
GRANT USAGE ON SCHEMA public TO rapor;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO rapor;
ALTER DEFAULT PRIVILEGES FOR ROLE uygulama IN SCHEMA public
  GRANT SELECT ON TABLES TO rapor;

05Yleisimmät psql-komennot

psql on PostgreSQL:n komentorivityökalu. Kenoviivalla alkavat metakomennot eivät lähetä SQL:ää palvelimelle, vaan työkalu tulkitsee ne itse, ja ne nopeuttavat selaamista merkittävästi.

psql -h localhost -U uygulama -d magaza
# Yhteysmerkkijonolla
psql "postgresql://uygulama@localhost:5432/magaza"

06Hallinta graafisella käyttöliittymällä pgAdminilla

pgAdmin on PostgreSQL:n virallinen ja ilmainen hallintatyökalu. Se tulee mukana Windows-asennusohjelmassa; macOS:ssä ja Linuxissa se asennetaan erikseen osoitteesta pgadmin.org tai ajetaan Docker-imagella.

Määritä ensimmäisellä käynnistyskerralla uusi palvelin valitsemalla Register > Server: syötä Connection-välilehdelle isännäksi localhost, portiksi 5432 sekä käyttäjätunnus ja salasana. Yhdistämisen jälkeen voit selata tauluja, ajaa kyselyitä Query Toolilla sekä tehdä varmuuskopioinnin ja palautuksen valikosta.

Jos yhdistät etäpalvelimeen, on turvallisempaa käyttää pgAdminin SSH-tunnelia kuin avata tietokantaportti internetiin.

07Etäyhteys ja perusasetukset

PostgreSQL kuuntelee oletuksena vain paikallisia yhteyksiä. Asetustiedostojen sijainti vaihtelee käyttöjärjestelmän ja version mukaan, joten selvitä ne ensin psql:n kautta.

Tiedoston postgresql.conf arvo listen_addresses määrää kuunneltavat osoitteet ja pg_hba.conf sen, mikä asiakas todennetaan millä menetelmällä. Käytä salasanayhteyksissä scram-sha-256-menetelmää ja pidä sallittu IP-alue mahdollisimman suppeana.

• shared_buffers: virallinen dokumentaatio suosittelee aloitusarvoksi noin neljäsosaa muistista erillisellä palvelimella.
• max_connections: älä nosta arvoa korkeaksi, vaan käytä sovelluksen puolella yhteyspoolia.
• Löydä hitaat kyselyt ottamalla käyttöön log_min_duration_statement-asetus ja tutki niitä EXPLAIN ANALYZE -komennolla.

SHOW config_file;
SHOW hba_file;
SHOW data_directory;

# TYPE  DATABASE  USER       ADDRESS          METHOD
host    magaza    uygulama   203.0.113.10/32  scram-sha-256

# pg_hba.conf-muutoksen jälkeen uudelleenlataus riittää
sudo systemctl reload postgresql
# listen_addresses-muutos vaatii uudelleenkäynnistyksen
sudo systemctl restart postgresql

08Varmuuskopiointi ja yleiset virheet

Ajasta automaattinen varmuuskopiointi heti asennuksen jälkeen. pg_dump ottaa yhdestä tietokannasta johdonmukaisen kopion; mukautetun muodon varmuuskopion voi palauttaa valikoiden pg_restorella. Säilytä varmuuskopio eri paikassa kuin palvelin.

Tietokannan suorituskyky vaikuttaa suoraan sovelluksen yleiseen nopeuteen. Tulosta syntyy, kun kyselyajat, indeksit ja palvelimen vasteaika käsitellään yhdessä; tätä kokonaisvaltaista otetta käytämme nopeus- ja suorituskykyoptimoinnissa.

pg_dump -h localhost -U uygulama -F c -f magaza.dump magaza
pg_restore -h localhost -U uygulama -d magaza --clean magaza.dump