Awọn afẹyinti postgresql ti o pọ si pẹlu pgbackrest - ipa-ọna fun ọmọ onija ọdọ lati ọdọ olupilẹṣẹ

AlAIgBA

Mo jẹ olupilẹṣẹ. Mo kọ koodu ati ṣe ajọṣepọ pẹlu data data nikan bi olumulo kan. Ni ọna kan ni MO ṣe dibọn lati jẹ oludari eto, o kere pupọ dba. Sugbon…

O ṣẹlẹ pe Mo nilo lati ṣeto afẹyinti ti aaye data postgresql. Ko si awọsanma - o kan lo SSH ati rii daju pe ohun gbogbo n ṣiṣẹ laisi beere fun owo. Kini a ṣe ni iru awọn ọran bẹ? Iyẹn tọ, a titari pgdump sinu cron, ṣe afẹyinti ohun gbogbo si ile ifi nkan pamosi lojoojumọ, ati pe ti a ba sọnu patapata, a firanṣẹ ile-ipamọ yii si ibikan ti o jinna.

Ni akoko yii iṣoro naa ni pe ni ibamu si awọn ero, data data yẹ ki o dagba nipasẹ nipa + - 100 MB fun ọjọ kan. Nitoribẹẹ, lẹhin ọsẹ meji kan ifẹ lati ṣe afẹyinti ohun gbogbo pẹlu pgdump yoo parẹ. Eyi ni ibi ti awọn afẹyinti afikun wa si igbala.

Awon nkan? Kaabo si ologbo.

Afẹyinti afikun jẹ iru afẹyinti nigba ti kii ṣe gbogbo awọn faili orisun ni a daakọ, ṣugbọn awọn tuntun nikan ati awọn ti o yipada lati igba ẹda ẹda ti tẹlẹ.

Bi eyikeyi Olùgbéejáde ti o wà Egba ko fẹ (ni akoko yẹn) lati ni oye awọn intricacies ti postgres, Mo fe lati wa awọn alawọ bọtini. O dara, o mọ, bii ninu AWS, DigitalOcean: o tẹ bọtini kan - o ni atunwi, o tẹ keji - o ṣeto awọn afẹyinti, ẹkẹta - o yi ohun gbogbo pada ni awọn wakati meji. Emi ko ri bọtini kan tabi ohun elo GUI ẹlẹwa kan. Ti o ba mọ ọkan (ọfẹ tabi olowo poku), kọ nipa rẹ ninu awọn asọye.

Lẹhin ti googling Mo ri awọn irinṣẹ meji pgbarman и pgbackrest. Emi ko ṣaṣeyọri nikan pẹlu ọkan akọkọ (awọn iwe fọnka pupọ, Mo gbiyanju lati ro ero ohun gbogbo ni ibamu si awọn iwe afọwọkọ atijọ), ṣugbọn pẹlu ọkan keji iwe-ipamọ naa ti jade lati jẹ deede, ṣugbọn kii ṣe laisi awọn abawọn. Láti mú kí iṣẹ́ àwọn tí wọ́n dojú kọ irú iṣẹ́ bẹ́ẹ̀ rọrùn, a kọ àpilẹ̀kọ yìí.

Lẹhin kika nkan yii, iwọ yoo kọ ẹkọ bi o ṣe le ṣe awọn afẹyinti afikun, fi wọn pamọ si olupin latọna jijin (ibi ipamọ pẹlu awọn afẹyinti) ati mu pada wọn ni ọran ti pipadanu data tabi awọn iṣoro miiran lori olupin akọkọ.

Igbaradi

Lati ṣe atunṣe iwe-itumọ iwọ yoo nilo VPS meji. Ni igba akọkọ ti yoo jẹ ibi ipamọ (ibi ipamọ ti awọn afẹyinti yoo wa ni ipamọ), ati keji, ni otitọ, olupin tikararẹ pẹlu postgres (ninu ọran mi, ẹya 11 ti postgres).

O ti ro pe lori olupin pẹlu postgres o ni gbongbo, olumulo sudo, olumulo postgres ati postgres funrararẹ ti fi sii (olumulo postgres ti ṣẹda laifọwọyi nigbati o ba nfi postgresql sori ẹrọ), ati lori olupin ibi ipamọ nibẹ ni root ati olumulo sudo (ninu afọwọṣe ao lo oruko olumulo pgbackrest) .

