Zowonjezera zosunga zobwezeretsera za postgresql ndi pgbackrest - maphunziro a wankhondo wachinyamata kuchokera kwa wopanga

Chodzikanira

Ndine wopanga. Ndimalemba kachidindo ndikulumikizana ndi nkhokwe ngati wogwiritsa ntchito. Sindimadziyesa ngati woyang'anira dongosolo, mocheperapo dba. Koma…

Zinachitika kuti ndikufunika kukonza zosunga zobwezeretsera za postgresql. Palibe mitambo - ingogwiritsani ntchito SSH ndikuwonetsetsa kuti zonse zikuyenda osapempha ndalama. Kodi zikatere timatani? Ndiko kulondola, timakankhira pgdump mu cron, kusunga zonse zosungidwa tsiku lililonse, ndipo ngati tatayika kwathunthu, timatumiza zosungirako kwinakwake kutali.

Panthawiyi vuto linali lakuti malinga ndi ndondomekoyi, malo osungirako zinthu amayenera kukula pafupifupi + - 100 MB patsiku. Zachidziwikire, pakatha milungu ingapo chikhumbo chosunga zonse ndi pgdump chidzazimiririka. Apa ndipamene ma backups owonjezera amabwera kudzapulumutsa.

Zosangalatsa? Takulandilani kumphaka.

Zosunga zobwezeretsera zowonjezera ndi mtundu wa zosunga zobwezeretsera pomwe si mafayilo onse oyambira omwe amakopedwa, koma atsopano ndi omwe asinthidwa kuyambira pomwe kope lapitalo.

Monga wopanga mapulogalamu omwe sanafune (panthawiyo) kuti amvetsetse zovuta za postgres, ndimafuna kupeza batani lobiriwira. Mukudziwa, monga mu AWS, DigitalOcean: mudadina batani limodzi - muli ndi kubwereza, munakanikiza chachiwiri - mumakhazikitsa zosunga zobwezeretsera, chachitatu - mudabweza chilichonse maola angapo. Sindinapeze batani kapena chida chokongola cha GUI. Ngati mukudziwa imodzi (yaulere kapena yotsika mtengo), lembani za izo mu ndemanga.

Pambuyo pa googling ndinapeza zida ziwiri pgbarman и pgbackrest. Sindinapambane ndi yoyambayo (zolemba zochepa kwambiri, ndinayesera kulingalira zonse malinga ndi zolemba zakale), koma ndi yachiwiri zolembazo zinakhala zofanana, koma osati zopanda zolakwika. Pofuna kupeputsa ntchito ya anthu amene akukumana ndi ntchito yofanana ndi imeneyi, nkhaniyi inalembedwa.

Pambuyo powerenga nkhaniyi, muphunzira momwe mungapangire ma backups owonjezera, kuwasunga ku seva yakutali (malo osungira okhala ndi zosunga zobwezeretsera) ndikubwezeretsanso ngati kutayika kwa data kapena mavuto ena pa seva yayikulu.

Kukonzekera

Kuti mupangenso bukuli mudzafunika ma VPS awiri. Yoyamba idzakhala yosungirako (malo osungiramo zosungirako zidzasungidwa), ndipo chachiwiri, makamaka, seva yomwe ili ndi postgres (kwa ine, 11 ya postgres).

Zimaganiziridwa kuti pa seva yokhala ndi ma postgres muli ndi mizu, wogwiritsa ntchito sudo, wogwiritsa ntchito postgres ndipo postgres yokha imayikidwa (wogwiritsa ntchito postgres amapangidwa pokhapokha akayika postgresql), ndipo pa seva yosungirako pali mizu ndi sudo wogwiritsa ntchito (mu bukhuli). dzina la wosuta pgbackrest lidzagwiritsidwa ntchito) .

Kuti mukhale ndi zovuta zochepa popanganso malangizowo, ndimalemba mopendekera kuti, ndi wogwiritsa ntchito ndani komanso ndi maufulu otani omwe ndidapereka lamulolo polemba ndikuyang'ana nkhaniyo.

Kukhazikitsa pgbackrest

Posungira (wosuta pgbackrest):

