Installere PostgreSQL og grunnleggende innstillinger

01Hva er PostgreSQL, og hvorfor velge det?

PostgreSQL er et gratis relasjonsdatabasesystem med åpen kildekode og en historie på nesten førti år. Takket være transaksjonssikkerhet (ACID), kraftige datatyper, JSONB-støtte og avanserte indekseringsmuligheter brukes det alt fra små prosjekter til applikasjoner med mye trafikk.

PostgreSQL er et trygt valg for data der konsistens er kritisk, som reservasjoner, lager, bestillinger eller annonser. I systemer der flere personer endrer samme post samtidig, for eksempel en hotellprogramvare eller en nettbutikkplattform, er transaksjonssikkerhet derfor viktig. Vil du raskt teste PHP og database lokalt, er XAMPP-installasjon også en praktisk start. MySQL brukes også mye i lignende oppgaver, og vil du sammenligne de to systemene, kan du se guiden til MySQL-installasjon.

I denne artikkelen går vi gjennom alt du trenger den første dagen etter installasjonen: opprette roller og databaser, innstillinger for ekstern tilkobling, sikkerhetskopiering og de vanligste feilmeldingene.

02Dette trenger du før installasjonen

Å avklare noen valg før du starter, gir færre problemer senere. Bestem deg tidlig for hvilken hovedversjon du skal bruke på en produksjonsserver. Å bytte mellom hovedversjoner krever sikkerhetskopi og gjenoppretting, eller pg_upgrade.

Valg av metode er også viktig. På en utviklingsmaskin er Docker den enkleste måten å kjøre ulike versjoner side om side uten konflikter. På en produksjonsserver er en tjeneste installert via operativsystemets pakkebehandler mer forutsigbar når det gjelder sikkerhetsoppdateringer, logger og automatisk oppstart.

• En bruker med administratorrettigheter (Windows) eller sudo-rettigheter (macOS, Linux)
• At standardporten 5432 ikke brukes av et annet program
• Gjeldende støttede hovedversjon på det offisielle nettstedet: postgresql.org
• Et sterkt passord for superbrukeren, lagret et trygt sted
• En fungerende Docker-installasjon hvis du bruker Docker

03Installasjon per operativsystem

Alle metodene nedenfor installerer fra offisielle eller offisielt anbefalte kilder. Bytt ut versjonsplassholderen i kommandoene med hovedversjonen du har valgt.

Windows

Last ned installasjonsprogrammet fra EDB på Windows-siden til postgresql.org. Veiviseren installerer serveren, pgAdmin, kommandolinjeverktøyene og Stack Builder i én operasjon. Den spør etter passordet til postgres-brukeren, porten og språkinnstillingen.

For å bruke psql-kommandoen i alle terminaler etter installasjonen, legger du bin-mappen til i PATH-variabelen. Snarveien SQL Shell (psql) i startmenyen gjør samme nytte.

# Mappenavnet varierer etter hovedversjonen du har installert
$env:Path += ";C:\Program Files\PostgreSQL\<versjon>\bin"
psql --version
psql -U postgres -h localhost

macOS (Homebrew)

I Homebrew leveres PostgreSQL som pakker med versjonsnummer. List først opp tilgjengelige pakker, installer deretter hovedversjonen du vil ha og start den som en tjeneste. Foretrekker du et grafisk grensesnitt, er Postgres.app et utbredt alternativ.

brew search postgresql
brew install postgresql@<versjon>
brew services start postgresql@<versjon>
# Pakker med versjonsnummer legges kanskje ikke til i PATH automatisk;
# bruk linjen echo 'export PATH=...' fra installasjonsutdataene
psql --version
psql postgres

Ubuntu og Debian

Pakken i distribusjonens eget depot holder for de fleste prosjekter. Trenger du en nyere hovedversjon, kan du legge til PGDG-depotet som postgresql.org beskriver, og installere derfra.

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

For et utviklingsmiljø er det offisielle postgres-imaget den reneste løsningen. Du kan starte og stoppe databasen uten å installere en fast tjeneste på maskinen. Koble alltid til et navngitt volum slik at data ikke går tapt.

• I offisielle images fra versjon 18 og nyere kobles volumet til stien /var/lib/postgresql.
• I images fra versjon 17 og eldre kobler du volumet til /var/lib/postgresql/data. Sjekk merknaden på imagets side på Docker Hub.

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

docker exec -it pg psql -U postgres

04Opprette bruker og database

Rollen postgres som følger med installasjonen, er en superbruker, og det er ikke trygt å koble applikasjonen til med den. Opprett en egen rolle for hvert prosjekt, med rettigheter bare i sin egen database.

I PostgreSQL er bruker og rolle det samme: en rolle med LOGIN-rettighet fungerer som en bruker. Rollen du gjør til eier av databasen, kan opprette tabeller i public-skjemaet i den databasen.

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

-- Bruker for skrivebeskyttet rapportering
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;

05De mest brukte psql-kommandoene

psql er PostgreSQLs kommandolinjeklient. Metakommandoene som begynner med omvendt skråstrek, sendes ikke til serveren som SQL, men tolkes av klienten selv, og de gjør navigeringen mye raskere.

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

06Administrere med pgAdmin

pgAdmin er det offisielle, gratis administrasjonsverktøyet for PostgreSQL. Det følger med Windows-installasjonsprogrammet. På macOS og Linux installeres det separat fra pgadmin.org, eller du kan kjøre det som Docker-image.

Første gang du åpner det, definerer du en ny server med Register > Server. Skriv inn localhost som host, port 5432, brukernavn og passord i Connection-fanen. Etter tilkoblingen kan du bla gjennom tabellene, kjøre spørringer i Query Tool og ta sikkerhetskopi og gjenopprette fra menyen.

Skal du koble til en ekstern server, er det tryggere å bruke SSH-tunnelen i pgAdmin enn å åpne databaseporten mot internett.

07Ekstern tilkobling og grunninnstillinger

Som standard lytter PostgreSQL bare etter lokale tilkoblinger. Plasseringen av konfigurasjonsfilene varierer med operativsystem og versjon, så finn først ut hvor de ligger fra psql.

Verdien listen_addresses i postgresql.conf bestemmer hvilke adresser det lyttes på, mens pg_hba.conf bestemmer hvilken klient som autentiseres med hvilken metode. Bruk metoden scram-sha-256 for tilkoblinger med passord, og hold IP-området du tillater, så smalt som mulig.

• shared_buffers: den offisielle dokumentasjonen anbefaler omtrent en fjerdedel av minnet som utgangspunkt på en dedikert server.
• max_connections: bruk heller tilkoblingspool i applikasjonen enn å sette den høyt.
• Slå på innstillingen log_min_duration_statement for å finne trege spørringer, og undersøk dem med EXPLAIN ANALYZE.

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

# Omlasting er nok for endringer i pg_hba.conf
sudo systemctl reload postgresql
# Endring av listen_addresses krever omstart
sudo systemctl restart postgresql

08Sikkerhetskopi og vanlige feil

Planlegg automatisk sikkerhetskopiering så snart installasjonen er ferdig. pg_dump tar en konsistent kopi av én enkelt database, og en kopi i egendefinert format kan gjenopprettes selektivt med pg_restore. Lagre kopien et annet sted, ikke på samme server.

Databaseytelse påvirker hastigheten til hele applikasjonen direkte. Resultater får du når spørretider, indekser og serversvar ses i sammenheng, og denne helhetlige tilnærmingen bruker vi i arbeidet vårt med hastighet og ytelse.

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