Otu esi ahazi mmegharị mgbasa ozi PostgreSQL 12 na CentOS 8


Ebe nchekwa data PostgreSQL na-akwado ọtụtụ azịza mmegharị iji wuo nnweta dị elu, scalable, ngwa na-anabata mmejọ, otu n'ime ha bụ Mbupu Write-Ahead Log (WAL). Ihe ngwọta a na-enye ohere ka emejuputa ihe nkesa na-echere site na iji mbupu faịlụ dabeere na faịlụ ma ọ bụ gụgharia, ma ọ bụ ebe enwere ike, nchikota nke ụzọ abụọ ahụ.

Site na mmegharị nkwanyegharị, a na-ahazi ihe nkesa nchekwa data nọrọ na njikere (ohu mmegharị) iji jikọọ na nna ukwu/isi nkesa, nke na-ebufe ndekọ WAL na njikere ka emepụtara ha, na-echeghị ka faịlụ WAL mejupụta.

Site na ndabara, nkwanyegharị nkwanyegharị na-adakọ ọnụ ebe edere data na sava nọrọ njikere ka emechara azụmahịa na sava mbụ. Nke a pụtara na enwere ntakịrị igbu oge n'etiti ime azụmahịa na ihe nkesa nna ukwu na mgbanwe ndị a na-ahụ anya na ihe nkesa njikere. Otu n'ime ụzọ a dị ala bụ na ọ bụrụ na ihe nkesa nna ukwu daa, azụmahịa ọ bụla a na-emeghị eme nwere ike ọ gaghị emeghachi ya na nke a nwere ike ime ka mfu data.

Ntuziaka a na-egosi otu esi ahazi Postgresql 12 master-standby streaming replication na CentOS 8. Anyị ga-eji \replication slots maka njikere dị ka ihe ngwọta iji zere ihe nkesa nna ukwu ka ọ ghara ịmegharị akụkụ WAL ochie tupu njikere nata ha.

Rịba ama na atụnyere usoro ndị ọzọ, oghere mmegharị na-ejigide naanị ọnụọgụ akụkụ nke amaara na achọrọ.

Ntuziaka a na-eche na ị jikọọ na nna gị ukwu na nchekwa data nchekwa dị ka mgbọrọgwụ site na SSH (jiri iwu Sudo ebe ọ dị mkpa ma ọ bụrụ na ejikọrọ gị dị ka onye ọrụ nkịtị nwere ikike nhazi):

Postgresql master database server: 		10.20.20.9
Postgresql standby database server:		10.20.20.8

Sava nchekwa data abụọ ga-enwerịrị Postgresql 12 arụnyere, ma ọ bụghị ya, hụ: Otu esi etinye PostgreSQL na pgAdmin na CentOS 8.

Rịba ama: PostgreSQL 12 na-abịa na mgbanwe dị ukwuu na mmejuputa mmeghari na nhazi dị ka ngbanwe nke recovery.conf na ntughari nke mgbake.conf na nhazi nhazi PostgreSQL nkịtị, na-eme ka ọ dịkwuo mfe ịhazi nchịkọta nchịkọta.

Kwụpụ 1: Na-ahazi PostgreSQL Master/Primary Database Server

1. Na nna ukwu nkesa, ịgbanwee na postgres usoro akaụntụ na hazie adreesị IP (es) nke nna ukwu nkesa ga-ege ntị maka njikọ si ahịa.

N'okwu a, anyị ga-eji * pụtara ihe niile.

# su - postgres
$ psql -c "ALTER SYSTEM SET listen_addresses TO '*';"

ALTER SYSTEM SET SQL iwu bụ ihe dị ike iji gbanwee paramita nhazi ihe nkesa, na ajụjụ SQL ozugbo. A na-echekwa nhazi ahụ na faịlụ postgresql.conf.auto dị na mgbọrọgwụ nke nchekwa data (dịka /var/lib/pgsql/12/data/) wee gụọ mgbakwunye na ndị echekwara na postgresql.conf. Mana nhazi na nke mbụ na-ebute ụzọ karịa ndị na-esote na faịlụ ndị ọzọ metụtara ya.

