Otu esi akwado ma weghachi nchekwa data PostgreSQL

N'ebe a na-emepụta ihe, n'agbanyeghị otú nchekwa data PostgreSQL gị si buru ibu ma ọ bụ dị nta, azụ azụ bụ akụkụ dị mkpa nke njikwa nchekwa data. N'edemede a, ị ga-amụta otu esi echekwa ma weghachi nchekwa data PostgreSQL.

Anyị na-eche na ị nweelarị nrụnye nke sistemụ nchekwa data PostgreSQL. Ọ bụrụ na ọ bụghị, gụọ akụkọ anyị na-esonụ ka ịwụnye PostgreSQL na nkesa Linux gị.

Etu esi etinye PostgreSQL na pgAdmin4 na Ubuntu 20.04
Otu esi etinye PostgreSQL na pgAdmin na CentOS 8
Otu esi etinye PostgreSQL na pgAdmin na RHEL 8

Ka anyị bido…

Ndabere otu ebe nchekwa data PostgreSQL

PostgreSQL na-enye ike pg_dump iji nyere gị aka ịkwado ọdụ data. Ọ na-ewepụta faịlụ nchekwa data nwere iwu SQL n'ụdị nke enwere ike iweghachi ngwa ngwa n'ọdịnihu.

Iji kwadoo, nchekwa data PostgreSQL, malite site na ịbanye n'ime ihe nkesa nchekwa data gị, wee gbanwee gaa na akaụntụ onye ọrụ Postgres, wee mee pg_dump dị ka ndị a (dochie tecmintdb na aha nchekwa data ịchọrọ ịkwado) . Site na ndabara, usoro mmepụta bụ faịlụ ederede SQL dị larịị.

$ pg_dump tecmintdb > tecmintdb.sql

Pg_dump na-akwado usoro mmepụta ndị ọzọ. Ị nwere ike ezipụta usoro mmepụta site na iji -F nhọrọ, ebe c pụtara omenala usoro faịlụ Archive, d pụtara akwụkwọ ndekọ aha, na > t pụtara faịlụ ebe a na-edebe akwụkwọ tar: usoro niile dabara adaba maka ntinye n'ime pg_restore.

Ọmụmaatụ:

$ pg_dump -F c tecmintdb > tecmintdb.dump
OR
$ pg_dump -F t tecmintdb > tecmintdb.tar

Iji tụfuo mmepụta n'ụdị mmepụta ndekọ aha, jiri ọkọlọtọ -f ọkọlọtọ (nke a na-eji akọwapụta faịlụ mmepụta) iji kọwaa ndekọ aha ebumnuche kama ịbụ faịlụ. Akwụkwọ ndekọ nke pg_dump ga-emepụta agaghị adị.

$ pg_dump -F d tecmintdb -f tecmintdumpdir

Iji kwado nchekwa data PostgreSQL niile, jiri ngwa pg_dumpall dịka egosiri.

$ pg_dumpall > all_pg_dbs.sql

Ị nwere ike iweghachi ihe mkpofu ahụ site na iji psql dị ka egosiri.

$ pgsql -f all_pg_dbs.sql postgres

Na-eweghachi ọdụ data PostgreSQL

Iji weghachi nchekwa data PostgreSQL, ị nwere ike iji psql ma ọ bụ pg_restore utilities. a na-eji psql weghachi faịlụ ederede nke pg_dump mepụtara ebe a na-eji pg_restore weghachite ebe nchekwa data PostgreSQL sitere na ebe pg_dump mebere n'otu ụdị ederede na-abụghị nke nkịtị (omenala, tar, ma ọ bụ ndekọ).

Nke a bụ ọmụmaatụ otu esi eweghachi mkpofu faịlụ ederede dị larịị:

$ psql tecmintdb < tecmintdb.sql

Dịka ekwuru n'elu, mkpofu usoro omenala abụghị edemede maka pgsql, yabụ a ga-eweghachi ya na pg_restore dịka egosiri.

$ pg_restore -d tecmintdb tecmintdb.dump
OR
$ pg_restore -d tecmintdb tecmintdb.tar
OR
$ pg_restore -d tecmintdb tecmintdumpdir

Ndabere nnukwu ọdụ data PostgreSQL

Ọ bụrụ na nchekwa data ị na-akwado buru ibu ma ịchọrọ ịmepụta faịlụ mmepụta dị ntakịrị, mgbe ahụ ị nwere ike ịme ihe mkpofu ebe ị ga-enyocha mmepụta pg_dump site na ngwa mkpakọ dị ka gzip ma ọ bụ nke ọ bụla ọkacha mmasị gị:

