I-ProHoster > Π‘Π»ΠΎΠ³ > Ukuphatha > Izipele ezikhulayo ze-postgresql nge-pgbackrest - isifundo se-fighter esencane evela kunjiniyela

Izipele ezikhulayo ze-postgresql nge-pgbackrest - isifundo se-fighter esencane evela kunjiniyela

Umusho wokuzihlangula

Ngingunjiniyela. Ngibhala ikhodi futhi ngisebenzisana nesizindalwazi kuphela njengomsebenzisi. Angizenzi neze njengomphathi wesistimu, ingasaphathwa eye-dba. Kodwa…

Kwenzeka ukuthi ngidinga ukuhlela ikhophi yasenqolobaneni ye-postgresql database. Awekho amafu - vele usebenzise i-SSH futhi uqiniseke ukuthi yonke into iyasebenza ngaphandle kokucela imali. Senzenjani ezimweni ezinjalo? Kulungile, sicindezela i-pgdump ku-cron, yenza isipele yonke into kungobo yomlando nsuku zonke, futhi uma silahleka ngokuphelele, sithumela le ngobo yomlando kwenye indawo ekude.

Ngalesi sikhathi ubunzima bekuwukuthi ngokwezinhlelo, i-database bekufanele ikhule cishe ngo-+- 100 MB ngosuku. Vele, ngemuva kwamasonto ambalwa isifiso sokwenza isipele yonke into nge-pgdump izoshabalala. Yilapho izipele ezikhuphukayo zisiza khona.

Kuyathakazelisa? Siyakwamukela kukati.

Ikhophi yasenqolobaneni ekhulayo iwuhlobo lwesipele lapho kungewona wonke amafayela omthombo akopishiwe, kodwa amasha kuphela nalawo ashintshiwe kusukela ekudalweni kwekhophi yangaphambilini.

Njenganoma yimuphi umthuthukisi owayengazimisele NGOKUPHELELE (ngaleso sikhathi) ukuqonda ubunkimbinkimbi be-postgres, bengifuna ukuthola inkinobho eluhlaza. Uyazi, njengaku-AWS, DigitalOcean: ucindezele inkinobho eyodwa - uthole ukuphindaphinda, wacindezela okwesibili - usetha izipele, okwesithathu - uhlehlise yonke into amahora ambalwa. Angizange ngithole inkinobho noma ithuluzi elihle le-GUI. Uma wazi eyodwa (yamahhala noma eshibhile), bhala ngayo kumazwana.

Ngemva kwe-googling ngathola amathuluzi amabili pgbarman ΠΈ pgbackrest. Angizange ngiphumelele ngeyokuqala (imibhalo emincane kakhulu, ngazama ukuthola yonke into ngokwezincwadi ezindala), kodwa ngeyesibili imibhalo yabonakala isezingeni, kodwa hhayi ngaphandle kwamaphutha. Ukuze kube lula umsebenzi walabo ababhekene nomsebenzi ofanayo, lesi sihloko sabhalwa.

Ngemva kokufunda lesi sihloko, uzofunda ukwenza ama-backups akhuphukayo, uwagcine kuseva ekude (inqolobane enezipele) futhi uwabuyisele uma kwenzeka ukulahleka kwedatha noma ezinye izinkinga kuseva enkulu.

Training

Ukuze ukhiqize kabusha imanuwali uzodinga ama-VPS amabili. Eyokuqala izoba isitoreji (inqolobane lapho izipele zizogcinwa khona), kanti okwesibili, empeleni, iseva ngokwayo nge-postgres (endabeni yami, inguqulo ye-11 ye-postgres).

Kucatshangwa ukuthi kuseva ene-postgres unezimpande, umsebenzisi we-sudo, umsebenzisi we-postgres kanye ne-postgres ngokwayo ifakiwe (umsebenzisi we-postgres udalwa ngokuzenzakalelayo lapho efaka i-postgresql), futhi kuseva yendawo yokugcina kukhona izimpande kanye nomsebenzisi we-sudo (ebhukwana). igama lomsebenzisi elithi pgbackrest lizosetshenziswa) .

