PostgreSQL: poprawa wydajności PG dump, PG restore

Kiedy zaczynałem, używałem pg_dump z domyślnym zwykłym formatem. Byłem nieoświecony.

Badania ujawniły mi poprawę czasu i rozmiaru pliku za pomocą pg_dump -Fc | gzip -9 -c > dumpfile.gz. Byłem oświecony.

Kiedy nadszedł czas, aby utworzyć bazę danych na nowo, 

# create tablespace dbname location '/SAN/dbname';
# create database dbname tablespace dbname;
# alter database dbname set temp_tablespaces = dbname;

% gunzip dumpfile.gz              # to evaluate restore time without a piped uncompression
% pg_restore -d dbname dumpfile   # into a new, empty database defined above

Czułem się nieoświecony: przywracanie zajęło 12 godzin, aby utworzyć bazę danych, która jest tylko ułamkiem tego, co się stanie: 

# select pg_size_pretty(pg_database_size('dbname'));
47 GB

Ponieważ są przewidywania, że ta baza danych będzie kilka terabajtów, muszę spójrz na poprawę wydajności teraz.

Proszę, oświeć mnie.
Author: Joe Creighton, 2010-01-19
Source

6 answers

Najpierw sprawdź, czy uzyskujesz rozsądną wydajność IO z konfiguracji dysku. Następnie sprawdź, czy instalacja PostgreSQL jest odpowiednio dostrojona. W szczególności shared_buffers powinno być ustawione poprawnie, {[1] } powinno być zwiększone podczas przywracania, {[2] } powinno być wyłączone podczas przywracania, wal_buffers powinno być zwiększone do 16MB podczas przywracania, checkpoint_segments powinno być zwiększone do czegoś takiego jak 16 podczas przywracania ,nie powinno mieć żadnego nieuzasadnionego logowania (jak logowanie każdej wykonanej instrukcji), auto_vacuum powinny być wyłączone podczas przywracania.

Jeśli używasz wersji 8.4 również eksperymentuj z przywracaniem równoległym, opcja --jobs dla pg_restore.

 60
Author: Ants Aasma,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-01-19 17:01:32

Improve PG dump & restore

PG_DUMP / Zawsze używaj opcji format-directory i -j 

time pg_dump -j 8 -Fd -f /tmp/newout.dir fsdcm_external

PG_RESTORE / Zawsze używaj tuningu dla postgres.opcje conf i format-directory oraz -j 

work_mem = 32MB
shared_buffers = 4GB
maintenance_work_mem = 2GB
full_page_writes = off
autovacuum = off
wal_buffers = -1

time pg_restore -j 8 --format=d -C -d postgres /tmp/newout.dir/
 26
Author: Yanar Assaf,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-11-11 21:17:02

Dwa zagadnienia/pomysły:

  1. Podając -Fc, wyjście pg_dump jest już skompresowane. Kompresja nie jest maksymalna, więc możesz zaoszczędzić trochę miejsca używając "gzip -9" , ale założę się, że to nie wystarczy, aby zagwarantować dodatkowy czas (i I/O) używany do kompresji i dekompresji wersji-Fc kopii zapasowej.

  2. Jeśli używasz PostgreSQL 8.4.x możesz potencjalnie przyspieszyć przywracanie z kopii zapasowej a-Fc za pomocą nowej opcji linii poleceń pg_restore " - j n" gdzie n = liczba połączeń równoległych, które mają zostać użyte do przywrócenia. Pozwoli to pg_restore na załadowanie więcej niż jednej tabeli danych lub wygenerowanie więcej niż jednego indeksu w tym samym czasie.

 14
Author: Matthew Wood,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-01-19 16:47:18

Zakładam, że potrzebujesz kopii zapasowej, a nie dużej aktualizacji bazy danych.

Do tworzenia kopii zapasowych dużych baz danych należy ustawić ciągłą archiwizację zamiast pg_dump.

  1. Skonfiguruj archiwizację WAL .

  2. Twórz kopie zapasowe bazy np. codziennie za pomocą
    psql template1 -c "select pg_start_backup('`date + % F - % T"') " rsync-a --delete /var/lib/pgsql/data/ /var/backups/pgsql/base/ PSQL template1-c "select pg_stop_backup()"`

Przywracanie byłoby jak proste jak przywrócenie bazy danych i dzienników WAL nie starszych niż pg_start_backup Czasu z miejsca kopii zapasowej i uruchomienie Postgres. I będzie znacznie szybciej.

 10
Author: Tometzky,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-01-19 18:20:32 
zcat dumpfile.gz | pg_restore -d db_name

Usuwa pełny zapis nieskompresowanych danych na dysk, który jest obecnie Twoim wąskim gardłem.

 7
Author: richo,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-01-22 02:34:03

Jak można się domyślić po prostu przez fakt, że kompresja kopii zapasowej powoduje szybszą wydajność, kopia zapasowa jest związana We / Wy. Nie powinno to dziwić, ponieważ backup jest prawie zawsze związany z I/O. Kompresowanie obciążenia I/o danych dla obciążenia CPU, a ponieważ większość procesorów jest bezczynna podczas przesyłania danych, kompresja wychodzi jako wygrana netto.

Aby przyspieszyć czas tworzenia kopii zapasowych/przywracania, potrzebujesz szybszego wejścia / Wyjścia. przykład, to prawie wszystko, co możesz zrobić.

 3
Author: Will Hartung,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-01-19 16:31:47