1. Tsitsani zolemba zakale kuchokera ku pgbackrest ndikusamutsa zomwe zili mu /build foda:

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

2. Ikani zodalira zofunika pakuphatikiza:

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

3. Kusonkhanitsa pgbackrest:

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

4. Koperani fayilo yotheka ku /usr/bin directory:

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

5. Pgbackrest imafuna perl. Ikani:

sudo apt-get install perl

6. Pangani maukonde a zipika, apatseni maufulu ena:

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. Onani:

pgbackrest version

Seva ya Postgres (wogwiritsa ntchito sudo kapena mizu):

Njira yoyika pgbackrest pa seva yokhala ndi postgres ndi yofanana ndi njira yokhazikitsira pamalo osungira (inde, pgbackrest iyenera kukhazikitsidwa pa seva zonse ziwiri), koma mu ndime ya 6 lamulo lachiwiri ndi lomaliza:

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

m'malo ndi:

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

Kukhazikitsa kulumikizana pakati pa ma seva kudzera pa SSH yopanda mawu

Kuti pgbackrest igwire ntchito moyenera, ndikofunikira kukonza kuyanjana pakati pa seva ya postgres ndi posungira pogwiritsa ntchito fayilo yayikulu.

Posungira (wosuta pgbackrest):

Pangani makiyi awiri:

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

Chonde chonde! Timayendetsa malamulo pamwambapa popanda sudo.

Seva ya Postgres (wogwiritsa ntchito sudo kapena mizu):

Pangani makiyi awiri:

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 ""

Posungira (wogwiritsa ntchito sudo):

Lembani kiyi yapagulu ya seva ya postgres ku seva yosungira:

