pg_dump lielai datubāzei: ātra izmete un atjaunošana, nekaitējot produkcijai

Palaist pg_dump lielai datubāzei ir viegli, bet labi to pabeigt ir grūti. Noklusējuma komanda strādā, taču izmanto vienu CPU kodolu, stundām ilgi tur momentuzņēmumu un tabulu slēdzenes un rada failu, kura atjaunošana notiek vēl lēnāk nekā izmete. Ar pareizu formātu, paralēliem procesiem un dažiem servera iestatījumiem to pašu izmeti un atjaunošanu var iekļaut plānotā logā. Un dažreiz pareizā atbilde ir pg_dump nemaz neizmantot.

Vispirms: vai pg_dump ir pareizais rīks?

pg_dump veido loģisku kopiju: SQL definīcijas un tabulu datus, kas nolasīti caur parastu savienojumu. Tās atjaunošana nozīmē atkal ielādēt katru rindu, pēc tam izveidot katru indeksu un pārbaudīt katru ārējo atslēgu. Vairāku terabaitu datubāzei tas var ilgt daudzas stundas, un laiks pieaug līdz ar datu apjomu un indeksu skaitu.

Tāpēc pg_dump nav laba galvenā rezerves kopija lielai produkcijas datubāzei. Avārijas atkopšanai vajag fiziskas rezerves kopijas ar WAL arhivēšanu, kas atjaunojas diska ātrumā un ļauj atkopties līdz konkrētam brīdim. Par to rakstījām rakstā PostgreSQL atkopšana līdz konkrētam brīdim. pg_dump joprojām ir pareizais rīks, lai:

• pārvietotu datubāzi uz jaunu galveno versiju, jaunu platformu vai pārvaldītu pakalpojumu, kur fiziska kopija nav iespējama;
• kopētu vienu datubāzi, shēmu vai tabulu kopu, nevis visu klasteri;
• veidotu ilgtermiņa arhīvus, kas jāspēj nolasīt nākamajām PostgreSQL versijām;
• izveidotu papildu, neatkarīgu drošības kopiju pirms riskantām izmaiņām.

Izmantojiet direktorijas formātu un paralēlus procesus

Svarīgākais slēdzis ir formāts. Vienkāršu SQL un custom formātu raksta viens process. Direktorijas formāts (-Fd) ir vienīgais, kurā pg_dump var rakstīt ar vairākiem procesiem vienlaikus, tāpēc tikai tas atbalsta -j:

# direktorijas formāts, 8 paralēli procesi (atver 9 savienojumus), zstd (PostgreSQL 16+)
pg_dump -h db1 -d shop -Fd -j 8 -Z zstd:3 \
        --lock-wait-timeout=30s \
        -f /backup/shop.dump

# lomas un tabulu telpas pg_dump izvadē neietilpst
pg_dumpall -h db1 --globals-only -f /backup/globals.sql

Dažas lietas, kas jāzina par paralēlu izmeti:

• pg_dump atver njobs + 1 savienojumus. Pirms lielas vērtības izmantošanas pārbaudiet max_connections un savienojumu pūla ierobežojumus.
• Paralēlisms ir pa tabulām: katrs process izmet veselas tabulas. Ja vienā tabulā ir lielākā daļa datu, viens process pie tās pavada stundas, kamēr pārējie dīkstāvē. Šeit palīdz particionētas tabulas, jo katra particija ir atsevišķa tabula.
• Visi procesi redz vienus un tos pašus datus caur sinhronizētu momentuzņēmumu, tāpēc izmete ir konsekventa, lai gan to veic vairāki savienojumi.
• Direktorijas un custom formāts pēc noklusējuma tiek saspiesti ar gzip. Kopš PostgreSQL 16 ar -Z var izvēlēties lz4 vai zstd, piemēram, -Z zstd:3. Tie parasti saspiež ātrāk nekā gzip, un tas ir svarīgi, ja izmeti ierobežo kompresija, nevis disks.

Rakstiet izmeti uz citu disku vai serveri, nevis tur, kur atrodas datubāzes dati. Lasīšana un rakstīšana uz viena sējuma ir bieži sastopams iemesls lēnai izmetei un produkcijas aizturei tās laikā.

Ko ilga izmete nodara produkcijai

Dokumentācijā teikts, ka pg_dump nebloķē lasītājus un rakstītājus, un tā ir taisnība. Taču izmetei, kas stundām ilgi strādā noslogotā datubāzē, ir blakusefekti, kas jāplāno.

