ProHoster > Π‘Π»ΠΎΠ³ > administratio > Incremental postgresql tergum cum pgbackrest - cursum iuveni pugnantis e elit

Incremental postgresql tergum cum pgbackrest - cursum iuveni pugnantis e elit

Disclaimer

Curabitur a elit. Codicem scribo et cum datorum penitus tantum ut user. Nullo modo dissimulo administratorem esse, nedum dba. Sed…

Forte factum est ut necesse esset me tergum datorum postgresql instituere. Nulla nubes - modo uti SSH et omnia opera fac sine pecunia quaerenda. Quid in talibus facimus? Iustum est, pgdump in cron impellimus, omnia ad archivum cotidie remitto, et si penitus perimus, hoc archivum longe alicubi mittimus.

Hoc tempore difficultas erat quod secundum consilia datorum +-100 MB per diem crescere existimabatur. Utique post aliquot septimanas desiderium ad tergum omnia cum pgdump peribit. Hoc est ubi incremental tergum succurrit.

Novus? Welcome to cat.

Tergum incrementale genus est tergum cum non omnes fontium imaginum transcribuntur, sed solum novae et illae mutatae sunt ex creatione exemplaris prioris.

Velut quis elit, qui absolute nolebat (tunc) intelligere subtilitates postgress, volui invenire bullam viridis. Bene scis, sicut in AWS, DigitalOcean: pressisti unam conjunctionem - replicationem sumpsisti, secundam premesti - tergum posuisti, tertiam - omnia revolvisti horis duobus. Bullam aut pulchrum GUI instrumentum non inveni. Si scias unum (liberum vel vile), scribe in comment.

Post Google inveni duo instrumenta pgbarman ΠΈ pgbackrest. Simpliciter non impetravi cum primis (discipulis valde raris, omnia iuxta veteres manuales figurare conabar), sed cum altera documenta in par evasit, sed non sine mendis. Articulus hic scriptus est ad simpliciorem opus eorum qui in simili negotio versantur.

Post hunc articulum perlectis, tergum incrementalem facere disces, salva servo remoto (repositorium cum tergum) et restitue in casu damni vel in aliis quaestionibus principali servo.

Training

Ad manualem reproducendum duobus VPS opus erit. Prima erit repositio (repositio in qua tergum reponentur), secunda, immo ipsa serviens cum postgres (me in casu, versio 11 postgres).

Ponitur in servo cum postgres habere radicem, sudo user, postgres usor et postgres ipsum institutum (usor postgres sponte creatur cum postgresql inserto), et in servo reposito radix et sudo usor (in manuali nomen usoris pgbackrest adhibebitur).

Ut difficultates habeas pauciores cum mandata reproducas, cursivis scribo ubi, quo usore et quo iure mandatum feci in scribendo et inhibendo articulum.

Installing pgbackrest

Repositorium (user pgbackrest);

1. tabularium ex pgbackrest Download et transferre contenta in / folder aedificate:

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

2. Instrue clientelas necessarias ad ecclesiam;

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

pgbackrest 3. convenientes;

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

4. Effingo documentum exsecutabile in /usr/bin directorium:

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

5. Pgbackrest requirit perl. Inaugurare:

sudo apt-get install perl

6. Directoria pro lignis crea, certa iura eis da;

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

pgbackrest version

Postgres server (sudo user vel radix);

Processus institutionis pgbackrest in servo cum postgres similis est processus institutionis in repositorio (sic, pgbackrest institui debet in utroque servientibus); sed in 6 paragrapho secundo et ultimo imperat;

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

cum reponere;

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

Profecta est commercium servers per passwordless SSH

Ut pgbackrest ad recte operandum, necesse est configurare commercium inter servitores postgressorum et repositorium utendi clavi tabellae.

Repositorium (user pgbackrest);

Creare par clavis:

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

Cautus esto Praeceptos ille sine sudo currimus.

Postgres server (sudo user vel radix);

Creare par clavis:

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

Repositorium (sudo user):

Effingo publicam clavem postgres servo repositorio servo:

(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

In hoc gradu tesseram radicis usoris rogaberis. Opus tesseram radicis usoris postgres servientis ingredi debes!

Postgres server (sudo user);

Effingo repositio clavem publicam servo cum 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

In hoc gradu tesseram radicis usoris rogaberis. Opus prorsus tesseram radicis usoris repositorii inire!

reprehendo:

Repositorium (usoris radicis; puritas experimenti);

sudo -u pgbackrest ssh postgres@<postgres_server_ip>

Postgres server (radix usor, ad experimentum puritatis);

sudo -u postgres ssh pgbackrest@<repository_server_ip>

Facimus nos sine problematibus accessum consequi.

Profecta servo postgres

Postgres server (sudo user vel radix);

1. Liceat pulsare tabellarios ab IPS externis. Ad hoc faciendum tabellam edit postgresql.conf (sita in /etc/postgresql/11/main folder), addita linea ad eam:

listen_addresses = '*'

Si linea talis iam existit, vel inconveniens est vel valorem parametri "*'.

In file pg_hba.conf (Et sita in folder /etc/postgresql/11/main) sequentes lineas adde:

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

ubi:

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

2. faciamus necesse occasus in postgresql.conf (Id est in folder /etc/postgresql/11/main) pgbackrest ad opus:

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

3. Necessarias obitus faciamus in lima configurationis pgbackrest (/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

Profecti sunt servo repositio

Repositorium (pgbackrest user);

Oportet occasus faciamus in configuration file 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

Creando repositio

Repositorium (pgbackrest user);

Novam repono pro botro summa:

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

inspectis

Postgres server (sudo user vel radix);

Nos reprehendo in servo postgres:

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

Repositorium (pgbackrest user);

Reprehendo in reposito servo:

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

Facimus ut in output lineam videmus "reprehendo imperium finis: feliciter consummatum".

Taedet? Ad partem maxime interesting transeamus.

Faciens tergum

Repositorium (pgbackrest user);

1. Praestare tergum:

sudo -u pgbackrest pgbackrest --stanza=main backup

2. Fac ut tergum creatus sit;

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

Pgbackrest primum plenum creabit tergum. Si vis, imperium tergum iterum currere potes et fac ut ratio incrementi tergum creat.

Si vis plenum tergum iterum facere, tum vexillum additum denota:

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

Si vis singula consolari output, tunc etiam specificare:

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

Restituendi tergum

Postgres server (sudo user vel radix);

1. Siste botrum portatum;

sudo pg_ctlcluster 11 main stop

2. tergum ex restituendo:

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

Ut datorum ad statum ultimi FULL tergum restituat, imperio utere non specificata recuperatione_target:

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

Maximus! Post recuperationem, fieri potest ut modus in recuperatione datorum adhaesit (errores erunt sicut ERROR: RORO DATABASE fungi non possunt in sola transactione lectione). Honestum esse, nondum intellexi quid hoc sit. Solutio talis est (exspectare te oportebit paulo post praeceptum supplicium);

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

Reapse proprio nomine tergum restituere potest. Hic ego solus Vinculum descriptioni huius plumae in documentis dabo. Tincidunt hac optione caute utentes moneo et cur exponas. A me addere possum quod usus sum. Si vere postulo, fac ut post recuperationem datorum exitus recuperationis modum (selectis pg_is_in_recovery() monstret "f") et, sicut in casu, plenum tergum post convaluisset fac.

3. Incipit botrus;

sudo pg_ctlcluster 11 main start

Post tergum restitutum, alterum tergum praestare debemus;

Repositorium (pgbackrest user);

sudo pgbackrest --stanza=main backup

Id omne. Demum te admonere velim, me nullo modo dba senium esse simulare conari, ac levissima occasione nubibus utar. In praesenti, ipse incipio variis argumentis studere ut tergum, replicationem, vigilantiam, etc. et parvas relationes de eventibus scribo ut parvam adiumentum communitati reddam et parvas schedas fraudatas mihi relinques.

In sequentibus articulis de notis additis loqui conabor - data recuperatione ad botrum mundum, encryptionem tergum et evulgationem ad S3, tergum per rsync.

Source: www.habr.com