2. Mgbe ahụ mepụta ọrụ mmegharị nke a ga-eji maka njikọ sitere na ihe nkesa na-echere na ihe nkesa nna ukwu, na-eji mmemme ihe okike. N'iwu na-esonụ, ọkọlọtọ -P na-akpali maka paswọọdụ maka ọrụ ọhụrụ yana -e na-ekwughachi iwu ndị na-emepụta ihe na-ewepụta ma na-eziga na sava nchekwa data.

# su – postgres
$ createuser --replication -P -e replicator
$ exit

3. Mgbe ahụ, tinye ntinye na-esonụ na njedebe nke /var/lib/pgsql/12/data/pg_hba.conf ahịa njirimara nhazi faịlụ na ebe nchekwa data setịpụrụ na-emegharị dị ka egosiri na nseta ihuenyo.

host    replication     replicator      10.20.20.8/24     md5

4. Ugbu a malitegharịa ọrụ Postgres12 site na iji usoro systemctl na-esonụ iji tinye mgbanwe.

# systemctl restart postgresql-12.service

5. Ọzọ, ọ bụrụ na ị nwere ọrụ firewalld na-agba ọsọ, ịkwesịrị ịgbakwunye ọrụ Postgresql na nhazi firewalld iji kwe ka arịrịọ sitere na ihe nkesa njikere na nna ukwu.

# firewall-cmd --add-service=postgresql --permanent
# firewall-cmd --reload

Kwụpụ 2: Ime ndabere ndabere ka Bootstrap nke sava nọrọ njikere

6. Ọzọ, ịkwesịrị ịme ndabere ndabere nke ihe nkesa ukwu site na ihe nkesa njikere; nke a na-enyere aka bootstrap ihe nkesa nọrọ na njikere. Ịkwesịrị ịkwụsị ọrụ postgresql 12 na ihe nkesa nọrọ na njikere, gbanwee gaa na akaụntụ onye ọrụ postgres, ndabere ndekọ data (/var/lib/pgsql/12/data/), wee hichapụ ihe niile dị n'okpuru ya dị ka egosiri, tupu ị were isi. ndabere.

