ProHoster > Ibhulogi > Ulawulo > I-backups ye-postgresql eyongeziweyo kunye ne-pgbackrest-ikhosi yomlo omncinci ovela kumphuhlisi

I-backups ye-postgresql eyongeziweyo kunye ne-pgbackrest-ikhosi yomlo omncinci ovela kumphuhlisi

Ukuzihlangula

Ndingumphuhlisi. Ndibhala ikhowudi kwaye ndisebenzisana nedathabheyisi kuphela njengomsebenzisi. Andiyi kuzenza ngathi ndingumlawuli wenkqubo, kancinci idba. Kodwa…

Kwenzekile ukuba ndifune ukuququzelela ukugcinwa kwedatha ye-postgresql. Akukho mafu-sebenzisa nje i-SSH kwaye uqiniseke ukuba yonke into isebenza ngaphandle kokucela imali. Senza ntoni kwiimeko ezinjalo? Kulungile, sityhala i-pgdump kwi-cron, sigcina yonke into kwindawo yokugcina yonke imihla, kwaye ukuba silahlekile ngokupheleleyo, sithumela lo vimba kwindawo ekude.

Ngeli xesha ubunzima kukuba ngokwezicwangciso, i-database yayifanele ikhule malunga ne- + - 100 MB ngosuku. Ewe kunjalo, emva kweeveki ezimbalwa umnqweno wokugcina yonke into nge-pgdump uya kunyamalala. Apha kulapho ii-backups ezongeziweyo zisiza khona.

Unomdla? Wamkelekile kwikati.

I-backup eyongezelelweyo luhlobo lwe-backup xa ingezizo zonke iifayile zomthombo ezikhutshelweyo, kodwa ezintsha kuphela kunye nezo zitshintshiweyo ukususela ekudalweni kwekopi yangaphambili.

Njengaye nawuphi na umthuthukisi owayengafuni (ngelo xesha) ukuqonda ubunzima be-postgres, ndandifuna ukufumana iqhosha eliluhlaza. Ewe, uyazi, njengakwi-AWS, iDijithaliOcean: ucinezele iqhosha elinye-ufumene impinda, ucinezele okwesibini-useta ii-backups, eyesithathu-uqengqele yonke into emva kweeyure ezimbalwa. Andizange ndifumane iqhosha okanye isixhobo esihle se-GUI. Ukuba uyazi enye (isimahla okanye itshiphu), bhala ngayo kwizimvo.

Emva kwe-googling ndafumana izixhobo ezimbini pgbarman ΠΈ pgbackrest. Andizange ndiphumelele ngeyokuqala (amaxwebhu amancinci kakhulu, ndizamile ukufumanisa yonke into ngokweencwadi ezindala), kodwa ngeyesibini amaxwebhu abonakala ehambelana, kodwa engenaziphene. Ukwenza lula umsebenzi wabo bajongene nomsebenzi ofanayo, eli nqaku labhalwa.

Emva kokufunda eli nqaku, uya kufunda indlela yokwenza i-backups eyongezelelweyo, uyigcine kwi-server ekude (indawo yokugcina kunye ne-backups) kwaye uyibuyisele kwimeko yokulahleka kwedatha okanye ezinye iingxaki kwi-server engundoqo.

Ukulungiselela

Ukuvelisa kwakhona incwadana uya kufuna iiVPS ezimbini. Eyokuqala iya kuba yindawo yokugcina (indawo yokugcina i-backups iya kugcinwa), kwaye okwesibini, ngokwenene, umncedisi ngokwawo kunye ne-postgres (kwimeko yam, i-11 ye-postgres).

Kucingelwa ukuba kwiseva ene-postgres unengcambu, umsebenzisi we-sudo, umsebenzisi we-postgres kunye ne-postgres ngokwayo ifakiwe (umsebenzisi we-postgres wenziwa ngokuzenzekelayo xa ufaka i-postgresql), kwaye kwiseva yokugcina kukho ingcambu kunye nomsebenzisi we-sudo (kwincwadana igama lomsebenzisi pgbackrest liza kusetyenziswa) .

Ukuze ube neengxaki ezimbalwa xa uphinda uphinda uvelise imiyalelo, ndibhala ngookekeleyo phi, ngowuphi umsebenzisi kwaye ngawaphi amalungelo ndenze umyalelo ngelixa ubhala kwaye ujonga inqaku.

Ifakela i-pgbackrest

Uvimba (umsebenzisi pgbackrest):

1. Khuphela i-archive kwi-pgbackrest kwaye uthumele imixholo yayo kwi/yakha ifolda:

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 izinto ezixhomekeke ekudibaneni nazo:

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

3. Ukudibanisa i-pgbackrest:

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

4. Khuphela ifayile ephunyezwayo kwi/usr/bin directory:

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

5. I-Pgbackrest ifuna i-perl. Faka:

sudo apt-get install perl

6. Yenza abalawuli beelog, ubanike 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. Jonga:

pgbackrest version

Iseva yePostgres (umsebenzisi we-sudo okanye ingcambu):

Inkqubo yokuhlohla i-pgbackrest kwiseva ene-postgres iyafana nenkqubo yokuhlohla kwindawo yokugcina (ewe, i-pgbackrest kufuneka ifakwe kuzo zombini iiseva), kodwa kumhlathi we-6 umyalelo wesibini nowokugqibela:

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

buyisela nge:

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

Ukumisela intsebenziswano phakathi kweeseva nge-SSH engenamagama

Ukuze i-pgbackrest isebenze ngokuchanekileyo, kuyimfuneko ukuqwalasela intsebenziswano phakathi komncedisi we-postgres kunye ne-repository usebenzisa ifayile engundoqo.