Ki o ba ni awọn iṣoro diẹ nigbati o tun ṣe awọn ilana, Mo kọ ni italics Nibo, pẹlu olumulo wo ati pẹlu awọn ẹtọ wo ni MO ṣe aṣẹ naa lakoko kikọ ati ṣayẹwo nkan naa.

Fifi pgbackrest sori ẹrọ

Ibi ipamọ (pgbackrest olumulo):

1. Ṣe igbasilẹ igbasilẹ lati pgbackrest ki o gbe awọn akoonu rẹ si folda / kọ:

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

2. Fi sori ẹrọ awọn igbẹkẹle pataki fun apejọ:

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

3. Npejọpọ pgbackrest:

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

4. Da faili ti o le ṣiṣẹ si / usr/bin liana:

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

5. Pgbackrest nilo perl. Fi sori ẹrọ:

sudo apt-get install perl

6. Ṣẹda awọn ilana fun awọn akọọlẹ, fun wọn ni awọn ẹtọ kan:

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. Ṣayẹwo:

pgbackrest version

olupin Postgres (olumulo sudo tabi gbongbo):

Ilana fifi pgbackrest sori olupin pẹlu postgres jẹ iru ilana fifi sori ẹrọ lori ibi ipamọ (bẹẹni, pgbackrest gbọdọ fi sori ẹrọ lori olupin mejeeji), ṣugbọn ninu paragika 6th aṣẹ keji ati ikẹhin:

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

ropo pẹlu:

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

Ṣiṣeto ibaraenisepo laarin awọn olupin nipasẹ SSH ti ko ni ọrọ igbaniwọle

Ni ibere fun pgbackrest lati ṣiṣẹ ni deede, o jẹ dandan lati tunto ibaraenisepo laarin olupin postgres ati ibi ipamọ nipa lilo faili bọtini.

Ibi ipamọ (pgbackrest olumulo):

Ṣẹda orisii bọtini kan:

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

Išọra A ṣiṣe awọn aṣẹ loke laisi sudo.

olupin Postgres (olumulo sudo tabi gbongbo):

Ṣẹda orisii bọtini kan:

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

Ibi ipamọ (olumulo sudo):

Daakọ bọtini gbogbo eniyan ti olupin postgres si olupin ibi ipamọ:

