PostgreSQL, kursanteckningar Advanced DBA

Detta är anteckningar från PostgreSQL-kursen i Kalmar 2012-02-28--03-01

Allmänt

Det finns inget företag som "äger" PostgreSQL på samma sätt som t ex MySQL. Ingen kan köpa upp PostgreSQL utan att köpa upp en massa småföretag. PostgreSQL följer BSD-licensen.

"Allt" är plugins, ett designkrav och så skall det också vara. Även replikering är ett plugin, även om det finns en "standardreplikeringsmetod". Dessutom är grundtanken att "reliability comes first, performance always comes second" då man inte vill ha ett system där data kan förloras. Det är helt och hållet skrivet i C, kan köras på som minst 32 MB RAM och som mest 128 GB RAM med 20-24 CPUer. PostgreSQL använder inte RAW-devices utan allt lagras i filer.

Installation och uppgradering

Använd gärna systemets egna PostgreSQL-installationer UTOM för RedHat eller Debian/Ubuntu LTS då dessa levererar en gammal eller för gammal PostgreSQL. Man kan från http://yum.postgresql.org/ välja en bättre YUM-repository eller hämta RPM-filer direkt för att få senaste version. Välj efter denna ordning:

1. Plattformspaket (RPM/DEB)
2. Binärfiler (TGZ)
3. Källkod

Uppgraderingar finns i två versioner. Minor och Major. Major är en helt ny version med uppräkning av andra versionssiffran (t ex 9.0 -> 9.1) och innehåller nya funktioner m.m. Minor är kritiska bugfixar och räknar upp tredje versionssiffran. Vanligtvis är det väldigt enkelt att uppgradera minorversioner.

RPM-paketen finns i dessa varianter:

• postgresql91 - bas-systemet inkl klient men inte server
• postgresql91-server - servern
• postgresql91-docs - dokumentation i HTML-format. Mansidor följer med övriga paket
• postgresql91-libs - delade bibliotek
• postgresql91-devel - filer för C-utvecklare
• postgresql91-contrib - plugin-moduler. Rekommenderas att installera då de inte startas utan bara tar upp extra diskutrymme
• postgresql91-pl* - olika paket för t ex stored procedures skrivna i Perl eller annat språk
• postgresql91-test - Testprogramvara. Behövs ej.
• postgresql91-debuginfo - Verktyg för debug om man t ex kan återskapa ett fel och få mer info via dessa verktyg

Före version 9 kunde PostgreSQL enbart installeras som en version med RPM. Senare versioner möjliggör att RPM kan hantera flera olika versioner installerade.

Använd INTE GUI-installationen i Linux. Den innehåller egna libs och utgör licenskonflikt. Bland annat försvinner readline (eller vad det heter) i psql.

Managera PostgreSQL

PostgreSQL "går inte att köra som root" utan man använder en annan användare, t ex postgres.

pg_ctl används för att styra PostgreSQL och använder UNIX-systemets ALLA signaler för att kommunicera med processerna. Dessa signaler har olika betydelser beroende på vilken process de kommunicerar med, så försök inte själv skicka dessa med kill-kommandot. pg_ctl körs som sagt inte som root.

pg_ctl tar dessa argument:

• start - start av databasen
• stop -m <method>- stoppar databasen, med tillägget -m som betyder:
  • smart (standard) väntar tills ALLA anslutningar stängts och tar ned databasen. Inga nya anslutningar tillåts under väntetiden.
  • fast (bättre än smart) Tillåter inga nya anslutningar och stänger de befintliga. Tar ned databasen normalt med att tömma cache m.m. (vilket smart också gör).
  • immediate (brutal) Tar ned databasen direkt utan att tömma ev. cache. Enbart för nödsituationer.
• restart - Stoppar och startar databasen
• reload - Läser om ALL konfiguration. Kontrollera i serverlog att det gick bra då processerna inte kan sända tillbaka till pg_ctl huruvida det gick eller ej.
• status - status

Dessutom kan man lägga till -t för att tala om hur länge kommandot ska vänta innan det anser att operationen misslyckades. Man kan t ex vänta en minut från "smart" innan man väljer "fast" vid nedstängning.

Anslut till databasen

Anslutningar sker antingen via

• Local connection (snabbast) som sker över Unix Domain Socket
• TCP
• SSL

Local connection skapar en fil i /tmp/.s.pgsql.5432 som är portnumret för PostgreSQL. Notera att i RedHat/Ubuntu ligger denna fil i /var/run/postgresql. Detta funkar inte för NFS-monterade partitioner.

TCP connection kör på port 5432 som standard. Lyssnar enbart på localhost vilket man får ändra i pg_hba.conf och listen_addresses.

PgAdmin3 är det grafiska sättet att ansluta till PostgreSQL.

psql är motsvarande MySQLs klient mysql men med fler funktioner. Man kan skicka kommandon till PostgreSQL genom att sätta ett backslash framför kommandot, t ex \x

