db.dobo.sk

Postgres – vsetko, co si chtel vediet, ale bal sa spytat svojho seniora, aby si nevypadal ako jelito 01

5 komentárov

postgres_physical

subory

postgresql.conf – hlavny konfigurak serveru, tu aj cesty k datafiles a binarkam a pod.

pg_hba.conf – definuje sposoby autentikacie klientov

pg_ident.conf – definuje mapovanie lokalnych uctov na postgres ucty

postmaster.opts – zapis parametrov, s ktorymi bol Db server naposledy spusteny

tabulky a indexy su storovane v datafiloch v adresari /base  a maju cisla podla OID databaze. Kazdy datafile ma 3 subory, napr. 14327, 14327_fsm (free space map) a 14327_vm (visibility map)

adresare

 /base – tu su subory per-database (hlavne datafajly)

/global – subory pre cely cluster, napr. tabulky pg_database, pg_control a pod.

/pg_tblspc – symbolicke linky na tablespaces….

databaze

Po instalacii su defaultne nainstalovane 3 databaze: postgres, template0 a template1. Postgres ma “sefovacia” databaza s informaciami o clustri, templaty s apouzivaju ako predlohy pri vytvarani user databaz. Preto sa instalacii postgresu hovori cluster, pretoze je tam hned niekolko databazi :)

tabulky – struktura

Pod tabulkou je myslena typicka heap tabulka. Tabulka a indexy su sucastou datafajlu. Datafajl je sled datovych blokov, kazdy 8192 B velky a cislovany sekvencne (od 0). Ked sa datafajl zvacsuje, tak sa proste na jeho koniec pridavaju nove bloky.

Blok ma 3 casti: header (popis bloku), pointery (ukazuju na data v tuples) a tuples (vlastne samotne data). Najdolezitejsim udajom v headeru je pd_lsn - je to ukazatel na odpovedajuci identifikator vo WAL logu.

V pripade, ze za cita z datafajlov tak pripadny index procesu vlastne len povie “pozadovane data su v bloku X a v tuple Y”.

postgres_tables

 

tablespaces

Su logicke kontajnery pre data (databazy, tabulky, indexy, etc…). Ich vyznam – napr. pokial sa zaplni partisna s postgresom, je mozne urobit novu tablespace nad inou partisnou a pokracovat. Pri vytvarani tablespace sa vzdy musi uviest full path v ramci filesystemu a systemuser postgres MUSI BYT VLASTNIKOM TOHOTO ADRESARA!!! Nepomaha ani chmod 777! po instalacii sa vytvaraju defaultne 2 tablespace – pg_global a pg_default.

Bez zastavenia serveru nie je mozne menit adresar pre tablespace. Objekty (napr. tabulky) je mozne presuvat medzi tablespacema.

SELECT spcname FROM pg_tablespace; - vylistovanie vsetkych tablespaci
SELECT tablespace FROM pg_tables WHERE tablename = 'NAZOV_TABULKY'; - zistim, do akeho tablespace patri konkretna tabulka

indexy – typy a struktura

Indexov ako maku, od klasickych (hash, Btree) az po mne nezname (GIN, RUM a pod).

perfektny serial o indexoch od Jegora Rogova.

 

procesy

postgres_procesy

Po nastartovani posgresu sa spusti hlavny proces, ktory spusta background procesy (checkpointer, writer, wal writer, autovacuum launcher, stats collector, bgworker/replicator, logger, archiver). Okrem toho kazda koneksna do DB (klientsky dotaz) sposobi fork postgresu.

ps -ef | grep postgres
postgres   941     1  0 Jul08 ?        00:00:01 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf
postgres   954   941  0 Jul08 ?        00:00:00 postgres: 10/main: checkpointer process   - cekpointy dat
postgres   955   941  0 Jul08 ?        00:00:00 postgres: 10/main: writer process   – dirty data z shared buffru su zapisovane na perzistentnu storage
postgres   956   941  0 Jul08 ?        00:00:00 postgres: 10/main: wal writer process   - zapisuje do wal logov
postgres   957   941  0 Jul08 ?        00:00:01 postgres: 10/main: autovacuum launcher process   - spusta vacuum
postgres   958   941  0 Jul08 ?        00:00:01 postgres: 10/main: stats collector process   – zhromazduje statistiky o databazi a zapisuje ich do pg_stat_database a pg_stat_activity
postgres   959   941  0 Jul08 ?        00:00:00 postgres: 10/main: bgworker: logical replication launcher
postgres   960   941  0 Jul08 ?        00:00:00 postgres: 10/main:archiver process - pokial je zapnuty archivny mod, tak archivuje wal logy

Bacha, nikdy napouzivat kill -9 s umyslom zastavit jeden proces – toto restartne cely server, i ked umysel bol len odstrelit jeden proces (napr. nejaky dlhy select). Obecne vzate je spatnou filozofiou zastavovat databazove procesy prostriedkami OS, miesto toho radsej priamo DB prostriekami. Priklad – nejaka koneksna/proces vyziera moc pamate a chcem ju zastavit:

lognutie do DB

SELECT * FROM pg_stat_activity WHERE state = 'active';
SELECT pg_cancel_backend(<pid of the process>) - ukoncuje query
alebo SELECT pg_terminate_backend(<pid of the process>) - pozor, ukoncuje sessnu
 

pgbouncer

Problem s pamatou posgresu suvisi s tym, ze kazda nova klientska koneksna odstartuje novy proces (master daemon sa forkne) a pokial to ide takto dalej, tak dojde pamet (nejde totizto o thready, ale procesy). Mnozstvo paralelnych koneksn povoluje parameter max_connections v hlavnom konfiguraku a defalutne je 100. Postgres neponuka nativne ziadny pooling/dispatcher mechanizmus pre pripajanie klientov a preto bola zrodena utilita pgbouncer.

pamet

postgres_memory

Su dve pamete – Local Memory Area (pre background procesy) a Shared Memory Area (pre data klientskych koneksn a pod.)

LMA: work_mem (hlavne joiny tabuliek); maintenance_work_mem (vacuum a pod)…

SMA: shared_buffer_pool (hlavna pracovna pamet pre dotazy, postgres tu naloaduje tabulky a indexy a pracuje s nimi); WAL buffer (pamet pro pracu ss wal logmi)….

basix s psql

psql connecting to database:
psql -h HOST -d DATABASE -U user
Quick cesta je psql postgres postgres = pod uzivatelom postgres sa konektni na postgres, predpoklad je, ze root sa nemusi autentizovat. Po defaultnej instalacii postgresu je v OS vytvoreny uzivatel postgres, ktory sa prihlasi do Db serveru bez kredentials

vylistovanie databazi v systemu \l
psql rychly switch na inu DB \c NAZOV_DATABAZE
zoznam tabuliek v databaze \dt
zoznam userov v databaze \du
zoznam funkcii v dtabaze \df
describe tabulku \d NAZOV_TABULKY
edituj komand vo svojom editore \e COMMAND

ine uzitocne prikazy

sudo -u postgres psql -c “SHOW data_directory”;  – ukaze data directory

píše: ďobo

Júl 4th, 2018 o 12:23 pm

chlievik: postgres,postgresql

5 odpovedí to 'Postgres – vsetko, co si chtel vediet, ale bal sa spytat svojho seniora, aby si nevypadal ako jelito 01'

Subscribe to comments with RSS or TrackBack to 'Postgres – vsetko, co si chtel vediet, ale bal sa spytat svojho seniora, aby si nevypadal ako jelito 01'.

  1. Ad Pgbouncer:
    1. Každé spojenie do databázy (hanshake, šifrovanie a pod.) si vezme tak 10 MB, preto je pgbouncer doležitý
    2. Hlavný konf je pgbouncer.ini
    3. Okrem spojenia s databázou sú najdoležitejšie parametry max_client_conn a default_pool_size – https://pgbouncer.github.io/config.html#max_client_conn
    4. kúzelná formulka znie max_client_conn + (max pool_size * total databases)
    5. https://www.depesz.com/2012/12/02/what-is-the-point-of-bouncing/

    dobo

    23 Júl 19 at 10:30

  2. ďobo

    23 Júl 19 at 14:04

  3. Ako si vyexportovat SELECT do CSV suboru, dva sposoby:
    1.:
    psql -d dbname -t -A -F”,” -c “select * from TABULKA” > output.csv
    2.:
    Log do DB a potom
    \copy (SELECT * from users) TO ‘/tmp/output.csv’ WITH CSV;

    ďobo

    5 Aug 19 at 13:21

  4. Jedna z možných konfigurácíí pgbounceru pre veľké prostredia:
    1. je vystavený na samostatnom proxy servri, ktorý odchytáva pripojenia a zprostredkuje ich na DB server. T.j. userom (napr. analytikom) sa dá len názov hosta (= proxyna) a databázy – pg bouncer na hostu to pekne predá ďalej na na správny db server.
    2. Na proxyne sa vytvori lokálny účet, na ktorý sa budú užívatelia DB mapovať (napr. app_pgbouncer) – neni nutno potom zakladať užívateľov priamo v DB.
    3. Je možné urobiť aj mapovanie názvov DB – užívatelia sa hlásia k DB “moja”, ale pgbouncer to prekladá na názov DB “juchuchu”.
    4. Všetko je to ošéfované v pgbouncer.ini, príklad jednoriadkového konfigu:
    moja = host=10.34.22.2 port=5432 dbname=juchuchu max_db_connections=10 auth_user=app_pgbouncer

    ďobo

    12 Aug 19 at 11:38

  5. reload konfigu (postgresql.conf) bez nutnosti restartnut DB>
    1. SELECT pg_reload_conf();
    2. /usr/bin/pg_ctl reload

    dobo

    1 Okt 19 at 8:28

okomentuj