(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

Ni igbesẹ yii iwọ yoo beere fun ọrọ igbaniwọle fun olumulo root. O nilo lati tẹ ọrọ igbaniwọle ti olumulo root ti olupin postgres sii!

olupin Postgres (olumulo sudo):

Daakọ bọtini ita gbangba si olupin pẹlu 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

Ni ipele yii iwọ yoo beere fun ọrọ igbaniwọle fun olumulo root. O nilo lati tẹ ọrọ igbaniwọle gangan ti olumulo gbongbo ti ibi ipamọ naa!

A ṣayẹwo:

Ibi ipamọ (olumulo gbongbo, fun mimọ ti adanwo):

sudo -u pgbackrest ssh postgres@<postgres_server_ip>

olupin Postgres (olumulo gbongbo, fun mimọ ti idanwo naa):

sudo -u postgres ssh pgbackrest@<repository_server_ip>

A rii daju wipe a gba wiwọle lai isoro.

Ṣiṣeto olupin postgres kan

olupin Postgres (olumulo sudo tabi gbongbo):

1. Jẹ ki a gba lilu lori olupin postgres lati awọn IPs ita. Lati ṣe eyi, ṣatunkọ faili naa postgresql.conf (ti o wa ninu /etc/postgresql/11/ folda akọkọ), fifi laini kun si:

listen_addresses = '*'

Ti iru laini kan ba wa tẹlẹ, yala ko dahun tabi ṣeto iye paramita bi '*'.

Ninu faili pg_hba.conf (tun wa ninu folda naa /etc/postgresql/11/akọkọ) fi awọn ila wọnyi kun:

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

nibo ni:

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

2. Jẹ ki ká ṣe awọn pataki eto ni postgresql.conf (o wa ninu folda /etc/postgresql/11/akọkọ) fun pgbackrest lati ṣiṣẹ:

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

3. Jẹ ki a ṣe awọn eto pataki ninu faili iṣeto pgbackrest (/etc/pgbackrest/pgbackrest.conf):

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

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

4. Tun postgresql gbee:

sudo service postgresql restart

Ṣiṣeto olupin ibi ipamọ kan

Ibi ipamọ (olumulo pgbackrest):

Jẹ ki a ṣe awọn eto pataki ninu faili iṣeto ni 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

Ṣiṣẹda ibi ipamọ kan

Ibi ipamọ (olumulo pgbackrest):

Ṣẹda ibi ipamọ tuntun fun iṣupọ akọkọ:

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

ayewo

olupin Postgres (olumulo sudo tabi gbongbo):

A ṣayẹwo lori olupin postgres:

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

Ibi ipamọ (olumulo pgbackrest):

A ṣayẹwo lori olupin ibi ipamọ:

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

A rii daju pe ninu abajade a rii laini “ṣayẹwo pipaṣẹ aṣẹ: pari ni aṣeyọri”.

O rẹwẹsi? Jẹ ki a lọ si apakan ti o nifẹ julọ.

Ṣiṣe afẹyinti

Ibi ipamọ (olumulo pgbackrest):

1. Ṣe afẹyinti:

sudo -u pgbackrest pgbackrest --stanza=main backup

2. Rii daju pe a ti ṣẹda afẹyinti:

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

Pgbackrest yoo ṣẹda afẹyinti kikun akọkọ. Ti o ba fẹ, o le tun ṣiṣẹ aṣẹ afẹyinti lẹẹkansi ati rii daju pe eto naa ṣẹda afẹyinti afikun.

Ti o ba fẹ ṣe afẹyinti ni kikun lẹẹkansi, lẹhinna pato asia afikun kan:

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

Ti o ba fẹ iṣẹjade console alaye, lẹhinna tun pato:

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

Nmu afẹyinti pada

olupin Postgres (olumulo sudo tabi gbongbo):

1. Da iṣupọ nṣiṣẹ duro:

sudo pg_ctlcluster 11 main stop

2. Pada sipo lati afẹyinti:

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

Lati mu data pada si ipo ti afẹyinti FULL ti o kẹhin, lo aṣẹ laisi pato recovery_target:

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

Pataki! Lẹhin imularada, o le ṣẹlẹ pe data data di ni ipo imularada (awọn aṣiṣe yoo wa bi Aṣiṣe: ko le ṣiṣẹ DROP DATABASE ni idunadura kika-nikan). Lati so ooto, Emi ko tii loye kini eyi ni asopọ pẹlu. Ojutu naa jẹ bi atẹle (iwọ yoo nilo lati duro diẹ lẹhin pipaṣẹ naa):

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

Ni otitọ, o ṣee ṣe lati mu pada afẹyinti kan pato nipasẹ orukọ rẹ. Emi nikan wa nibi Emi yoo pese ọna asopọ si apejuwe ẹya yii ninu iwe-ipamọ naa. Awọn olupilẹṣẹ ni imọran lilo aṣayan yii pẹlu iṣọra ati ṣalaye idi. Mo le ṣafikun lati ara mi pe Mo lo. Ti o ba nilo looto, rii daju pe lẹhin imularada data data jade ni ipo imularada (yan pg_is_in_recovery() yẹ ki o ṣafihan “f”) ati, ni ọran, ṣe afẹyinti ni kikun lẹhin imularada.

3. Bẹrẹ iṣupọ:

sudo pg_ctlcluster 11 main start

Lẹhin mimu-pada sipo afẹyinti, a nilo lati ṣe afẹyinti keji:

Ibi ipamọ (olumulo pgbackrest):

sudo pgbackrest --stanza=main backup

Gbogbo ẹ niyẹn. Ni ipari, Emi yoo fẹ lati leti pe Emi ko gbiyanju lati dibọn bi oga dba ati pe Emi yoo lo awọn awọsanma ni aye diẹ. Lọwọlọwọ, Emi funrarami n bẹrẹ lati kawe awọn akọle oriṣiriṣi bii afẹyinti, ẹda, ibojuwo, ati bẹbẹ lọ. ati pe Mo kọ awọn ijabọ kekere nipa awọn abajade lati le ṣe ilowosi kekere si agbegbe ati fi awọn iwe iyanjẹ kekere silẹ fun ara mi.

Ninu awọn nkan wọnyi Emi yoo gbiyanju lati sọrọ nipa awọn ẹya afikun - imularada data si iṣupọ mimọ, fifi ẹnọ kọ nkan ti awọn afẹyinti ati titẹjade si S3, awọn afẹyinti nipasẹ rsync.

orisun: www.habr.com