# systemctl stop postgresql-12.service
# su - postgres
$ cp -R /var/lib/pgsql/12/data /var/lib/pgsql/12/data_orig
$ rm -rf /var/lib/pgsql/12/data/*

7. Mgbe ahụ, jiri pg_basebackup ngwá ọrụ iji nweta ndabere ndabere na ikike ikike (onye ọrụ nchekwa data bụ Postgres, n'ime akaụntụ onye ọrụ Postgres) yana ikike ziri ezi.

N'iwu a, nhọrọ:

  • -h - ezipụta onye ọbịa nke bụ ihe nkesa ukwu.
  • -D - na-akọwapụta ndekọ data.
  • -U - ezipụta onye ọrụ njikọ.
  • -P - na-enyere aka mkpesa ọganihu.
  • -v - na-enyere ọnọdụ okwu ọnụ aka.
  • -R - na-enyere aka ịmepụta nhazi mgbake: Mepụta faịlụ standby.signal wee tinye ntọala njikọ na postgresql.auto.conf n'okpuru ndekọ data.
  • -X - ejiri tinye faịlụ ndekọ ederede achọrọ (faịlụ WAL) na nkwado ndabere. Uru iyi pụtara ịkwanye WAL ka emebere ndabere.
  • -C - na-enyere aka ịmepụta oghere mmegharị nke nhọrọ -S aha ya tupu ịmalite ndabere.
  • -S - na-akọwapụta aha oghere mmegharị.

$ pg_basebackup -h 10.20.20.9 -D /var/lib/pgsql/12/data -U replicator -P -v  -R -X stream -C -S pgstandby1
$ exit

8. Mgbe usoro ndabere na-eme, akwụkwọ ndekọ data ọhụrụ na ihe nkesa njikere kwesịrị ịdị ka nke ahụ na nseta ihuenyo. Emebere akara standby.signal ma tinye ntọala njikọ na postgresql.auto.conf. Ị nwere ike depụta ọdịnaya ya site na iji iwu ls.

# ls -l /var/lib/pgsql/12/data/

Ohu mmegharị ga-agba ọsọ na ọnọdụ \Ọkụ Njikere ma ọ bụrụ na atọrọ paramita hot_standby na (uru nke ndabara) na postgresql.conf na enwere faịlụ standby.signal dị na ndekọ data.

9. Ugbu a laghachi na ihe nkesa nna ukwu, ị ga-enwe ike ịhụ oghere mmegharị a na-akpọ pgstandby1 mgbe ị mepee pg_replication_slots ele dị ka ndị a.

# su - postgres
$ psql -c "SELECT * FROM pg_replication_slots;"
$ exit

10. Ka ilele ntọala njikọ agbakwunyere na faịlụ postgresql.auto.conf, jiri iwu pusi.

# cat /var/lib/pgsql/12/data/postgresql.auto.conf

11. Ugbu a malite ọrụ nchekwa data nkịtị na ihe nkesa njikere site na ịmalite ọrụ PostgreSQL dị ka ndị a.

# systemctl start postgresql-12

Kwụpụ 3: Na-anwale mmegharị mgbasa ozi PostgreSQL

12. Ozugbo ejikọtara njikọ nke ọma n'etiti nna ukwu na njikere, ị ga-ahụ usoro nnabata WAL na ihe nkesa njikere na ọkwa nke nkwanye, ị nwere ike ịlele nke a site na iji pg_stat_wal_receiver view.

$ psql -c "\x" -c "SELECT * FROM pg_stat_wal_receiver;"

yana usoro izipu WAL kwekọrọ na nna ukwu/isi nkesa nwere ọnọdụ nkwanye na sync_state nke async, ị nwere ike lelee pg_stat_replication pg_stat_replication a.

$ psql -c "\x" -c "SELECT * FROM pg_stat_replication;"

Site na nseta ihuenyo dị n'elu, mmegharị nkwanyegharị ahụ enweghị ihe jikọrọ ya. N'akụkụ na-esote, anyị ga-egosi otu esi eme ka ọ bụrụ nhọrọ ịmekọrịta mmegharị.

13. Ugbu a nwalee ma ọ bụrụ na mmegharị ahụ na-arụ ọrụ nke ọma site na ịmepụta nchekwa data ule na ihe nkesa nna ukwu wee lelee ma ọ dị na ihe nkesa njikere.
[master]postgres=# Mepụta DATABASE tecmint;
[njikere] postgres=# \l

Nhọrọ: Na-eme ka mmegharị mmekọrịta

14. Nmegharị mmekọrịta na-enye ikike ịme azụmahịa (ma ọ bụ dee data) na nchekwa data bụ isi na njikere/ oyiri n'otu oge. Ọ na-akwado naanị na azụmahịa na-aga nke ọma mgbe ebugharị mgbanwe niile nke azụmahịa ahụ mere na otu sava njikere ma ọ bụ karịa mekọrịta.

Iji mee ka mmegharị mmekọrịta dị n'otu, a ga-etinyekwa synchronous_commit na (nke bụ uru ndabara, yabụ na ọ dịghị mkpa maka mgbanwe ọ bụla) yana ịkwesịrị ịtọ paramita synchronous_standby_names ka ọ bụrụ uru efu. Maka ntuziaka a, anyị ga-edozi ya niile.

$ psql -c "ALTER SYSTEM SET synchronous_standby_names TO  '*';"

15. Mgbe ahụ, bugharịa ọrụ PostgreSQL 12 iji tinye mgbanwe ọhụrụ.

# systemctl reload postgresql-12.service

16. Ugbu a, mgbe ị na-ajụ usoro izipu WAL na isi ihe nkesa ọzọ, ọ kwesịrị igosi ọnọdụ nke nkwanye na sync_state nke mmekọrịta.

$ psql -c "\x" -c "SELECT * FROM pg_stat_replication;"

Anyị abịala na njedebe nke ntuziaka a. Anyị egosila otu esi edozi PostgreSQL 12 master-standby database streaming replication na CentOS 8. Anyị kpuchirikwa otu esi eme ka mmegharị mmekọrịta dị na ụyọkọ nchekwa data PostgreSQL.

Enwere ọtụtụ ojiji nke mmụgharị ma ị nwere ike họrọ mgbe niile ngwọta nke na-egbo gburugburu IT gị na/ma ọ bụ ngwa akọwapụtara. Maka nkọwa ndị ọzọ, gaa na Sava Njikere Mbupu na akwụkwọ PostgreSQL 12.