ProHoster > Π‘Π»ΠΎΠ³ > Kev tswj hwm > Incremental postgresql thaub qab nrog pgbackrest - ib chav kawm rau cov tub ntxhais hluas fighter los ntawm tus tsim tawm

Incremental postgresql thaub qab nrog pgbackrest - ib chav kawm rau cov tub ntxhais hluas fighter los ntawm tus tsim tawm

Tsis lees paub

Kuv yog tus tsim tawm. Kuv sau code thiab cuam tshuam nrog cov database nkaus xwb raws li tus neeg siv. Tsis muaj txoj hauv kev ua kuv ua txuj ua tus tswj hwm kev tswj hwm, tsawg dua dba. Tab sis…

Nws thiaj li tshwm sim uas kuv yuav tsum tau npaj ib tug thaub qab ntawm lub postgresql database. Tsis muaj huab - tsuas yog siv SSH thiab xyuas kom txhua yam ua haujlwm yam tsis tau thov nyiaj. Peb ua li cas rau cov xwm txheej zoo li no? Yog lawm, peb thawb pgdump rau hauv cron, thaub qab txhua yam rau hauv archive txhua hnub, thiab yog tias peb ploj tag, peb xa cov ntaub ntawv no mus rau qhov chaw deb.

Lub sijhawm no qhov nyuaj yog tias raws li cov phiaj xwm, cov ntaub ntawv yuav tsum loj hlob li ntawm +- 100 MB ib hnub. Tau kawg, tom qab ob peb lub lis piam lub siab xav backup txhua yam nrog pgdump yuav ploj mus. Qhov no yog qhov uas incremental backups tuaj cawm.

Txaus siab? Zoo siab txais tos miv.

Incremental backup yog ib hom thaub qab thaum tsis yog tag nrho cov ntaub ntawv tau theej, tab sis tsuas yog cov tshiab thiab cov hloov pauv txij thaum tsim cov ntawv luam dhau los.

Zoo li txhua tus tsim tawm uas tsis txaus siab (thaum lub sijhawm ntawd) kom nkag siab txog qhov tsis sib xws ntawm postgres, Kuv xav nrhiav lub pob ntsuab. Zoo, koj paub, zoo li hauv AWS, DigitalOcean: koj nias ib lub pob - koj tau txais kev rov ua dua, koj nias qhov thib ob - koj teeb tsa thaub qab, qhov thib peb - ​​koj dov txhua yam rov qab ob peb teev. Kuv tsis pom lub pob lossis lub cuab yeej GUI zoo nkauj. Yog tias koj paub ib qho (dawb lossis pheej yig), sau txog nws hauv cov lus.

Tom qab googling kuv pom ob lub cuab yeej pgbarman ΠΈ pgbackrest. Kuv tsuas ua tsis tau tiav nrog thawj tus (cov ntaub ntawv sib txawv heev, kuv sim txheeb xyuas txhua yam raws li phau ntawv qub), tab sis nrog tus thib ob cov ntaub ntawv tau hloov mus rau qhov sib npaug, tab sis tsis muaj qhov tsis zoo. Txhawm rau ua kom yooj yim rau kev ua haujlwm ntawm cov neeg uas tau ntsib nrog txoj haujlwm zoo sib xws, kab lus no tau sau.

Tom qab nyeem tsab xov xwm no, koj yuav kawm paub yuav ua li cas ua incremental backups, txuag lawv mus rau ib tug tej thaj chaw deb neeg rau zaub mov (repository nrog backups) thiab restore lawv nyob rau hauv cov ntaub ntawv ntawm poob los yog lwm yam teeb meem ntawm lub ntsiab server.

Kev cob qhia

Yuav rov tsim dua phau ntawv koj yuav xav tau ob VPS. Thawj qhov yuav yog qhov chaw cia (qhov chaw khaws cia uas cov ntaub ntawv khaws cia), thiab qhov thib ob, qhov tseeb, lub server nws tus kheej nrog postgres (hauv kuv rooj plaub, version 11 ntawm postgres).

Nws yog assumed tias nyob rau hauv lub server nrog postgres koj muaj paus, sudo neeg siv, postgres neeg siv thiab postgres nws tus kheej yog ntsia (tus neeg siv postgres yog tsim cia li thaum txhim kho postgresql), thiab nyob rau hauv lub repository neeg rau zaub mov muaj paus thiab sudo neeg siv (hauv phau ntawv tus neeg siv lub npe pgbackrest yuav raug siv).

