Ƙaddamarwa na baya bayangresql tare da pgbackrest - hanya don matashin soja daga mai haɓakawa

Disclaimer

Ni mai haɓakawa ne. Ina rubuta lamba kuma ina hulɗa tare da bayanan bayanai kawai a matsayin mai amfani. Ko kadan ba zan yi kamar ni mai kula da tsarin ba, sai dai dba. Amma…

Ya faru da cewa ina buƙatar tsara ajiyar bayanan bayanan postgresql. Babu gizagizai - kawai amfani da SSH kuma tabbatar da cewa komai yana aiki ba tare da neman kuɗi ba. Menene muke yi a irin waɗannan lokuta? Haka ne, muna tura pgdump cikin cron, mu adana komai zuwa ma'ajiyar bayanai kowace rana, kuma idan muka ɓace gaba ɗaya, za mu aika da wannan tarihin wani wuri mai nisa.

A wannan karon wahalar ita ce, bisa ga tsare-tsare, ya kamata a yi girma da kusan +- 100 MB a kowace rana. Tabbas, bayan makonni biyu sha'awar madadin komai tare da pgdump zai ɓace. Anan ne madaidaicin kari ke zuwa don ceto.

Abin sha'awa? Barka da zuwa cat.

Ajiyayyen ƙara wani nau'in madadin ne lokacin da ba duk fayilolin tushen ba ne ake kwafi ba, amma sababbi ne kawai da waɗanda aka canza tun ƙirƙirar kwafin baya.

Kamar kowane mai haɓakawa wanda bai yarda da gaske ba (a wancan lokacin) don fahimtar abubuwan da ke cikin postgres, Ina so in nemo maɓallin kore. To, ka sani, kamar a cikin AWS, DigitalOcean: kun danna maballin ɗaya - kun sami kwafi, kun danna na biyu - kun saita madadin, na uku - kun mirgine komai baya bayan sa'o'i biyu. Ban sami maɓalli ko kyakkyawan kayan aikin GUI ba. Idan kun san ɗaya (kyauta ko arha), rubuta game da shi a cikin sharhi.

Bayan gogling na sami kayan aiki guda biyu pgrman и pgbackrest. Kawai ban yi nasara da na farko ba (takaddun da ba su da yawa, na yi ƙoƙarin gano komai bisa ga tsoffin litattafai), amma tare da na biyu takaddun ya zama daidai, amma ba tare da lahani ba. Don sauƙaƙe aikin waɗanda suke fuskantar irin wannan aiki, an rubuta wannan labarin.

