Afeef
Waxaan ahay horumariye Waxaan qoraa koodka oo waxaan la falgalaa xogta xogta isticmaale ahaan oo keliya. Sinaba ugama dhigo inaan ahay maamulaha nidaamka, in ka yar dba. Laakiinβ¦
Waxa dhacday in aan u baahday in aan abaabulo kaydka xogta postgresql. Daruur malaha - kaliya isticmaal SSH oo hubi in wax walba ay shaqeeyaan adiga oo aan lacag waydiisan. Maxaan ku samaynaa xaaladahan oo kale? Taasi waa sax, waxaan ku riixeynaa pgdump cron, wax walbana waxaan ku celineynaa kaydka maalin kasta, oo haddii aan gebi ahaanba weyno, waxaan u dirnaa kaydkan meel fog.
Markan dhibku waxa uu ahaa in sida qorshuhu yahay, kaydinta xogta waxa ay ahayd in ay koraan ilaa +-100 MB maalintii. Dabcan, dhowr toddobaad ka dib rabitaanka in wax walba lagu kaydiyo pgdump way baabi'i doontaa. Halkani waa halka kaydka kordhinta ay u soo gurmadaan.
Xiiso leh? Ku soo dhawoow bisad
Kobcinta korodhka waa nooc ka mid ah kaydinta marka aan dhammaan faylasha isha la koobiyeeyay, laakiin kaliya kuwa cusub iyo kuwa la beddelay tan iyo markii la abuuray nuqulkii hore.
Sida horumariye kasta oo gabi ahaanba aan rabin (waqtigaas) inuu fahmo qalafsanaanta postgres, waxaan rabay inaan helo badhanka cagaaran. Hagaag, waad ogtahay, sida AWS, DigitalOcean: waxaad riixday hal badhan - waxaad heshay ku celcelin, waxaad riixday labaad - waxaad dejisay kayd, kii saddexaad - wax walba dib ayaad u rogtay dhowr saacadood. Ma aanan helin badhan ama qalab GUI oo qurux badan. Haddii aad mid taqaanid (bilaash ama raqiis), ku qor faallooyinka.
Googling ka dib waxaan helay laba qalab pgbarman ΠΈ PGbackrest. Si fudud kuma aan guulaysan kii ugu horreeyay (dokumentiyo aad u yar, waxaan isku dayay inaan wax walba ogaado sida ku cad buug-gacmeedyadii hore), laakiin kan labaadna dukumeentiyadu waxay noqdeen kuwo heersare ah, laakiin aan lahayn cillado. Si loo fududeeyo shaqada kuwa ay la kulmaan hawl la mid ah, qodobkan ayaa la qoray.
Kadib markaad akhrido maqaalkan, waxaad baran doontaa sida loo sameeyo kayd kordhin, u badbaadi server fog (bakhaar kayd ah) oo dib u soo celi haddii ay dhacdo xog lumis ama dhibaatooyin kale oo ku saabsan server-ka ugu weyn.
Tababarka
Si aad u soo saarto buug-gacmeedka waxaad u baahan doontaa laba VPS. Marka hore waxay noqon doontaa kaydinta (bakhaarka kaydinta lagu kaydin doono), iyo tan labaad, dhab ahaantii, server-ka laftiisa oo leh postgres (kiiskeyga, nooca 11 ee postgres).
Waxaa loo maleynayaa in server-ka leh postgres aad leedahay xidid, sudo user, postgres user iyo postgres laftiisa ayaa lagu rakibay (isticmalaha postgres si toos ah ayaa loo abuuray marka la rakibayo postgresql), iyo server-ka kaydka waxaa jira isticmaale xidid iyo sudo ( buug-gacmeedka Magaca isticmaale ee pgbackrest ayaa la isticmaali doonaa) .
Si ay kuu yaraadaan dhibaatooyinku marka aad dib u soo saarayso tilmaamaha, waxaan ku qoraa farriimaha Halkee, isticmaale iyo xuquuqdee ayaan amarka ku fuliyay markaad qorayso oo hubinayso maqaalka.
Ku rakibida pgbackrest
Kaydka (isticmaalka pgbackrest):
1. Ka soo deji kaydka pgbackrest oo ku wareeji waxa ku jira gal/dhis:
sudo mkdir /build
sudo wget -q -O -
https://github.com/pgbackrest/pgbackrest/archive/release/2.18.tar.gz |
sudo tar zx -C /build
2. Ku xidh waxyaalaha lagama maarmaanka u ah isu-ururinta:
sudo apt-get update
sudo apt-get install build-essential libssl-dev libxml2-dev libperl-dev zlib1g-dev
libpq-dev
3. Isku xirka pgbackrest:
cd /build/pgbackrest-release-2.18/src && sudo ./configure
sudo make -s -C /build/pgbackrest-release-2.18/src
4. Ku koobbi faylka la fulin karo tusaha/usr/bin:
sudo cp /build/pgbackrest-release-2.18/src/pgbackrest /usr/bin
sudo chmod 755 /usr/bin/pgbackrest
5. Pgbackrest waxay u baahan tahay perl. Ku rakib:
sudo apt-get install perl
6. U samee hagaha diiwaanka, sii xuquuqaha qaarkood:
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. Hubi:
pgbackrest version
Serverka Postgres (isticmalaha sudo ama xidid):
Habka lagu rakibo pgbackrest server-ka leh postgres wuxuu la mid yahay habka rakibida ee kaydka (haa, pgbackrest waa in lagu rakibaa labada adeegba), laakiin cutubka 6aad amarrada labaad iyo kan dambe:
sudo chown pgbackrest:pgbackrest /var/log/pgbackrest
sudo chown pgbackrest:pgbackrest /etc/pgbackrest/pgbackrest.conf
ku beddel:
sudo chown postgres:postgres /var/log/pgbackrest
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
Dejinta isdhexgalka ka dhexeeya server-yada iyada oo loo marayo SSH bilaa sir ah
Si pgbackrest ay si sax ah ugu shaqeyso, waxaa lagama maarmaan ah in la habeeyo isdhexgalka ka dhexeeya server-ka postgres iyo kaydka iyadoo la adeegsanayo faylka muhiimka ah.
Kaydka (isticmaalka pgbackrest):
Samee lamaane fure ah:
mkdir -m 750 /home/pgbackrest/.ssh
ssh-keygen -f /home/pgbackrest/.ssh/id_rsa
-t rsa -b 4096 -N ""
Digniin Waxaan wadnaa amarrada kor ku xusan iyada oo aan sudo lahayn.
Serverka Postgres (isticmalaha sudo ama xidid):
Samee lamaane fure ah:
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 ""
Kaydka (isticmalaha sudo):
Ku koobbi furaha dadweynaha ee seerfarka boostada kaydka serfarka:
(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
Tallaabadan waxaa lagu weydiin doonaa erayga sirta ah ee isticmaalaha xididka. Waxaad u baahan tahay inaad geliso erayga sirta ah ee isticmaalaha xididka serverka postgres!
Adeegaha Postgres ( isticmaalaha sudo):
Ku koobbi furaha dadweynaha ee kaydka ee seerfarka oo wata boostada:
(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
Tallaabadan waxay ku weydiin doontaa erayga sirta ah ee isticmaalaha xididka. Waxaad u baahan tahay inaad geliso erayga sirta ah ee xididka isticmaalaha kaydka!
Hubinta:
Kaydka ( isticmaalaha xididka, daahirsanaanta tijaabada):
sudo -u pgbackrest ssh postgres@<postgres_server_ip>
Boostada Postgres ( isticmaalaha xididka, daahirsanaanta tijaabada):
sudo -u postgres ssh pgbackrest@<repository_server_ip>
Waxaan hubineynaa inaan heli karno dhibaato la'aan.
Dejinta server-ka boostada
Serverka Postgres (isticmalaha sudo ama xidid):
1. Aynu ogolaano garaacista server-ka boostada ee IP-yada dibadda. Si tan loo sameeyo, wax ka beddel faylka postgresql.conf (wuxuu ku yaalaa /etc/postgresql/11/faylka weyn), ku darida xariiqda:
listen_addresses = '*'
Haddii xariiqda noocaan ahi hore u jirtay, ama ha ka faalloon ama u dhig qiimaha cabbirka '*'.
Faylka ku jira pg_hba.conf (sidoo kale waxay ku taal galka /etc/postgresql/11/main) ku dar sadarradan soo socda:
hostssl all all 0.0.0.0/0 md5
host all all 0.0.0.0/0 md5
meesha:
hostssl/host - ΠΏΠΎΠ΄ΠΊΠ»ΡΡΠ°Π΅ΠΌΡΡ ΡΠ΅ΡΠ΅Π· SSL (ΠΈΠ»ΠΈ Π½Π΅Ρ)
all - ΡΠ°Π·ΡΠ΅ΡΠ°Π΅ΠΌ ΠΏΠΎΠ΄ΠΊΠ»ΡΡΠ΅Π½ΠΈΠ΅ ΠΊΠΎ Π²ΡΠ΅ΠΌ Π±Π°Π·Π°ΠΌ
all - ΠΈΠΌΡ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»Ρ, ΠΊΠΎΡΠΎΡΠΎΠΌΡ ΡΠ°Π·ΡΠ΅ΡΠ°Π΅ΠΌ ΠΏΠΎΠ΄ΠΊΠ»ΡΡΠ΅Π½ΠΈΠ΅ (Π²ΡΠ΅ΠΌ)
0.0.0.0/0 - ΠΌΠ°ΡΠΊΠ° ΡΠ΅ΡΠΈ Ρ ΠΊΠΎΡΠΎΡΠΎΠΉ ΠΌΠΎΠΆΠ½ΠΎ ΠΏΠΎΠ΄ΠΊΠ»ΡΡΠ°ΡΡΡΡ
md5 - ΡΠΏΠΎΡΠΎΠ± ΡΠΈΡΡΠΎΠ²Π°Π½ΠΈΡ ΠΏΠ°ΡΠΎΠ»Ρ
2. Aynu samayno goobaha lagama maarmaanka ah postgresql.conf (waxa ay ku jirtaa galka /etc/postgresql/11/mainpgbackrest si uu u shaqeeyo:
archive_command = 'pgbackrest --stanza=main archive-push %p' # ΠΠ΄Π΅ main - Π½Π°Π·Π²Π°Π½ΠΈΠ΅ ΠΊΠ»Π°ΡΡΠ΅ΡΠ°. ΠΡΠΈ ΡΡΡΠ°Π½ΠΎΠ²ΠΊΠ΅ postgres Π°Π²ΡΠΎΠΌΠ°ΡΠΈΡΠ΅ΡΠΊΠΈ ΡΠΎΠ·Π΄Π°Π΅Ρ ΠΊΠ»Π°ΡΡΠ΅Ρ main.
archive_mode = on
max_wal_senders = 3
wal_level = replica
3. Aynu ka samayno goobaha lagama maarmaanka ah ee faylka qaabeynta pgbackrest (/etc/pgbackrest/pgbackrest.conf):
[main]
pg1-path=/var/lib/postgresql/11/main
[global]
log-level-file=detail
repo1-host=<repository_server_ip>
4. Dib u soo deji postgresql:
sudo service postgresql restart
Dejinta seerfarka kaydka
Kaydka (isticmalaha pgbackrest):
Aynu samayno goobaha lagama maarmaanka ah ee faylka qaabeynta 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
Abuuritaanka kayd
Kaydka (isticmalaha pgbackrest):
U samee kayd cusub kooxda ugu weyn ee:
sudo mkdir -m 770 /var/lib/pgbackrest
sudo chown -R pgbackrest /var/lib/pgbackrest/
sudo -u pgbackrest pgbackrest --stanza=main stanza-create
kormeerka
Serverka Postgres (isticmalaha sudo ama xidid):
Waxaan hubineynaa server-ka boostada:
sudo -u postgres pgbackrest --stanza=main --log-level-console=info check
Kaydka (isticmalaha pgbackrest):
Waxaan hubineynaa server-ka keydka:
sudo -u pgbackrest pgbackrest --stanza=main --log-level-console=info check
Waxaan hubineynaa in wax soo saarka aan ku aragno xariiqda "hubi amarka dhamaadka: si guul leh loo dhammeeyey".
Daalay? Aan u gudubno qaybta ugu xiisaha badan.
Samaynta kayd
Kaydka (isticmalaha pgbackrest):
1. Samee gurmad:
sudo -u pgbackrest pgbackrest --stanza=main backup
2. Hubi in kayd ah la sameeyay:
ls /var/lib/pgbackrest/backup/main/
Pgbackrest ayaa abuuri doonta kaydkii ugu horreeyay ee buuxa. Haddii aad rabto, waxaad mar kale socodsiin kartaa amarka kaydinta oo aad hubiso in nidaamku abuurayo koobitaan kordhin ah.
Haddii aad rabto in aad samayso gurmad buuxa mar kale, ka dib cadee calan dheeraad ah:
sudo -u pgbackrest pgbackrest --stanza=main --type=full backup
Haddii aad rabto soo saarista console faahfaahsan, ka dibna sidoo kale sheeg:
sudo -u pgbackrest pgbackrest --stanza=main --type=full --log-level-console=info backup
Soo celinta kaydka
Serverka Postgres (isticmalaha sudo ama xidid):
1. Jooji kooxda ordaya:
sudo pg_ctlcluster 11 main stop
2. Soo celinta kaydinta:
sudo -u postgres pgbackrest --stanza=main --delta restore
3. Bilow kooxda:
sudo pg_ctlcluster 11 main start
Ka dib markii aan soo celinay kaabta, waxaan u baahanahay inaan samayno gurmad labaad:
Kaydka (isticmalaha pgbackrest):
sudo pgbackrest --stanza=main backup
Waa intaas. Gabagabadii, waxaan rabaa in aan idin xasuusiyo in aanan sinaba isku dayin in aan iska dhigo qof sare oo dba ah oo aan daruuraha u adeegsan doono fursada ugu yar. Hadda, aniga qudhaydu waxaan bilaabayaa inaan barto mawduucyo kala duwan sida kaabaynta, ku celcelinta, la socodka, iwm. waxaanan ka qoraa warbixino yaryar oo ku saabsan natiijada si aan wax yar ugu soo kordhiyo bulshada oo aan uga tago xaashiyo yar oo khiyaamo ah.
Maqaallada soo socda waxaan isku dayi doonaa inaan ka hadlo sifooyinka dheeraadka ah - soo kabashada xogta koox nadiif ah, sirta kaydinta iyo daabacaadda S3, kaydinta iyada oo loo marayo rsync.
Source: www.habr.com