Tabulu slēdzenes. pg_dump uzliek ACCESS SHARE slēdzeni katrai izmetamajai tabulai un tur to līdz beigām. Parastos vaicājumus tas neietekmē, bet viss, kam vajag ekskluzīvu slēdzeni, gaida: ALTER TABLE, DROP, TRUNCATE, daudzas migrācijas. Vēl ļaunāk, gaidošs ALTER TABLE slēdzeņu rindā nostājas pirms parastajiem vaicājumiem uz šo tabulu, tāpēc izvietošana izmetes laikā var apturēt lietotni. Neplānojiet shēmas migrācijas izmetes laikā. Paralēlas izmetes gadījumā notiek arī pretējais: ja kāds pieprasa ekskluzīvu slēdzeni tabulai, kas vēl nav izmesta, procesa NOWAIT slēdzenes pieprasījums neizdodas un pg_dump pārtrauc visu izmeti.

Slēdzeņu tabulas izmērs. Visas šīs slēdzenes tiek ņemtas vienā transakcijā. max_locks_per_transaction noklusējuma vērtība ir 64 uz servera procesu, un no tās tiek aprēķināts koplietojamās slēdzeņu tabulas izmērs. Datubāze ar desmitiem tūkstošu tabulu un particiju var likt pg_dump beigties ar kļūdu "out of shared memory". Iestatījumu var mainīt tikai ar restartu, tāpēc pārbaudiet to pirms migrācijas nakts.

Vacuum. Izmete notiek vienā ilgā transakcijā ar vienu momentuzņēmumu. Kamēr tā ir atvērta, vacuum nevar izņemt rindas, kas dzēstas vai atjauninātas pēc momentuzņēmuma izveides. Tabulām ar intensīviem atjauninājumiem astoņu stundu izmete nozīmē astoņas stundas uzpūšanās, kas tiek sakopta tikai pēc izmetes beigām.

Izmantojiet --lock-wait-timeout, lai pg_dump ātri beigtos ar kļūdu, nevis gaidītu bezgalīgi, ja nevar iegūt sākotnējās slēdzenes, piemēram, aiz ilgas migrācijas.

Izmete no rezerves servera

Veicot izmeti no straumēšanas replikas, lasīšanas slodze tiek noņemta no primārā servera. Paralēla izmete rezerves serveros tiek atbalstīta kopš PostgreSQL 10. Āķis ir atskaņošanas konflikti: rezerves serverim jāpiemēro izmaiņas no primārā, un, ja tās konfliktē ar izmetes momentuzņēmumu, PostgreSQL atceļ izmetes vaicājumus pēc max_standby_streaming_delay. Mehānismu skaidrojām rakstā canceling statement due to conflict with recovery.

Ir divi ierasti risinājumi. Var uz izmetes laiku palielināt max_standby_streaming_delay speciālai replikai, pieņemot, ka tā izmetes laikā atpaliek. Vai var ieslēgt hot_standby_feedback, kas novērš konfliktus, bet atgriež vacuum problēmu uz primāro serveri. Speciālai izmetes replikai parasti labāka ir pirmā izvēle.

Ātra atjaunošana ar pg_restore

Lielākā daļa laika aiziet atjaunošanai, un tieši pg_restore paralēlisms dod vislielāko ieguvumu. Ar -j tas datu ielādi, indeksu izveidi un ierobežojumu izveidi veic vairākās sesijās vienlaikus:

psql -h db2 -d postgres -f /backup/globals.sql

createdb -h db2 shop
pg_restore -h db2 -d shop -j 8 --exit-on-error /backup/shop.dump

# pg_dump pēc noklusējuma neiekļauj plānotāja statistiku
vacuumdb -h db2 -d shop --analyze-in-stages --jobs=8

• -j darbojas ar custom un direktorijas formātu, un ievadei jābūt parastam failam vai direktorijai, nevis konveijeram. Vienkāršas SQL izmetes, kas ielādētas ar psql, nevar atjaunot paralēli.
• -j nevar apvienot ar --single-transaction. Kopš PostgreSQL 17 --transaction-size ļauj apstiprināt transakciju ik pēc dažiem tūkstošiem objektu, un dokumentācija to iesaka lielām datubāzēm, jo viena transakcija var izsmelt slēdzeņu tabulu.
• Pēc noklusējuma pg_restore turpina darbu pēc kļūdām un beigās izdrukā kļūdu skaitu. Lielai atjaunošanai --exit-on-error parasti ir labāks nekā pēc sešām stundām uzzināt, ka pirmā tabula neizdevās.

Dokumentācija par lielapjoma ielādi uzskaita servera iestatījumus, kurus vērts mainīt atjaunošanai:

• Palieliniet maintenance_work_mem, kas paātrina CREATE INDEX un ārējo atslēgu pievienošanu.
• Palieliniet max_wal_size, lai ielādes laikā būtu mazāk kontrolpunktu.
• Ja mērķis ir jauns serveris, apsveriet wal_level = minimal, archive_mode = off un max_wal_senders = 0 atjaunošanas laikam. Tam vajag restartu, un iepriekšējās bāzes kopijas kļūst neizmantojamas arhīva atkopšanai, tāpēc pēc tam atgrieziet vērtības, izveidojiet jaunu bāzes kopiju un tikai tad veidojiet rezerves serverus.

Statistika: solis, ko aizmirst

Tikko atjaunotai datubāzei nav plānotāja statistikas, kamēr tā nav analizēta. PostgreSQL 18 pg_dump var iekļaut statistiku ar --statistics, bet noklusējums ir --no-statistics, un vecākās versijas to nekad neiekļauj. Kamēr nav izpildīts ANALYZE, plānotājs strādā ar minējumiem, un lieliem savienojumiem var būt ļoti slikti plāni. Autovacuum tabulas galu galā analizēs, bet lielā datubāzē tas var aizņemt ilgu laiku pēc tam, kad trafiks jau pārslēgts. Palaidiet vacuumdb --analyze-in-stages uzreiz pēc atjaunošanas: tas ātri izveido aptuvenu statistiku visām tabulām un pēc tam to precizē.

Atjaunojiet tikai to, kas vajadzīgs

Ar custom un direktorijas formātu var izvadīt arhīva saturu, rediģēt sarakstu un atjaunot daļu jūsu izvēlētā secībā:

pg_restore -l /backup/shop.dump > shop.list
# rediģējiet shop.list: dzēsiet rindas vai aizkomentējiet tās ar ';'
pg_restore -h db2 -d shop -j 8 -L shop.list /backup/shop.dump

Opcija --section sadala atjaunošanu daļās pre-data (tabulu definīcijas), data un post-data (indeksi, ierobežojumi, trigeri). Lielās migrācijās tas ļauj vispirms ielādēt datus un indeksus veidot kā atsevišķu, uzraudzītu soli. Ja vajag tikai tabulas struktūru, nevis saturu, --exclude-table-data izmetes laikā izlaiž žurnālu, rindu un audita tabulu datus, kas var veidot lielu daļu no izmēra.

Lielas datubāzes: loga plānošana

• Mēriet, nevis minējiet. Vienreiz izmetiet un atjaunojiet pilna izmēra kopiju un nomēriet katru fāzi: datu izmeti, datu ielādi, indeksu izveidi. Indeksu un ierobežojumu izveide var ilgt tikpat ilgi kā datu ielāde vai ilgāk.
• Disks. Vajag vietu pašai izmetei, un mērķa serverī datiem, katram indeksam un ielādes laikā radītajam WAL.
• Tīkls. Izmete pa tīklu no attāla klienta pievieno pārsūtīšanas laiku. Palaidiet pg_dump tuvu avotam un pg_restore tuvu mērķim.
• Dīkstāve. pg_dump fiksē vienu brīdi. Ieraksti pēc momentuzņēmuma izmetē nenonāk, tāpēc migrācijai vai nu jāaptur ieraksti visas izmetes un atjaunošanas laikā, vai jāizmanto loģiskā replikācija, lai jaunā datubāze būtu sinhronizēta, un jāpārslēdzas dažu minūšu laikā.
• Viena milzīga tabula. Ja viena tabula dominē, tā nosaka minimālo laiku neatkarīgi no procesu skaita. Apsveriet tās particionēšanu iepriekš vai pārvietošanu atsevišķi.

Kontrolsaraksts pg_dump lielai datubāzei

1. Izlemiet, vai pg_dump ir pareizais rīks, vai labāk der fiziska rezerves kopija vai loģiskā replikācija.
2. Izmantojiet -Fd -j N, pārbaudiet max_connections un PostgreSQL 16+ izvēlieties lz4 vai zstd.
3. Pārbaudiet max_locks_per_transaction attiecībā pret tabulu un particiju skaitu.
4. Nepalaidiet shēmas migrācijas izmetes laikā; iestatiet --lock-wait-timeout.
5. Ja izmetat no rezerves servera, vispirms atrisiniet atskaņošanas konfliktus.
6. Izmetiet globālos objektus ar pg_dumpall --globals-only.
7. Atjaunojiet ar pg_restore -j, lielāku maintenance_work_mem un max_wal_size un --exit-on-error.
8. Palaidiet vacuumdb --analyze-in-stages, pirms sūtāt trafiku uz atjaunoto datubāzi.
9. Nomēriet visu procesu uz pilna izmēra kopijas pirms īstā loga.