Generowanie danych, a właściwie serii w PostgreSQLu

Pracując z bazami danych przy generowaniu dużej i różnej ilości raportów, musieliście się kiedyś spotkać z funkcją generate_series, dzięki której np. numerowaliście rekordy. Ja używałam tej funkcji także do wytwarzania danych testowych. W tym poście przedstawię wam kilka przypadków, dla których ta funkcja stała się dla mnie bardzo pomocna. Zacznijmy od jej definicji.

Funkcja przyjmuje 2 lub 3 argumenty, które są liczbami lub datą. Trzeci argument (step) nie jest wymagany dla wartości typu biginteger (domyślnie jest równy 1), ale jest konieczny gdy chcemy stworzyć serie dla timestapów:

generate_series(start, stop, step)

Sprawdźmy kilka podstawowych przypadków:

test=# select generate_series(1,5) as d;
 d 
---
 1
 2
 3
 4
 5
(5 rows)

test=# select generate_series(5,1,-1) as d;
 d 
---
 5
 4
 3
 2
 1
(5 rows)

test=# select generate_series('2010-03-01'::timestamp, '2010-03-05', '24 hours');
   generate_series   
---------------------
 2010-03-01 00:00:00
 2010-03-02 00:00:00
 2010-03-03 00:00:00
 2010-03-04 00:00:00
 2010-03-05 00:00:00
(5 rows)

test=# select generate_series(1,2) as id , date(generate_series('2010-03-01'::timestamp, '2010-03-05', '24 hours'));
 id |    date    
----+------------
  1 | 2010-03-01
  2 | 2010-03-02
  1 | 2010-03-03
  2 | 2010-03-04
  1 | 2010-03-05
  2 | 2010-03-01
  1 | 2010-03-02
  2 | 2010-03-03
  1 | 2010-03-04
  2 | 2010-03-05
(10 rows)

Funkcja jest także bardzo pomocna przy generowaniu danych testowych. Wystarczy, że użyjemy razem z nią funkcji random:

test=# select r from (select generate_series(1,5) as id, round(random()*100) as r) as foo;
 r  
----
 76
 93
 91
 68
 34
(5 rows)

Generowanie kolejno zdefiniowanych ciągu znaków:

test=# SELECT myarray[i] as element
 FROM  (SELECT ARRAY['one','two','tree','four','five','six'] As myarray) as foo
  CROSS JOIN generate_series(1, 6) As i;
 element 
---------
 one
 two
 tree
 four
 five
 six
(6 rows)

A co z tekstami? Jeśli wystarczy, że będą to ciągi znaków w pełni losowych, to możemy użyć do tego jeszcze funkcji substring i md5:

test=# select c2 from (select generate_series(1,10) as id, substring(md5(random()::text) from 0 for 20) as c2) foo;
         c2          
---------------------
 81b7985f41be65f56cb
 dd516dc4e04d39b3356
 b40721cfc0738bf98ed
 5ca0de341103fbb87de
 2e8b3a407257743261f
 7e45b2df06fb93d0821
 cbec578dcd1a86366fb
 83fecb8953dc5b07813
 6317b12d53e5da59d87
 ad35dc89a0772e49684
(10 rows)

Przejdźmy jednak do konkretnego przypadku raportu, który potrzebuję wygenerować. W tabeli table1 trzymam informacje o klientach, którzy pojawili się w danym czasie (kolumna: appeared_at, typ timestamp). Ja jednak potrzebuję wiedzieć tylko ile było takich osób danego dnia. Raport wymaga aby policzyć klientów dla wszystkich dni (więc dla dni, gdzie klienci się nie pojawili potrzebuję wyświetlić 0). Dlatego potrzebuję użyć RIGHT JOINa z tabelką, która jest serią podstawowych danych o datach z wybranego przedziału i zwykłym iterowaniem (dla lepszego pokazaniu wyniku).

test=# SELECT seq.id, seq.date, concat(data.count,0) 
FROM (
  SELECT date(appeared_at) AS date, count(*) 
  FROM table1 
  WHERE appeared_at BETWEEN '2010-03-01' AND '2010-03-10' 
  GROUP BY DATE(appeared_at)
) data 
RIGHT JOIN (
  SELECT generate_series(1,10) as id, DATE(generate_series('2010-03-01'::timestamp, '2010-03-10', '24 hours')) AS date
) seq 
ON seq.date = data.date 
ORDER BY seq.id;
 id |    date    | concat 
----+------------+--------
  1 | 2010-03-01 | 20400
  2 | 2010-03-02 | 12330
  3 | 2010-03-03 | 29770
  4 | 2010-03-04 | 15500
  5 | 2010-03-05 | 15170
  6 | 2010-03-06 | 22150
  7 | 2010-03-07 | 140620
  8 | 2010-03-08 | 106960
  9 | 2010-03-09 | 34480
 10 | 2010-03-10 | 0
(10 rows)

Proste, prawda? :-)

Zapytania ze słowem WITH - zapytania CTE w PostgreSQL

Coś ostatnie kilka miesięcy za bardzo skupiłam się na MySQL. Pora to zmienić i napisać coś o PostgreSQL. Chciała bym się z Wami podzielić informacją na temat zapytań ze słowem kluczowym WITH czyli zapytań CTEs (Common Table Expressions, link do dokumentacji znajdziecie tutaj).

Zobaczmy jakiś przykład, aby zobrazować sobie problem, z którym mamy się zmierzyć. W tabeli table1 przechowuje informację o numerach telefonów, ich czasu pojawienia się i zweryfikowania, do jakiego areacodu, operatora i jakiej grupy należą. Ja na to miast potrzebuje wyciągnąć informację o 10 numerach telefonu, które zostały zweryfikowane najwcześniej, ale dla areacodów zweryfikowanych najpóźniej. Może mały realny przypadek, no ale chyba mi wybaczycie :-P

test=# WITH last10_areacode AS (
   select max(verified_at), areacode 
   from table1 
   group by areacode 
   order by 1 DESC 
   limit 10),
top_mobile_number AS (
   select min(verified_at) as min_verified_at, mobile_number 
   from table1 
   where areacode in (select areacode from last10_areacode) 
   group by mobile_number 
   order by 1 ASC
   limit 10)
select template_id as tid,template_info_id as tinfoid,mobile_number,appeared_at,verified_at,external_operator_id,areacode 
from table1 
where verified_at IN (select min_verified_at from top_mobile_number) 
limit 10;
 tid | tinfoid | mobile_number |     appeared_at     |     verified_at     | operator | areacode 
