PostgreSQL
Contents |
JDBC parametri konekcije za PostgreSQL
JDBC connection string je oblika:
jdbc:postgresql://server.rcub.bg.ac.rs/baza
Default port je 5432, a drajver je:
org.postgresql.Driver
Hijerarhija
Hijerarhija u Postgresu je baza, šema, tabela. JDBC string za konekciju prima kao parametar samo ime baze, a ne i šemu. Tako da je verovatno najlakše kreirati posebne baze i koristiti njihove default "public" šeme.
Instalacija putem YUM managera
Potrebne informacije su ovde.
PSQL komande
psql je linux konzolni SQL klijent. Pokretanje iz klijenta kao root će vam verovatno prouzrokovati poruku o grešci
psql: FATAL: database "root" does not exist
Jer psql pokusava da otvori bazu koja ima isti naziv kao i korisnik, a koja ne postoji. Zbog toga je potrebno prvo se prebaciti na korisnika postgres sa
su - postgres
i zatim pozvati psql.
Komande koje vam mogu biti od interesa:
\l - listanje baza podataka \c - izbor aktivne baze podataka \dn - listanje svih sema u izabranoj bazi \dt - listanje svih tabela \du - listanje svih korisnika
Kreiranje baze u Postgresu. Prilikom kreiranja treba obratiti pažnju na lokalizaciju. Ukoliko je potrebno da se latinična sortiranja rade po srpskoj latinici, potrebno je navesti odgovarajuće parametre, npr:
CREATE DATABASE test_baza ENCODING = 'UTF8' LC_COLLATE = 'sr_RS' LC_CTYPE = 'sr_RS' TEMPLATE template0;
Ukoliko je potrebno da se latinična sortiranja rade po engleskim pravilima:
CREATE DATABASE test_baza WITH ENCODING='UTF8';
Srpska ćirilična sortiranja bi trebalo da rade u oba slučaja ispravno.
Kreiranje korisnika:
CREATE USER username PASSWORD 'l0zinka';
Izbor baze:
\c test_baza
Dodeljivanje privilegija nad default "public" šemom.
GRANT ALL ON SCHEMA public TO username;
Alternativa kreiranju korisnika koji pristupa public šemi, je kreiranje istoimene šeme i korisnika sa
CREATE SCHEMA AUTHORIZATION username
čime će biti kreirana šema username nad kojom korisnik username po defaultu ima sve privilegije (nije potreban grant).
Kratka myslq -> psql tabela
MySQL mysql
|
PostgreSQL psql
|
|---|---|
SHOW COLUMNS;
|
\d table-name
|
SHOW DATABASES;
|
\l
|
SHOW PROCESSLIST;
|
SELECT * FROM pg_stat_activity;
|
SHOW TABLES;
|
\d
|
SHOW USERS;
|
\du
|
USE db-name;
|
\c db-name
|
Konfigurisanje servera
Da biste omogućili pristup serveru preko JDBC-ja odnosno TCP/IP-ja, potrebno je da u data/postgresql.conf (najčešće u /var/lib/pgsql) navedete:
listen_addresses = '*' # what IP address(es) to listen on; port = 5432 # (change requires restart)
i da u data/pg_hba.conf dodate:
# TYPE DATABASE USER CIDR-ADDRESS METHOD host mydb ognjen 147.91.4.123/32 md5
posle promena na kofiguracionim fajlovima potrebno je da restartujete server ili da kao korisnik postgres pozovete reload konfiguracije:
sudo -u postgres pg_ctl reload -D /var/lib/pgsql/data/
Za postgres 9.0:
sudo -u postgres /usr/pgsql-9.0/bin/pg_ctl reload -D /var/lib/pgsql/9.0/data/
Optimizovanje servera
Za izvlačenje maksimuma mogućnosti iz PostgreSQL-a poželjno je pročitati detljno ceo Optimization Guide. Poglavlje Tuning se najviše tiče optimizovanja svakodnevnog rada servera i objašnjava sve tajne postgres.conf. Jedan od interesatnih članaka je ovde a postoji i cela knjiga.
Moguće je dati neke osnovne preporuke za optimizovanje PostgreSQL servera. Poželjno je dodeliti serveru više memorije nego što je konfigurisano u podrazumevanim postavkama. U sledećim postavkama u tabeli, koje treba uzeti s zrncem soli, se nalazi jedan predlog postavki koji bi trebalo da omogući performantniji rad PostgreSQL servera za konfiguraciju koja bi PostgreSQL serveru ostavila 1GB radne memorije, 16GB radne memorije i poređenje sa podrazumenvanim postavkama u PostgreSQL-u 8.3:
| 8.3 def | 1GB | 16GB | komentar | |
|---|---|---|---|---|
max_connections
|
100 | 30 | 30 | proceniti koliko je konekcija zaista potrebno i dodati neku rezervu |
shared_buffers
|
32MB | 256MB | 4096MB | treba RAM/4 |
effective_cache_size
|
1000 | 128MB | 10240MB | treba RAM/2, mada može i do RAM*3/4 |
chekpoint_segments
|
3 | 15 | 15 | minimum 10 ako je write intensive aplikacija može i 32 |
checkpoint_completion_target
|
0.5 | 0.9 | 0.9 | uvek se povećava na 0.9 kada se povećeva chekpoint_segments
|
default_statistics_target
|
10 | 100 | 100 | uvek na 100 |
work_mem
|
1MB | 4MB | 40MB | obratiti pažnju da max_connections*work_mem bude manji od npr. RAM/8
|
Slične rezultate moguće je dobiti i ovim odličnim online kalkulatorom za podešavanja PostgreSQL-a online kalkulatorom za podešavanja PostgreSQL-a.
Koriscenje proceduralnih jezika
PostgreSQL podrzava vise proceduralnih jezika: PL/pgSQL, PL/Tcl, PL/Perl, PL/Python. Uključivanje podrške za odgovarajući jezik na nivou baze podataka se radi sa, npr:
CREATE LANGUAGE plpgsql;
Backup i restore
Backup celog DB klastera (dakle svih baza podataka) radi lokalni korisnik 'postgres' direktno na serveru sa:
pg_dumpall > db.dump
Restore dumpa baze podataka radi na sveze instaliranom postgresu tj. sveze inicijalizovanom DB klasteru [1] lokalni korisnik 'postgres':
psql -f db.dump postgres
Ukoliko za restore nije na raspolaganju sveze instaliran Postgresa, tj. sveze inicijalizovan klaster, vec na primer treba uraditi restore posto je klaster ostecen (corrupted), onda je procedura sledeca:
- Bekapovati fajlove pg_hba.conf i postgresql.conf iz DB klaster direktorijuma (najcesce /var/lib/pgsql/data).
- Obrisati ceo DB klaster direktorijum.
- Inicijalizovati DB klaster.
- psql -f db.dump postgres
Nakon restore-a DB klastera pametno je da se update-uju statistike sa:
vacuumdb -az
Upgrade major verzije
Kod Postgresa su prve dve cifre oznaka major verzije, što znači da je npr. 9.2 major upgrade u odnosu na 9.1 i slično.
Major upgrade podrazumeva da je došlo do promene na strukturi podataka u fajl sistemu i da je potreban export/import prilikom upgrade-a.
Za razliku od toga kod minor upgrade-a (npr. 9.1.2 u 9.1.4) struktura podataka je sigurno ostala ista i nije potreban eksport i import.
Pre upgrade-a uraditi backup, i premestiti DB klaster direktorijum:
su - postgres pg_dumpall > pg_all.sql exit service postgresql-9.3 stop mv /var/lib/pgsql/9.3 /var/lib/pgsql/9.3-old
Opisano u
http://www.postgresql.org/docs/9.5/static/upgrading.html
Za upgrade servera treba koristiti Postgresov yum repozitorijum:
http://yum.pgrpms.org/reporpms/
Prateci linkove, nađite repository RPM, na primer:
https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6-x86_64/pgdg-centos95-9.5-2.noarch.rpm https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6-i386/pgdg-centos95-9.5-2.noarch.rpm
https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-5-x86_64/pgdg-centos95-9.5-2.noarch.rpm https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-5-i386/pgdg-centos95-9.5-2.noarch.rpm
Instalirajte RPM sa:
wget https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-5-x86_64/pgdg-centos95-9.5-2.noarch.rpm yum install pgdg-centos95-9.5-2.noarch.rpm yum remove pgdg-centos93 yum remove pgdg-centos91
Proverite da li se u /etc/yum.repos.d/ nalazi pgdg-XX-centos.repo. Proveriti da nema viškova (9.1 i 9.3). Ako ima konflikta deinstalirati stari Postgres.
Isključite traženje Postgresa iz standardnog repozitorijuma tako što ćete u fajlu /etc/yum.repos.d/CentOS-Base.repo na kraj sekcija [base] i [updates] dodati liniju:
exclude=postgresql*
Za 9.5, instalirajte postgres:
yum install postgresql95-server
Terebalo bi da je kreiran novi, prazan direktorijum /var/lib/pgsql/9.3/data. Proveriti. Kao korisnik root izvršiti inicijalizaciju DB klastera:
service postgresql-9.5 initdb
Startovati Postgres service kao korisnik root:
service postgresql-9.5 start
Uraditi restore baze:
su - postgres psql -d postgres -f pg_all.sql
Uraditi vacuum:
vacuumdb -az exit
Ukljičiti novu verziju postgresa:
chkconfig postgresql-9.3 off chkconfig postgresql-9.5 on --levels 2345
Podesiti postgresql.conf i pg_hba.conf. Podesiti iptables.
su - postgres mv 9.5/data/pg_hba.conf 9.5/data/pg_hba.conf.orig cp 9.3/data/pg_hba.conf 9.5/data/pg_hba.conf cp 9.5/data/postgresql.conf 9.5/data/postgresql.conf.orig
