ProHoster > Blog > Utongi > Inowedzera postgresql backups ine pgbackrest - kosi yemurwi mudiki kubva kumugadziri

Inowedzera postgresql backups ine pgbackrest - kosi yemurwi mudiki kubva kumugadziri

Disclaimer

Ndiri developer. Ini ndinonyora kodhi uye ndinopindirana nedhatabhesi chete semushandisi. Hapana nzira ini ndinonyepedzera kunge ndiri maneja wehurongwa, ndoda dba. Asi…

Zvakaitika kuti ini ndaida kuronga backup yepostgresql dhatabhesi. Hapana makore - ingoshandisa SSH uye ita shuwa kuti zvese zvinoshanda pasina kukumbira mari. Tinoita sei mumamiriro ezvinhu akadaro? Ndizvozvo, isu tinosundira pgdump mu cron, kuchengetedza zvese kune archive mazuva ese, uye kana isu takarasika zvachose, tinotumira iyi dura kune imwe nzvimbo kure.

Panguva ino dambudziko raive rekuti maererano nehurongwa, dhatabhesi raifanirwa kukura nenge + - 100 MB pazuva. Ehe, mushure memavhiki mashoma chishuwo chekuchengetedza zvese ne pgdump chinopera. Apa ndipo apo ma backups ekuwedzera anouya kuzonunura.

Inonakidza? Kugamuchirwa kukati.

Kuwedzera backup imhando yekuchengetedza kana asiri ese mafaera ekutanga akakopwa, asi matsva chete uye ayo akachinjwa kubva pakusikwa kwekopi yapfuura.

Senge chero mugadziri anga asiri ABSOLUTELY asingadi (panguva iyoyo) kuti anzwisise kuomarara kwepostgres, ndaida kutsvaga bhatani regirini. Zvakanaka, iwe unoziva, senge muAWS, DigitalOcean: wakadzvanya bhatani rimwe - wakawana replication, wakadzvanya yechipiri - iwe unoseta backups, yechitatu - wakatenderedza zvese kumashure kwemaawa akati wandei. Ini handina kuwana bhatani kana yakanaka GUI chishandiso. Kana iwe uchiziva imwe (yemahara kana yakachipa), nyora nezvazvo mumashoko.

Mushure me googling ndakawana maturusi maviri pgbarman ΠΈ pgbackrest. Ini handina kungobudirira neyekutanga (zvinyorwa zvishoma, ndakaedza kufunga zvese maererano nezvinyorwa zvekare), asi neyechipiri zvinyorwa zvakazove zvinoenderana, asi zvisina zvikanganiso. Kurerutsa basa reavo vakatarisana nebasa rakafanana, chinyorwa ichi chakanyorwa.

Mushure mekuverenga chinyorwa ichi, iwe uchadzidza maitiro ekuita ma backups ekuwedzera, chengetedza kune iri kure sevha (repository ine backups) uye udzorere ivo kana kukanganisa data kana mamwe matambudziko pane huru server.

Kugadzirira kwe

Kuti ugadzire bhuku racho iwe uchada maviri VPS. Yekutanga ichava yekuchengetedza (iyo repository iyo iyo backups ichachengeterwa), uye yechipiri, chokwadi, sevha pachayo ine postgres (munyaya yangu, vhezheni 11 ye postgres).

Zvinofungidzirwa kuti pane sevha ine postgres iwe une midzi, sudo mushandisi, postgres mushandisi uye postgres pachayo inoiswa (mushandisi wepostgres anogadzirwa otomatiki kana achiisa postgresql), uye pane repository server pane mudzi uye sudo mushandisi (mubhuku rekushandisa. zita remushandisi pgbackrest richashandiswa) .

Kuti uve nematambudziko mashoma paunenge uchigadzira mirairo, ndinonyora nemavara kupi, nemushandisi upi uye nekodzero dzipi ndakaita murairo tichinyora nekutarisa chinyorwa.

Kuisa pgbackrest

Repository (mushandisi pgbackrest):

1. Dhawunirodha dura kubva pgbackrest uye woendesa zvirimo ku/build folda:

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

2. Isa zvinotsamira zvinodikanwa pakuungana:

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

3. Kuunganidza pgbackrest:

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

4. Kopa faira rinogoneka ku /usr/bin dhairekitori:

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

5. Pgbackrest inoda perl. Isa:

sudo apt-get install perl

6. Gadzira madhairekitori ematanda, vape dzimwe kodzero:

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

pgbackrest version

Postgres server (sudo mushandisi kana mudzi):

Maitiro ekuisa pgbackrest pane sevha ine postgres yakafanana neyekugadzika maitiro pane repository (hongu, pgbackrest inofanira kuiswa pamaseva ese ari maviri), asi mundima yechitanhatu murairo wechipiri newekupedzisira:

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

tsiva ne:

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

Kumisikidza kudyidzana pakati pemaseva kuburikidza nepassword SSH

Kuti pgbackrest ishande nemazvo, zvinodikanwa kugadzirisa kupindirana pakati peiyo postgres server uye repository uchishandisa kiyi faira.

Repository (mushandisi pgbackrest):

Gadzira makiyi maviri:

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

Cherechedza chinyorwa! Isu tinomhanya mirairo iri pamusoro pasina sudo.

Postgres server (sudo mushandisi kana mudzi):

Gadzira makiyi maviri:

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

Repository (sudo mushandisi):

Kopa kiyi yeruzhinji ye postgres server kune repository server:

(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

Pane ino nhanho iwe uchabvunzwa password yemudzi mushandisi. Iwe unofanirwa kuisa password yemudzi mushandisi weiyo postgres server!

Postgres server (sudo mushandisi):

Kopa repository public kiyi kune server ine 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

Pane ino nhanho iwe uchabvunzwa password yemudzi mushandisi. Iwe unofanirwa kuisa chaiyo password yemudzi mushandisi we repository!

Tinotarisa:

Repository (mudzi mushandisi, yekuchena kwekuyedza):

sudo -u pgbackrest ssh postgres@<postgres_server_ip>

Postgres server (mudzi mushandisi, yekuchena kwekuyedza):

sudo -u postgres ssh pgbackrest@<repository_server_ip>

Isu tinoita shuwa kuti tinowana mukana pasina matambudziko.

Kugadzira postgres server

Postgres server (sudo mushandisi kana mudzi):

1. Ngatibvumirei kugogodza pane postgres server kubva kunze kweIPs. Kuti uite izvi, gadzirisa faira postgresql.conf (iri mu /etc/postgresql/11/main folda), kuwedzera mutsetse kwairi:

listen_addresses = '*'

Kana mutsara wakadaro watovepo, bvisa kana kuti isa kukosha kweparameter se'*'.

Mufaira pg_hba.conf (inowanikwawo mune folda /etc/postgresql/11/main) wedzera mitsara inotevera:

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

kupi:

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

2. Ngatiite zvigadziro zvinodiwa mukati postgresql.conf (iri mufolda /etc/postgresql/11/main) kuti pgbackrest ishande:

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

3. Ngatiite zvigadziro zvinodiwa mupgbackrest configuration file (/etc/pgbackrest/pgbackrest.conf):

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

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

4. Reload postgresql:

sudo service postgresql restart

Kugadzira repository server

Repository (pgbackrest mushandisi):

Ngatiite zvigadziriso zvinodiwa mufaira rekugadzirisa 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

Kugadzira repository

Repository (pgbackrest mushandisi):

Gadzira chengetedzo nyowani ye cluster kuru:

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

kuonorora

Postgres server (sudo mushandisi kana mudzi):

Isu tinotarisa pane postgres server:

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

Repository (pgbackrest mushandisi):

Isu tinotarisa pane repository server:

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

Isu tinoita shuwa kuti mune zvakabuda tinoona mutsara "check command kupera: wapedza zvinobudirira".

Wakaneta? Ngatienderere mberi kune chikamu chinonyanya kufadza.

Kuita backup

Repository (pgbackrest mushandisi):

1. Ita backup:

sudo -u pgbackrest pgbackrest --stanza=main backup

2. Ita shuwa kuti backup yagadzirwa:

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

Pgbackrest ichagadzira yekutanga yakazara backup. Kana uchida, unogona kumhanyisa murairo wekuchengetedza zvakare uye ita shuwa kuti sisitimu inogadzira backup yekuwedzera.

Kana iwe uchida kuita yakazara backup zvakare, wobva watsanangura imwe mureza:

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

Kana iwe uchida yakadzama console kubuda, wobva watsanangura:

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

Kudzoreredza backup

Postgres server (sudo mushandisi kana mudzi):

1. Misa sumbu rinomhanya:

sudo pg_ctlcluster 11 main stop

2. Kudzoreredza kubva kune backup:

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

Kuti udzorere dhatabhesi kumamiriro ekupedzisira FULL backup, shandisa murairo usingatsanangure recovery_target:

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

Zvakakosha! Mushure mekupora, zvinogona kuitika kuti dhatabhesi inonamira munzira yekudzoreredza (pachave nezvikanganiso senge ERROR: haigone kuita DROP DATABASE mune yekuverenga-chete transaction). Kutaura chokwadi, handisati ndanzwisisa kuti izvi zvakabatana nei. Mhinduro yacho ndeiyi inotevera (iwe uchafanirwa kumirira zvishoma mushure mekunge murairo waitwa):

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

Muchokwadi, zvinokwanisika kudzoreredza imwe backup nezita rayo. Ndiri pano chete Ini ndichapa chinongedzo kune tsananguro yechinhu ichi mune zvinyorwa. Vagadziri vanopa zano kushandisa iyi sarudzo nekuchenjerera uye tsanangura chikonzero nei. Ndinogona kuwedzera kubva kwandiri kuti ndakaishandisa. Kana iwe uchida chaizvo, ita shuwa kuti mushure mekudzoreredza iyo dhatabhesi inobuda yekudzoreredza modhi (sarudza pg_is_in_recovery () inofanira kuratidza "f") uye, kana zvikaitika, ita backup yakazara mushure mekupora.

3. Tanga chikwata:

sudo pg_ctlcluster 11 main start

Mushure mekudzoreredza backup, isu tinofanirwa kuita backup yechipiri:

Repository (pgbackrest mushandisi):

sudo pgbackrest --stanza=main backup

Ndizvo zvose. Mukupedzisa, ndinoda kukuyeuchidza kuti ini handisi kuedza kunyepedzera kuva mukuru dba uye ndichashandisa makore pamukana mudiki. Parizvino, ini pachangu ndiri kutanga kudzidza dzakasiyana misoro senge backup, kudzokorora, kutarisa, nezvimwe. uye ndinonyora mishumo midiki pamusoro pezvabuda kuitira kuti ndiite mupiro mudiki kunharaunda uye ndinosiya madiki mashiti ekubiridzira ini.

Muzvinyorwa zvinotevera ini ndichaedza kutaura nezve mamwe maficha - data kudzoreredza kune yakachena cluster, encryption ye backups uye kushambadza kuS3, backups kuburikidza rsync.

Source: www.habr.com

Voeg