PostgreSQL – kilka notatek

Kilka notatek związanych z PostgreSQL – sprawdzanie rozmiarów baz, tabel, index’ów i przenoszenie i backup  baz z kompresją w locie.

Sprawdzenie rozmiarów

  • Sprawdzenie rozmiaru wszystkich baz wchodzących w skład danego klastra PostgreSQL:

    SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
    lub dla nowszych wersji poprostu \l+

  • Sprawdzenie wybranej bazy:

    SELECT pg_size_pretty(pg_database_size('nazwa_bazy'));

  • Sprawdzenie rozmiaru wybranej tabeli, index’u, itp:

    SELECT pg_size_pretty(pg_relation_size('nazwa_tabeli'));

Przenoszenie/kopiowanie baz danych PostgreSQL pomiędzy komputerami

  1. Na maszynie docelowej:
    createdb -U postgres nazwa_bazy
    nc -l 7777 2>nc.nazwa_bazy.YYYY-MM-DD.err | psql -U postgres nazwa_bazy >psql.nazwa_bazy.YYYY-M-DD.err 2>&1 &
  2. Na maszynie źródłowej:
    time pg_dump -U nazwa_urzytkownika_bazy nazwa_bazy 2>pg_dump.nazwa_bazy.YYYY-MM-DD.err | nc naza_lub_IP_docelowe 7777 2>nc.nazwa_bazy.YYYY-MM-DD.err &

Uwaga: Netcat (nc) przesyła dane bez szyfrowania i uwierzytelnienia, a pg_dump dostarcza mu SQL, a wiec można w banalny sposób podejrzeć lub podmienić tak przesyłane dane, w związku z czym tą metodę powinno się stosować tylko w bezpiecznych sieciach – np. jak mamy bezpośrednio spięte oba hosty.
W przypadku przesyłania danych przez niezaufane sieci (w szczególności np.: internet, duże LANy) należy zastosować tunelowanie poprzez jakiś szyfrowany protokół z autoryzacja, najprościej przez SSH.

Backup baz danych PostgreSQL z kompresją gzip w locie

time pg_dump -U nazwa_urzytkownika_bazy nazwa_bazy 2>pg_dump.nazwa_bazy.YYYY-MM-DD.err | gzip > nazwa_bazy.YYYY-MM-DD.sql.gz 2>gzip.nazwa_bazy.YYYY-MM-DD.err &
time pg_dump -U postgres zabbix-master 2>pg_dump.zabbix-master-2017-02-11-test4-pigz-p6.err | pigz -p6 > zabbix-master-2017-02-11-pigz.sql.gz 2>pigz-p6.zabbix-master-2017-02-11.err

Przykłady dla testów na bazie zabbix-master, na heplx70 (Seagate ST32000645NS (7200RPM 2TB), AMD Opteron(TM) Processor 6234 * X CPU = 48 wątków)
rozmiar bazy, zwracany przez pg_database_size() 79 GB

time pg_dump -U postgres -f zabbix-master-2017-02-11.sql zabbix-master 2>pg_dump.zabbix-master-2017-02-11.err
real 39m49.981s
user 2m41.541s
sys 2m18.051s
rozmiar na dysku: 21GB
WNIOSEK: Można podejżewać że głównym ograniczeniem jest wydajność dysku.

time pg_dump -U postgres zabbix-master 2>pg_dump.zabbix-master-2017-02-11-test2.err | gzip > zabbix-master-YYYY-MM-DD.sql.gz 2>gzip.zabbix-master-2017-02-11.err
real 54m22.792s
user 52m11.319s
sys 1m19.421s
rozmiar na dysku: 6,8GB
WNIOSEK: Tu wygląda na to wąskim gardłem stała się kompresja w jednym wątku.

time pg_dump -U postgres zabbix-master 2>pg_dump.zabbix-master-2017-02-11-test3-pigz.err | pigz > zabbix-master-2017-02-11-pigz.sql.gz 2>pigz.zabbix-master-2017-02-11.err

real 22m41.702s
user 57m19.174s
sys 3m22.478s
rozmiar pliku ?
WNIOSEK: Dzięki kompresji w wielu wątkach, kompresja nie była już wąskim gardłem, a zmniejszenie rozmiaru zapisywanego pliku pozwoliło zmniejszyć wykorzystanie dysku, i uzyskać mniejszy czas całkowity

z ograniczeniem do 6 wątków
time pg_dump -U postgres zabbix-master 2>pg_dump.zabbix-master-2017-02-11-test4-pigz-p6.err | pigz -p6 > zabbix-master-2017-02-11-pigz.sql.gz 2>pigz-p6.zabbix-master-2017-02-11.err

real 13m8.767s
user 52m57.479s
sys 2m23.179s
rozmiar na dysku: 6,9GB
WNIOSEK: Optymalnym rozwiązaniem, jak można się było spodziewać jest kompresja z wykorzystaniem jak najmniejszej liczby wątków, w tym wypadku, prawdopodbnie można ją było jeszcze nieznacznie zmniejszyć, a optymalna ilość wątków jest oczywiście zależna od wydajności dysku i CPU

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *