Zálohování a obnova PostgreSQL metodou PITR (point in time recovery)

24. 10. 2012 | Tomáš Crhonek | Návody | 4829×

Pro zálohování databázového serveru se obvykle používají nástroje pg_dump v kombinaci s jeho obnovením pomocí standardního klienta psql (to v případě logické zálohy, která probíhá vytvořením sekvence SQL příkazů) nebo nástroje pg_restore. Tato klasická metoda zálohování pomocí kompletních dumpů databáze má své přednosti ale i omezení. Mezi přednosti patří jistě to, že je možné zálohovat kteroukoliv databázi, případně jen několik tabulek.

V případě logických dumpů (sekvence SQL příkazů) dokonce i množnost zasahovat do zálohovaných dat (a efektivně tak použít tuto metodu i na hromadnou změnu dat). Hlavní a podstatná nevýhoda zálohování pomocí dumpů je omezení velikosti. Pomocí dumpů je vhodné zálohovat pouze malé databáze o velikosti několika desítek gigabajtů. Pro větší databáze bude čas zálohy a zejména obnovy velmi dlouhý a databázový server bude na dlouhou dobu velmi zatížen.

Zálohování metodou pitr je naproti tomu velice rychlé a kontinuální (využívá stávajícího mechanismu zápisu změn v databázi nejprve do WAL logů a až poté do datových souborů). Obnova je v porovnáním s obnovou logických dumpů velmi rychlá. Lze tak velice rychle obnovit kompletní databázový server bez ohledu na jeho velikost. Tímto způsobem ovšem není možné zálohovat pouze vybrané databáze, ani zasahovat do obnovovaných dat, zálohuje se vždy celý databázový server.

Obsah

• Write Ahead Log (WAL)
• Nastavení migrace WAL logů na vzdálený adresář
• Zahájení plné (base) zálohy
• Plná base záloha
• Ukončení base zálohy
• Obnova
• Vytvoření recovery.conf
• Spustit službu PostgreSQL
• Závěr

Write Ahead Log (WAL)

Transakční databáze zapisují všechny změny dat nejprve do transakčního logu a až poté do datových souborů. S WAL logy se pracuje výhradně sekvenčně a jejich ukládání a následné čtení je velmi rychlé. Po nějakém čase, až se transakční logy (u Postgresu je to série souborů po 16 MiB, jejiž počet je nastaven v konfiguraci) naplní, dojde k události checkpoint (kterou lze vyvolat i příkazem CHECKPOINT;) a data se zapracují do trvalých datových souborů. Na tuto nákladnou operaci však klient nemusí čekat (data z jeho potvrzené transakce jsou již bezpečně na disku právě ve WAL). Při pádu databázového serveru (například výpadek napájení) se po jeho opětovném spuštění sekvenčně projde celý WAL a nezapsané změny se zapíší do datových souborů. Tím je zajištěna rychlost a také bezpečí dat, klient čeká pouze na rychlý sekvenční zápis a fsync WAL logu, který je později zapracován do datových souborů.

Vzhledem k tomu, že v těchto souborech jsou všechny změny a že se s nimi pracuje výhradně sekvenčně (jednou naplněný soubor logu se už nemění), je možné tento soubor odkopírovat na jiné úložiště a později jej využít při obnovení serveru po jeho totálním pádu.

A na tom je právě založena metoda zálohy WAL logů a obnovení metodou PIRT. WAL logy se za běhu databáze kopírují na jiné úložiště a jednou za čas se provede plná záloha prostým odkopírováním adresáře s databází. Obnova potom spočívá v obnovení plné zálohy a v přehrání všech uložených WAL logů.

Nastavení migrace WAL logů na vzdálený adresář

Nejprve je třeba nastavit migraci WAL logů na externí úložiště. Na tomto místě bychom čekali nastavení adresáře, kam se mají logy migrovat, ovšem PostgreSQL dává administrátorovi do ruky daleko mocnější nástroj. Nastavuje se příkaz (skript, chcete-li), který požadovanou akci udělá. Je pouze na administrátorovi jakou metodu si zvolí (od jednoduchého cp, až třeba po speciální zálohovací nástroj).

K tomu slouží parametr archive_command a v nejjednodušší funkční podobě může vypadat takto:

archive_command = 'test ! -f /storage/WAL/%f && cp %p /storage/WAL/%f'

Kde %f je zástupný znak pro jméno souboru logu a %p je celá cesta k souboru (nejčastěji /var/lib/pgsql/data/pg_xlog/jmeno_souboru), odkud se má soubor zkopírovat. Soubory se budou kopírovat do vzdáleného NFS adresáře připojeného do /storage/WAL. Na skript jsou kladeny požadavky, nesmí se přepisovat soubor stejného jména na vzdáleném úložišti a při jakékoliv chybě musí vrátit nenulový návratový kód. Naopak nula se musí vrátit pouze v případě úspěchu a bezpečného uložení souboru na externí úložiště. Výše zmíněný skript z dokumentace tyto požadavky splňuje.

Dále je v postgresql.conf potřeba nastavit úroveň WAL logů (v základním nastavení je to minimal, která je vhodná pro bezpečné obnovení databáze po pádu, ale nelze ji používat pro tuto metodu zálohování) a také aktivovat archivaci:

wal_level = archive
archive_mode = on

To je z nastavení migrace wal logu vše a po restartu služby PostgreSQL by se měly na vzdáleném úložišti ukládat 16 MiB soubory logu. Toto je třeba bezpečně ověřit ještě před zahájením samotné zálohy a není od věci si také vyzkoušet rychlost přenosu logů při zatížení db například vytvořením obrovské tabulky (třeba pomocí pgbench -i -s 1000).

Zahájení plné (base) zálohy

Když už se WAL logy spolehlivě migrují na externí úložiště, je možné provést samotnou plnou zálohu. Ta spočívá v prostém odkopírování adresáře (nejčastěji /var/lib/pgsql/, jiné distribuce to mohou mít na jiných místech) na externí úložiště (ideálně někam k WAL logům). Před kopírováním je třeba uvést PostgreSQL do stavu vhodném pro kopírování datových souborů.

K tomu slouží SQL příkaz SELECT pg_start_backup('label');, který je třeba spustit jako superuživatel postgres, nebo jiný vyhrazený uživatel s právy superuser.

Parametr label je pro označení zálohy a slouží jen jako poznámka bez technického významu. Před kopírováním adresáře s databází je nutno počkat až tento příkaz doběhne, což může trvat delší čas. Doba plné base zálohy, tedy doba mezi příkazy pg_start_backup() a pg_stop_backup(), není kritická a databáze funguje pro klienty zcela bez komplikací.

Plná base záloha

Plná záloha adresáře s databází (/var/lib/pgsql/). Opět je možné použít jakýkoliv nástroj pro spolehlivé zkopírování celého adresáře (cp, tar, rsync apod.).

cp -a /var/lib/pgsql/ /storage/WAL/base_backup

Není třeba zálohovat podadresář pg_xlog (což je adresář, ve kterém má PostgreSQL WAL logy, které jsou ale už v tuto chvíli odkopírované i na externí úložiště) případně je nutno před obnovením zálohy jeho obsah vymazat (viz obnovení zálohy).

Ukončení base zálohy

Po odkopírování adresáře s databází je nutné base zálohu ukončit, opět jako superuživatel:

SELECT pg_stop_backup();

Tímto postupem je plná záloha hotová a WAL logy se migrují pryč na externí úložiště. Zálohování tedy probíhá systémem plná záloha (base backup) a potom se migrují jen změny pomocí ukládání wal logů na externí úložiště. Nemá smysl ukládat WAL logy starší, než je base záloha (wal logy vytvořené před zahájením plné zálohy je tak možno smazat).

Obnova

Nejprve je nutno obnovit adresář (/var/lib/pgsql/) z poslední plné zálohy:

cp -a /storage/WAL/base_backup /var/lib/pgsql/

