View source for 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:
#Plattformspaket (RPM/DEB)
#Binärfiler (TGZ)
#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 '''context'''fä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

<nowiki>*</nowiki>: 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)

[[Kategori:PostgreSQL]]