Yog li kom koj muaj teeb meem tsawg dua thaum rov tsim cov lus qhia, kuv sau ua ntawv qaij qhov twg, nrog tus neeg siv dab tsi thiab nrog cov cai dab tsi kuv tau ua tiav cov lus txib thaum sau ntawv thiab xyuas cov ntawv.

Txhim kho pgbackrest

Repository (neeg siv pgbackrest):

1. Download tau lub archive los ntawm pgbackrest thiab hloov nws cov ntsiab lus mus rau lub /build folder:

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

2. Txhim kho qhov dependencies tsim nyog rau kev sib dhos:

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

3. Sib dhos pgbackrest:

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

4. Luam cov ntaub ntawv executable rau /usr/bin directory:

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

5. Pgbackrest xav tau perl. Nruab:

sudo apt-get install perl

6. Tsim cov ntawv teev npe rau cov cav, muab qee txoj cai rau lawv:

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

pgbackrest version

Postgres server (sudo neeg siv lossis hauv paus):

Cov txheej txheem ntawm kev txhim kho pgbackrest ntawm lub server nrog postgres zoo ib yam li cov txheej txheem kev teeb tsa ntawm lub chaw cia khoom (yog, pgbackrest yuav tsum tau nruab rau ntawm ob lub servers), tab sis nyob rau hauv nqe 6 cov lus txib thib ob thiab zaum kawg:

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

hloov nrog:

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

Teeb tsa kev sib cuam tshuam ntawm servers ntawm passwordless SSH

Txhawm rau kom pgbackrest ua haujlwm kom raug, nws yog ib qho tsim nyog los teeb tsa kev sib cuam tshuam ntawm postgres server thiab lub chaw cia khoom siv cov ntaub ntawv tseem ceeb.

Repository (neeg siv pgbackrest):

Tsim ib khub tseem ceeb:

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

Ceev faj Peb khiav cov lus txib saum toj no yam tsis muaj sudo.

Postgres server (sudo neeg siv lossis hauv paus):

Tsim ib khub tseem ceeb:

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 (tus neeg siv sudo):

Luam tus yuam sij pej xeem ntawm postgres server mus rau lub chaw khaws ntaub ntawv:

