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 # Параметр, указывающий сколько хранить полных бэкапов. Т.е. если у вас есть два полных бэкапа и вы создаете третий - первые два будут удалены вместе с инкрементами. 
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 --delta restore

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