Uvimba (umsebenzisi pgbackrest):

Yenza isibini esibalulekileyo:

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

Nceda nceda! Siqhuba le miyalelo ingasentla ngaphandle kwe-sudo.

Iseva yePostgres (umsebenzisi we-sudo okanye ingcambu):

Yenza isibini esibalulekileyo:

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):

Khuphela isitshixo sikawonke-wonke seseva ye-postgres kwiseva 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

Kweli nyathelo uya kucelwa igama eligqithisiweyo lomsebenzisi wengcambu. Kufuneka ufake igama eligqithisiweyo lengcambu yomsebenzisi we-postgres server!

Iseva yePostgres (umsebenzisi we-sudo):

Khuphela isitshixo sikawonke-wonke sogcino kumncedisi ngeposi:

(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

Kweli nyathelo uya kucelwa igama eligqithisiweyo lomsebenzisi wengcambu. Kufuneka ufake kanye igama eligqithisiweyo lengcambu yomsebenzisi wendawo yokugcina!

Ukuhlola:

Indawo yokugcina (ingcambu yomsebenzisi, ukucoceka kovavanyo):

sudo -u pgbackrest ssh postgres@<postgres_server_ip>

Umncedisi we-Postgres (umsebenzisi weengcambu, ukucoceka kovavanyo):

sudo -u postgres ssh pgbackrest@<repository_server_ip>

Siyaqinisekisa ukuba siyafikelela ngaphandle kweengxaki.

Ukumisela iseva ye-postgres

Iseva yePostgres (umsebenzisi we-sudo okanye ingcambu):

1. Masivumele ukunkqonkqoza kwiseva ye-postgres evela kwii-IP zangaphandle. Ukwenza oku, hlela ifayile postgresql.conf (ibekwe kwi /etc/postgresql/11/ifolda enkulu), yongeza umgca kuyo:

listen_addresses = '*'

Ukuba umgca onje sele ukhona, mhlawumbi ungawukhupheli ucomment okanye usete ixabiso le parameter njenge '*'.

Kwifayile pg_hba.conf (ekwakhona kwifolda /etc/postgresql/11/main) yongeza le migca ilandelayo:

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

apho:

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

2. Masenze izicwangciso eziyimfuneko postgresql.conf (ikwifolda /etc/postgresql/11/main) ukuze 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 izicwangciso eziyimfuneko kwifayile yoqwalaselo ye-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 kwakhona i-postgresql:

sudo service postgresql restart

Ukumisela iseva yokugcina

Uvimba (umsebenzisi we-pgbackrest):

Masenze izicwangciso eziyimfuneko kwifayile yoqwalaselo 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 indawo yokugcina

Uvimba (umsebenzisi we-pgbackrest):

Yenza indawo yokugcina entsha yeqela eyona:

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

ukuhlola

Iseva yePostgres (umsebenzisi we-sudo okanye ingcambu):

Sijonga kwiseva ye-postgres:

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

Uvimba (umsebenzisi we-pgbackrest):

Sijonga kwiseva yendawo:

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

Siqinisekisa ukuba kwimveliso sibona umgca "jonga ekupheleni komyalelo: ugqitywe ngempumelelo".

Ndidiniwe? Masiqhubele phambili kweyona ndawo inomdla.

Ukwenza i-backup

Uvimba (umsebenzisi we-pgbackrest):

1. Yenza i-backup:

sudo -u pgbackrest pgbackrest --stanza=main backup

2. Qinisekisa ukuba ugcino lwenziwe:

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

I-Pgbackrest iya kudala ugcino olupheleleyo lokuqala. Ukuba unqwenela, ungaphinda usebenzise umyalelo ogcinayo kwaye uqiniseke ukuba inkqubo yenza ugcino olongezelelekileyo.

Ukuba ufuna ukwenza ugcino olupheleleyo kwakhona, emva koko uchaze iflegi eyongezelelweyo:

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

Ukuba ufuna imveliso ye-console eneenkcukacha, phinda ucacise:

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

Ukubuyisela i-backup

Iseva yePostgres (umsebenzisi we-sudo okanye ingcambu):

1. Misa iqela eliqhubayo:

sudo pg_ctlcluster 11 main stop

2. Ukubuyisela kwi-backup:

sudo -u postgres pgbackrest --stanza=main --delta restore

3. Qala iqela:

sudo pg_ctlcluster 11 main start

Emva kokubuyisela i-backup, kufuneka senze i-backup yesibini:

Uvimba (umsebenzisi we-pgbackrest):

sudo pgbackrest --stanza=main backup

Kuko konke. Ukuqukumbela, ndingathanda ukukukhumbuza ukuba andizami nangayiphi na indlela ukuzenza i-dba ephezulu kwaye ndiya kusebenzisa amafu ngelona thuba lincinci. Okwangoku, mna ngokwam ndiqala ukufunda izihloko ezahlukeneyo ezifana ne-backup, ukuphindaphinda, ukubeka iliso, njl. kwaye ndibhala iingxelo ezincinci malunga neziphumo ukwenzela ukuba ndenze igalelo elincinci kuluntu kwaye ndishiye amaphepha amancinci okukopela kum.

Kumanqaku alandelayo ndiya kuzama ukuthetha malunga neempawu ezongezelelweyo - ukubuyisela idatha kwi-cluster ecocekileyo, i-encryption of backups kunye nokushicilela kwi-S3, i-backups nge-rsync.

umthombo: www.habr.com

Yongeza izimvo