-----+---------+---------------+---------------------+---------------------+----------+----------
 239 |      10 | 18017255282   | 2007-07-13 19:55:09 | 2007-07-13 19:55:09 |    31002 | 801
  91 |      10 | 18012057665   | 2007-04-06 02:20:27 | 2007-04-06 02:20:27 |    31005 | 801
  91 |      10 | 17087032052   | 2007-04-06 18:16:04 | 2007-04-06 18:16:04 |    31002 | 708
  91 |      10 | 17086465706   | 2007-04-04 02:13:34 | 2007-04-04 02:13:34 |    31005 | 708
  91 |      10 | 17082885755   | 2007-04-11 16:18:08 | 2007-04-11 16:18:08 |    31002 | 708
  91 |      10 | 12144035942   | 2007-07-14 18:30:24 | 2007-07-14 18:30:24 |    31005 | 214
 138 |      10 | 19373612169   | 2007-04-16 16:23:25 | 2007-04-16 16:23:25 |    31005 | 937
 138 |      10 | 14059244193   | 2007-04-18 02:13:48 | 2007-04-18 02:13:48 |    31005 | 405
 138 |      10 | 16193475127   | 2007-04-23 19:31:51 | 2007-04-23 19:31:51 |    31005 | 619
  91 |      10 | 12147144210   | 2007-07-24 23:59:08 | 2007-07-24 23:59:08 |    31002 | 214
(10 rows)

Dzięki słowu WITH tworzymy zapytanie pomocnicze, którego wynik jest zapisywany do tabeli tymczasowej, istniejącej tylko i wyłącznie na czas działania całego zapytania. Jeśli dobrze zauważyliście, to już wiecie, że wynik zapytania last10_areacode został użyty do wygenerowania wyniku dla tabeli top_mobile_number, a ten wynik dla głównego zapytania. Idźmy jednak dalej.

Zapytania RECURSIVE (ale tak na prawdę iteracyjne!)

Razem ze słowem WITH możemy użyć RECURSIVE. Dzięki temu będziemy mieli możliwość odwołania się do własnego wyniku.

Aby wytłumaczyć o co mi chodzi, stworzę prostą tabelę, zawierającą informację o kategoriach. Kategoria może być główną (kolumna parent jest pusta) lub podkategorią. Ilość poziomów podkategorii jest dowolna.

test=# create table category (id serial, name varchar(255), parent integer, created_at timestamp);
CREATE TABLE
test=# \d category
                                     Table "public.category"
   Column   |            Type             |                       Modifiers                       
------------+-----------------------------+-------------------------------------------------------
 id         | integer                     | not null default nextval('category_id_seq'::regclass)
 name       | character varying(255)      | 
 parent     | integer                     | 
 created_at | timestamp without time zone | 

test=# select * from category;
 id |      name      | parent |         created_at         
----+----------------+--------+----------------------------
  1 | top category 1 |        | 2015-04-14 18:31:32.312868
  2 | top category 2 |        | 2015-04-14 18:31:37.280778
  3 | top category 3 |        | 2015-04-14 18:31:40.952949
  4 | top category 4 |        | 2015-04-14 18:31:45.809776
  5 | top category 5 |        | 2015-04-14 18:31:49.808637
  6 | category 6     |      2 | 2015-04-14 18:32:30.0089
  7 | category 7     |      2 | 2015-04-14 18:32:39.504864
  8 | category 8     |      2 | 2015-04-14 18:32:43.632998
  9 | category 9     |      4 | 2015-04-14 18:32:59.016916
 10 | category 10    |      6 | 2015-04-14 18:33:36.224982
 11 | category 11    |      7 | 2015-04-14 18:34:05.088904
 12 | category 12    |     11 | 2015-04-14 18:35:00.69685
(12 rows)

Aby wybrać rekordy, które dotyczą tylko i wyłącznie kategorii głównej o id = 2 i jej dzieci, stworzyłam następujące zapytanie:

test=# WITH RECURSIVE get_parents_category(id, name, parent) AS (
  SELECT id, name, parent FROM category WHERE parent = 2 OR id = 2
UNION    
  SELECT c.id, c.name, c.parent 
  FROM get_parents_category gpc, category c 
  WHERE gpc.id = c.parent)
SELECT * FROM get_parents_category;
 id |      name      | parent 
----+----------------+--------
  2 | top category 2 |       
  6 | category 6     |      2
  7 | category 7     |      2
  8 | category 8     |      2
 10 | category 10    |      6
 11 | category 11    |      7
 12 | category 12    |     11
(7 rows)

Aby wytłumaczyć co się dzieję w tej konstrukcji zapytania, po patrzcie na poniższy psełdo kod:

WITH RECURSIVE RECURSIVE_QUERY_NAME(ARG1, ARG2) AS (
  START_QUERY
UNION
  LOOP_QUERY
)
MAIN_QUERY;

Aby zwrócić do MAIN_QUERY (to wywołanie RECURSIVE_QUERY_NAME) wynik działania, PostgreSQL wykonuje następujące kroki:

1. Wywołanie zapytanie START_QUERY, a wynik zapisywany jest w tabeli pracującej (working table) i do zapytania LOOP_QUERY.
2. Dopóki w tabeli pracującej (working table) są jakieś rekordy, powtarzane są czynności:
1. Wywołanie zapytania LOOP_QUERY. Wynik jest zapisywany i w zależności od typu złączenia UNION [ALL], rekordy zduplikowane są usuwane lub nie. Wszystkie wiersze zostają wstawione jako wynik działania LOOP_QUERY oraz tymczasowej tabeli intermediate table.
2. Zamiana zawartości tabeli pracującej z zawartością tabeli intermediate, a następnie opróżnienie tabeli intermediate. 

Zapytanie LOOP_QUERY jest wykonywane tyle razy, ile będzie zwracać jakiś wynik. W MAIN_QUERY możemy dodatkowo łączyć inne tabele lub po prostu filtrować wyniki.

Modyfikacja danych

PostgreSQL daje nam także możliwość modyfikacji danych (INSERT, UPDATE lub DELETE) przy użyciu słowa kluczowego WITH.

Nie ma możliwości użycia polecenia modyfikującego w zapytaniu RECURSIVE, ale możemy zwrócić rekordy poszukiwane do tej operacji.

