Li-backups tse ntseng li eketseha tsa postgresql tse nang le pgbackrest - thupelo ea mohlabani e monyane ho tsoa ho nts'etsopele

Boithorollo

Ke moqapi. Ke ngola khoutu mme ke sebelisana le database feela joalo ka mosebelisi. Ha ho mohla ke iketsang eka ke mookameli oa tsamaiso, haholo-holo dba. Empa…

Ho ile ha etsahala hore ke hloke ho hlophisa bekapo ea database ea postgresql. Ha ho na maru - sebelisa SSH feela 'me u netefatse hore tsohle lia sebetsa ntle le ho kopa chelete. Re etsa eng maemong a joalo? Ho joalo, re sutumelletsa pgdump ho cron, re boloka ntho e 'ngoe le e' ngoe sebakeng sa polokelo letsatsi le leng le le leng, 'me haeba re lahleha ka ho feletseng, re romela boitsebiso bona kae-kae hole.

Lekhetlong lena bothata e ne e le hore ho ea ka merero, database e ne e lokela ho hola ka hoo e ka bang + - 100 MB ka letsatsi. Ehlile, kamora libeke tse 'maloa takatso ea ho boloka tsohle ka pgdump e tla nyamela. Mona ke moo li-backups tse ntseng li eketseha li tla thusa.

E thahasellisa? Rea u amohela ho katse.

Backup e ntseng e eketseha ke mofuta oa bekapo ha e se lifaele tsohle tsa mohloli tse kopitsoang, empa ke tse ncha feela le tse fetotsoeng ho tloha ha ho qaptjoa kopi e fetileng.

Joalo ka moetsi leha e le ofe ea neng a sa ikemisetsa (ka nako eo) ho utloisisa lintho tse rarahaneng tsa postgres, ke ne ke batla ho fumana konopo e tala. Ua tseba, joalo ka ho AWS, DigitalOcean: u tobetse konopo e le 'ngoe - u fumane phetisetso, u tobetse ea bobeli - u theha li-backups, ea boraro - u khutlisitse ntho e ngoe le e ngoe morao lihora tse 'maloa. Ha kea fumana konopo kapa sesebelisoa se setle sa GUI. Haeba u tseba e le 'ngoe (mahala kapa e theko e tlaase), ngola ka eona ho litlhaloso.

Ka mor'a googling ke ile ka fumana lisebelisoa tse peli pgbarman и pgbackrest. Ha kea ka ka atleha ho ea pele (litokomane tse fokolang haholo, ke lekile ho bala ntho e 'ngoe le e' ngoe ho ea ka libuka tsa khale), empa ka ea bobeli litokomane li ile tsa bonahala li lekana, empa li se na liphoso. Ho nolofatsa mosebetsi oa ba tobaneng le mosebetsi o tšoanang, sehlooho sena se ngotsoe.

Ka mor'a ho bala sehlooho sena, u tla ithuta ho etsa li-backups tse ntseng li eketseha, ho li boloka ho seva se hōle (sebaka sa polokelo e nang le li-backups) 'me u li khutlisetse tabeng ea tahlehelo ea data kapa mathata a mang ho seva se seholo.

Ho lokisetsa

Ho hlahisa bukana hape o tla hloka VPS tse peli. Ea pele e tla ba polokelo (sebaka seo li-backups li tla bolokoa ho sona), 'me ea bobeli, ha e le hantle, seva ka boeona e na le li-postgres (tabeng ea ka, phetolelo ea 11 ea postgres).

Ho nahanoa hore ho seva e nang le li-postgres u na le motso, mosebelisi oa sudo, mosebelisi oa postgres le li-postgres ka boeona li kentsoe (mosebelisi oa postgres o entsoe ka bohona ha o kenya postgresql), mme ho seva sa polokelo ho na le motso le mosebelisi oa sudo (ka bukeng lebitso la mosebelisi pgbackrest le tla sebelisoa) .

E le hore u be le mathata a fokolang ha u hlahisa litaelo, ke ngola ka mongolo o tšekaletseng kae, ka mosebedisi ofe le ka litokelo life ke phethile taelo ha u ntse u ngola le ho hlahloba sehlooho.

Ho kenya pgbackrest

Sebaka sa polokelo (pgbackrest ea mosebelisi):

1. Khoasolla polokelo ea litaba ho tsoa ho pgbackrest ebe u fetisetsa litaba tsa eona ho foldareng ea /build:

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

2. Kenya litšepeho tse hlokahalang bakeng sa kopano:

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

3. Ho kopanya pgbackrest:

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

4. Kopitsa faele e sebetsang ho /usr/bin directory:

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

5. Pgbackrest e hloka perl. Kenya:

sudo apt-get install perl

6. Etsa li-directory bakeng sa li-log, u li fe litokelo tse itseng:

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

pgbackrest version

Seva ea Postgres (mosebelisi oa sudo kapa motso):

Ts'ebetso ea ho kenya pgbackrest ho seva e nang le li-postgres e tšoana le ts'ebetso ea ho kenya polokelong (e, pgbackrest e tlameha ho kenngoa ho li-server ka bobeli), empa serapeng sa 6 taelo ea bobeli le ea ho qetela:

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

nkela sebaka ka:

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

Ho theha tšebelisano lipakeng tsa li-server ka SSH e se nang password

E le hore pgbackrest e sebetse ka nepo, hoa hlokahala ho lokisa tšebelisano pakeng tsa seva sa postgres le polokelo ho sebelisa faele ea bohlokoa.

Sebaka sa polokelo (pgbackrest ea mosebelisi):

Theha sehlopha sa bohlokoa:

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

Ela hloko ka kopo! Re tsamaisa litaelo tse ka holimo ntle le sudo.

Seva ea Postgres (mosebelisi oa sudo kapa motso):

Theha sehlopha sa bohlokoa:

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

Sebaka sa polokelo (mosebelisi oa sudo):

Kopitsa senotlolo sa sechaba sa seva sa postgres ho seva sa polokelo:

(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

Mohato ona o tla botsoa password bakeng sa mosebelisi oa motso. U hloka ho kenya phasewete ea motso oa mosebelisi oa seva sa postgres!

Seva ea Postgres (mosebelisi oa sudo):

Kopitsa senotlolo sa sechaba sa polokelo ho seva ka li-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

Mohato ona o tla botsoa password bakeng sa mosebelisi oa motso. U hloka ho kenya phasewete hantle ea motso oa mosebelisi oa polokelo!

Re hlahloba:

Sebaka sa polokelo (mosebetsi oa motso, bakeng sa bohloeki ba teko):

sudo -u pgbackrest ssh postgres@<postgres_server_ip>

Seva ea Postgres (mosebetsi oa motso, bakeng sa bohloeki ba teko):

sudo -u postgres ssh pgbackrest@<repository_server_ip>

Re etsa bonnete ba hore re fumana phihlello ntle le mathata.

Ho theha seva sa postgres

Seva ea Postgres (mosebelisi oa sudo kapa motso):

1. A re lumelle ho kokota ho seva sa postgres ho tloha ka ntle ho IPs. Ho etsa sena, fetola faele postgresql.conf (e fumaneha ho /etc/postgresql/11/main foldareng), e eketsa mola ho eona:

listen_addresses = '*'

Haeba mola o joalo o se o ntse o le teng, o ka o hlakola kapa o behe boleng ba parametha joalo ka '*'.

Ka faele pg_hba.conf (hape e fumaneha foldareng /etc/postgresql/11/main) eketsa mela e latelang:

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

moo:

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

2. Ha re etseng litlhophiso tse hlokahalang ho postgresql.conf (e ka har'a foldara /etc/postgresql/11/main) hore pgbackrest e sebetse:

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

3. Ha re etseng litlhophiso tse hlokahalang faeleng ea tlhophiso ea pgbackrest (/etc/pgbackrest/pgbackrest.conf):

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

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

4. Kenya posogresql hape:

sudo service postgresql restart

Ho theha seva sa polokelo

Sebaka sa polokelo (pgbackrest user):

Ha re etseng litlhophiso tse hlokahalang faeleng ea tlhophiso 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

Ho theha polokelo

Sebaka sa polokelo (pgbackrest user):

Theha polokelo e ncha bakeng sa sehlopha ka sehloohong:

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

hlahlobeloang

Seva ea Postgres (mosebelisi oa sudo kapa motso):

Re sheba seva sa postgres:

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

Sebaka sa polokelo (pgbackrest user):

Re sheba seva sa polokelo:

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

Re etsa bonnete ba hore ka tlhahiso re bona mola "check command end: phethoa ka katleho".

O khathetse? Ha re feteleng pele ho karolo e khahlisang haholo.

Ho etsa backup

Sebaka sa polokelo (pgbackrest user):

1. Etsa bekapo:

sudo -u pgbackrest pgbackrest --stanza=main backup

2. Etsa bonnete ba hore bekapo e entsoe:

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

Pgbackrest e tla theha bekapo ea pele e felletseng. Haeba u lakatsa, u ka tsamaisa taelo ea "backup" hape 'me u netefatse hore sistimi e theha backup e ntseng e eketseha.

Haeba u batla ho etsa backup e felletseng hape, hlakisa folakha e eketsehileng:

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

Haeba u batla tlhahiso e qaqileng ea console, joale bolela hape:

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

Ho khutlisetsa backup

Seva ea Postgres (mosebelisi oa sudo kapa motso):

1. Emisa sehlopha se mathang:

sudo pg_ctlcluster 11 main stop

2. Ho khutlisa ho tsoa ho bekapo:

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

Ho khutlisetsa database boemong ba bekapo ea ho qetela e FULL, sebelisa taelo ntle le ho hlakisa recovery_target:

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

Bohlokoa! Ka mor'a ho hlaphoheloa, ho ka 'na ha etsahala hore database e khomarele mokhoa oa ho hlaphoheloa (ho tla ba le liphoso tse kang ERROR: e ke ke ea phethahatsa DROP DATABASE ka mokhoa oa ho bala feela). Ho bua 'nete, ha ke e-s'o utloisise hore na sena se amana le eng. Tharollo ke e latelang (o tla hloka ho ema hanyane kamora hore taelo e phethoe):

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

Ha e le hantle, hoa khoneha ho tsosolosa bekapo e itseng ka lebitso la eona. Ke mona feela Ke tla fana ka sehokelo ho tlhaloso ea tšobotsi ena litokomaneng. Bahlahisi ba eletsa ho sebelisa khetho ena ka hloko le ho hlalosa lebaka. Nka eketsa ho tsoa ho 'na hore ke e sebelisitse. Haeba u hlile u hloka ho etsa joalo, etsa bonnete ba hore ka mor'a hore u hlaphoheloe, database e tsoa ka mokhoa oa ho hlaphoheloa (khetha pg_is_in_recovery () e lokela ho bontša "f") 'me, haeba ho ka etsahala, etsa bekapo e feletseng ka mor'a ho hlaphoheloa.

3. Qala sehlopha:

sudo pg_ctlcluster 11 main start

Kamora ho khutlisa backup, re hloka ho etsa backup ea bobeli:

Sebaka sa polokelo (pgbackrest user):

sudo pgbackrest --stanza=main backup

Ke phetho. Qetellong, ke rata ho le hopotsa hore ho hang ha ke leke ho iketsa dba e kholo mme ke tla sebelisa maru ka monyetla o monyenyane. Hajoale, le 'na ke qala ho ithuta lihlooho tse fapaneng tse kang bekapo, ho pheta-pheta, ho beha leihlo, jj. 'me ke ngola litlaleho tse nyenyane mabapi le liphello e le hore ke etse monehelo o monyenyane ho sechaba le ho siea lipampiri tse nyenyane tsa bolotsana bakeng sa ka.

Lihloohong tse latelang ke tla leka ho bua ka likarolo tse ling - ho hlaphoheloa ha data ho sehlopha se hloekileng, ho kenyelletsa li-backups le ho phatlalatsa ho S3, li-backups ka rsync.

Source: www.habr.com