1. No category

PostgreSQL utbildning, IT-avdelningen.
Sida 1 (17)
PostgreSQL utbildning
Innehållsförteckning
1 PostgreSQL Introduktion...............................................................................................3
1.1 PostgreSQL Historia..............................................................................................3
1.2 PostgreSQL idag....................................................................................................3
Licensformen............................................................................................................3
1.3 Administrationsverktyg..........................................................................................3
psql:..........................................................................................................................3
pgAdmin:..................................................................................................................4
1.4 Anpassningsbar......................................................................................................4
1.5 Tips.........................................................................................................................4
1.6 Övning 1, Installation.............................................................................................4
2 Konfiguration.................................................................................................................6
2.1 Konfigurationsfiler.................................................................................................6
postgresql.conf:........................................................................................................6
pg_ctl.conf och start.conf.........................................................................................6
pg_hba.conf:.............................................................................................................7
pg_ident.conf: ..........................................................................................................7
2.2 Övning....................................................................................................................8
3 Server administration.....................................................................................................9
3.1 Konfiguration av server.........................................................................................9
3.2 Övning....................................................................................................................9
4 Användare hantering....................................................................................................10
4.1 Övning..................................................................................................................10
5 Administrativa sysslor..................................................................................................11
5.1 Templates.............................................................................................................11
5.2 Tablespace............................................................................................................11
6 Backup och restore......................................................................................................12
6.1 Backup.................................................................................................................12
pd_dump.................................................................................................................12
6.2 Restore.................................................................................................................12
6.3 Övning..................................................................................................................13
7 Övervakning och prestanda analys..............................................................................14
8 Replikering..................................................................................................................15
8.1 Inbyggt.................................................................................................................15
8.2 Slony....................................................................................................................15
8.3 Pg-pool.................................................................................................................15
8.4 Postgres-XC.........................................................................................................15
PostgreSQL utbildning, IT-avdelningen.
Tidsplan:
10:30 – 11:30
Intro installation av PostgreSQL i virtuella maskiner
11:30 – 12:30
LUNCH
12:30 – 14:45
Konfiguration, Server administration. Psql och pgAdmin
14:45 – 15:00
FIKA
15:00 – 16:00
Användare hantering, Administrativa sysslor, backup
09:00 – 09:30
Summering av gårdagen, fortsättning
09:30 – 09:45
FIKA
09:45 – 11:30
Fortsatt backup och recovery. Övervakning och prestanda analys
11:30 – 12:30
LUNCH
12:30 – 14:00
Replikering
Sida 2 (17)
PostgreSQL utbildning, IT-avdelningen.
Sida 3 (17)
1 PostgreSQL Introduktion
1.1 PostgreSQL Historia.
Började vid University of California at Berkley 1986.
Hete från börja postgres, Professor Michael Stonebroker → CTO för Informix. (IBM
köpte Informix 2001 för 1 billion dollar)
Efterföljare till Ingres så det blev ”after Ingres” postgres
1995 in med en del SQL så det blev Postgres95.
1996 lämnade den ”akademiska världen” och blev open source
1.2 PostgreSQL idag
Många plattformar
ACID
MVCC
Transaktioner
Triggers
Procedurer i olika språk
Skapa egna typer
Utöka funktionalitet med extensions. Skriva egna funktioner
Licensformen
Egen som heter PostgreSQL, väldigt lik BSD och MIT licens.
Vem äger PostgreSQL?
Ingen äger PostgreSQL. Det är en icke vinstdrivande förening med frivilliga och företag
som delar kod under PostgreSQL Licens. Det finns ca 200 utvecklare.
1.3 Administrationsverktyg.
psql:
CLI vertyg som följer med postgres vid installation.
För hjälp se:
http://www.postgresql.org/docs/9.1/static/app-psql.html
alt.
# man psql
Kan användas som en interaktiv konsol eller som ett program.
Vi användning som interaktiv konsol så loggar man in mot en maskin genom att ange
host och port. Default är localhost och port 5432. Man väljer en databas man vill jobba
mot. Inne i konsolen kan man skriva rena SQL kommandon och specifika kommandon
för psql. Dessa kommandon börjar alltid med ”\”. T.ex
PostgreSQL utbildning, IT-avdelningen.
Sida 4 (17)
postgres=# \?
Som är hjälp funktionen i psql.
Det finns många kommandon och vi kommer att använda en del under denna kurs.
Man kan i konsolen också skriva SQL direkt. En SQL sats avslutas med ”;” så man kan
skriva flera rader innan man exekverar en sats.
pgAdmin:
GUI applikation som utvecklas utanför PostgreSQL men som Open Source.
Samma Licens som PostgreSQL. Utvecklat i C/C++, använder wxWidgets som GUI
framework. Kan köras på Windows, Mac och Linux
1.4 Anpassningsbar
PostgreSQL är mycket anpassningsbar genom stöd för att skriva egna
procedurer/funktioner i olika programmeringsspråk.
Lika så finns det många bra script etc som några utvecklat. Dessa saker finns inte med i
kärnan av postgres utan finns som ett contrib paket.
Vidare kan man utöka postgres med egna typer och funktioner skrivna tex i ett
kompilerande språk för att få upp hastighet.
Man kan då paka ihop dessa typer och funktioner till en EXTENSION. Tidigare
versioner av postgres var man tvungen att registrera dessa extensions genom att köra
SQL filer mot sin databas. I version 9.1 så har man skapat en egen funktioner för det se
CREATE EXTENSION
1.5 Tips
Om man vill slipa ange password hela tiden då man använder sudo komandot så kan
man ändra i filen /etc/sudoers och skriva följande sist i filen
itadmin ALL=NOPASSWD:ALL
Då behöver man inte skriva sitt password då man använder sudo.
Detta rekomenderas inte på en produktions server men vid labb och utbildning kan man
använda det
1.6 Övning 1, Installation
Denna övning bygger på att man använder Ubuntu Server version 12.04 LTS.
Vi börjar med att installera PostgreSQL server och contrib paketet. Vi skall använda ett
script/ extensions i contrib paketet som underlättar då vi senare kopplar upp oss med
pgAdmin mot servern.
# sudo apt­get install postgresql
# sudo apt­get install postgresql­contrib
kolla att postgres har startat genom
# ps ­auxf
Nästa steg är att sätta password för super admin kontot ”postgres” vilket är den enda
användare som finns i databsen från början. När man installerar postgres så skapas
också en användare och en grupp på maskinen som heter postgres. Denna användare
har i ubuntu ett bash shell default. ( kolla i /etc/passwd resp /etc/group)
PostgreSQL utbildning, IT-avdelningen.
Sida 5 (17)
Om vi vill ändra på lösenordet för detta konto skriver man
# sudo passwd postgres
men det går också att ända sig till postgres användaren med.
# sudo su – postgres
Nu skall vi sätta ett password för användare postgres som finns i databasen. Vi visar två
sätt, först
# sudo ­u postgres psql postgres
postgres=# \password postgres Enter new password: Enter it again: postgres=# \q ##
det andra sättet är
# sudo ­u postgres psql postgres
postgres=# ALTER USER postgres WITH PASSWORD 'secretpassword';
Nu skall vi lägga till två extensions.
Det första är att vi skall lägga till en sak från contrib paketet som gör att vi kan göra en
del admin saker på servern från pgAdmin.
Så vi gör följande:
# sudo ­u postgres psql
Här anger vi inge databas då kommer psql att försöka ansluta till en databas med samma
namn som den USER som man anger. Och i linux så är default USER den user som man
är inloggad som. Vi kan se hur vi har anslutit oss genom kommandot:
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432". nu vet vi att vi jobbar mot databasen postgres som är system databasen i PostgreSQL
server. Så då skapar vi vår extension.
postgres=# CREATE EXTENSION adminpack;
PostgreSQL utbildning, IT-avdelningen.
2 Konfiguration
2.1 Konfigurationsfiler
Alla konfigurationsfiler ligger samlade på sökvägen:
/etc/postgresql/9.1/main/
Här finns filerna:
­rw­r­­r­­ 1 postgres postgres 316 nov 7 17:53 environment ­rw­r­­r­­ 1 postgres postgres 143 nov 7 17:53 pg_ctl.conf ­rw­r­­­­­ 1 postgres postgres 4649 nov 7 17:53 pg_hba.conf ­rw­r­­­­­ 1 postgres postgres 1636 nov 7 17:53 pg_ident.conf ­rw­r­­r­­ 1 postgres postgres 19259 nov 7 17:53 postgresql.conf ­rw­r­­r­­ 1 postgres postgres 378 nov 7 17:53 start.conf postgresql.conf:
kontrollerar servern inställningar.
Vi går igenom det i kapitel 3.
pg_ctl.conf och start.conf
Används då man skapa kluster
Sida 6 (17)
PostgreSQL utbildning, IT-avdelningen.
Sida 7 (17)
pg_hba.conf:
Filen innehåller regler för hur man tillåter och nekar uppkoppling mot databasen.
Formen är alltid
TYPE
DATABASE
USER
ADRESS
METOD
Vanliga typer är local, host, hostssl. För hostssl krävs att man sätter upp ett cerifikat
DATBASE kan vara alla eller en enskild databas
USER kan vara en användare eller en grupp eller alla.
För host behövs ange en adress. Här kan det vara en unik ip-adress eller ett nät. Vill man
tillåta allt anger man 0.0.0.0/0. Siffran efter / är nätmasken.
Vanliga metoder är trust, md5, password, ident men man kan också ha reject. Peer är
nytt i version 9.1 och finns bara på unix maskiner. Den försöker hämta användaren från
systemet via kernel. Ident fungerar så att den försöker hämta användaren från ident
servern. I båda fallen kan man göra mappning mot en användare i databasen. Password
är plain text och bör undvikas, använd md5 istället. Sedan finns det stöd för andra sätt i
Linux tex PAM, Radius, LDAP, Kerberos, SSPI, GSSAPI eller skapa ett eget.
Nedan visar utdrag från filen på en standard Ubuntu 12.04 installation.
# DO NOT DISABLE! # If you change this first entry you will need to make sure that the # database superuser can access the database using some other method. # Noninteractive access to all databases is required during automatic # maintenance (custom daily cronjobs, replication, and similar tasks). # # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres peer #host replication postgres 127.0.0.1/32 md5 #host replication postgres ::1/128 md5 Man ser då att det bara är local host som har behörighet att ansluta mot databasen. Vill
man öppna upp ett subnät så kan man ange
host
all
all
10.0.0.0/8
md5
pg_ident.conf:
används för att mappa en system användare mot en användare i databasen.
PostgreSQL utbildning, IT-avdelningen.
Sida 8 (17)
2.2 Övning
Börja med att öppna pgAdmin.
Försök att koppla upp mot er egen maskin. Dvs skapa en ny anslutning med knappen
längst till vänster på verktygsraden. Knappa in namn och host som är er egen maskin.
Ange database till postgres och username likaså. Password det som ni valde i första
övningen. Försök att koppla upp.
Vad händer?..................................
Varför?..........................................
Det första vi skall göra är att göra så att det går att ansluta via tcp/ip mot servern. Vi
behöver då ändra i postgresql.conf filen. Öppna upp filen och leta efter ”listen_address”.
Som ni ser i konfigurationsfilen så kan man här ange vilken adress som man vill lyssna
på. Vi ändrar raden så att vi lyssnar på alla interface
listen_addresses = '*'
sedan behöver vi starta om servern
sudo /etc/init.d/postgresql restart Försök koppa upp igen med pgAdmin.
Vad händer?..................................
Varför?..........................................
Nästa steg blir nu att ändra i pg_hba.conf filen så att vi tillåts att logga in mot maskinen.
Lägg till följande:
host
all
all
0.0.0.0/0
md5
under IPv4 konfigurationerna.
Sedan behöver vi starta om servern
sudo /etc/init.d/postgresql restart Försök koppa upp igen med pgAdmin. Fungerar?
PostgreSQL utbildning, IT-avdelningen.
Sida 9 (17)
3 Server administration.
3.1 Konfiguration av server
PostgreSQL server configures genom filen postgresql.conf.
Nu skall vi gå igenom några konfigurations parametrar som är bra att känna till.
Följande värden kräver omstart av servern för att de skall börja gälla:
•
max_connection, anger hur många samtida anslutningar som får se mot servern.
•
shared_buffers, anger hur mycket minne som alla anslutningar skall dela på för
spara ställa frågor. Denna parameter har mest inverkan på query-prestanda.
Minst 25% av tillgängligt RAM minne.
Följande parametrat är också viktiga men kräver ingen omstart men väl en reload för att
börja gälla.
•
effective_cache_size, är en uppskattning av hur mycket minne som du tror att
OS:et kan tilldela postgres processen. Används för ”query planner” för att
planera om ett index eller fråga rymms i RAM minne. Sätter man denna för lågt
så så kan det påverka utnyttjandet av index. Har man en dedikerad PostgreSQL
server så kan man minst sätta halva RAM minnet som en bra början.
•
work_mem, anger hur mycket minne som varje operation så som sortering, hash
join etc. Värdet här beror på hur man använder servern och hur många
användare som ansluter mot servern. Hur mycket RAM minne som finns i
servern. Detta värde kan också sättas per användare så att en power-user som gör
tyngre och mera sofistikerade frågor får mera minne.
•
maintenance_work_mem, detta anger den totala mängd RAM minne som
städnings aktiviteter så som vacuuming.
3.2 Övning
Öppna upp postgresql.conf filen. Kolla igenom vilka värden som är satta för värdena
ovan.
PostgreSQL utbildning, IT-avdelningen.
Sida 10 (17)
4 Användare hantering
Man kan skapa grupper och användare.
En användare kan tillhöra en eller ingen grupp.
En grupp är egentligen en användare i databasen med skillnaden att den användaren inte
kan logga in.
4.1 Övning
Vi skall börja med att kolla vart användarna sparas i Postgres.
Använd pgAdmin och koppla upp mot din databas.
1. koppa upp mot databasen postgres
2. Expandera Catalogs
3. Öppna katalogen ”PostgreSQL (pg_catalog)”
4. Gå till tabeller
5. Leta upp tabellen pg_authid
6. Lista den, vilka användare finns det?
7. Behåll denna lista öppen vi kommer att göra refresh då vi lagt till användare.
Nu skall du skapa 2 grupper och 3 användare med pgAdmin.
1. Skapa grupperna ”techer” och ”student”.
2. Skapa 3 st användare, test1, test2 och test3. Väl valfria password
3. Test 1 skall vara medlem i techer och test2 i student.
4. Gör nu refresh på listan från ovan.
5. Hur ser man att vad som är användare och grupp?
6. Ändra så att användare test3 blir en grupp
Prova att skapa en ny anslutning från pgAdmin men en av de användare som du nyss
skapat. När det fungerar. Ändra expiredat för den anväöndaren så att det slutar att gälla.
Prova att logga in in igen.
PostgreSQL utbildning, IT-avdelningen.
Sida 11 (17)
5 Administrativa sysslor
5.1 Templates
Default finns det två st default templates
1. template0
2. template1
Template1 är den som används som mall då man skapar en ny databas.
Template0 är en kopia på template1 och bör aldrig förändras.
Vill man att ett en viss EXTENSIONS skall finnas med i alla databaser på servern så
kan man lägga till det i template1
# sudo ­u postgres psql ­d template1 ­c 'CREATE EXTENSION adminpack;'
Likaså om det är ett programmeringsspråk för att skriva funktioner som man vill skall
finnas med i alla databaser så kan man lägga till det till template1
# sudo ­u postgres psql ­d template1 ­c 'CREATE LANGUAGE plperl;;'
Skapa egna genom
Two useful flags exist in pg_database for each database: the columns datistemplate and
datallowconn. Datistemplate can be set to indicate that a database is intended as a
template for CREATE DATABASE
# UPDATE pg_database SET datistemplate=true WHERE datname='mytemplate';
5.2 Tablespace
Skapa nya med:
CREATE TABLESPACE tablespace_name [ OWNER user_name ] LOCATION 'directory'
Exempel:
CREATE TABLESPACE dbspace LOCATION '/data/dbs';
CREATE TABLESPACE indexspace OWNER genevieve LOCATION '/data/indexes';
Ändra TABLESPACE för en databas:
ALTER DATABASE name SET TABLESPACE new_tablespace
Changes the default tablespace of the database. Only the database owner or a
superuser can do this; you must also have create privilege for the new
tablespace. This command physically moves any tables or indexes in the
database's old default tablespace to the new tablespace. Note that tables and
indexes in non-default tablespaces are not affected.
PostgreSQL utbildning, IT-avdelningen.
Sida 12 (17)
5.3 Extensions
Kolla vilka extensions som är installerade
select * from pg_available_extensions;
5.4 Övning
1. Ladda ner filen create_db.sql på
http://gislab.miun.se
2. Denna fil kräver ett extension som heter postgis
Installera det på maskinen med apt-get install
3. Kolla om detta EXTENSION finns med i databasen.
Vilken version har den?................
Vilken är den senaste versionen av POSTGIS på nätet?..................
4. skapa en ny användare i databasen som heter ggi2.
5. Kör skriptet mot databasen. Via psql, du måste skicka upp filen på servern med
scp eller Filezilla eller motsv....
6. Kolla i pgAdmin att databasen har skapats.
# sudo apt­get install python­software­properties
# sudo add­apt­repository ppa:ubuntugis/ubuntugis­unstable
# sudo apt­get update
# sudo apt­get dist­upgrade
# sudo apt­get install postgresql­9.1­postgis­2.0
Nästa steg så skall vi prova att skapa en ny TABLESPACE.
1. Skapa en ny katalog i home som heter dbstore
/home/dbstore. Ändra ägare och grupp på katalogen till postgres
sudo chown postgres.postgres /home/dbstore
2. Skapa en ny TABLESPACE utan ägare till denna katalog
3. Ändra TABLESPACE för GGI2 databasen till denna nya tablespace.
4. Kolla i katalogen vad som hänt.........
Nästa steg så skall vi skapa en ny template
1. Börja med att skapa en databas som heter template_postgis.
2. Ägaren skall vara postgres
3. Lägg till EXTENSION postgis till denna databas
4. Ändra nu så att denna databas blir en template.
5. Skapa en ny databas som heter GISTEST och som använder template_postgis
som template.
PostgreSQL utbildning, IT-avdelningen.
6. Kolla i den nya databasen att postgis finns med.
Sida 13 (17)
PostgreSQL utbildning, IT-avdelningen.
Sida 14 (17)
6 Backup och restore
6.1 Backup
Det finns två verktyg för att göra backup i postgres
1. pg_dump
2. pg_dumpall
pg_dumpall används för att göra backup på alla databaser i servern. Detta komando
måste köras som användaren postgres.
pg_dump kan användas för att göra backup på en specifik databas.
pd_dump
­f file, ­­file=file Send output to the specified file. This parameter can be omitted for file
based output formats, in which case the standard output is used. It must be
given for the directory output format however, where it specifies the target
directory instead of a file. In this case the directory is created by
pg_dump and must not exist before.
­F format, ­­format=format
Selects the format of the output. format can be one of the following:
p, plain
Output a plain­text SQL script file (the default).
c, custom
Output a custom­format archive suitable for input into pg_restore.
Together with the directory output format, this is the most flexible
output format in that it allows manual selection and reordering of
archived items during restore. This format is also compressed by
default.
d, directory
Output a directory­format archive suitable for input into pg_restore.
This will create a directory with one file for each table and blob being
dumped, plus a so­called Table of Contents file describing the dumped
objects in a machine­readable format that pg_restore can read. A
directory format archive can be manipulated with standard Unix tools;
for example, files in an uncompressed archive can be compressed with the
gzip tool. This format is compressed by default.
t, tar
Output a tar­format archive suitable for input into pg_restore. The
tar­format is compatible with the directory­format; extracting a
tar­format archive produces a valid directory­format archive. However,
the tar­format does not support compression and has a limit of 8 GB on
the size of individual tables. Also, the relative order of table data
items cannot be changed during restore.
6.2 Restore
Två sätt
1. psql med filer som skapats med pg_dump eller pg_dumpall som klartext filer.
2. pg_restore programmet för återställa databser som backuptas med pg_dump i
valfritt format som inte är klar text.
Så skall man importera en hel databas så måste man använda psql med en dump i
klartext.
PostgreSQL utbildning, IT-avdelningen.
För enskilda databaser så kan man använda någon av de två metoderna.
6.3 Övning
1. Gör en backup på servern av hela databasen.
2. Gör en backup av GGI2 databasen i både klartext och komprimerat.
3. Deleta GGI2 databasen
4. Gör en återställning med psql i klartext
5. Radera igen
6. Gör en återställning pg_restore från den komprimerade filen
Sida 15 (17)
PostgreSQL utbildning, IT-avdelningen.
7 Övervakning och prestanda analys
Munin
Nagios
pgAdmin - > TOOLS | Server status
Sida 16 (17)
PostgreSQL utbildning, IT-avdelningen.
Sida 17 (17)
8 Replikering
Omfattande område vi kommer bara berörö detta yttligt då jag saknar erfarenhet från
någon av dessa lösningar.
8.1 Inbyggt
Inbyggt i PostgreSQL finns idag stöd för master-slave replikering via Streaming
replikering. Man använder WAL Write Ahead Log. Postgres använder WAL på en singel
maskin. Så vill man ha en slave så kan man föra över all WAL data till en slave och köra
samma WAL data på den som man gör på master servern.
Med version 9 av postgres så införde man möjligheten att strömma denna data från en
master till andra maskiner. Detta kan göras som asyncron och syncron replikering.
PostgreSQL kan inte själv sköta om skiftning mellan master och slave. Här måste man
ha någon form av HA och Heartbeat.
Finna några olika lösningar så som
OpenAIS, Corosync, Linux-HA och Pacemaker
8.2 Slony
Slony en av de äldsta replikeringarna till postgres som fortfarande finns kvar och aktivt
utvecklas /används. Styrkor. Kan replikera på tabell nivå. Olika tabeller mellan olika
maskiner.
8.3 Pg-pool
Lastbalansering.
8.4 Postgres-XC
Är egentligen en egen version av postgres. Så det installeras från ett eget källkodsträd.
Här har man data-noder en global transaktions hanterare och koordinatorer.