(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

Hauv cov kauj ruam no koj yuav raug nug rau tus password rau tus neeg siv hauv paus. Koj yuav tsum nkag mus rau tus password ntawm tus neeg siv hauv paus ntawm postgres server!

Postgres server (tus neeg siv sudo):

Luam theej cov chaw khaws ntaub ntawv pej xeem rau lub server nrog 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

Hauv cov kauj ruam no koj yuav raug nug rau tus password rau tus neeg siv hauv paus. Koj yuav tsum nkag mus raws nraim tus password ntawm tus neeg siv hauv paus ntawm lub chaw cia khoom!

Peb tshawb xyuas:

Repository (tus neeg siv hauv paus, rau kev ua kom huv ntawm qhov kev sim):

sudo -u pgbackrest ssh postgres@<postgres_server_ip>

Postgres server (tus neeg siv hauv paus, rau kev ua kom huv ntawm qhov kev sim):

sudo -u postgres ssh pgbackrest@<repository_server_ip>

Peb xyuas kom meej tias peb nkag tau yam tsis muaj teeb meem.

Teem lub postgres server

Postgres server (sudo neeg siv lossis hauv paus):

1. Cia peb khob lub postgres server los ntawm lwm tus IPs. Ua li no, kho cov ntaub ntawv postgresql.conf (nyob hauv /etc/postgresql/11/main folder), ntxiv cov kab rau nws:

listen_addresses = '*'

Yog tias cov kab no twb muaj lawm, tsis hais nws lossis teeb tsa tus nqi parameter li '*'.

Hauv cov ntaub ntawv pg_hba.conf (tseem nyob hauv daim nplaub tshev /etc/postgresql/11/main) ntxiv cov kab hauv qab no:

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

qhov twg:

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

2. Cia peb ua qhov tsim nyog nyob rau hauv postgresql.conf (nws yog nyob rau hauv lub folder /etc/postgresql/11/main) rau pgbackrest ua haujlwm:

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

3. Cia peb ua qhov tsim nyog nyob rau hauv cov ntaub ntawv pgbackrest configuration (/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

Teeb tsa lub chaw khaws cia server

Repository (pgbackrest neeg siv):

Cia peb ua qhov tsim nyog nyob rau hauv cov ntaub ntawv configuration 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

Tsim ib lub chaw cia khoom

Repository (pgbackrest neeg siv):

Tsim ib qho chaw cia tshiab rau pawg ntsiab:

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

soj ntsuam

Postgres server (sudo neeg siv lossis hauv paus):

Peb tshawb xyuas ntawm postgres server:

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

Repository (pgbackrest neeg siv):

Peb txheeb xyuas ntawm lub chaw khaws cia server:

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

Peb xyuas kom meej tias nyob rau hauv cov zis peb pom cov kab "check command end: ua tiav tiav".

nkees? Cia peb mus rau qhov nthuav tshaj plaws.

Ua ib qho thaub qab

Repository (pgbackrest neeg siv):

1. Ua ib qho thaub qab:

sudo -u pgbackrest pgbackrest --stanza=main backup

2. Nco ntsoov tias ib qho thaub qab tau tsim:

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

Pgbackrest yuav tsim thawj qhov thaub qab tag nrho. Yog tias koj xav tau, koj tuaj yeem khiav cov lus txib rov qab dua thiab xyuas kom meej tias lub kaw lus tsim ib qho kev sib txuas ntxiv.

Yog hais tias koj xav ua ib tug tag nrho thaub qab, ces qhia ib tug ntxiv chij:

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

Yog tias koj xav tau cov ncauj lus kom ntxaws console, ces qhia meej:

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

Restoring ib tug thaub qab

Postgres server (sudo neeg siv lossis hauv paus):

1. Nres cov pawg khiav:

sudo pg_ctlcluster 11 main stop

2. Rov qab los ntawm thaub qab:

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

Txhawm rau kho cov ntaub ntawv rov qab mus rau lub xeev ntawm qhov kawg FULL thaub qab, siv cov lus txib yam tsis tau hais meej txog qhov rov qab_target:

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

Tseem ceeb! Tom qab rov qab, nws yuav tshwm sim tias cov ntaub ntawv tau daig hauv hom rov qab (yuav muaj qhov yuam kev zoo li ERROR: tsis tuaj yeem ua DROP DATABASE hauv kev nyeem nkaus xwb). Ua ncaj ncees, kuv tseem tsis tau nkag siab tias qhov no txuas nrog dab tsi. Cov kev daws teeb meem yog raws li nram no (koj yuav tsum tau tos me ntsis tom qab cov lus txib raug tua):

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

Qhov tseeb, nws muaj peev xwm rov qab tau ib qho kev thaub qab los ntawm nws lub npe. Ntawm no kuv tsuas yog Kuv yuav muab qhov txuas rau qhov kev piav qhia ntawm qhov tshwj xeeb no hauv cov ntaub ntawv. Cov neeg tsim tawm qhia siv qhov kev xaiv no nrog ceev faj thiab piav qhia vim li cas. Kuv tuaj yeem ntxiv los ntawm kuv tus kheej uas kuv tau siv. Yog tias koj xav tau tiag tiag, xyuas kom meej tias tom qab rov qab cov ntaub ntawv tawm ntawm hom rov qab (xaiv pg_is_in_recovery() yuav tsum qhia "f") thiab, tsuas yog nyob rau hauv rooj plaub, ua tag nrho thaub qab tom qab rov qab.

3. Pib pawg:

sudo pg_ctlcluster 11 main start

Tom qab rov kho qhov thaub qab, peb yuav tsum ua qhov thaub qab thib ob:

Repository (pgbackrest neeg siv):

sudo pgbackrest --stanza=main backup

Yog tag nrho. Hauv kev xaus, Kuv xav ceeb toom rau koj tias kuv tsis muaj txoj hauv kev sim ua txuj ua tus neeg laus dba thiab yuav siv cov huab ntawm lub sijhawm me ntsis. Tam sim no, kuv tus kheej tab tom pib kawm ntau yam xws li thaub qab, rov ua dua, saib xyuas, thiab lwm yam. thiab kuv sau cov ntawv qhia me me txog cov txiaj ntsig txhawm rau ua kom muaj txiaj ntsig me me rau hauv zej zog thiab tso cov ntawv khib nyiab me me rau kuv tus kheej.

Hauv cov ntawv hauv qab no kuv yuav sim tham txog cov yam ntxwv ntxiv - cov ntaub ntawv rov qab mus rau pawg huv, encryption ntawm thaub qab thiab tshaj tawm rau S3, thaub qab ntawm rsync.

Tau qhov twg los: www.hab.com

Ntxiv ib saib