Ukuze ube nezinkinga ezimbalwa lapho ukhiqiza imiyalo, ngibhala ngomalukeke kuphi, ngamuphi umsebenzisi futhi ngawaphi amalungelo engenza umyalo ngenkathi sibhala futhi sibheka isihloko.

Ifaka i-pgbackrest

Indawo yokugcina (umsebenzisi pgbackrest):

1. Landa ingobo yomlando kusuka ku-pgbackrest bese udlulisela okuqukethwe kuyo kufolda ethi/build:

sudo mkdir /build
sudo wget -q -O - 
       https://github.com/pgbackrest/pgbackrest/archive/release/2.18.tar.gz | 
       sudo tar zx -C /build

2. Faka okuncikile okudingekayo ekuhlanganiseni:

sudo apt-get update
sudo apt-get install build-essential libssl-dev libxml2-dev libperl-dev zlib1g-dev 
       libpq-dev

3. Ukuhlanganisa i-pgbackrest:

cd /build/pgbackrest-release-2.18/src && sudo ./configure
sudo make -s -C /build/pgbackrest-release-2.18/src

4. Kopisha ifayela elisebenzisekayo kuhla lwemibhalo /usr/bin:

sudo cp /build/pgbackrest-release-2.18/src/pgbackrest /usr/bin
sudo chmod 755 /usr/bin/pgbackrest

5. I-Pgbackrest idinga i-perl. Faka:

sudo apt-get install perl

6. Dala izinkomba zamalogi, uzinikeze amalungelo athile:

sudo mkdir -p -m 770 /var/log/pgbackrest
sudo chown pgbackrest:pgbackrest /var/log/pgbackrest
sudo mkdir -p /etc/pgbackrest
sudo mkdir -p /etc/pgbackrest/conf.d
sudo touch /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown pgbackrest:pgbackrest /etc/pgbackrest/pgbackrest.conf

7. Hlola:

pgbackrest version

Iseva ye-Postgres (umsebenzisi we-sudo noma impande):

Inqubo yokufaka i-pgbackrest kuseva ene-postgres iyafana nenqubo yokufaka endaweni yokugcina (yebo, i-pgbackrest kufanele ifakwe kuzo zombili iziphakeli), kodwa esigabeni sesi-6 umyalo wesibili nowokugcina:

sudo chown pgbackrest:pgbackrest /var/log/pgbackrest
sudo chown pgbackrest:pgbackrest /etc/pgbackrest/pgbackrest.conf

buyisela ngokuthi:

sudo chown postgres:postgres /var/log/pgbackrest
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf

Ukusetha ukuxhumana phakathi kwamaseva nge-SSH engenaphasiwedi

Ukuze i-pgbackrest isebenze kahle, kuyadingeka ukulungisa ukusebenzisana phakathi kweseva ye-postgres nendawo yokugcina usebenzisa ifayela elingukhiye.

Indawo yokugcina (umsebenzisi pgbackrest):

Dala ipheya yokhiye:

mkdir -m 750 /home/pgbackrest/.ssh
ssh-keygen -f /home/pgbackrest/.ssh/id_rsa 
       -t rsa -b 4096 -N ""

Ukuqapha Sisebenzisa imiyalo engenhla ngaphandle kwe-sudo.

Iseva ye-Postgres (umsebenzisi we-sudo noma impande):

Dala ipheya yokhiye:

sudo -u postgres mkdir -m 750 -p /var/lib/postgresql/.ssh
sudo -u postgres ssh-keygen -f /var/lib/postgresql/.ssh/id_rsa 
       -t rsa -b 4096 -N ""

Indawo yokugcina (umsebenzisi we-sudo):

Kopisha ukhiye osesidlangalaleni weseva ye-postgres kuseva yendawo yokugcina:

(echo -n 'no-agent-forwarding,no-X11-forwarding,no-port-forwarding,' && 
       echo -n 'command="/usr/bin/pgbackrest ${SSH_ORIGINAL_COMMAND#* }" ' && 
       sudo ssh root@<postgres_server_ip> cat /var/lib/postgresql/.ssh/id_rsa.pub) | 
       sudo -u pgbackrest tee -a /home/pgbackrest/.ssh/authorized_keys