Som standard ansluter psql med den användare man är inloggad som, vilket gör att man inte kan vara root. Vill man ansluta som annan användare anger man exv: psql [OPTIONS] [DBNAME[USERNAME]] eller psql -d dbname -U username

Datatyper

Välj alltid korrekt datatyp. Spara t ex inte heltal som strängar då vissa heltal direkt mappar mot CPU-registren.

Numeriska datatyper är:

• int4 (int) 32-bit teckensatt heltal
• int8 (bigint) 64-bit teckensatt heltal
• real - 32-bit flyttal med avrundningsfel
• double - 64-bit flyttal med avrundningsfel
• numeric/deciaml - flyttal med angiven precision. Välj mellan
  • NUMERIC(precision,scale) - scale är antalet tecken till höger om decimal.
  • NUMERIC(precision) - inga decimaler
  • NUMERIC - varierande, systemet väljer själv lämpligast precision och scale.

Notera att NUMERIC är långsamt då det har en del overhead.

Textdatatyper är:

• text - oändligt antal tecken (< 1GB)
• varchar(n) - upp till n antal tecken
• varchar - samma som text
• char(n) - upp till n antal tecken där resten blir blanksteg. Dessa lagras INTE i databasen.
• char - Ett enda tecken. Snabbare än char(1).

Tidsdatatyper:

• timestamp with time zone ger både datum och tid med kännedom om korrekt time zone. Detta sköter databasen själv.
  • Från 4713 före kristus till 5874897 efter honom. För större spann finns plugin.
• date ger bara datum. Välj detta framför timestamp där man sätter tiden till 0.
• time ger bara tiden.
• Intervall

Exempel:

set timezone='Europe/Stockholm
select now() at time zone 'America/Chicago' 
select now() + '4 hours 30 minutes'; 
select '2012-02-20 14:00' - now();

PostgreSQL har koll på tidszoner, sommar/vintertid, skottdagar m.m.

Använd inte SQL-standarden "time with timezone".

Ytterligare datatyper:

• bool - nyttjar en bit, så lägg flera av dessa efter varandra för att fylla en byte
• inet - IP-adress i ipv4- eller ipv6-format
• cidr
• macaddr - MAC-adressformat
• uuid - UUID-format (som bara är annat sätt att uttrycka 128 bitar)
• xml

Transaktioner och MVCC

Allt är transaktioner i PostgreSQL. Även en select även om det inte syns för användaren.

Som standard är "autocommit" satt men vissa drivrutiner, t ex pythons, kan ändra detta utan att meddela. Detta är viktigt att kontrollera om man t ex satt den till OFF och sedan den slår över till ON utan att man egentligen ville det.

MVCC (Multi Version Concurrency Control) gör att samma rad kan existera på flera ställen samtidigt. Om flera försöker uppdatera samma rad sker olika saker beroende på vad den som hann först gör.

Vacuum

När en rad uppdateras görs en kopia av den gamla med det nya värdet i. Den gamla raden ligger kvar för de som ännu inte fått tillgång till den nya raden eller har kopplat från databasen. När en gammal rad inte längre har några anslutningar mot sig anses den "död" av Vacuum som markerar området som ledigt. En ny uppdatering kan då hamna där. Finns inte plats före senaste uppdaterad rad fylls det på efter och man kan i värsta fall få jättemånga rader som borde ha slängts för länge sen.

En sökning måste gå igenom ALLA dessa rader vilket kan ta lång tid om man inte fått städat på länge.

Buffer Cache

Lagrar sånt som senare kan skrivas till disk. Istf att direkt jobba mot disk. För att buffer cache inte ska bli full körs en Clocksweep algorithm som räknar ned en räknare för varje block i bufferten. Om detta block sedan används räknas denna räknare upp igen. När det når noll skrivs blocket till disk.

Konfiguration

Konfiguration kan visas med show <variable>;. T ex

show shared_buffers;
shared_buffers
--------------
24MB
(1 row)

eller select * from pg_settings where name='<variable>'; som ger ett utförligare svar. I det utförligare svaret finns ett contextfält som talar om "på vilken nivå" konfigurationen gäller.

• POSTMASTER = servern måste startas om för att konfig ska gälla
• SIGHUP = Ändras i konfigfil (ej med set-kommandot)
• BACKEND = Ändras i konfigfil men gäller inte de som redan är inloggade
• SUPERUSER = Kan ändras av superuser från psql (motsv)
• USER = Kan ändras från psql (motsv)

Konfig finns i filen postgresql.conf. Modellen är namn = värde. När det är ändrat, kör en pg_ctl reload och kontrollera logfilen att det blev som tänkt.

Alla värden i konfigfilen finns men de flesta är bortkommenterade. OM man ändrar ett sånt värde och sedan sätter tillbaka kommentarstecknet kommer standardvärdet att gälla (utom för äldre versioner av PostgreSQL).

