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
- 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 & - 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