Pokud se zálohoval i adresář pg_xlog, je třeba vymazat jeho obsah (rm pg_xlog/* v příslušném adresáři databáze). Pokud se nezálohoval, je třeba jej před spuštěním serveru vytvořit s patřičnými právy a vlastníkem (mkdir pg_xlog && chown postgres.postgres pg_xlog). Osobně doporučuji dělat věci co možná nejjednodušeji a zálohu dělat jako kopírování celého adresáře psql včetně pg_xlog (jehož velikost je i pro doporučených 30 segmentů malá, asi 1,5 GiB oproti celé databázi, která může mít stovky GiB) a potom jej před obnovou jednoduše promazat, než si komplikovat zálohu exclude pravidly a při obnově vytvářením adresáře s právy. Při obnově je člověk většinou ve stresu (obnovuje se po havárii) a čím méně kroků obnovy, tím lépe.

Vytvoření recovery.conf

Soubor recovery.conf (pozor, tento soubor musí být v adresáři databáze, tedy v adresáři, kde jsou i podadresáře base, pg_xlog apod.; některé distribuce, třeba Debian, mají ostatní konfigurační soubory v /etc/postgresql/verze/main/, ale soubor recovery.conf databáze očekává právě ve svém základním adresáři) slouží pro určení cesty (resp opět se jedná o skript) k zálohovaným WAL logům. V nejjednodušší variantě:

restore_command = 'cp /storage/WAL/%f %p'

Spustit službu PostgreSQL

V jeho logu lze sledovat průběh obnovy WAL logů, například:

2012-09-11 12:46:22 CEST LOG:  restored log file „00000001000000000000003D“ from archive

Po dokončení obnovy je v logu hláška:

2012-09-11 12:46:46 CEST LOG:  database system is ready to accept connections

A soubor recovery.conf je automaticky přejmenován na recovery.done. Tento soubor lze v adresáři nechat, bude součástí další base zálohy. V případě další obnovy jej stačí přejmenovat zpět na recovery.conf. Opět se jedná o ulehčení kroků obnovy.

Po dokončení obnovy se lze připojit k db, která je ve stavu posledního WAL logu. Databázový server lze obnovit i k libovolnému bodu v čase, případně k přesnému číslu transakce (pokud například víme, že v transakci x došlo k zavolání příkazu DROP, je možné server obnovit ke stavu transkakce x-1). Více si lze přečíst k dokumentaci k souboru recovery.conf.

Závěr

K tomuto způsobu zálohování a obnovy jsme přikročili u jednoho významného projektu a to ze dvou hlavních důvodů. Zákazník z technických důvodů nechce a nemůže zálohovat celé virtuální servery pomocí snapshotů (což je jinak můj preferovaný způsob zálohování, kdy v případě obnovy stačí obnovit celý virtuální server ze zálohy a jednoduše jej spustit ve stavu poslední zálohy). Druhým důvodem je očekávaná velikost databáze ve stovkách gigabajtů dat a je tedy nemožné zálohovat a především obnovit tyto servery se stanoveném čase pomocí klasických dumpů.

Zálohování a obnova pomocí metody pitr je naproti tomu velmi rychlá (kromě base zálohy databázový server nedělá nic navíc, kromě občasného odkopírování obyčejného souboru na NFS) a v případě obnovy se nakopíruje base zpět a přejmenuje se soubor recovery.conf. Rychlost obnovy potom probíhá rychlostí sítě, typicky 100 MiB/s. Obnova wal je z principu práce databáze velmi rychlá a spolehlivá.

V praxi je třeba rozhodnout, jak zacházet se staršími wal logy a base zálohami, stejně jako je tomu u každé zálohy. Je třeba si uvědomit, že ukládané wal logy mohou být snadno větší, než je velikost databáze na disku. Na disku je aktuální stav, zatímco wal logy obsahují úplně všechny změny a vytvoření například 32 GiB databáze zabere postupně o něco více než 32 GiB wal logů a také odpovídající místo na disku a tedy i v následující base záloze.

       

Komentáře

[Käyttäjä 11133]

Rychlost obnovy z recovery logů je zhruba rychlost LTO4 páskové mechaniky - 120MB/s, ale u velkých databází je to prostě stále moc pomalé, pokud se dělá full backup jednou týdně (říkáš jí base záloha), tak u rozumné databáze může jít velikost logů až do velikosti terabytů.

A zálohování databázových serverů přes VMware (nebo jinými VM-based) snapshoty je prostě zlo... Od kdy to výrobce databáze podporuje? Leda že byste nastartovali base zálohu, udělali VM-based snapshot a pak ukončili base zálohu, pak se jedná o stejnou formu konzistence, jako u uvedeného archivního režimu.

[Heron]

Pro vetsi velikosti DB existuji zase jine techniky, treba replikace. Jak pise nekdo dole, z tohoto zpusobu zalohovani se snadno udela i hot_standby. Dneska uz je k disposizi i streaming replication.

Zalohovani pomoci snapshotu "podporuje" kazda db, ktera splnuje ACID. Snapshot se z principu dela mezi dvema fsync (a flush) souboroveho systemu a pro db to nepredstavuje problem. Je to totez, jako recovery po vypadku napajeni. Postgres si precte cely wal a jede se dal.

[Lampa]

No defacto hotstanby je asynchronni streaming replication, da se samozrejme nastavit i synchronni, ale na pomale lince je to trosku problem pri velkem poctu aktualizaci

[Pavel Francírek]

Plét si zálohování s replikací se vám může pěkně vymstít. Záloha je to, co vás ochrání i v případě DROP DATABASE CASCADE. Replikace to není.

[Heron]

To ale nikdo netvrdí . Replikace je další místo, kde lze s DB něco dělat, relativně nezávisle na hlavním serveru. Od velkého logického dumpu počínaje (takhle se to používá, bohužel s velkým rizikem poškození dat, na Mysql), po kompletní konzistentní zálohu serveru na pásku konče. Tedy ano, replikace sama o sobě není záloha, ale může ji usnadnit.

[Käyttäjä 11133]

Zalohovani pomoci snapshotu "podporuje" kazda db, ktera splnuje ACID.

To že má něco fungovat, automaticky neznamená, že až nastane problém, že ti vendor databáze neřekne, že si idiot, a že pokud chceš jeho pomoc s problémem, tak si musíš pořídit professional services s cenou 2500EUR/den.

To máš jako s provozem Oracle DB pod VMware, spoustě lidem to funguje, spousta firem to provozuje, ale jakmile nastane problém, můžou si stěžovat leda na lampárně a ne u Oracle.

[Heron]

To že má něco fungovat, automaticky neznamená, že až nastane problém, že ti vendor databáze neřekne, že si idiot, a že pokud chceš jeho pomoc s problémem, tak si musíš pořídit professional services s cenou 2500EUR/den.

Jinými slovy si připlatit za funkci, která má fungovat by default a kterou ten produkt deklaruje.

To máš jako s provozem Oracle DB pod VMware, spoustě lidem to funguje, spousta firem to provozuje, ale jakmile nastane problém, můžou si stěžovat leda na lampárně a ne u Oracle.

Což je další bod pro OpenSource. Většinou to funguje, když ne, tak se to považuje za bug, který se opraví. A když ani to ne, tak je to alespoň zadarmo. S drahým nefunkčním komerčním software jsem si za poslední rok užil své, ale on tom třeba někdy do blogu.

[TomasVondra]

Ne zcela rozumím v čem vidíte problém.

To že vám vendor hodí na hlavu zbastlené řešení které se chová nestandardně není nijak divné. Bohužel s proprietárními řešeními máme dost malou možnost odhalit kde ta chyba vlastně je a hodit to na hlavu správnému vendorovi, a oni se do investigace rozhodně hrnout nebudou. Alespoň pokud jim zaplatíte.

Jinak snapshotting na úrovni filesystému se pro zálohování DB běžně používá - například na AWS jsou snapshoty EBS celkem oblíbené (a spolehlivé), to samé se dá říct o LVM snapshotech (i když tam jsou často pozorované dopady na výkon).

[Käyttäjä 11133]

Ale jinak pěkný článek, brzo se mi bude hodit, když ho trošku upravím, alespoň to nemusím studovat (znovu, jednou už jsem backup scripty pro PostgreSQL psal, ale dávno jsem to zapoměl :) ).

[Lampa]

Nedavno jsem resil neco podobneho, ale je na to vyhrazeny server, takze jsem pouzil hot_standby rezim.

Kopiruji se logy na cilovy server, tam se automaticky zpracuji (postgresql si to hlida). Dalsi vyhodou je ze standby db je pristupna pro read (tj daji se provadet selecty) - data jsou tam podle nastaveni archive_timeout.

[Franta]

Výborný článek, díky!

zálohovat celé virtuální servery pomocí snapshotů (což je jinak můj preferovaný způsob zálohování, kdy v případě obnovy stačí obnovit celý virtuální server ze zálohy a jednoduše jej spustit ve stavu poslední zálohy)

Ale stejně je dobré PostgreSQL říct, že se bude dělat snapshot, ne? Stejně tak by o tom měl vědět souborový systém, LVM...

[Heron]

Výborný článek, díky!

Díky.

Ale stejně je dobré PostgreSQL říct, že se bude dělat snapshot, ne? Stejně tak by o tom měl vědět souborový systém, LVM...

To už je mimo nad rámec článku. Jasně, když to jde, tak je to lepší. VMWare (který používáme) umí pomocí toolsů kontaktovat OS a připravit na to systém souborů (minimálně tedy udělat flush a sync FS). V případě snapshotů BTRFS (stejně asi i u ZFS) si to ten FS pořeší rovnou sám a snapshot bude udělán mezi dvěma fdatasync walu. (I když PostgreSQL na BRTFS je dost pomalý, tam to asi nikdo vážně provozovat nebude.) Druhá část odpovědi je, že ta DB musí tak jako tak umět přežít i nekorektní ukončení OS a také přežije. Je to jeden z požadavků ACID a co jsem viděl testy, tak žádná ACID DB s tím neměla problém (testovalo se vypnutí celého serveru). Potom byla data v pořádku ve stavu posledního úspěšného commitu.

[Program]

Víte, k čemu slouží ono pg_stop_backup()? Poradím, minimálně udělá checkpoint. Další nekonzistence se přepíše přehráváním wal logů, protože ty obsahují data celé stránky (min. při provádění zálohy, pokud je to v configu vyplé) a všechny stránky na kterých se co hnulo.

[Program]

Jo, i když jsem úplně nepochopil příspěvek, sorry.

[TomasVondra]

No, to pg_stop_backup zcela určitě nedělá. Předpokládám že si to pletete s pg_start_backup, který právě udělá checkpoint a zamezí změnám které by znemožnily vytvoření konzistentní kopie na úrovni filesystému.

[BrainLess]

U opravdu bussines reseni se pouzivaji draha diskova pole (EMC atd.) + drahy software ktery umi za pomoci features diskovych poli udelat konzistentni zalohu. PGSQL jsem nikdy nepouzil ale jsem rad ze se priblizuje komercnim resenim ( Oracle ). K tem zaloham to v praxi funguje tak ze se DB uvede do stavu zalohy (analogie k tomu pg_start_backup). V principu to funguje stejne zapisuje se do archive logu ( obdoba WAL logu u Oracle ) a zmeny si sice propisuji i do DB files ale nemeni se SCN v hlavicce DB souboru. Pak se pomoci HW diskoveho pole udela "snapshot" v radu jednotek sekund a DB se znovu uvede do end backup state. Takze vlastni zaloha probehne u nekolika TB databaze v radu desitek sekund, to ze to potom na pozadi diskove pole resi jeste nekolik hodin nikoho netrapi od toho tam je a je to tak spravne. Nasledne kdyz je snapshot ok, se provede odzalohovani na pasky ( nejsou pasky jako pasky viz StorageTek PowderHorn, pozdeji Sun PowderHorn a nyni Oracle PowderHorn nebo jak se to dnes jmenuje ) ... Pro zajimavost si najdete jak ten powderhorn vypada, co si tak matne pamatuji tak v max konfiguraci to bylo schopno zazalohova tusim 1200TB/hodina + celkova kapacita zalohy 28000PB. Ahoj. Linuxu zdar.

[Käyttäjä 11133]

PowerHorn je obsolete, byl nahrazený modulární knihovnou SL8500. Maximální rychlost 552.9TB/hr (nekomprimovaných) s T10000C mechanikama. V letech 2010/2011 jsme právě odstraňovali PowerHorny a nahrazovali je SL8500. SL8500 je mi mnohem sympatičtější :)

[BrainLess]

Jasne ze to tak bude, jelikoz se sotrage systemama nezivim tak to moc nesleduju. Jenom si udrzuji vseobecny prehled jak se co dneska dela ..