$ pg_dump tecmintdb | gzip > tecmintdb.gz

Ọ bụrụ na nchekwa data ahụ buru ibu nke ukwuu, ị nwere ike ịkwatu n'otu oge site na ịwụfu nọmba_of_jobs tebụl n'otu oge na-eji ọkọlọtọ -j, dị ka egosiri.

$ pg_dump -F d -j 5 -f tecmintdumpdir

Ọ dị mkpa iburu n'obi na nhọrọ nkwụsịtụ yiri ya na-ebelata oge nkwụsịtụ, ma n'aka nke ọzọ, ọ na-abawanye ibu na ihe nkesa nchekwa data.

Ndabere Remote PostgreSQL ọdụ data

pg_dump bụ ngwa ahịa ndị ahịa PostgreSQL, ọ na-akwado arụmọrụ na sava nchekwa data dịpụrụ adịpụ. Iji kọwapụta ihe nkesa nchekwa data dịpụrụ adịpụ pg_dump kwesịrị ịkpọtụrụ, jiri nhọrọ ahịrị iwu -h kọwapụta onye nnabata remote na -p na-akọwapụta ọdụ ụgbọ mmiri dịpụrụ adịpụ sava nchekwa data na-ege na ya. . E wezụga nke ahụ, jiri ọkọlọtọ -U kọwaa aha ọrụ nchekwa data iji jikọọ dị ka.

Cheta iji dochie 10.10.20.10 na 5432 na tecmintdb na adreesị IP gị nke dịpụrụ adịpụ ma ọ bụ aha nnabata, ọdụ data ọdụ data, na aha nchekwa data n'otu n'otu.

$ pg_dump -U tecmint -h 10.10.20.10 -p 5432 tecmintdb > tecmintdb.sql

Gbaa mbọ hụ na onye ọrụ na-ejikọ anya nwere ohere achọrọ iji nweta nchekwa data, yana usoro nyocha data kwesịrị ekwesị ka edoziri na sava nchekwa data, ma ọ bụghị ya, ị ga-enweta njehie dịka nke egosiri na nseta ihuenyo na-esonụ.

Ọ dịkwa ike ịkwanye nchekwa data ozugbo site na otu nkesa gaa na nke ọzọ, jiri pg_dump na psql utilities dị ka egosiri.

$ pg_dump -U tecmint -h 10.10.20.10 tecmintdb | pqsl -U tecmint -h 10.10.20.30 tecmintdb

Nchekwa nchekwa data PostgreSQL na-eji Cron arụ ọrụ

Ị nwere ike ịme nkwado ndabere na mpaghara oge niile site na iji ọrụ cron. Ọrụ Cron bụ ụzọ a na-ejikarị maka ịhazi ụdị ọrụ dị iche iche iji rụọ ọrụ na sava.

Ị nwere ike hazie ọrụ cron iji megharịa ndabere nchekwa data PostgreSQL dị ka ndị a. Rịba ama na ịkwesịrị ịgba ọsọ iwu ndị a dị ka onye nlekọta PostgreSQL:

$ mkdir -p /srv/backups/databases

Na-esote, gbaa iwu a ka ị dezie crontab ka ịgbakwunye ọrụ cron ọhụrụ.

$ crontab -e

Detuo na mado ahịrị na-esote na njedebe nke crontab. Ị nwere ike iji usoro mkpofu ọ bụla akọwara n'elu.

0 0 * * *  pg_dump  -U postgres tecmintdb > /srv/backups/postgres/tecmintdb.sql

Chekwaa faịlụ wee pụọ.

Ọrụ cron ga-amalite na-arụ ọrụ ọhụrụ a na-akpaghị aka na-enweghị mmalite. Na ọrụ cron a ga-agba ọsọ kwa ụbọchị n'etiti abalị, ọ bụ ihe ngwọta kacha nta maka ọrụ ndabere.

Maka ozi ndị ọzọ ka esi hazie ọrụ cron, hụ: Otu esi emepụta na jikwaa ọrụ Cron na Linux

Nke ahụ bụ maka ugbu a! Ọ dị mma ime ka ndabere data bụrụ akụkụ nke usoro njikwa nchekwa data gị. Iji nweta anyị ajụjụ ma ọ bụ nkọwa ọ bụla, jiri ụdị nzaghachi dị n'okpuru. Maka ozi ndị ọzọ, hụ ibe ntụaka pg_restore.