Spróbuję usunąć wszystkie wiersze z tabeli category, które dotyczą głównej kategorii o id=2. Zacznijmy od sprawdzenia zawartości całej tabeli:

test=# select * from category;
 id |      name      | parent |         created_at         
----+----------------+--------+----------------------------
  1 | top category 1 |        | 2015-04-14 18:31:32.312868
  2 | top category 2 |        | 2015-04-14 18:31:37.280778
  3 | top category 3 |        | 2015-04-14 18:31:40.952949
  4 | top category 4 |        | 2015-04-14 18:31:45.809776
  5 | top category 5 |        | 2015-04-14 18:31:49.808637
  6 | category 6     |      2 | 2015-04-14 18:32:30.0089
  7 | category 7     |      2 | 2015-04-14 18:32:39.504864
  8 | category 8     |      2 | 2015-04-14 18:32:43.632998
  9 | category 9     |      4 | 2015-04-14 18:32:59.016916
 10 | category 10    |      6 | 2015-04-14 18:33:36.224982
 11 | category 11    |      7 | 2015-04-14 18:34:05.088904
 12 | category 12    |     11 | 2015-04-14 18:35:00.69685
(12 rows)

test=# WITH RECURSIVE delete_old_category(id) AS (
   SELECT id FROM category WHERE parent = 2 OR id = 2
UNION
   SELECT c.id FROM delete_old_category foo, category c WHERE foo.id = c.parent)
DELETE FROM category WHERE id IN (SELECT id FROM delete_old_category);
DELETE 7

test=# select * from category;
 id |      name      | parent |         created_at         
----+----------------+--------+----------------------------
  1 | top category 1 |        | 2015-04-14 18:31:32.312868
  3 | top category 3 |        | 2015-04-14 18:31:40.952949
  4 | top category 4 |        | 2015-04-14 18:31:45.809776
  5 | top category 5 |        | 2015-04-14 18:31:49.808637
  9 | category 9     |      4 | 2015-04-14 18:32:59.016916
(5 rows)

Zapytanie usuwające stare kategorie jest bardzo podobne do powyższego SELECTa, bo wyciągnięte dane z zapytania RECURSIVE, są użyte w zapytaniu głównym do usunięcia interesujących nas rekordów po kluczu głównym.

Czasami jednak przydało by się zapisać gdzieś usunięte rekordy do jakiś logów (w końcu może jednak w przyszłości się okazać, że były to dość istotne informacje). W takim przypadku PostgreSQL także przyjdzie nam z pomocą w jednym zapytaniu.

test=# select * from table1 where areacode = '654';
 template_id | template_info_id | mobile_number |     appeared_at     |     verified_at     |  alias_name  |     created_at      |     updated_at      | fake_id | external_operator_id | areacode 
-------------+------------------+---------------+---------------------+---------------------+--------------+---------------------+---------------------+---------+----------------------+----------
        4019 |               10 | 16549684775   | 2010-03-11 07:23:53 | 2010-03-11 07:23:53 | OTHERS       | 2011-03-01 20:16:32 | 2011-03-01 20:16:32 | 1500323 |                31002 | 654
        3687 |               10 | 16543245678   | 2010-04-05 20:25:08 | 2010-04-05 20:25:08 | AIRBENDERVID | 2011-03-01 21:15:02 | 2011-03-01 21:15:02 | 2140545 |                    0 | 654
        2977 |               10 | 16546547654   | 2009-10-12 00:06:20 | 2009-10-12 00:06:20 | PARAVID      | 2011-03-01 19:17:56 | 2011-03-01 19:17:56 | 2396877 |                    0 | 654
(3 rows)

test=# 
  WITH move_rows AS (
    DELETE FROM table1 where areacode = '654'
    RETURNING * )
  INSERT INTO table1_log
  SELECT * FROM move_rows;
INSERT 0 3

test=# select * from table1_log;
 template_id | template_info_id | mobile_number |     appeared_at     |     verified_at     |  alias_name  |     created_at      |     updated_at      | fake_id | external_operator_id | areacode 
-------------+------------------+---------------+---------------------+---------------------+--------------+---------------------+---------------------+---------+----------------------+----------
        4019 |               10 | 16549684775   | 2010-03-11 07:23:53 | 2010-03-11 07:23:53 | OTHERS       | 2011-03-01 20:16:32 | 2011-03-01 20:16:32 | 1500323 |                31002 | 654
        3687 |               10 | 16543245678   | 2010-04-05 20:25:08 | 2010-04-05 20:25:08 | AIRBENDERVID | 2011-03-01 21:15:02 | 2011-03-01 21:15:02 | 2140545 |                    0 | 654
        2977 |               10 | 16546547654   | 2009-10-12 00:06:20 | 2009-10-12 00:06:20 | PARAVID      | 2011-03-01 19:17:56 | 2011-03-01 19:17:56 | 2396877 |                    0 | 654
(3 rows)

W zapytaniu ze słowem kluczowym WITH usuwane są rekordy z tabeli table1, a przy pomocy słowa RETURNING, są zwracane do zapytania głównego. Tam są wstawiane do tabeli table1_log.

Szkoda, że MySQL nie wspiera tego typu zapytań. Przydałyby mi się w pracy :-P

Enum type w MySQLu

Jeśli kiedykolwiek używałeś w swojej bazie danych MySQL kolumn z typem ENUM, być może zauważyłeś kilka niemiłych rzeczy, których bez czytania dokumentacji nie spodziewałbyś się zobaczyć.

Przypuśćmy, że mamy następującą tak zdefiniowaną tabelę, z kolumnami typu enum:

mysql> show create table test_enum \G
*************************** 1. row ***************************
       Table: test_enum
Create Table: CREATE TABLE `test_enum` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `e1` enum('Y','F','') DEFAULT NULL,
  `e2` enum('Y','F') NOT NULL DEFAULT 'Y',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Wstawmy kilka wierszy:

mysql> insert into test_enum (e1,e2) VALUES ('Y','Y'),('F','F'),('',''),(NULL,NULL),('N','N');
Query OK, 5 rows affected, 4 warnings (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 4

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'e2' at row 3 |
| Warning | 1048 | Column 'e2' cannot be null              |
| Warning | 1265 | Data truncated for column 'e1' at row 5 |
| Warning | 1265 | Data truncated for column 'e2' at row 5 |
+---------+------+-----------------------------------------+
4 rows in set (0.00 sec)

mysql> select * from test_enum;
+----+------+----+
| id | e1   | e2 |
+----+------+----+
|  1 | Y    | Y  |
|  2 | F    | F  |
|  3 |      |    |
|  4 | NULL |    |
|  5 |      |    |
+----+------+----+
5 rows in set (0.00 sec)

Zauważyliście coś ciekawego? Dwa pierwsze wpisy raczej nikogo nie dziwią - w końcu są poprawne. Jednak gdy przyjrzymy się trzem następnym, zauważymy że:

• insert stringa pustego '' do kolumny e2 powoduje wstawienie tej właśnie wartości, mimo iż nie ma jej zdefiniowanej jako wartość dozwolona,
• insert NULLa do kolumny e2 powoduje wstawienie pustego stringa mimo iż mamy zdefiniowaną wartość domyślną,
• insert wartości niepoprawnych powodują ustawienie na puste stringi.

Zanim omówię powyższe 'niespodzianki' wstawmy jeszcze dwa rekordy:

mysql> insert into test_enum (e1) VALUES (NULL);
Query OK, 1 row affected (0.02 sec)

mysql> insert into test_enum (id) VALUES (7);
Query OK, 1 row affected (0.05 sec)

mysql> select * from test_enum where id > 5;
+----+------+----+
| id | e1   | e2 |
+----+------+----+
|  6 | NULL | Y  |
|  7 | NULL | Y  |
+----+------+----+
2 rows in set (0.00 sec)

Tym razem nasze dane są takie jakich byśmy się spodziewali.

Cechy typu ENUM, których być może się nie spodziewaliście:

• Typ ENUM jest obiektem, który definiuje wartości dozwolone w formie stringów, ale tak na prawdę wartość zapisywana jest przy pomocy indexów typu integer, zaczynając od 1. Weźmy pod uwagę kolumnę e1 naszej tabeli test_enum. Wartości ('Y','F','') są zapisywane jako (1,2,3). Poniżej przedstawię kilka przykładów SELECTów.
• Wartość 0 jest zarezerwowana dla pustego stringa - wartość błędu. 
• Wartość NULL jest zapisywana jako NULL lub wartość błędu (jeśli kolumna jest zdefiniowana jako NOT NULL).
• Jeśli zdfiniujemy enum jako ciagi znaków numerycznych, to wartości wstawiane do takiej kolumny są traktowane jako indexy dopuszczalnych wartości (zobacz poniższy przykład 2, dla tablicy test_enum2).

Przykład 1: Przykłady wyszukiwania wierszy po wartościach lub indexach dopuszczalnych wartości:

mysql> SELECT * FROM test_enum WHERE e1 = '';
+----+------+----+
| id | e1   | e2 |
+----+------+----+
|  3 |      |    |
|  5 |      |    |
+----+------+----+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test_enum WHERE e1 = 0;
+----+------+----+
| id | e1   | e2 |
+----+------+----+
|  5 |      |    |
+----+------+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM test_enum WHERE e1 = 3;
+----+------+----+
| id | e1   | e2 |
+----+------+----+
|  3 |      |    |
+----+------+----+
1 row in set (0.00 sec)

Przykład 2: Inny przykład tablicy z columną typu enum:

mysql> show create table test_enum2 \G
*************************** 1. row ***************************
       Table: test_enum2
Create Table: CREATE TABLE `test_enum2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `e1` enum('0','1','2','3') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO test_enum2 (e1) VALUES (1),('1'),('4');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test_enum2;
+----+------+
| id | e1   |
+----+------+
|  1 | 0    |
|  2 | 1    |
|  3 | 3    |
+----+------+
3 rows in set (0.00 sec)

Przykład 3: Aby podejrzeć wartość string i jej index wystraczy:

mysql> select id, e1, e1+0 from test_enum2;
+----+------+------+
| id | e1   | e1+0 |
+----+------+------+
|  1 | 0    |    1 |
|  2 | 1    |    2 |
|  3 | 3    |    4 |
+----+------+------+
3 rows in set (0.00 sec)

Każdy nowy element wartości dopuszczalnej powinien być dopisany bez zmiany kolejności. W ten sposób nowa wartość będzie miała nadaną kolejną wartość indexu. W przeciwnym przypadku tablica będzieme musiała być przepisana aby starym wartościom nadać nowe indexy. Dla bardzo dużych tabel może być to czasochłonna opercja, a i chyba nie potrzebna.

INSERTing z opcją ON DUPLICATE KEY UPDATE w MySQLu

Zwróciliście kiedyś uwagę na działanie polecenia INSERT ... ON DUPLICATE KEY UPDATE i mały problem z 'luką' w wartościach kolumny, która jest AUTO_INCREMENT dla InnoDB. Pewnie nie, ale jeśli dziennie wstawiamy tysiące rekordów przy pomocy tej opcji, to po jakimś czasie być może będziemy stać przed problemem wyczerpania dozwolonych wartości dla takiej kolumny. Ale zacznijmy od przykładowej tabeli:

(owa@localhost) [test]> show create table multi_insert_test \G
*************************** 1. row ***************************
       Table: multi_insert_test
Create Table: CREATE TABLE `multi_insert_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_c1_c2` (`c1`,`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Tabela posiada dwa klucze unikalne: dla klucza głównego (kolumna id) i dla klucza unikalnego uniq_c1_c2 (kolumny c1,c2). Zacznę teraz wstawianie nowych rekordów dla wszystkich wartości kolumn oprócz tej z AUTO_INCREMENT:

(owa@localhost) [test]> select * from multi_insert_test;
Empty set (0.00 sec)

(owa@localhost) [test]> INSERT  INTO multi_insert_test (c1,c2) VALUES (2,3),(3,2);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

(owa@localhost) [test]> select * from multi_insert_test;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    2 |    3 | NULL |
|  2 |    3 |    2 | NULL |
+----+------+------+------+
2 rows in set (0.00 sec)

(owa@localhost) [test]> INSERT  INTO multi_insert_test (c1,c2,c3) VALUES (2,3,333) ON DUPLICATE KEY UPDATE c1=VALUES(c1),c2=VALUES(c2),c3=VALUES(c3);
Query OK, 2 rows affected (0.05 sec)

(owa@localhost) [test]> select * from multi_insert_test;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    2 |    3 |  333 |
|  2 |    3 |    2 | NULL |
+----+------+------+------+
2 rows in set (0.00 sec)

Do tej pory nie stało się nic co mogło by nas zaskoczyć. Ale po wstawieniu rekordu z wartościami istniejącymi już na unikalnym kluczu, 'luka' w kolumnie AUTO_INCREMENT jest już zauważalna:

(owa@localhost) [test]> insert into multi_insert_test (c1,c2,c3) VALUES (5,6,22);
Query OK, 1 row affected (0.03 sec)

(owa@localhost) [test]> select * from multi_insert_test;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    2 |    3 |  333 |
|  2 |    3 |    2 | NULL |
|  4 |    5 |    6 |   22 |
+----+------+------+------+
3 rows in set (0.00 sec)

Wstawiamy tym razem rekord z danymi już istaniejącymi dla naszych kluczy z zdefinicją jak zupdatować klucz główny:

mysql> INSERT  INTO multi_insert_test (id,c1,c2,c3) VALUES (1,2,3,334) ON DUPLICATE KEY UPDATE id=VALUES(id),c1=VALUES(c1),c2=VALUES(c2),c3=VALUES(c3);
Query OK, 2 rows affected (0.04 sec)

mysql> select * from multi_insert_test;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    2 |    3 |  334 |
|  2 |    3 |    2 | NULL |
|  4 |    5 |    6 |   22 |
+----+------+------+------+
3 rows in set (0.00 sec)

mysql> insert into multi_insert_test (c1,c2,c3) VALUES (6,6,23);
Query OK, 1 row affected (0.04 sec)

mysql> select * from multi_insert_test;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    2 |    3 |  334 |
|  2 |    3 |    2 | NULL |
|  4 |    5 |    6 |   22 |
|  5 |    6 |    6 |   23 |
+----+------+------+------+
4 rows in set (0.00 sec)

Tym razem luka nie powstała, ale tylko dlatego, że nowy rekord miał  zdefiniowaną wartość dla kolumny AUTO_ICREMENT.

A czy zauważyliście może ile rekordów 'brało udział' przy insertach z opcją ON DUPLICATE KEY UPDATE?

mysql> INSERT  INTO multi_insert_test (id,c1,c2,c3) VALUES (1,2,3,334) ON DUPLICATE KEY UPDATE id=VALUES(id),c1=VALUES(c1),c2=VALUES(c2),c3=VALUES(c3);
Query OK, 2 rows affected (0.04 sec)

Czyżby MySQL dla InnoDB tak na prawdę nie updatował wiersza tylko go najpierw usuwał, a później wstawiał na nowo?! Tak czy inaczej 'nowa' wartość kolumny auto_increment jest równa wartości zwracanej przez funkcję LAST_INSERT_ID();

Inną ciekawą rzeczą jaką zauważyłam przy tym poleceniu jest ilość updatowanych wierszy gdy tabela ma więcej niż jeden index unikalny:

mysql> show create table multi_insert_test2\G
*************************** 1. row ***************************
       Table: multi_insert_test2
Create Table: CREATE TABLE `multi_insert_test2` (
  `id` int(11) DEFAULT NULL,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  UNIQUE KEY `uniq_c1_c2` (`c1`,`c2`),
  UNIQUE KEY `uniq_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from multi_insert_test2;
+------+------+------+------+
| id   | c1   | c2   | c3   |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
|    2 |    2 |    2 |    2 |
|    3 |    3 |    3 |    3 |
|    4 |    4 |    4 |    4 |
|    5 |    1 |    2 |    1 |
+------+------+------+------+
5 rows in set (0.00 sec)

mysql> insert into multi_insert_test2 (id, c1,c2,c3) values (5,1,1,1000) ON DUPLICATE KEY UPDATE c3=1000;
Query OK, 2 rows affected (0.05 sec)

Czego byście się spodziewali po wykonaniu powyższego inserta? Żaden z wierszy nie ma jednocześnie wartości 5 dla kolumny id i wartości 1,1 dla kolumn odpowiednio c1,c2:

mysql> select * from multi_insert_test2;
+------+------+------+------+
| id   | c1   | c2   | c3   |
+------+------+------+------+
|    1 |    1 |    1 | 1000 |
|    2 |    2 |    2 |    2 |
|    3 |    3 |    3 |    3 |
|    4 |    4 |    4 |    4 |
|    5 |    1 |    2 |    1 |
+------+------+------+------+
5 rows in set (0.00 sec)

Tak na prawdę to MySQL wykonał następujące polecenie:

UPDATE multi_insert_test2 SET c3 = 1001 WHERE id = 5 OR (c1 = 1 AND c2 = 1) LIMIT 1;

Dla zaciekawionych polecam zaznajomienie się z dokumentacją, którą możecie znaleść tutaj.

Import danych do MySQL lub PostgreSQL

Zacznijmy od MySQLa

Kiedyś napisałam dwa krótkie posty na temat exportu danych z MySQLa (znajdziecie go tutaj) i z PostgreSQLa (przeczytacie go tutaj) ale nie napisałam jak wykonać ich import. Czas uzupełnić tą lukę.

Zaimportujmy dane do tabeli test1.

mysql> show create table test1 \G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `c` int(11) DEFAULT NULL,
  `t` int(11) DEFAULT NULL,
  `cn` varchar(2) DEFAULT NULL,
  `d` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)



ela@skyler:~/Downloads$ mysqlimport -uroot -proot --lines-terminated-by='\n' --fields-terminated-by='\t' --local test test1.csv
test.test1: Records: 9765275  Deleted: 0  Skipped: 0  Warnings: 29193
ela@skyler:~/Downloads$ ll
total 212080
drwxr-xr-x  2 ela ela      4096 sty  5 13:15 ./
drwxr-xr-x 19 ela ela      4096 sty  5 13:15 ../
-rw-rw-r--  1 ela ela   1914553 sty  5 12:40 arch_geoip_201411.csv.tar.gz
-rw-rw-r--  1 ela ela   1299844 sty  5 12:39 PerconaToolkit-2.2.12.pdf
-r-xr-xr-x  1 ela ela 213941449 gru 30 16:47 test1.csv*
ela@skyler:~/Downloads$ wc -l test1.csv 
9765275 test1.csv

Do importu danych najlepiej użyć dedykowanej do tego binarki: mysqlimport, której dokumentację znajdziecie tutaj, a i wspominałam o tej binarce w innym poście, na temat dostępnych programów w instalacji serwera MySQL (Binarki MySQLa).

W trakcie importu danych mamy jednak dane, które musimy odpowiednio najpierw zapisać. Przykładowo znak NULL, który jak wcześniej zauważyliście spowodował wystąpienie Warningów przy imporcie, nie możemy zapisać jako string NULL tylko jako odpowiedni znak \N (link do dokumentacji). Dopiero wtedy zostanie poprawnie zaimportowany do MySQLa. Oto przykładowa zawartość pliku CSV:

ela@skyler:~/Downloads$ head test1.csv 
45 42 NULL 2014-11-01
45 42 NULL 2014-11-02
45 42 NULL 2014-11-06
45 42 NULL 2014-11-07
45 42 NULL 2014-11-08
45 42 NULL 2014-11-09
45 42 NULL 2014-11-09
45 42 NULL 2014-11-10
45 42 NULL 2014-11-10
45 42 NULL 2014-11-11

Importując je do naszej tabeli test1, dane z kolumny cn zostaną obcięte do dwóch znaków. W dodatku te dane zostały potraktowane jako stringi, a nie jak powinny jako znak NULL. Dlatego musimy poprawić nasz plik zanim zaczniemy proces importu:

ela@skyler:~/Downloads$ cat test1.csv | replace NULL '\N' > test2.csv
ela@skyler:~/Downloads$ head test2.csv 
45 42 N 2014-11-01
45 42 N 2014-11-02
45 42 N 2014-11-06
45 42 N 2014-11-07
45 42 N 2014-11-08
45 42 N 2014-11-09
45 42 N 2014-11-09
45 42 N 2014-11-10
45 42 N 2014-11-10
45 42 N 2014-11-11
ela@skyler:~/Downloads$ time mysqlimport -uroot -proot --lines-terminated-by='\n' --fields-terminated-by='\t' --local --columns=c,t,cn,d test test2.csv
test.test2: Records: 9765275  Deleted: 0  Skipped: 0  Warnings: 0

real 2m18.113s
user 0m0.059s
sys 0m0.188s

Dane został tym razem zapisane i zaimportowane do tabeli test2. Jeśli dobrze zauważyliście to tym razem wskazałam do jakich kolumn mają zostać zaimportowane dane przy pomocy opcji --column (-C).

Teraz czas przyszedł na PostgreSQL

No cóż. Nie ma sensu aby wspominać o tym ponownie. Ciąg dalszy tego posta był by tylko kopią innego napisanego przeze mnie jakiś czas temu o PGLoaderze (PGloader - narzędzie do importu danych). Mając prawa administracyjne do serwera, na którym stoi ten serwer, możecie bezpośrednio użyć polecenia COPY, a wcześniej przekopiować ten plik na ten serwer.

Miłej zabawy :-)

Wprowadzenie do replikacji master-slave w Redisie

W poprzednim poście (którego napisałam już baaardzo dawno temu) wprowadziłam nas do tematyki Redisa - post możecie go przeczytać tutaj. Główny wątek polegał na zapoznanu się z możliwościami tego systemu, który stał się bardzo popularny ze względu na swoją szybkość działania.

Administratorów może jednak bardziej zastanawiać jak postawić taki system w replikacji. Zacznijmy od wymienienia kilku podstawowych faktów na ten temat:

• replikacja jest asynchroniczna
• master może mieć wiele slavów, które są tylko read-only
• nie istnieje replikacja master-master
• replikacja jest non-blocking po stronie mastera (gdy podpinany jest nowy slave), a także po stronie slava (dopóki synchronizacja jest inicjalizowana i wykorzystuje plik redis.conf, wykorzystywane są stare dane, który muszą zostać usunięte i zastąpone nowymi).

Przejdźmy do zadań praktycznych. Zacznijmy od uruchomienia 3 serwerów, na tej samej maszynie, które będą działały w replikacji slave-master-slave. Każdy z serwerów będzie miał własny plik konfiguracyjny z własnymi ścieżkami do katalogu danych czy zdefiniowanym numerem portu. Oto jeden z plików konfiguracyjnych:

pidfile /var/run/redis-6389.pid                               # ścieżka do PID fila
port 6389                                                     # port
loglevel notice
logfile logfile.log
databases 16                                                  # liczba baz danych zaczynając od liczby 0
save 60 10000                                                 # zapis bazy na dysk: save  
dbfilename dump-6389.rdb                                      # nazwa pliku z dampem baz danych
dir /home/ela/work/redis_data/redis-6389                      # ścieżka do katalogu z danymi
maxclients 100

# Replikacja
slaveof 127.0.0.1 6379                                        # jak polaczyc sie do mastera 
slave-priority 100

Jeśli uruchomimy serwer:

./../redis-2.8.17/src/redis-server /home/ela/work/redis_data/redis-6379/redis-6379-small.conf

w logu zobaczymy następującą informację:

[4026] 28 Oct 19:19:06.832 # Server started, Redis version 2.8.17
[4026] 28 Oct 19:19:06.832 # WARNING overcommit_memory is set to 0! Background save may fail under low memory condition. To fix this issue add 'vm.overcommit_memory = 1' to /etc/sysctl.conf and then reboot or run the command 'sysctl vm.overcommit_memory=1' for this to take effect.
[4026] 28 Oct 19:19:06.832 * DB loaded from disk: 0.000 seconds
[4026] 28 Oct 19:19:06.832 * The server is now ready to accept connections on port 6379
[4026 | signal handler] (1414520349) Received SIGINT scheduling shutdown...
[4026] 28 Oct 19:19:09.334 # User requested shutdown...
[4026] 28 Oct 19:19:09.334 * Saving the final RDB snapshot before exiting.
[4026] 28 Oct 19:19:09.376 * DB saved on disk
[4026] 28 Oct 19:19:09.376 # Redis is now ready to exit, bye bye...
                _._                                                  
           _.-``__ ''-._                                             
      _.-``    `.  `_.  ''-._           Redis 2.8.17 (00000000/0) 64 bit
  .-`` .-```.  ```\/    _.,_ ''-._                                   
 (    '      ,       .-`  | `,    )     Running in stand alone mode
 |`-._`-...-` __...-.``-._|'` _.-'|     Port: 6379
 |    `-._   `._    /     _.-'    |     PID: 4029
  `-._    `-._  `-./  _.-'    _.-'                                   
 |`-._`-._    `-.__.-'    _.-'_.-'|                                  
 |    `-._`-._        _.-'_.-'    |           http://redis.io        
  `-._    `-._`-.__.-'_.-'    _.-'                                   
 |`-._`-._    `-.__.-'    _.-'_.-'|                                  
 |    `-._`-._        _.-'_.-'    |                                  
  `-._    `-._`-.__.-'_.-'    _.-'                                   
      `-._    `-.__.-'    _.-'                                       
          `-._        _.-'                                           
              `-.__.-'                                               

[4029] 28 Oct 19:19:16.045 # Server started, Redis version 2.8.17
[4029] 28 Oct 19:19:16.045 # WARNING overcommit_memory is set to 0! Background save may fail under low memory condition. To fix this issue add 'vm.overcommit_memory = 1' to /etc/sysctl.conf and then reboot or run the command 'sysctl vm.overcommit_memory=1' for this to take effect.
[4029] 28 Oct 19:19:16.045 * DB loaded from disk: 0.000 seconds
[4029] 28 Oct 19:19:16.045 * The server is now ready to accept connections on port 6379

W logach slava zobaczymy dodatkowo informację o replikacji:

[4054] 28 Oct 19:22:23.536 * Connecting to MASTER 127.0.0.1:6379
[4054] 28 Oct 19:22:23.536 * MASTER <-> SLAVE sync started
[4054] 28 Oct 19:22:23.536 * Non blocking connect for SYNC fired the event.
[4054] 28 Oct 19:22:23.537 * Master replied to PING, replication can continue...
[4054] 28 Oct 19:22:23.537 * Partial resynchronization not possible (no cached master)
[4054] 28 Oct 19:22:23.541 * Full resync from master: 4430276f00317e4fc1b84bedbb1dbb288949b175:1
[4054] 28 Oct 19:22:23.642 * MASTER <-> SLAVE sync: receiving 35 bytes from master
[4054] 28 Oct 19:22:23.642 * MASTER <-> SLAVE sync: Flushing old data
[4054] 28 Oct 19:22:23.642 * MASTER <-> SLAVE sync: Loading DB in memory
[4054] 28 Oct 19:22:23.642 * MASTER <-> SLAVE sync: Finished with success

Testujemy slavy:

ela@skyler:~/work$ ./redis-2.8.17/src/redis-cli -p 6389
127.0.0.1:6389> 
127.0.0.1:6389> 
127.0.0.1:6389> keys *
1) "test"
2) "test2"
127.0.0.1:6389> set test3 hhhh
(error) READONLY You can't write against a read only slave.

Jeśli z jakiegoś powodu dojdzie do zatrzymania serwera primary (master) w logach slava będziemy mogli zobaczyć następujący komunikat:

[4054] 28 Oct 19:31:21.432 * Connecting to MASTER 127.0.0.1:6379
[4054] 28 Oct 19:31:21.432 * MASTER <-> SLAVE sync started
[4054] 28 Oct 19:31:21.432 # Error condition on socket for SYNC: Connection refused

Ale jak mamy promować nowy master w przypadku padnięcia starego?
Pomocne nam będzie Redis Sentinel. Jest to system stworzony aby pomóc w zarządzaniu instancjami Redisa (szczegółowe informacje znajdziecie na stronie dokumentacji tutaj). Jest używane do:

• monitorowania
• notyfikacji
• automatyczny failover
• konfiguracji

Skonfigurujmy taki system monitoringu dla naszej replikacji:

ela@skyler:~/work/redis_data/redis-sentinel$ cat sentinel.conf

port 26379
dir /tmp

sentinel monitor mymaster 127.0.0.1 6379 1
sentinel down-after-milliseconds mymaster 30000
sentinel parallel-syncs mymaster 1
sentinel failover-timeout mymaster 180000

Powyższy plik konfiguracyjny oznacza, że Redis Sentiel:

• będzie działać na porcie 26379,
• będzie monitorować master mymaster na hoście 127.0.0.1 i porcie standardowym (dla redisa jest to 6379) z liczbą Sentieli wymaganych do wykrycia padnięcia mastera (ten ostatni parametr nie jest wymagany ale bez niego nie dojdzie do automatycznego failovera),
• uzna, że jeśli po 30000 milisekundach proces nadal nie będzie odpowiadać (parametr down-after-milliseconds) to znaczy, że stary master już padł i musi rozpocząć procedurę promowania nowego mastera,
• w tym samym czasie tylko jeden slave może zostać przekonfigurowany aby promować nowego mastera (parametr parallel-syncs)

Przetestujmy nasze rozwiązanie. Po uruchomieniu replikacji, gdzie master pracuje na 127.0.0.1:6379 i mamy dwa działające slavy (127.0.0.1:6389 i 127.0.0.1:6399) uruchomiłam serwer Sentinel (uruchamiamy go przy pomocy specialnej programu redis-sentinel lub tak jak zwykłą instancję redisa ale z opcją --sentinel):

ela@skyler:~/work$ ./redis-2.8.17/src/redis-sentinel redis_data/redis-sentinel/sentinel-small.conf 

                _._                                                  
           _.-``__ ''-._                                             
      _.-``    `.  `_.  ''-._           Redis 2.8.17 (00000000/0) 64 bit
  .-`` .-```.  ```\/    _.,_ ''-._                                   
 (    '      ,       .-`  | `,    )     Running in sentinel mode
 |`-._`-...-` __...-.``-._|'` _.-'|     Port: 26379
 |    `-._   `._    /     _.-'    |     PID: 3994
  `-._    `-._  `-./  _.-'    _.-'                                   
 |`-._`-._    `-.__.-'    _.-'_.-'|                                  
 |    `-._`-._        _.-'_.-'    |           http://redis.io        
  `-._    `-._`-.__.-'_.-'    _.-'                                   
 |`-._`-._    `-.__.-'    _.-'_.-'|                                  
 |    `-._`-._        _.-'_.-'    |                                  
  `-._    `-._`-.__.-'_.-'    _.-'                                   
      `-._    `-.__.-'    _.-'                                       
          `-._        _.-'                                           
              `-.__.-'                                               

[3994] 12 Nov 13:40:35.613 # Sentinel runid is 24a06b8417caf25365713a87ad87f1b68239e89e
[3994] 12 Nov 13:40:35.613 # +monitor master mymaster 127.0.0.1 6379 quorum 1
[3994] 12 Nov 13:40:35.614 * +slave slave 127.0.0.1:6389 127.0.0.1 6389 @ mymaster 127.0.0.1 6379
[3994] 12 Nov 13:40:35.614 * +slave slave 127.0.0.1:6399 127.0.0.1 6399 @ mymaster 127.0.0.1 6379

Po ubiciu procesu mastera (127.0.0.1:6379) po chwili w logach Sentinel pojawił się komunikat padnięciu mastera i uruchomieniu procesu failover:

[3994] 12 Nov 13:42:27.956 # +sdown master mymaster 127.0.0.1 6379
[3994] 12 Nov 13:42:27.956 # +odown master mymaster 127.0.0.1 6379 #quorum 1/1
[3994] 12 Nov 13:42:27.956 # +new-epoch 1
[3994] 12 Nov 13:42:27.956 # +try-failover master mymaster 127.0.0.1 6379
[3994] 12 Nov 13:42:27.980 # +vote-for-leader 24a06b8417caf25365713a87ad87f1b68239e89e 1
[3994] 12 Nov 13:42:27.980 # +elected-leader master mymaster 127.0.0.1 6379
[3994] 12 Nov 13:42:27.980 # +failover-state-select-slave master mymaster 127.0.0.1 6379
[3994] 12 Nov 13:42:28.043 # +selected-slave slave 127.0.0.1:6399 127.0.0.1 6399 @ mymaster 127.0.0.1 6379
[3994] 12 Nov 13:42:28.043 * +failover-state-send-slaveof-noone slave 127.0.0.1:6399 127.0.0.1 6399 @ mymaster 127.0.0.1 6379
[3994] 12 Nov 13:42:28.109 * +failover-state-wait-promotion slave 127.0.0.1:6399 127.0.0.1 6399 @ mymaster 127.0.0.1 6379
[3994] 12 Nov 13:42:29.073 # +promoted-slave slave 127.0.0.1:6399 127.0.0.1 6399 @ mymaster 127.0.0.1 6379
[3994] 12 Nov 13:42:29.073 # +failover-state-reconf-slaves master mymaster 127.0.0.1 6379
[3994] 12 Nov 13:42:29.098 * +slave-reconf-sent slave 127.0.0.1:6389 127.0.0.1 6389 @ mymaster 127.0.0.1 6379
[3994] 12 Nov 13:42:30.140 * +slave-reconf-inprog slave 127.0.0.1:6389 127.0.0.1 6389 @ mymaster 127.0.0.1 6379
[3994] 12 Nov 13:42:30.140 * +slave-reconf-done slave 127.0.0.1:6389 127.0.0.1 6389 @ mymaster 127.0.0.1 6379
[3994] 12 Nov 13:42:30.206 # +failover-end master mymaster 127.0.0.1 6379
[3994] 12 Nov 13:42:30.206 # +switch-master mymaster 127.0.0.1 6379 127.0.0.1 6399
[3994] 12 Nov 13:42:30.206 * +slave slave 127.0.0.1:6389 127.0.0.1 6389 @ mymaster 127.0.0.1 6399
[3994] 12 Nov 13:42:30.241 * +slave slave 127.0.0.1:6379 127.0.0.1 6379 @ mymaster 127.0.0.1 6399
[3994] 12 Nov 13:43:00.298 # +sdown slave 127.0.0.1:6379 127.0.0.1 6379 @ mymaster 127.0.0.1 6399

Slave, który działał na porcie 6399 został promowany na nowego mastera. Redis Sentiel zaktualizował swój plik konfiguracyjny na:

ela@skyler:~/work/redis_data/redis-sentinel$ cat sentinel-small.conf 
port 26379

dir "/tmp"

sentinel monitor mymaster 127.0.0.1 6399 1
sentinel config-epoch mymaster 1
sentinel leader-epoch mymaster 1
sentinel known-slave mymaster 127.0.0.1 6379
# Generated by CONFIG REWRITE
maxclients 4064
sentinel known-slave mymaster 127.0.0.1 6389

sentinel current-epoch 1

Sprawdźmy czy nasze instancje Redis naprawdę działają, tak jak to przedstawia proces Sentinel:

ela@skyler:~/work$ ./redis-2.8.17/src/redis-cli -p 6399
127.0.0.1:6399> keys *
1) "test2"
2) "test"
127.0.0.1:6399> set test3 hhhh
OK
127.0.0.1:6399> keys *
1) "test2"
2) "test3"
3) "test"
127.0.0.1:6399> quit
ela@skyler:~/work$ 
ela@skyler:~/work$ 
ela@skyler:~/work$ 
ela@skyler:~/work$ ./redis-2.8.17/src/redis-cli -p 6389
127.0.0.1:6389> keys *
1) "test3"
2) "test"
3) "test2"
127.0.0.1:6389> set test4 hhhh
(error) READONLY You can't write against a read only slave.
127.0.0.1:6389> quit
ela@skyler:~/work$ 
ela@skyler:~/work$ 
ela@skyler:~/work$ 
ela@skyler:~/work$ ./redis-2.8.17/src/redis-cli -p 6379
Could not connect to Redis at 127.0.0.1:6379: Connection refused
not connected> 
not connected> 
not connected> 
not connected> quit

Gdy uruchomimy starego mastera (6379) do działania, zostanie on wykryty przez system monitoringu i podpięty do replikacji jako slave. Log z procesu Redis Sentinel:

[3994] 12 Nov 14:03:32.875 # -sdown slave 127.0.0.1:6379 127.0.0.1 6379 @ mymaster 127.0.0.1 6399
[3994] 12 Nov 14:03:42.831 * +convert-to-slave slave 127.0.0.1:6379 127.0.0.1 6379 @ mymaster 127.0.0.1 6399

Dodatkowo pliki konfiguracujne naszych instancji Redisa w replikacji zostały zmienione tak, aby miały jak naświeższą informację na temat, który z nich jest masterem, a który slavem (parametr slaveof).

Oto plik konfiguracyjny starego mastera:

ela@skyler:~/work/redis_data/redis-6379$ cat redis-6379-small.conf
##############################################################################
### Master !!!

pidfile "/var/run/redis-6379.pid"
port 6379
loglevel notice
logfile "logfile.log"
databases 16
save 60 10000
dbfilename "dump-6379.rdb"
dir "/home/ela/work/redis_data/redis-6379"

maxclients 100

# Generated by CONFIG REWRITE
slaveof 127.0.0.1 6399

Jest to na tyle szeroki temat, że trzeba go rozbić na kilka postów aby móc poznać ten system dobrze. Więcej informacji znajdziecie, na stronie projektu dotyczącej replikacji i Redis Sentinel.  Mam nadzieję, że ten post zachęcił Was do zapoznania się z tym tematem.