Bayan karanta wannan labarin, za ku koyi yadda ake yin ƙarin ajiya, adana su zuwa uwar garken nesa (majiya mai ma'auni) da mayar da su idan an sami asarar bayanai ko wasu matsaloli akan babban uwar garken.

Horo

Don sake buga littafin za ku buƙaci VPS biyu. Na farko zai zama ajiyar ajiya (majigin da za a adana ma'ajin), na biyu, a gaskiya, uwar garken kanta tare da postgres (a cikin akwati na, sigar 11 na postgres).

An ɗauka cewa a kan uwar garken tare da postgres kuna da tushen, mai amfani sudo, mai amfani da postgres da kuma postgres kanta an shigar da shi (an halicci mai amfani da postgres ta atomatik lokacin shigar da postgresql), kuma akan uwar garken ajiya akwai tushen da sudo mai amfani (a cikin littafin jagora). za a yi amfani da sunan mai amfani pgbackrest) .

Domin ku sami ƴan matsaloli yayin sake fitar da umarnin, na rubuta cikin rubutun inda, da wane mai amfani kuma da wane hakki na aiwatar da umarnin yayin rubutawa da duba labarin.

Ana shigar da pgbackrest

Wurin ajiya (pgbackrest mai amfani):

1. Zazzage ma'ajin daga pgbackrest kuma canja wurin abinda ke ciki zuwa babban fayil / ginawa:

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

2. Shigar da abin dogaro da ake buƙata don haɗawa:

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

3. Haɗa pgbackrest:

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

4. Kwafi fayil ɗin da za a iya aiwatarwa zuwa ga /usr/bin directory:

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

5. Pgbackrest yana buƙatar perl. Shigar:

sudo apt-get install perl

6. Ƙirƙiri kundayen adireshi don rajistan ayyukan, ba su wasu haƙƙoƙi:

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

pgbackrest version

Sabar Postgres (mai amfani da sudo ko tushen):

Tsarin shigar pgbackrest akan uwar garken tare da postgres yayi kama da tsarin shigarwa akan ma'ajiyar (eh, dole ne a shigar da pgbackrest akan sabobin biyu), amma a cikin sakin layi na 6 umarni na biyu da na ƙarshe:

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

maye gurbinsu da:

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

Saita hulɗa tsakanin sabobin ta hanyar SSH mara kalmar sirri

Domin pgbackrest yayi aiki daidai, yana da mahimmanci don saita hulɗar tsakanin uwar garken postgres da ma'ajiyar ta amfani da fayil ɗin maɓalli.

Wurin ajiya (pgbackrest mai amfani):

Ƙirƙiri maɓalli biyu:

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

Tsanaki Muna gudanar da umarni na sama ba tare da sudo ba.

Sabar Postgres (mai amfani da sudo ko tushen):

Ƙirƙiri maɓalli biyu:

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

Wurin ajiya (mai amfani da sudo):

Kwafi maɓallin jama'a na uwar garken postgres zuwa uwar garken ma'ajiya:

(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

A wannan mataki za a tambaye ku kalmar sirri don tushen mai amfani. Kuna buƙatar shigar da kalmar sirrin tushen mai amfani da sabar postgres!

Sabar Postgres (mai amfani da sudo):

Kwafi maɓalli na jama'a zuwa uwar garken tare da 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

A wannan mataki za a tambaye ku kalmar sirri don tushen mai amfani. Kuna buƙatar shigar da ainihin kalmar sirri na tushen mai amfani da ma'ajiyar!

Binciken:

Wurin ajiya (mai amfani da tushen, don tsarkin gwajin):

sudo -u pgbackrest ssh postgres@<postgres_server_ip>

Sabar Postgres (mai amfani da tushen, don tsarkin gwajin):

sudo -u postgres ssh pgbackrest@<repository_server_ip>

Muna tabbatar da cewa mun sami damar shiga ba tare da matsala ba.

Saita uwar garken postgres

Sabar Postgres (mai amfani da sudo ko tushen):

1. Bari mu ƙyale bugawa a kan uwar garken postgres daga IPs na waje. Don yin wannan, shirya fayil ɗin postgresql.conf (wanda yake cikin /etc/postgresql/11/ babban fayil), yana ƙara layin zuwa gare shi:

listen_addresses = '*'

Idan irin wannan layin ya riga ya wanzu, ko dai a raba shi ko saita ƙimar siga a matsayin '*'.

A cikin fayil pg_hba.conf (kuma yana cikin babban fayil /etc/postgresql/11/main) ƙara wadannan layukan:

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

inda:

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

2. Bari mu sanya saitunan da suka dace a ciki postgresql.conf (yana cikin babban fayil /etc/postgresql/11/main) don pgbackrest don aiki:

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

3. Bari mu yi saitunan da suka dace a cikin fayil ɗin daidaitawar pgbackrest (/etc/pgbackrest/pgbackrest.conf):

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

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

4. Sake saka postgresql:

sudo service postgresql restart

Saita uwar garken wurin ajiya

Wurin ajiya (mai amfani da pgbackrest):

Bari mu yi saitunan da suka dace a cikin fayil ɗin sanyi 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

Ƙirƙirar wurin ajiya

Wurin ajiya (mai amfani da pgbackrest):

Ƙirƙiri sabon ajiya don tari main:

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

dubawa

Sabar Postgres (mai amfani da sudo ko tushen):

Muna duba uwar garken postgres:

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

Wurin ajiya (mai amfani da pgbackrest):

Muna duba uwar garken ma'ajiya:

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

Mun tabbatar da cewa a cikin fitarwa mun ga layin "duba umarnin ƙare: an kammala nasara".

Gaji? Bari mu ci gaba zuwa mafi ban sha'awa.

Yin madadin

Wurin ajiya (mai amfani da pgbackrest):

1. Yi wariyar ajiya:

sudo -u pgbackrest pgbackrest --stanza=main backup

2. Tabbatar cewa an ƙirƙiri madadin:

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

Pgbackrest zai ƙirƙiri cikakken madadin na farko. Idan kuna so, zaku iya sake gudanar da umarnin wariyar ajiya kuma ku tabbata cewa tsarin ya ƙirƙiri madadin kari.

Idan kuna son sake yin cikakken wariyar ajiya, sannan saka ƙarin tuta:

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

Idan kuna son fitar da cikakkun bayanan na'urar bidiyo, sannan kuma saka:

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

Ana dawo da madadin

Sabar Postgres (mai amfani da sudo ko tushen):

1. Dakatar da gungu mai gudu:

sudo pg_ctlcluster 11 main stop

2. Ana dawowa daga madadin:

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

Don mayar da bayanan zuwa yanayin FULL madadin na ƙarshe, yi amfani da umarnin ba tare da ƙayyade recovery_target ba:

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

Muhimmanci! Bayan an dawo, yana iya faruwa cewa bayanan sun makale a yanayin dawowa (za a sami kurakurai kamar ERROR: ba zai iya aiwatar da DROP DATABASE a cikin ma'amalar karantawa kawai ba). A gaskiya, har yanzu ban fahimci abin da wannan ke da alaƙa da shi ba. Maganin shine kamar haka (za ku buƙaci jira kaɗan bayan an aiwatar da umarnin):

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

A gaskiya ma, yana yiwuwa a mayar da takamaiman madadin ta sunansa. Ga ni kadai Zan ba da hanyar haɗi zuwa bayanin wannan fasalin a cikin takaddun. Masu haɓakawa suna ba da shawarar yin amfani da wannan zaɓi tare da taka tsantsan kuma suna bayyana dalilin. Zan iya ƙara daga kaina cewa na yi amfani da shi. Idan kuna buƙatar gaske, tabbatar cewa bayan an dawo da bayanan bayanan sun fita yanayin dawowa (zaɓi pg_is_in_recovery() ya kamata ya nuna “f”) kuma, kawai idan, yi cikakken madadin bayan dawowa.

3. Fara gungu:

sudo pg_ctlcluster 11 main start

Bayan maido da madadin, muna buƙatar yin madadin na biyu:

Wurin ajiya (mai amfani da pgbackrest):

sudo pgbackrest --stanza=main backup

Shi ke nan. A ƙarshe, Ina so in tunatar da ku cewa ba ni da wata hanya ta ƙoƙarin yin kamar ni babban dba ne kuma zan yi amfani da gajimare a ko da yaushe. A halin yanzu, ni kaina na fara nazarin batutuwa daban-daban kamar su madadin, kwafi, saka idanu, da dai sauransu. kuma ina rubuta kananan rahotanni game da sakamakon don in ba da gudummawa kaɗan ga al'umma kuma in bar wa kaina ƙananan zanen gado.

A cikin labaran da ke gaba zan yi ƙoƙarin yin magana game da ƙarin fasali - dawo da bayanai zuwa gungu mai tsabta, ɓoye bayanan ajiya da bugawa zuwa S3, madadin ta hanyar rsync.

source: www.habr.com