Kulesi sinyathelo uzocelwa iphasiwedi yomsebenzisi wempande. Udinga ukufaka iphasiwedi yomsebenzisi oyimpande yeseva ye-postgres!

Iseva ye-Postgres (umsebenzisi we-sudo):

Kopisha ukhiye osesidlangalaleni oyinqolobane kuseva ngama-postgres:

(echo -n 'no-agent-forwarding,no-X11-forwarding,no-port-forwarding,' && 
       echo -n 'command="/usr/bin/pgbackrest ${SSH_ORIGINAL_COMMAND#* }" ' && 
       sudo ssh root@<repository_server_ip> cat /home/pgbackrest/.ssh/id_rsa.pub) | 
       sudo -u postgres tee -a /var/lib/postgresql/.ssh/authorized_keys

Kulesi sinyathelo uzocelwa iphasiwedi yomsebenzisi wempande. Udinga ukufaka ngqo iphasiwedi yomsebenzisi oyimpande wendawo yokugcina!

Sihlola:

Inqolobane (umsebenzisi wezimpande, ukuze kutholakale ubumsulwa bokuhlolwa):

sudo -u pgbackrest ssh postgres@<postgres_server_ip>

Iseva ye-Postgres (umsebenzisi wezimpande, ukuze kuhlanzeke isilingo):

sudo -u postgres ssh pgbackrest@<repository_server_ip>

Siyaqinisekisa ukuthi sithola ukufinyelela ngaphandle kwezinkinga.

Isetha iseva ye-postgres

Iseva ye-Postgres (umsebenzisi we-sudo noma impande):

1. Masivumele ukungqongqoza kuseva ye-postgres kuma-IP angaphandle. Ukuze wenze lokhu, hlela ifayela postgresql.conf (etholakala kufolda /etc/postgresql/11/main), ingeza umugqa kuyo:

listen_addresses = '*'

Uma ulayini onjalo usuvele ukhona, ungawukhulula noma usethe inani lepharamitha njenge-'*'.

Kufayela pg_hba.conf (futhi itholakala kufolda /etc/postgresql/11/main) engeza imigqa elandelayo:

hostssl  all  all  0.0.0.0/0  md5
host  all  all  0.0.0.0/0  md5

lapho:

hostssl/host - ΠΏΠΎΠ΄ΠΊΠ»ΡŽΡ‡Π°Π΅ΠΌΡΡ Ρ‡Π΅Ρ€Π΅Π· SSL (ΠΈΠ»ΠΈ Π½Π΅Ρ‚)
all - Ρ€Π°Π·Ρ€Π΅ΡˆΠ°Π΅ΠΌ ΠΏΠΎΠ΄ΠΊΠ»ΡŽΡ‡Π΅Π½ΠΈΠ΅ ΠΊΠΎ всСм Π±Π°Π·Π°ΠΌ
all - имя ΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚Π΅Π»Ρ, ΠΊΠΎΡ‚ΠΎΡ€ΠΎΠΌΡƒ Ρ€Π°Π·Ρ€Π΅ΡˆΠ°Π΅ΠΌ ΠΏΠΎΠ΄ΠΊΠ»ΡŽΡ‡Π΅Π½ΠΈΠ΅ (всСм)
0.0.0.0/0 - маска сСти с ΠΊΠΎΡ‚ΠΎΡ€ΠΎΠΉ ΠΌΠΎΠΆΠ½ΠΎ ΠΏΠΎΠ΄ΠΊΠ»ΡŽΡ‡Π°Ρ‚ΡŒΡΡ
md5 - способ ΡˆΠΈΡ„Ρ€ΠΎΠ²Π°Π½ΠΈΡ пароля

2. Masenze izilungiselelo ezidingekayo ku postgresql.conf (kufolda /etc/postgresql/11/main) ukuze i-pgbackrest isebenze:

archive_command = 'pgbackrest --stanza=main archive-push %p' # Π“Π΄Π΅ main - Π½Π°Π·Π²Π°Π½ΠΈΠ΅ кластСра. ΠŸΡ€ΠΈ установкС postgres автоматичСски создаСт кластСр main.
archive_mode = on
max_wal_senders = 3
wal_level = replica

3. Masenze izilungiselelo ezidingekayo kufayela lokumisa le-pgbackrest (/etc/pgbackrest/pgbackrest.conf):

[main]
pg1-path=/var/lib/postgresql/11/main

[global]
log-level-file=detail
repo1-host=<repository_server_ip>

4. Layisha kabusha i-postgresql:

sudo service postgresql restart

Isetha iseva yenqolobane

Indawo yokugcina (pgbackrest user):

Masenze izilungiselelo ezidingekayo kufayela lokumisa pgbackrest
(/etc/pgbackrest/pgbackrest.conf):

[main]
pg1-host=<postgres_server_ip>
pg1-path=/var/lib/postgresql/11/main

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2 # ΠŸΠ°Ρ€Π°ΠΌΠ΅Ρ‚Ρ€, ΡƒΠΊΠ°Π·Ρ‹Π²Π°ΡŽΡ‰ΠΈΠΉ сколько Ρ…Ρ€Π°Π½ΠΈΡ‚ΡŒ ΠΏΠΎΠ»Π½Ρ‹Ρ… бэкапов. Π’.Π΅. Ссли Ρƒ вас Π΅ΡΡ‚ΡŒ Π΄Π²Π° ΠΏΠΎΠ»Π½Ρ‹Ρ… бэкапа ΠΈ Π²Ρ‹ создаСтС Ρ‚Ρ€Π΅Ρ‚ΠΈΠΉ, Ρ‚ΠΎ самый старый бэкап Π±ΡƒΠ΄Π΅Ρ‚ ΡƒΠ΄Π°Π»Π΅Π½. МоТно ΠΏΡ€ΠΎΠΈΠ·Π½ΠΎΡΠΈΡ‚ΡŒ ΠΊΠ°ΠΊ "Ρ…Ρ€Π°Π½ΠΈΡ‚ΡŒ Π½Π΅ Π±ΠΎΠ»Π΅Π΅ Π΄Π²ΡƒΡ… бэкапов" - ΠΏΠΎ Π°Π½Π°Π»ΠΎΠ³ΠΈΠΈ с ротациями Π»ΠΎΠ³ΠΎΠ². Бпасибо @Aytuar Π·Π° исправлСниС ошибки.
start-fast=y # НачинаСт Ρ€Π΅Π·Π΅Ρ€Π²Π½ΠΎΠ΅ ΠΊΠΎΠΏΠΈΡ€ΠΎΠ²Π°Π½ΠΈΠ΅ Π½Π΅ΠΌΠ΅Π΄Π»Π΅Π½Π½ΠΎ, ΠΏΡ€ΠΎΡ‡ΠΈΡ‚Π°Ρ‚ΡŒ ΠΏΡ€ΠΎ этот ΠΏΠ°Ρ€Π°ΠΌΠ΅Ρ‚Ρ€ ΠΌΠΎΠΆΠ½ΠΎ Ρ‚ΡƒΡ‚ https://postgrespro.ru/docs/postgrespro/9.5/continuous-archiving

Ukudala inqolobane

Indawo yokugcina (pgbackrest user):

Dala isitoreji esisha seqoqo main:

sudo mkdir -m 770 /var/lib/pgbackrest
sudo chown -R pgbackrest /var/lib/pgbackrest/
sudo -u pgbackrest pgbackrest --stanza=main stanza-create

wokuhlola

Iseva ye-Postgres (umsebenzisi we-sudo noma impande):

Sibheka iseva ye-postgres:

sudo -u postgres pgbackrest --stanza=main --log-level-console=info check

Indawo yokugcina (pgbackrest user):

Sibheka iseva yenqolobane:

sudo -u pgbackrest pgbackrest --stanza=main --log-level-console=info check

Siqinisekisa ukuthi ekuphumeni sibona umugqa "hlola ukuphela komyalo: kuqedwe ngempumelelo".

