Emva kokuba ndandisa DB yokukhiqiza yam nge UPDATE ebomvu kwaye ayikho iibhanki ezintsha. Waya ~30% yentengiso, iingxaki ezininzi kunye nokuba iiyure ezininzi kakhulu. Le ubunzima yandisa ukuba ukwakha kunye ne-open-source PostgreSQL yebhanki & izixhobo zokuhamba ukuba ngoku usebenzisa zonke. Table of content Umxholo we-open-source ye-PostgreSQL backup project I-History of how I broke the DB and couldn't fully recover Yintoni ndiqala ukuvelisa inkqubo Roadmap & iiplani zangaphambili Izicelo ze-DB zokhuseleko ziye zikhuselwe ngoku ku zonke iiprojekthi Ukucinga Umxholo we-open source PostgreSQL Backup Project Ndifumene isisombululo lwayo yokulawula kunye nokuphepha i-PostgreSQL. Ndiyila kwaye usebenzisa iinyanga ezimbini ngaphezu kwiminyaka emininzi. Okokuqala, iye yenzelwe yokusebenza kwimihla yam kwaye iiprojekthi ezimbini zeemfutha. Kwiintsuku ezidlulileyo ndiye ndingathanda ukuba le nkqubo ngokwenene kubonakala kakuhle kakhulu ukuba ibonise ngokubanzi, kunceda abathandi zayo kwaye kunokuba lula kwi-community. Go, gin, gorm, React, TypeScript, PostgreSQL - yonke into ifakwe kwi-Docker. I-version yokuqala yaba kwi-Java, kodwa ndibhalwe kwi-Go ngexesha elide. Stack: Ngokutsho, i-UI wrapper malunga ne-standard i-custom format, kunye neengxaki ze-extra ezincinciphisa i-UX kunye ne-integrations kunye neenkonzo ze-storage kunye ne-notification. pg_dump What it can do: Ukulungiselela i-backups (isib. ngosuku zonke kwi-4 AM okanye ngosuku zonke kwi-midnight) ye-PostgreSQL 13-17. Izixhobo ze-compressed ziyafumaneka kwi-server, kwi-S3 okanye kwi-Google Drive (i-NS servers kunye ne-FTP ziyafumaneka kwi-roadmap). Thumela umyalezo emva kokufaka yonke into ... okanye akukho. Izibuyekezo ziyafumaneka. I-Ping yakho kwi-Discord, i-imeyile, i-Telegram kunye ne-Slack ukuba i-DB ibonakele ukuxhaswa. I-Ping ibonelela kuphela emva kwe-n i-checks ezihambayo (ukunceda i-fake positives ngenxa yeengxaki ze-network) kwaye ibonisa i-diagram ye-availability. Ngokutsho, le nophuhliso i-free, i-open source (MIT), i-self-hosted kunye ne-humane web UI. Iwebhusayithi yeprojekthi: https://postgresus.com/ https://postgresus.com/ Ukucinga: https://github.com/RostislavDugin/postgresus https://github.com/RostislavDugin/postgresus P.S. Ukuba le nkqubo ibonelela kwaye unayo i-akhawunti ye-GitHub, ⭐️. Iimveliso ezidlulileyo ziyafumaneka kakhulu. I’d really appreciate a I-History of how I broke the DB and couldn't fully recover Kwangathi ngo-2023, i-imeyili yeprojekthi ebandayo ebandayo ye-ChatGPT (i-3.5). Ngokuvamile, i-API yokuthengisa kuphela kunye ne-UI elungileyo kunye ne-shortcuts. I-project iye yandiswa, emva koko iye yaye ngexesha lokugqibela ndithengiswa. I-DB iye ilawulwa ngexesha elinye ngosuku nge-konsole tool efana ne-console Ukusuka kwi-server entsha PgBackRest Kwixesha xa le nkqubo yandisa malunga ne-1,500 USD ngempumelelo kunye nokufumana iiphakheji yayo yokuthengisa, kukho into embalwa: . I broke the data in the DB I-Friday night. Ndingathanda, ndingathanda ngexesha lokugqibela ukusuka kwi-coding ukuya kwi-messages enxulumene, ngokugqithisileyo ngokupheleleyo. I-client ibiza ukuba ukuguqulele i-imeyile ye-akhawunti yayo. nge-SSH kunye Ndingathanda kwi-VPS yokukhiqiza kwaye ndingathanda into efana ne: psql UPDATE users SET email = 'customer@email.com' WHERE email ILIKE '%%'; Emva koko ndibandakanya ukucacisa i-imeyile elifanelekileyo kwi-chat kwaye... uxhumane i-Enter kwi-"autopilot". Yintoni elandelayo ngaba ndingathanda into efana ne- . AFFECTED ROWS: 10 000 Kwaye ngexesha elinye kwiintsuku ezininzi, ezininzi ngexesha ndingathanda i-sweat ye-cold emzimbeni. : ngoko ke, ndingathanda ukuba ndingathanda a Okokuqala, mhlawumbi set a , njl. Kodwa njengoko zonke iingoma ze-horror, izicelo zokhuseleko zokuqala ziye zithunywe, yaye zonke ziye zithunywe kwi-catastrophe. Disclaimers SELECT SAVEPOINT Zonke i-imeyile yabasebenzisi ziye zithunyelwe. Kwaye apha iinkcukacha ebalulekileyo: iinkqubo ze-payment zihlanganisa iinkqubo ezininzi - ukuba umdlali awukwazi ukufikelela kwiinkonzo ze-payment, nto leyo ingxaki elikhulu. Ngokuqhelekileyo, akukho umdlali awukwazi ukufikelela kwaye izibopho zangaphambili zangaphakathi. Ndingathanda kwi-backups - kwaye i-cold sweat iye yandibene. I-backup eyenziwe ngexesha elidlulileyo malunga neenyanga 😐. Akukho indlela yokuguqula kwakhona. Ukusuka kwakhona iintlawulo ezintsha zangaphakathi, i-subscriptions ziye zithambulwe (ukuba ngaba awukwazi ukuguqula bonke - ezinye abantu baye baye baye), njl. Nangona kunjalo, ngexesha elilandelayo kunye neentsuku, ndiza ukuguqula malunga ne-65% ye-DB ngokusebenzisa i-scripts usebenzisa i-ID ye-user. Kwixesha elilandelayo, ndiza ukuguqula i-subscriptions kunye nokuguqula abantu. Yinto enzima, enzima kwaye enzima. Ukufundwa kwimfundo. Yintoni ndiqala ukuvelisa inkqubo ixesha lokuphendula: Ndiza ukwakha i-imeyile ye-backup ebonakalayo ngosuku zonke ukuba yonke into elungileyo! Kwaye ukuguqulwa kwi-click eziliqela! Kwaye i-blackjack kunye ne-microservices! Kwaye ndiza ukongeza i-API ye-health-check endpoint kakhulu! Ndicwangcisa i-Postgresus ye-version yokuqala kwi-Java kwiinyanga eziliqela. Yenza ukusetyenziswa. Nceda abalandeli abalandeli. Qhagamshelana nokupholisa kulingana nezidingo zayo kunye neengxaki zayo. Ukubonisa: kunzima. Kwiimeko ezininzi iiphakheji ezidlulileyo (akukho kuphela). I-name "Postgresus" ifumaneka iinyanga ezimbini ezidlulileyo, phambi kokuba i-repo yaziwa kuphela . “pg-web-backup” Okwangoku, I-Postgresus isixazululo iingxaki zayo: Izixhobo yokuphepha yokuqala ukuba iiprojekthi i-small okanye ifumaneka kwi-VPS kunokuba yi-cloud DB service. Kuyinto tool backup fallbackif inkqubo enkulu kwaye ibekwe kwi-DBaaS kunye ne-cloud backups yayo.I-backup "ngokuthi ngexesha elifanayo" (ukuba i-cloud ibekwe, ibekwe, i-DB ibekwe ngempumelelo kunye ne-cloud backups ngenxa ye-non-payment, njl). Kukho kuluhlu olungcono ukufumana i-backup ye-duplicate kunokuba kufike kwi-0.01% yaye ngexesha elizayo xa kwakhona i-cloud ibekwe kwaye akukho i-Plan B. Roadmap & iiplani zangaphakathi ndicwangcisa ukukhuthaza iiprojekthi kwiindawo ezilandelayo: Add more PostgreSQL-specific load monitoring (pg_stat_activity, pg_stat_system, pg_locks) with a friendly UI. Think of it as an alternative to postgres_exporter + Grafana, but bundled out of the box with backups. Observe and alert on slowdown of key queries. In my work project, there are tables and specific functions that are too early to optimize (if the hypothesis fails, we might drop them), but they could grow and slow down. For example, if INSERT INTO users (...) VALUES (...) starts taking more than 100 ms while the flow of new users is growing - we'll get a notification and go optimize. Collect query stats by CPU time and execution frequency to see where resources are actually going and what’s worth improving. Add more channels for notifications and more storage providers. Izicelo ze-DB zokhuseleko ziye zikhuselwe ngoku ku zonke iiprojekthi Nceda ndicinga iingxowa ezimbini zeengxowa zomphakathi: Iintlawulo ze-System zihlanganisa kwiinkalo ezimbini: Amaso abakwazi ukwenza i-backups kwaye Amaso abavela. Iintlawulo ze-System zihlanganisa kwiinkalo ezimbini: Amaso abakwazi ukwenza i-backups kwaye Amaso abavela. Ngaba usebenze i-backups kuphela - ngokuvamile ukubonelela ukuba unako ngokwenene ukuguqulwa kwakhona. Ngaba usebenze i-backups kuphela - ngokuvamile ukubonelela ukuba unako ngokwenene ukuguqulwa kwakhona. Ukususela i-DB yeprojekthi yam, ndiyavumelana nezi zekhwalithi ngaphandle kweemeko ezininzi: Kwi-UPDATE, uqhagamshelane ngokuzenzakalelayo i-SELECT kwaye uqinisekisa ukuba uqhagamshelane ngexabiso ze-1-2. Ukuba ukuguqulwa kubaluleke kakhulu, uqhagamshelane manually i-SAVEPOINT. Yenza i- "i-fire alarms" kunye ne-restore ngexesha elide ze-3 ngenyanga: ukuguqulwa kwiphepha le-cloud kunye ne-local. Ngoko ke xa kuxhomekeke, unokufumana ukuba akukho idatha, i-backups ayisebenza, okanye i-restores iya kuthatha ixesha elide. " Kwiintsuku ezimbini ezidlulileyo, kukho iimeko ezincinane apho ufuna ukuguqula ukusuka kwi-backups - zonke iimeko ezisebenza, kunye ne-cloud kunye ne-Postgresus. Akukho iingxaki, ngenxa yokuba inqubo yaba kufumaneka ngexesha lokucwangcisa. Iimeko zokhuseleko ezisebenzayo. Ukucinga Ndingathanda ukuba le nkqubo iya kuba lula kwiinkalo ezininzi ze-developer, i-DBAs kunye ne-DevOps. Ndingathanda ukuxhaswa kwakhona ukuze kwenziwe nangaphezulu kakhulu kwiinkalo zehlabathi. Ndingathanda ukufumana iingcebiso kunye ne-feedback zokuphucula.
