Fa'ailoga
O a'u o se tagata atia'e. Ou te tusia code ma fegalegaleai ma le database na o se tagata faʻaoga. E leai se auala ou te faafoliga o se pule o le polokalama, e itiiti ifo i le dba. Ae…
Na tupu na ou manaʻomia le faʻatulagaina o se faʻamaumauga o le postgresql database. Leai ni ao - na'o le fa'aoga SSH ma ia mautinoa e lelei mea uma e aunoa ma le fesili mo se tupe. O le a le mea tatou te faia i tulaga faapena? E saʻo, matou te tuleia le pgdump i totonu o le cron, faʻasaʻo mea uma i le archive i aso uma, ma afai matou te leiloa atoa, matou te lafoina lenei archive i se mea mamao.
O le taimi lenei o le faigata ona e tusa ai ma fuafuaga, o le database e tatau ona tuputupu aʻe e tusa ma le +- 100 MB i le aso. Ioe, a maeʻa ni nai vaiaso o le manaʻo e faʻasaʻo mea uma i le pgdump o le a mou atu. O i'ina e sau ai fa'aola fa'aopoopo e laveai.
Fa'aopoopo fa'aopoopo o se ituaiga o fa'amaumauga pe a le kopi uma faila fa'apogai, ae na'o faila fou ma na suia talu mai le faia o le kopi muamua.
E pei o soʻo se atinaʻe na matua le manaʻo (i lena taimi) e malamalama i faʻalavelave o postgres, na ou manaʻo e suʻe le lanu meamata. Ia, e te iloa, pei o le AWS, DigitalOcean: na e oomiina le tasi ki - na e toe faia, na e oomi le lona lua - na e setiina faʻamaumauga, lona tolu - na e toe faʻafoʻi mea uma i ni nai itula. Ou te lei mauaina se faamau po o se meafaigaluega GUI matagofie. Afai e te iloa se tasi (saoloto pe taugofie), tusi e uiga i ai i faʻamatalaga.
Ina ua uma le googling sa ou mauaina ni meafaigaluega se lua pgbarman и pgbackrest. Naʻo loʻu le manuia i le mea muamua (faʻapitoa tele faʻamaumauga, na ou taumafai e faʻavasega mea uma e tusa ai ma tusi lesona tuai), ae faʻatasi ai ma le lona lua o faʻamaumauga na foliga mai e fetaui, ae leai ni faaletonu. Ina ia faafaigofie le galuega a i latou o loo feagai ma se galuega faapena, na tusia ai lenei mataupu.
A maeʻa ona e faitau i lenei tusiga, o le a e aʻoaʻoina pe faʻafefea ona fai faʻamaumauga faʻaopoopo, faʻasaoina i se server mamao (fale teu oloa ma faʻamaumauga) ma toe faʻafoʻi i le tulaga o le leiloa o faʻamatalaga poʻo isi faʻafitauli i luga o le server autu.
Sauniuniga
Ina ia toe gaosia le tusi lesona e te manaʻomia lua VPS. O le mea muamua o le teuina (le fale teu oloa o le a teuina ai faʻamaumauga), ma le lona lua, o le mea moni, o le server lava ia ma postgres (i loʻu tulaga, version 11 of postgres).
E faʻapea o luga o le 'auʻaunaga ma postgres e iai sau aʻa, sudo user, postgres user ma postgres lava ia faʻapipiʻiina (o le postgres user e faʻapipiʻiina otometi pe a faʻapipiʻi le postgresql), ma i luga o le fale teu oloa o loʻo i ai le aʻa ma le sudo user (i le tusi lesona. o le a fa'aaogaina le igoa fa'aoga pgbackrest).
Ina ia faʻaitiitia au faʻafitauli pe a toe faia faʻatonuga, ou te tusia i le faatusilima o fea, o le a le tagata faʻaoga ma o a aia tatau na ou faʻatinoina ai le poloaiga a o tusitusi ma siaki le tala.
Fa'apipi'i le pgbackrest
Faleteu (user pgbackrest):
1. Si'i mai le fa'amaumauga mai le pgbackrest ma fa'afeiloa'i mea i totonu i le /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. Fa'apipi'i mea fa'alagolago e mana'omia mo le fa'apotopotoga:
sudo apt-get update
sudo apt-get install build-essential libssl-dev libxml2-dev libperl-dev zlib1g-dev
libpq-dev
3. Fa'aopoopoina pgbackrest:
cd /build/pgbackrest-release-2.18/src && sudo ./configure
sudo make -s -C /build/pgbackrest-release-2.18/src
4. Kopi le faila faila i le /usr/bin directory:
sudo cp /build/pgbackrest-release-2.18/src/pgbackrest /usr/bin
sudo chmod 755 /usr/bin/pgbackrest
5. Pgbackrest mana'omia perl. Fa'apipi'i:
sudo apt-get install perl
6. Fausia lisi mo ogalaau, tuu atu ia i latou ni aia tatau:
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. Siaki:
pgbackrest version
Postgres server (sudo user poʻo aʻa):
O le faagasologa o le faʻapipiʻiina o le pgbackrest i luga o se 'auʻaunaga ma postgres e tutusa ma le faʻapipiʻiina o le faʻapipiʻi i luga o le fale teu oloa (ioe, pgbackrest e tatau ona faʻapipiʻi i luga o sapalai uma e lua), ae i le palakalafa lona 6 o le poloaiga lona lua ma le mulimuli:
sudo chown pgbackrest:pgbackrest /var/log/pgbackrest
sudo chown pgbackrest:pgbackrest /etc/pgbackrest/pgbackrest.conf
sui i le:
sudo chown postgres:postgres /var/log/pgbackrest
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
Fa'atulaga fegalegaleaiga i le va o 'au'aunaga e ala i le SSH e leai ni fa'amatalaga
Ina ia mafai ona galue saʻo le pgbackrest, e manaʻomia le faʻatulagaina o fegalegaleaiga i le va o le postgres server ma le fale teu oloa e faʻaaoga ai le faila autu.
Faleteu (user pgbackrest):
Fausia se paga autu:
mkdir -m 750 /home/pgbackrest/.ssh
ssh-keygen -f /home/pgbackrest/.ssh/id_rsa
-t rsa -b 4096 -N ""
Faʻamolemole faʻamolemole! Matou te faʻatautaia tulafono o loʻo i luga e aunoa ma sudo.
Postgres server (sudo user poʻo aʻa):
Fausia se paga autu:
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 ""
Faleteu (sudo fa'aoga):
Kopi le ki fa'alaua'itele o le 'au'aunaga postgres i le 'au'aunaga teu oloa:
(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
I lenei laasaga o le a fesiligia oe mo le upu faʻamaonia mo le tagata faʻaoga aʻa. E te manaʻomia le faʻaogaina o le upega tafaʻilagi a le tagata faʻaoga aʻa o le postgres server!
Postgres server (sudo user):
Kopi le ki faʻasalalau lautele i le 'auʻaunaga ma 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
I lenei laasaga o le a fesiligia oe mo le upu faʻamaonia mo le tagata faʻaoga aʻa. E tatau ona e ulufale tonu i le upu faataga a le tagata faʻaoga aʻa o le fale teu oloa!
Matou siaki:
Repository (tagata faʻaoga aʻa, mo le mama o le suʻega):
sudo -u pgbackrest ssh postgres@<postgres_server_ip>
Postgres server (tagata faʻaoga aʻa, mo le mama o le suʻega):
sudo -u postgres ssh pgbackrest@<repository_server_ip>
Matou te mautinoa matou te maua avanoa e aunoa ma ni faʻafitauli.
Fa'atuina se 'au'aunaga postgres
Postgres server (sudo user poʻo aʻa):
1. Sei o tatou faʻatagaina tuʻituʻi i luga o le postgres server mai IP fafo. Ina ia faia lenei mea, faʻasaʻo le faila postgresql.conf (o loʻo i totonu o le /etc/postgresql/11/main folder), faʻaopoopo le laina i ai:
listen_addresses = '*'
Afai ua i ai se laina fa'apea, ia fa'alilolilo pe seti le tau o le '*'.
I le faila pg_hba.conf (o loʻo iai foʻi i totonu o le faila /etc/postgresql/11/main) fa'aopoopo laina nei:
hostssl all all 0.0.0.0/0 md5
host all all 0.0.0.0/0 md5
pe afai:
hostssl/host - подключаемся через SSL (или нет)
all - разрешаем подключение ко всем базам
all - имя пользователя, которому разрешаем подключение (всем)
0.0.0.0/0 - маска сети с которой можно подключаться
md5 - способ шифрования пароля
2. Se'i o tatou faia tulaga talafeagai i totonu postgresql.conf (o loʻo i totonu o le faila /etc/postgresql/11/main) mo le pgbackrest e galue:
archive_command = 'pgbackrest --stanza=main archive-push %p' # Где main - название кластера. При установке postgres автоматически создает кластер main.
archive_mode = on
max_wal_senders = 3
wal_level = replica
3. Sei o tatou faia tulaga talafeagai i le pgbackrest file configuration (/etc/pgbackrest/pgbackrest.conf):
[main]
pg1-path=/var/lib/postgresql/11/main
[global]
log-level-file=detail
repo1-host=<repository_server_ip>
4. Toe uta postgresql:
sudo service postgresql restart
Fa'atulaga se 'au'aunaga teu oloa
Faleteu (pgbackrest tagata fa'aoga):
Sei o tatou faia tulaga talafeagai i le faila faatulagaina 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
Fausia se fale teu oloa
Faleteu (pgbackrest tagata fa'aoga):
Fausia se teuina fou mo le fuifui tele:
sudo mkdir -m 770 /var/lib/pgbackrest
sudo chown -R pgbackrest /var/lib/pgbackrest/
sudo -u pgbackrest pgbackrest --stanza=main stanza-create
asiasiga
Postgres server (sudo user poʻo aʻa):
Matou te siakiina le postgres server:
sudo -u postgres pgbackrest --stanza=main --log-level-console=info check
Faleteu (pgbackrest tagata fa'aoga):
Matou te siakiina le server repository:
sudo -u pgbackrest pgbackrest --stanza=main --log-level-console=info check
Matou te faʻamautinoa i totonu o le gaioiga matou te vaʻai i le laina "siaki le faʻaiʻuga o le poloaiga: maeʻa manuia".
Lelavā? Sei o tatou agai atu i le vaega e sili ona manaia.
Faia o se faaleoleo
Faleteu (pgbackrest tagata fa'aoga):
1. Fai se faaleoleo:
sudo -u pgbackrest pgbackrest --stanza=main backup
2. Ia mautinoa ua faia se faaleoleo:
ls /var/lib/pgbackrest/backup/main/
O le a faia e le Pgbackrest le fa'amaumauga atoa muamua. Afai e te manaʻo ai, e mafai ona e toe faʻatautaia le faʻatonuga faʻamaumauga ma ia mautinoa o le faiga e fatuina ai se faʻaopoopoga faʻaopoopo.
Afai e te manaʻo e toe fai se sao atoatoa, ona faʻamaonia lea o se fuʻa faaopoopo:
sudo -u pgbackrest pgbackrest --stanza=main --type=full backup
Afai e te manaʻo i faʻamatalaga faʻamafanafanaga, faʻapea foi:
sudo -u pgbackrest pgbackrest --stanza=main --type=full --log-level-console=info backup
Toe fa'afo'isia se fa'amaumauga
Postgres server (sudo user poʻo aʻa):
1. Taofi le fuifui tamo'e:
sudo pg_ctlcluster 11 main stop
2. Toe fa'afo'isia mai fa'amaumauga:
sudo -u postgres pgbackrest --stanza=main --log-level-console=info --delta --recovery-option=recovery_target=immediate restore
Чтобы восстановить базу в состояние последнего ПОЛНОГО бэкапа используйте команду без указания recovery_target:
sudo -u postgres pgbackrest --stanza=main --log-level-console=info --delta restore
Важно! После восстановления может оказаться так, что база зависнет в режиме восстановления (будут ошибки в духе ERROR: cannot execute DROP DATABASE in a read-only transaction). Честно говоря, я еще не понял, с чем это связано. Решается следующим образом (нужно будет малость подождать после исполнения команды):
sudo -u postgres psql -c "select pg_wal_replay_resume()"
На самом деле, есть возможность восстановить конкретный бэкап по его имени. Здесь я лишь
3. Amata le fuifui:
sudo pg_ctlcluster 11 main start
A maeʻa ona toe faʻaleleia le faʻamaumauga, matou te manaʻomia le faia o se faʻamaumauga lona lua:
Faleteu (pgbackrest tagata fa'aoga):
sudo pgbackrest --stanza=main backup
Pau lava lena. I le faaiuga, ou te fia faamanatu atu ia te oe ou te le o taumafai lava e faafoliga o se dba sinia ma o le a ou faaaogaina le ao i sina avanoa itiiti. I le taimi nei, o aʻu lava ia ua amata ona suʻesuʻeina mataupu eseese e pei o le faʻamaumauga, toe faia, mataʻituina, ma isi. ma ou te tusia ni lipoti laiti e uiga i taunuuga ina ia mafai ai ona faia sina sao i le nuu ma tuʻu ai ni nai pepa faʻatau mo aʻu.
I tala o loʻo mulimuli mai, o le a ou taumafai e talanoa e uiga i faʻaopoopoga faʻapitoa - toe faʻaleleia o faʻamaumauga i se fuifui mama, faʻailoga o faʻamaumauga ma faʻasalalau i le S3, faʻamaumauga e ala i le rsync.
puna: www.habr.com