Anslutningskonfig

listen_addresses

Standard: localhost. Lyssnar på unix sockets + 127.0.0.1 + ::1 (ipv6)

tom: Lyssnar bara på unix sockets

*: Lyssnar på alla IP + unix sockets

Kommaseparerad lista med IP: Lyssnar på angivna IP + unix sockets

Glöm inte ändra i hba.conf med. Ovanstående ändringar kräver omstart.

port

Standard: 5432

eller TCP/IP portnummer att lyssna på

Måste vara unik för varje server (oavsett IP) och högre än 1024.

max_connections

Standard: Beror på system. Normalt 100 vilket betyder samtidiga anslutningar. Av dessa är 3 reserverade för superuser. Denna använder systemresurser, sätt ej för högt. För lågt gör dock att anslutningar inte kan ske utan bara nekas inträde.

ssl

Standard: OFF utom för Debian/Ubuntu som ändå har "falska" certifikat. Sätt den till OFF där. Använd bara ON om man har satt vettiga certikat i systemet.

shared_buffers

Standard: Beror på systemet, normalt 32MB. Sätt till 1/4-1/3 av systemet men har man lite minne (<=1GB), sätt INTE till 1/3. Ska sällan vara över 8GB. Dessutom behöver man troligen uppdatera sysctl enligt:

sysctl.conf:
kernel.shmall=11111111111111 (jättehögt värde)
kernel.shmmax=11111111111111 (jättehögt värde här med)

Detta är enbart ett tak och inte de faktiska värden som kommer att användas.

effecive_cache_size

Standard: Beror på systemet. Detta är bara en vägledning och avgör hur mycket filsystemscache som kommer att finnas. Låt systemet gå en stund och se vad som används, sätt detta värde.

max_prepared_transactions

Standard: 0 (äldre versioner satte denna till 5). Används för two-phase commit och ska inte användas om man inte har såna. Annars drar det onödig prestanda ur systemet.

work_mem

Standard: 1MB. Gäller per sortering, inte per anslutning. För högt satt ger SWAPPING

meintenance_work_mem

Standard: 16MB. Används för create index, vacuum m.m. För dedikerade DB-servrar, sätt till 512MB. Vid stora DB-jobb kan detta vara bra att öka. Autovacuum kan köra 3 samtidiga sessioner vilket då blir 3xvärdet

fsync

Standard: PÅ. STÄNG INTE AV. Enda giltiga orsaken för att stänga av detta är vid laddning av mycket data då "inget" skrivs till disk. Efter att laddning är klar, ändra värdet till PÅ, starta om DB OCH server för att vara säker på att alla cachear är skrivna till disk.

synchronous_commit

Standard: PÅ vilket är rekommenderat. Vid ev krasch kan man förlora de senaste transaktionerna men i övrigt är detta ändå atomiskt och ger prestandaförbättring.

full_page_writes

Standard: PÅ. Skydd mot torn pages.

wal_buffers

Standard -1 = autotune = bra. (versioner äldre än 9.1 är denna satt till 64kB. Öka till 1MB för dedikerade system). Har man stängt av synchronous_commit bör denna ökas.

checkpoint_timeout

Standard: 300 sekunder. Hur ofta skrivs buffer cache till disk.

checkpoing_segment

Standard: 3x16 vilket är lågt. Sätt till 100x16. Betyder att cache skrivs till disk efter 100x16MB

checkpoint_warning

Standard: 30 sekunder. 0 ger ingen varning. Bör ökas till 90-120 s. Det betyder att en varning ges om en checkpoint körs oftare än 90 sekunder.

checkpoint_completion_target

Standard: 0.5. Ökas till 0.8 för dedikerade system med bra IO. Detta styr hur lång tid en checkpoint ska ta.

Säkerhet

Loginmetoder

Trust

Undvik. Det räcker med att man loggar in som rätt användare. Inget lösen.

Peer

Fungerar bara om man använder unix sockets. Det bygger på vem man är inloggad i operativsystemet som.

Ident

Lokalt på systemet: Samma som Peer. Remote: Ident-protokollet. Undvik då det inte är säkert idag.

MD5-lösen

Standardmodell, dubbelhashat. Blanda inte ihop med "PASSWORD" som lagrar i klartext på disk.

Konfig pg_hba.conf

Loginkonfig:

Källa      DB      Användare  IP                 Typ
local      all     all                           Peer (=Unix sockets)
host       all     all        127.0.0.1/32       md5  (TCP)
hostnossl  webdb   webuser    10.1.1.0/30        md5  (TCP men utan SSL)
hostssl    all     +admin     192.168.0.0./24    gss  (+admin betyder att användare är medlem i admin-grupp)
host       all     all        0.0.0.0/0          md5  (Alla hosts med korrekt lösen)