Ukhathele? Masiqhubekele engxenyeni ethakazelisa kakhulu.

Ukwenza ikhophi yasenqolobaneni

Indawo yokugcina (pgbackrest user):

1. Yenza isipele:

sudo -u pgbackrest pgbackrest --stanza=main backup

2. Qiniseka ukuthi isipele senziwe:

ls /var/lib/pgbackrest/backup/main/

I-Pgbackrest izodala isipele esigcwele sokuqala. Uma uthanda, ungaphinda usebenzise umyalo wokulondoloza futhi uqinisekise ukuthi isistimu idala isipele esikhuphukayo.

Uma ufuna ukwenza isipele esigcwele futhi, bese usho ifulegi elengeziwe:

sudo -u pgbackrest pgbackrest --stanza=main --type=full backup

Uma ufuna ukuphuma kwe-console enemininingwane, bese usho futhi:

sudo -u pgbackrest pgbackrest --stanza=main --type=full --log-level-console=info backup

Ibuyisela ikhophi yasenqolobaneni

Iseva ye-Postgres (umsebenzisi we-sudo noma impande):

1. Misa iqoqo elisebenzayo:

sudo pg_ctlcluster 11 main stop

2. Ukubuyisela kusuka kusipele:

sudo -u postgres pgbackrest --stanza=main --log-level-console=info --delta --recovery-option=recovery_target=immediate restore

Ukuze ubuyisele isizindalwazi esimweni sekhophi yasenqolobaneni OKUGCWELE yokugcina, sebenzisa umyalo ngaphandle kokucacisa recovery_target:

sudo -u postgres pgbackrest --stanza=main --log-level-console=info --delta restore

Okubalulekile! Ngemva kokululama, kungase kwenzeke ukuthi isizindalwazi sibambeke kumodi yokutakula (kuzoba namaphutha afana ne-ERROR: ayikwazi ukwenza i-DROP DATABASE kumsebenzi wokufunda kuphela). Uma ngikhuluma iqiniso, angikakaqondi ukuthi lokhu kuhlobene nani. Isixazululo simi kanje (uzodinga ukulinda kancane ngemva kokukhishwa komyalo):

sudo -u postgres psql -c "select pg_wal_replay_resume()"

Eqinisweni, kungenzeka ukubuyisela isipele esithile ngegama laso. Ngilapha kuphela Ngizohlinzeka ngesixhumanisi encazelweni yalesi sici kumadokhumenti. Onjiniyela bayeluleka ukusebenzisa le nketho ngokuqapha futhi bachaze ukuthi kungani. Ngingengeza ngokwami ​​ukuthi ngiyisebenzise. Uma udinga ngempela, qiniseka ukuthi ngemva kokuthola i-database iphuma kumodi yokutakula (khetha pg_is_in_recovery() kufanele ibonise "f") futhi, uma kwenzeka, yenza isipele esigcwele ngemva kokululama.

3. Qala iqoqo:

sudo pg_ctlcluster 11 main start

Ngemva kokubuyisela isipele, sidinga ukwenza isipele sesibili:

Indawo yokugcina (pgbackrest user):

sudo pgbackrest --stanza=main backup

Yilokho kuphela. Sengiphetha, ngithanda ukukukhumbuza ukuthi angizami nakancane ukuzenza i-dba ephezulu futhi ngizosebenzisa amafu nganoma isiphi isikhathi. Njengamanje, mina ngokwami ​​ngiqala ukufunda izihloko ezahlukahlukene ezifana nokwenza isipele, ukuphindaphinda, ukuqapha, njll. futhi ngibhala imibiko emincane ngemiphumela ukuze ngenze umnikelo omncane emphakathini futhi ngizishiyele amashidi amancane okukopela.

Ezihlokweni ezilandelayo ngizozama ukukhuluma ngezici ezengeziwe - ukubuyisela idatha kuqoqo elihlanzekile, ukubethela kwama-backups nokushicilela ku-S3, izipele nge-rsync.

Source: www.habr.com

Engeza amazwana