ProHoster > Блог > Nchịkwa > Nkwado postgresql na-abawanye na pgbackrest - nkuzi maka onye agha na-eto eto sitere na onye nrụpụta

Nkwado postgresql na-abawanye na pgbackrest - nkuzi maka onye agha na-eto eto sitere na onye nrụpụta

Nkwuputa

Abụ m onye nrụpụta. M na-ede koodu na-emekọrịta ihe na nchekwa data naanị dị ka onye ọrụ. N'ụzọ ọ bụla m na-eme ka à ga-asị na m bụ onye nchịkwa sistemu, ma ọ dịkarịa ala dba. Mana…

O mere na achọrọ m ịhazi ndabere nke nchekwa data postgresql. Enweghị igwe ojii - naanị jiri SSH wee hụ na ihe niile na-arụ ọrụ na-arịọghị ego. Kedu ihe anyị na-eme n'ọnọdụ ndị dị otú ahụ? Nke ahụ ziri ezi, anyị na-atụgharị pgdump n'ime cron, na-echekwa ihe niile na ebe nchekwa kwa ụbọchị, ma ọ bụrụ na anyị efunahụla kpamkpam, anyị na-eziga ebe a na-edebe ihe ochie n'ebe dị anya.

Oge a ihe isi ike bụ na dị ka atụmatụ si dị, nchekwa data kwesịrị itolite ihe dịka + - 100 MB kwa ụbọchị. N'ezie, mgbe izu ole na ole gachara, ọchịchọ ịkwado ihe niile na pgdump ga-apụ n'anya. Nke a bụ ebe nkwado ndabere na mpaghara na-abịa napụta.

Na-akpali mmasị? Nabata nwamba.

Ndabere na-abawanye bụ ụdị nkwado ndabere na mpaghara mgbe ọ bụghị faịlụ isi mmalite niile ka edepụtaghachiri, mana ọ bụ naanị ndị ọhụrụ na ndị gbanwere kemgbe e mepụtara mbipụta gara aga.

Dị ka onye nrụpụta ọ bụla nke na-achọghị (n'oge ahụ) ịghọta mgbagwoju anya nke postgres, achọrọ m ịchọta bọtịnụ akwụkwọ ndụ akwụkwọ ndụ. Ọ dị mma, ị maara, dị ka na AWS, DigitalOcean: ị pịa otu bọtịnụ - ị nwetara replication, ị pịa nke abụọ - ị debere ndabere, nke atọ - ị tụgharịrị ihe niile azụ awa ole na ole. Ahụghị m bọtịnụ ma ọ bụ ngwá ọrụ GUI mara mma. Ọ bụrụ na ị maara otu (n'efu ma ọ bụ ọnụ ala), dee banyere ya na nkwupụta.

Mgbe m gachara, ahụrụ m ngwá ọrụ abụọ onye pgbarman и pgbackrest. Enweghị m ihe ịga nke ọma na nke mbụ (akwụkwọ ndị na-adịghị ahụkebe, agbalịrị m ịchọpụta ihe niile dị ka akwụkwọ ntuziaka ochie si dị), ma na nke abụọ akwụkwọ ahụ wee bụrụ nke ruru, ma ọ bụghị na-enweghị ntụpọ. Iji mee ka ọrụ ndị a na-arụ ọrụ yiri nke ahụ dị mfe, e dere isiokwu a.

Mgbe ị gụchara isiokwu a, ị ga-amụta otú e si eme nkwado ndabere na mpaghara, chekwaa ha na ihe nkesa dịpụrụ adịpụ (ebe nchekwa na nkwado ndabere) ma weghachite ha ma ọ bụrụ na enweghị data ma ọ bụ nsogbu ndị ọzọ na isi ihe nkesa.

Ọzụzụ