(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

Pa sitepe iyi mudzafunsidwa achinsinsi kwa muzu wosuta. Muyenera kuyika mawu achinsinsi a muzu wa seva ya postgres!

Seva ya Postgres (wogwiritsa ntchito sudo):

Koperani fungulo la anthu onse ku seva ndi ma 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

Pa sitepe iyi mudzafunsidwa achinsinsi kwa muzu wosuta. Muyenera kulowetsa ndendende mawu achinsinsi a muzu wogwiritsa ntchito posungira!

Kufufuza:

Repository (wogwiritsa ntchito mizu, kuti ayese kuyesa):

sudo -u pgbackrest ssh postgres@<postgres_server_ip>

Seva ya Postgres (wogwiritsa ntchito mizu, kuti ayese kuyesa):

sudo -u postgres ssh pgbackrest@<repository_server_ip>

Timaonetsetsa kuti tikupeza popanda mavuto.

Kupanga seva ya postgres

Seva ya Postgres (wogwiritsa ntchito sudo kapena mizu):

1. Tiyeni tilole kugogoda pa seva ya postgres kuchokera ku ma IP akunja. Kuti muchite izi, sinthani fayilo postgresql.conf (yomwe ili mu /etc/postgresql/11/main foda), ndikuwonjezera mzerewo:

listen_addresses = '*'

Ngati mzere woterewu ulipo kale, masulani kapena ikani mtengo wa parameter ngati '*'.

Mu fayilo pg_hba.conf (ilinso mufoda /etc/postgresql/11/main) onjezani mizere iyi:

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

kumene:

hostssl/host - подключаемся через SSL (или нет)
all - разрешаем подключение ко всем базам
all - имя пользователя, которому разрешаем подключение (всем)
0.0.0.0/0 - маска сети с которой можно подключаться
md5 - способ шифрования пароля

2. Tiyeni tipange zoikamo zofunika postgresql.conf (ili mu foda /etc/postgresql/11/main) kuti pgbackrest igwire ntchito:

archive_command = 'pgbackrest --stanza=main archive-push %p' # Где main - название кластера. При установке postgres автоматически создает кластер main.
archive_mode = on
max_wal_senders = 3
wal_level = replica

3. Tiyeni tipange zoikamo zofunika mu fayilo ya pgbackrest (/etc/pgbackrest/pgbackrest.conf):

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

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

4. Kwezaninso postgresql:

sudo service postgresql restart

Kukhazikitsa seva yosungira

Posungira (pgbackrest wosuta):

Tiyeni tipange zoikamo zofunika mu fayilo yosinthira 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

Kupanga posungira

Posungira (pgbackrest wosuta):

Pangani chosungira chatsopano cha gulu waukulu:

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

kuyendera

Seva ya Postgres (wogwiritsa ntchito sudo kapena mizu):

Timayang'ana pa seva ya postgres:

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

Posungira (pgbackrest wosuta):

Timayang'ana pa seva yosungira:

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

Timaonetsetsa kuti muzotulutsa tikuwona mzere "fufuzani kumapeto kwa lamulo: kutsirizidwa bwino".

Wotopa? Tiyeni tipite ku gawo losangalatsa kwambiri.

Kupanga zosunga zobwezeretsera

Posungira (pgbackrest wosuta):

1. Pangani zosunga zobwezeretsera:

sudo -u pgbackrest pgbackrest --stanza=main backup

2. Onetsetsani kuti zosunga zobwezeretsera zapangidwa:

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

Pgbackrest ipanga zosunga zobwezeretsera zonse. Ngati mukufuna, mutha kuyendetsanso lamulo losunga zobwezeretsera ndikuwonetsetsa kuti dongosololi limapanga zosunga zobwezeretsera.

Ngati mukufuna kubwezeretsanso zonse, tchulani mbendera yowonjezera:

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

Ngati mukufuna kutulutsa kwatsatanetsatane kwa console, fotokozaninso:

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

Kubwezeretsa zosunga zobwezeretsera

Seva ya Postgres (wogwiritsa ntchito sudo kapena mizu):

1. Imitsa gulu lothamanga:

sudo pg_ctlcluster 11 main stop

2. Kubwezeretsa kuchokera ku zosunga zobwezeretsera:

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

Kuti mubwezeretse nkhokwe ku malo omaliza FULL zosunga zobwezeretsera, gwiritsani ntchito lamulo osatchula recovery_target:

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

Zofunika! Pambuyo pochira, zitha kuchitika kuti nkhokweyo imakakamira (padzakhala zolakwika ngati ERROR: sangathe kuchita DROP DATABASE pakuwerenga kokha). Kunena zowona, sindinamvetsetse kuti izi zikugwirizana ndi chiyani. Yankho lake ndi motere (muyenera kudikirira pang'ono lamulolo litaperekedwa):

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

M'malo mwake, ndizotheka kubwezeretsa zosunga zobwezeretsera ndi dzina lake. Ndili pano ndekha Ndipereka ulalo wofotokozera za gawoli muzolemba. Madivelopa amalangiza kugwiritsa ntchito njirayi mosamala ndikufotokozera chifukwa chake. Ndikhoza kuwonjezera kuchokera kwa ine ndekha kuti ndagwiritsa ntchito. Ngati mukufunikiradi kutero, onetsetsani kuti mutatha kubwezeretsanso malo osungirako zinthu zakale (sankhani pg_is_in_recovery () kusonyeza "f") ndipo, pokhapokha, sungani zosunga zobwezeretsera pambuyo pochira.

3. Yambitsani gulu:

sudo pg_ctlcluster 11 main start

Pambuyo pobwezeretsa zosunga zobwezeretsera, tiyenera kuchita zosunga zobwezeretsera kachiwiri:

Posungira (pgbackrest wosuta):

sudo pgbackrest --stanza=main backup

Ndizomwezo. Pomaliza, ndikufuna ndikukumbutseni kuti sindikuyesera kudziyesa ngati dba wamkulu ndipo ndidzagwiritsa ntchito mitambo pamwayi wochepa. Pakadali pano, inenso ndikuyamba kuphunzira mitu yosiyanasiyana monga zosunga zobwezeretsera, kubwerezabwereza, kuwunika, ndi zina. ndipo ndimalemba malipoti ang'onoang'ono okhudza zotsatira kuti ndipereke chithandizo chochepa kwa anthu ammudzi ndikusiya mapepala ang'onoang'ono achinyengo.

M'nkhani zotsatirazi ndiyesera kulankhula za zowonjezera - kubwezeretsa deta ku gulu loyera, kubisala zosunga zobwezeretsera ndikusindikiza ku S3, zosunga zobwezeretsera kudzera pa rsync.

Source: www.habr.com