Zmień tabelę w bazach danych produkcji na żywo

Jak większość „popularnych” systemów baz danych (MySQL, Postgres ...) obsługuje zmienianie tabel w bazach danych na żywo (takich jak dodawanie, usuwanie lub zmiana typu kolumn)?

Wiem, że poprawnym sposobem jest tworzenie kopii zapasowej wszystkich harmonogramów przestojów, a następnie wprowadzanie zmian.

Ale ... czy jakiś obecny system baz danych obsługuje robienie tych rzeczy „on-line” bez zatrzymywania czegokolwiek? (być może opóźniając zapytania odnoszące się do właśnie zmienianej / usuwanej kolumny)

A co się stanie, gdy po prostu ALTER TABLE...uruchomię działającą bazę danych na żywo? Czy wszystko się kończy, kiedy to się dzieje? Czy dane mogą ulec uszkodzeniu? itp.

Ponownie mam na myśli głównie Postgres lub MySQL, ponieważ to właśnie się spotykam.

(I tak, za każdym razem, gdy musiałem to zrobić, zanim zrobiłem to „we właściwy sposób”, wykonując kopie zapasowe, planując przestoje itp.… Ale chcę tylko wiedzieć, czy można to zrobić i to „szybko i szybko” brudne ”lub jeśli istnieje system DB, który faktycznie obsługuje zmiany schematu„ szybkie, aktywne i brudne ”)

Ktoś właśnie zasugerował zmianę schematu online dla MySQL ze skryptu Facebooka (z tutorialem tutaj i źródłem tutaj ) ... wydaje się być dobrym sposobem na zautomatyzowanie zestawu „hackich” sposobów, aby to zrobić ... w którym ktoś kiedykolwiek go używał coś przypomina produkcję?

Kiedy ALTER TABLEwydasz w PostgreSQL , zajmie ACCESS EXCLUSIVEblokadę, która blokuje wszystko, w tymSELECT . Jednak ta blokada może być dość krótki, jeśli tabela nie wymaga ponownego pisania, brak nowych UNIQUE, CHECKlub FOREIGN KEYograniczenia potrzeba drogich skanów pełnej tabeli, aby sprawdzić, itd.

W razie wątpliwości możesz po prostu spróbować! Wszystkie DDL w PostgreSQL są transakcyjne, więc całkiem dobrze jest anulować i ALTER TABLEjeśli trwa to zbyt długo i zaczyna wstrzymywać inne zapytania. Poziomy blokady wymagane przez różne polecenia są udokumentowane na stronie blokowania .

Niektóre normalnie powolne operacje można przyspieszyć, aby były bezpieczne bez przestojów. Na przykład, jeśli masz tabelę ti chcesz zmienić kolumnę customercode integer NOT NULLna, textponieważ klient zdecydował, że wszystkie kody klientów muszą teraz zaczynać się od X, możesz napisać:

ALTER TABLE t ALTER COLUMN customercode TYPE text USING ( 'X'||customercode::text );

... ale to zablokowałoby cały stół do ponownego zapisu. Podobnie jak dodanie kolumny z DEFAULT. Można to zrobić w kilku krokach, aby uniknąć długiej blokady, ale aplikacje muszą być w stanie poradzić sobie z tymczasową duplikacją:

ALTER TABLE t ADD COLUMN customercode_new text;
BEGIN;
LOCK TABLE t IN EXCLUSIVE MODE;
UPDATE t SET customercode_new = 'X'||customercode::text;
ALTER TABLE t DROP COLUMN customercode;
ALTER TABLE t RENAME COLUMN customercode_new TO customercode;
COMMIT;

Zapobiegnie to tylko zapisom w ttrakcie procesu; nazwa zamka EXCLUSIVEjest nieco zwodnicza, ponieważ wyklucza wszystko opróczSELECT ; ACCESS EXCLUSIVEtryb jest jedynym, który wyklucza absolutnie everyting. Zobacz tryby blokady . Istnieje ryzyko, że operacja ta może zostać przywrócona do impasu z powodu wymaganej przez aktualizację blokady ALTER TABLE, ale w najgorszym wypadku trzeba to zrobić ponownie.

Można nawet uniknąć tej blokady i zrobić całość na żywo, tworząc funkcję wyzwalania na tktóry ilekroć INSERTalbo UPDATEprzychodzi, automatycznie zapełnia customercode_newz customercode.