Iji megharịa akwụkwọ ntuziaka, ị ga-achọ VPS abụọ. Nke mbụ ga-abụ nchekwa (ebe nchekwa nke a ga-echekwa ndabere), na nke abụọ, n'ezie, ihe nkesa n'onwe ya na postgres (n'ọnọdụ m, ụdị 11 nke postgres).

A na-eche na na ihe nkesa nwere postgres ị nwere mgbọrọgwụ, onye ọrụ sudo, onye ọrụ postgres na postgres n'onwe ya (a na-emepụta onye ọrụ postgres na-akpaghị aka mgbe ị na-etinye postgresql), na na ebe nchekwa nchekwa enwere mgbọrọgwụ na onye ọrụ sudo (na ntuziaka. a ga-eji aha njirimara pgbackrest) .

Ka ị nwee nsogbu ole na ole mgbe ị na-edegharị ntuziaka ahụ, m na-ede n'akwụkwọ edemede ebe, onye ọrụ na kedu ikike m ji mebie iwu ahụ mgbe ị na-ede ma na-enyocha akụkọ ahụ.

Ịwụnye pgbackrest

Ebe nchekwa (pgbackrest onye ọrụ):

1. Budata ebe nchekwa site na pgbackrest wee bufee ọdịnaya ya na nchekwa / wuo:

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

2. Wụnye ndabere dị mkpa maka mgbakọ:

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

3. Ịgbakọta pgbackrest:

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

4. Detuo faịlụ nwere ike ime na /usr/bin ndekọ:

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

5. Pgbackrest chọrọ perl. Wụnye:

sudo apt-get install perl

6. Mepụta akwụkwọ ndekọ aha maka ndekọ, nye ha ụfọdụ ikike:

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

pgbackrest version

Ihe nkesa Postgres (onye ọrụ sudo ma ọ bụ mgbọrọgwụ):

Usoro nke ịwụnye pgbackrest na sava nwere postgres yiri usoro nrụnye na ebe nchekwa (ee, pgbackrest ga-etinyerịrị na sava abụọ ahụ), mana na paragraf nke 6 iwu nke abụọ na nke ikpeazụ:

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

jiri: dochie

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

Ịtọlite ​​mmekọrịta n'etiti sava site na SSH na-enweghị paswọọdụ

Ka pgbackrest rụọ ọrụ nke ọma, ọ dị mkpa ịhazi mmekọrịta n'etiti ihe nkesa postgres na ebe nchekwa site na iji faịlụ igodo.

Ebe nchekwa (pgbackrest onye ọrụ):

Mepụta otu ụzọ igodo:

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

Ịkpachara anya Anyị na-agba ọsọ iwu ndị a na-enweghị sudo.

Ihe nkesa Postgres (onye ọrụ sudo ma ọ bụ mgbọrọgwụ):

Mepụta otu ụzọ igodo:

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

Ebe nchekwa (onye ọrụ sudo):

Detuo igodo ọha nke sava postgres na nkesa ebe nchekwa:

(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

Na nzọụkwụ a, a ga-ajụ gị maka paswọọdụ maka onye ọrụ mgbọrọgwụ. Ịkwesịrị itinye paswọọdụ nke onye ọrụ mgbọrọgwụ nke ihe nkesa postgres!

Ihe nkesa Postgres (onye ọrụ sudo):

Jiri postgres detuo igodo ọha na sava ahụ:

(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

Na nzọụkwụ a, a ga-ajụ gị maka paswọọdụ maka onye ọrụ mgbọrọgwụ. Ịkwesịrị ịbanye kpọmkwem paswọọdụ nke onye ọrụ mgbọrọgwụ nke ebe nchekwa ahụ!

Anyị na-elele:

Ebe nchekwa (onye ọrụ mgbọrọgwụ, maka ịdị ọcha nke nnwale):

sudo -u pgbackrest ssh postgres@<postgres_server_ip>

Ihe nkesa Postgres (onye ọrụ mgbọrọgwụ, maka ịdị ọcha nke nnwale):

sudo -u postgres ssh pgbackrest@<repository_server_ip>

Anyị na-eme ka anyị nweta ohere na-enweghị nsogbu.

Ịtọlite ​​​​sava postgres

Ihe nkesa Postgres (onye ọrụ sudo ma ọ bụ mgbọrọgwụ):

1. Ka anyị kwe ka ịkụ aka na ihe nkesa postgres site na IP dịpụrụ adịpụ. Iji mee nke a, dezie faịlụ postgresql.conf (nke dị na /etc/postgresql/11/ main folder), na-agbakwunye ahịrị na ya:

listen_addresses = '*'

Ọ bụrụ na ahịrị dị otú ahụ adịlarị, ma ọ bụ degharịa ya ma ọ bụ tọọ uru paramita dị ka '*'.

Na faịlụ pg_hba.conf (dịkwa na folda ahụ /etc/postgresql/11/main) tinye ahịrị ndị a:

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

ebe:

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

2. Ka anyị mee ntọala ndị dị mkpa na postgresql.conf (ọ dị na folda /etc/postgresql/11/main) maka pgbackrest ịrụ ọrụ:

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

3. Ka anyị mee ntọala ndị dị mkpa na faịlụ nhazi pgbackrest (/etc/pgbackrest/pgbackrest.conf):

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

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

4. Bugharịa postgresql:

sudo service postgresql restart

Ịtọlite ​​​​sava nchekwa

Ebe nchekwa (onye ọrụ pgbackrest):

Ka anyị mee ntọala ndị dị mkpa na faịlụ nhazi 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

Ịmepụta ebe nchekwa

Ebe nchekwa (onye ọrụ pgbackrest):

Mepụta nchekwa ọhụrụ maka ụyọkọ ahụ isi:

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

nnyocha

Ihe nkesa Postgres (onye ọrụ sudo ma ọ bụ mgbọrọgwụ):

Anyị na-elele ihe nkesa postgres:

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

Ebe nchekwa (onye ọrụ pgbackrest):

Anyị na-elele ihe nkesa ebe nchekwa:

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

Anyị na-ejide n'aka na na mmepụta anyị na-ahụ ahịrị "elele njedebe iwu: emechara nke ọma".

Ike gwụrụ? Ka anyị gaa n'ihu n'akụkụ kacha amasị.

Ime ndabere

Ebe nchekwa (onye ọrụ pgbackrest):

1. Mee nkwado ndabere na mpaghara:

sudo -u pgbackrest pgbackrest --stanza=main backup

2. Gbaa mbọ hụ na emepụtara nkwado ndabere:

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

Pgbackrest ga-emepụta nkwado ndabere zuru oke nke mbụ. Ọ bụrụ na-ịchọrọ, ị nwere ike ịgba ọsọ nkwado ndabere na mpaghara ọzọ wee hụ na sistemụ na-emepụta nkwado ndabere na mpaghara.

Ọ bụrụ na ịchọrọ ịme nkwado ndabere zuru oke ọzọ, tinyezie ọkọlọtọ agbakwunyere:

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

Ọ bụrụ na ịchọrọ mmepụta console zuru ezu, tinyezie:

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

Na-eweghachi ndabere

Ihe nkesa Postgres (onye ọrụ sudo ma ọ bụ mgbọrọgwụ):

1. Kwụsị ụyọkọ na-agba ọsọ:

sudo pg_ctlcluster 11 main stop

2. Iweghachi na ndabere:

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

Ka iweghachite nchekwa data na steeti nkwado ndabere zuru oke, jiri iwu ahụ na-akọwapụtaghị recovery_target:

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

Ihe dị mkpa! Mgbe mgbake gasịrị, ọ nwere ike ime na nchekwa data ahụ na-arapara na ọnọdụ mgbake (a ga-enwe njehie dị ka ERROR: enweghị ike ịme DROP DATABASE na azụmahịa na-agụ naanị). N'ikwu eziokwu, aghọtabeghị m ihe jikọrọ ya na nke a. Ihe ngwọta bụ nke a (ị ga-echere ntakịrị mgbe emechara iwu ahụ):

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

N'ezie, ọ ga-ekwe omume iweghachite nkwado ndabere na mpaghara aha ya. Ebe a bụ naanị m M ga-enye njikọ na nkọwa nke atụmatụ a na akwụkwọ. Ndị mmepe na-enye ndụmọdụ iji nhọrọ a na-akpachara anya ma kọwaa ihe kpatara ya. Enwere m ike ịgbakwunye n'onwe m na m ji ya. Ọ bụrụ n'ezie na ị chọrọ, jide n'aka na mgbe mgbake nchekwa data na-apụ na ọnọdụ mgbake (họrọ pg_is_in_recovery() kwesịrị igosi "f") na, ọ bụrụ na, mee nkwado ndabere zuru oke mgbe mgbake gasịrị.

3. Malite ụyọkọ:

sudo pg_ctlcluster 11 main start

Mgbe iweghachite nkwado ndabere na mpaghara, anyị kwesịrị ime nkwado ndabere nke abụọ:

Ebe nchekwa (onye ọrụ pgbackrest):

sudo pgbackrest --stanza=main backup

Ọ gwụla. N'ikpeazụ, m ga-achọ ichetara gị na ọ dịghị ụzọ m na-agbalị ime ka à ga-asị na m bụ agadi dba na m ga-eji igwe ojii na obere ohere. Ugbu a, mụ onwe m na-amalite ịmụ isiokwu dị iche iche dị ka ndabere, mmegharị, nlekota oru, wdg. na m na-ede obere akụkọ banyere nsonaazụ ka m wee nye obere onyinye na obodo ma hapụ onwe m obere mpempe akwụkwọ aghụghọ.

N'isiokwu ndị na-esonụ, m ga-agbalị ikwu banyere atụmatụ ndị ọzọ - mgbake data na ụyọkọ dị ọcha, izo ya ezo nke ndabere na ibipụta na S3, nkwado ndabere na mpaghara rsync.

isi: www.habr.com

Tinye a comment