Rozwiązania do archiwizacji baz danych

Kontynuując pytanie zadane przeze mnie w sprawie Czy dobrym pomysłem jest przeniesienie tabel o dużej objętości i dostępu do oddzielnej bazy danych? , szukam różnych technik / rozwiązań do archiwizacji baz danych w PostgreSQL.

Kilka rozwiązań, o których mogę myśleć to:

1. Partycjonowanie tabeli
2. Oddzielny obszar tabel i / lub schemat
3. Przenoszenie zarchiwizowanych rekordów / tabel na inny dysk twardy

Wszelkie inne sugestie / wskazówki / rozwiązania są bardzo mile widziane i doceniane.

UWAGA: korzystamy z PostgreSQL v9.1.3 na CentOS5.2

Moja sugestia dotycząca archiwizacji:

1. Utwórz archive_tablespace(jeśli chcesz, możesz oddzielić sprzęt na archiwum)

2. Twórz tabele. Na przykład chcemy zarchiwizować posty w tabeli.

   create table  posts_all ( LIKE public.posts)  ;
   create table  posts_archive () inherits  ( public.posts_all)  ;
   alter table  public.posts  inherits ( public.posts_all ) ;

   Następnie będziemy mieć 2 nowe tabele: public.posts_all (z tymi samymi kolumnami, co w postach) do przeszukiwania wszystkich postów (archiwum i produkcja) oraz public.posts_archive do przeszukiwania wszystkich postów archiwalnych. Public.posts odziedziczy po post_all.
   Inserty powinny iść w stary sposób (do tabeli public.posts), chyba że napiszesz wyzwalacze na posts_all w celu przekierowania wstawek do tabeli postów. Jeśli masz partycjonowanie, będzie to bardziej skomplikowane. W działającej aplikacji i przed migracją starych danych nie trzeba nic zmieniać w kodzie aplikacji, aby pracować z tym podejściem.

3. Utwórz archiwum schematów dla logicznej separacji. Moją sugestią będzie rozdzielenie danych archiwalnych według pewnego okresu (roku lub miesiąca), jeśli to możliwe (archive_2005).

4. Utwórz tabele archiwów w schemacie archiwum_rok

   create table archive_2005.posts (
     check(record_date >= '2005-01-01 00:00:00'::timestamp 
       and record_date <  '2006-01-01 00:00:00'::timestamp)
   ) inherits (posts_archive) tablespace archive_tablesapce;

   Następnie będziesz mieć nowe posty w tabeli w schemacie archive_2005, a planista postgresql będzie wiedział, że dane istnieją tylko w wyznaczonym okresie. Jeśli zapytasz o inny okres, postgresql nie będzie wyszukiwał w tej tabeli.

5. Utwórz funkcje / procedury / wyzwalacze, aby przenieść dane do tabel archiwum.

6. Zarchiwizuj raz na pewien okres (tutaj rok) i odkurz stary stół lub zrób to automatycznie za pomocą wyzwalaczy (cięższe na autovacuum). Obie techniki mają wiele zalet i wad.

Jeśli wdrożony:

1. Może osobno przesyłać kwerendy do archiwum (wybierz * z post_archive), wszystkich (wybierz * z posts_all) i danych produkcyjnych (wybierz * z public.posts)
2. Można zrzucić schematy archiwum osobno i w łatwy sposób upuścić na nie kaskady. pg_dump -s archive_2005 nazwa_bazy_danych schemat upuszczenia kaskada_archiwum_2005; - bądź ostrożny, ponieważ usuwa wszystkie powiązane tabele
3. Stare dane rozdzielone fizycznie według obszaru tabel i logicznie według schematu.
4. Dość skomplikowana struktura do zarządzania procesem archiwizacji
5. Potrafi tworzyć różne indeksy w tabelach produkcji i archiwizacji w celu optymalizacji zapytań zarówno (mniejsze i wyspecjalizowane indeksy = szybsze zapytania i mniejsze zapotrzebowanie na miejsce)
6. Jeśli masz partycjonowane tabele (według roku lub miesiąca), proces archiwizacji będzie polegał na przeniesieniu całej tabeli archive_tablespace polegał lub po prostu zmianie jej na dziedziczenie z post_archive (nie testowałem tego)
7. Jeśli nie chcesz uzyskać dostępu do starych (zarchiwizowanych) danych, nie musisz nic zmieniać w aplikacji.

To ogólna technika, którą powinieneś dostosować do swoich potrzeb. Wszelkie sugestie, aby to poprawić?

Dalsza lektura: Dziedziczenie PostgreSQL , partycjonowanie