Istnieją również wbudowane narzędzia, takie jak CREATE INDEX CONCURRENTLYi ALTER TABLE ... ADD table_constraint_using_indexzaprojektowane w taki sposób, aby umożliwić DBA skrócenie wyłącznych czasów blokowania poprzez wolniejsze wykonywanie pracy w sposób przyjazny dla współbieżności.

pg_reorgNarzędzie lub jego następca pg_repackmoże być stosowany w przypadku niektórych działań restrukturyzacyjnych oraz stołowych.

Percona ma własne narzędzie do wykonywania zmian schematu online

Narzędzie to nazywa się zmiana schematu pt-online

Obejmuje wyzwalacze, więc proszę uważnie przeczytać dokumentację.

Według Dokumentacji wykonano główne operacje

• Kontrola poczytalności
• Wydzielanie
• Zmiana schematu online
  • Utwórz i zmień tabelę tymczasową
  • Przechwyć zmiany z tabeli do tabeli tymczasowej
  • Skopiuj wiersze z tabeli do tabeli tymczasowej
  • Zsynchronizuj tabelę i tabelę tymczasową
  • Zamień / zmień nazwę tabeli i tabeli tymczasowej
  • Sprzątać

Wyłączenie systemu i wykonanie wszystkich zmian naraz może być bardzo ryzykowne. Jeśli coś pójdzie nie tak i często tak się dzieje, nie ma łatwego powrotu.

Jako programista Agile czasami potrzebuję refaktoryzować tabele bez żadnych przestojów, ponieważ tabele te są modyfikowane i odczytywane.

Poniższe podejście wiąże się z niskim ryzykiem, ponieważ zmiany dokonuje się w kilku etapach niskiego ryzyka, które bardzo łatwo wycofać:

• Upewnij się, że wszystkie moduły uzyskujące dostęp do tabeli są dobrze objęte automatycznymi testami.
• Utwórz nowy stół. Zmień wszystkie procedury, które modyfikują starą tabelę, tak aby modyfikowały zarówno stare, jak i nowe tabele.
• Przeprowadź migrację istniejących danych do nowej struktury. Zrób to małymi partiami, aby nie miało to poważnego wpływu na ogólną wydajność serwera.
• Sprawdź, czy migracja danych zakończyła się powodzeniem.
• Przekieruj niektóre procedury wyboru ze starej tabeli do nowych. Użyj automatycznych testów, aby upewnić się, że zmienione moduły są nadal poprawne. Upewnij się, że ich wydajność jest akceptowalna. Wdróż zmienione procedury.
• Powtarzaj poprzedni krok, aż wszystkie raporty wykorzystają nową tabelę.
• Zmień procedury modyfikujące tabele, aby miały dostęp tylko do nowej tabeli.
• Zarchiwizuj stary stół i usuń go z systemu.

Stosowaliśmy to podejście wiele razy, aby zmieniać duże tabele produkcji na żywo bez przestojów, bez żadnych problemów.

Tak, wiele nowoczesnych baz danych pozwala po prostu dodać kolumnę lub zmienić charakterystykę kolumny, na przykład dodając lub usuwając wartość zerową.

Jeśli upuścisz kolumnę, dane zostaną utracone, ale strach przed korupcją jest niewielki.

Narzędzie Percona używa wyzwalaczy, aby pomóc w jego zmianie, i nie gra dobrze, jeśli twój stół ma już istniejące wyzwalacze. Skończyło się na tym, że napisałem taki, który faktycznie dobrze obsługuje istniejące wyzwalacze, ponieważ są one bardzo ważne dla naszej bazy danych https://github.com/StirlingMarketingGroup/smg-live-alter

Aby odpowiedzieć na pytanie o to, co dzieje się z ALTER TABLEinstrukcją, zależy to od zakresu zmian. W szczególnych przypadkach, jeśli dodasz nową kolumnę, przynajmniej w MS SQL Server, silnik utworzy tymczasową kopię tabeli, jednocześnie tworząc nową definicję tabeli, a następnie wstawi tam dane z powrotem. Na czas zmiany tabela byłaby zatem niedostępna dla użytkowników.

Przykład konkretnych operacji na serwerze MSSQL znajduje się tutaj: http://support.microsoft.com/kb/956176/en-us

Zakładam, że inne RMDB mają podobne metody, chociaż dokładna implementacja byłaby czymś, co musiałbyś zweryfikować z dokumentacją dostawcy.