vb9ahyKF

1# TCI Migration (Khorasan Razavi)
2We want to move data of following tables from one server (A: Khorasan Razavi) to another server (B: TCI).
3
4User tables: 
5- users
6- user_attrs
7- normal_users
8
9Group table; we only need this table for group mapping:
10- groups
11
12## Step 1 (Server A)
13Export tables data from server **A** as CSV files and import them into server **B**.
14
15Create a directory in `/tmp/` to save our export files:
16```
17# rm -rf /tmp/migration_a/
18# mkdir /tmp/migration_a/
19# chmod 777 /tmp/migration_a/
20```
21
22Export User tables data:
23```
24IBSng=# COPY (SELECT user_id, isp_id, credit, deposit, deposit_recharge, status, group_id, creation_date, nearest_exp_date FROM users) TO '/tmp/migration_a/migration_a_users.csv' WITH DELIMITER ',';
25
26IBSng=# COPY (SELECT user_id, attr_name, attr_value FROM user_attrs) TO '/tmp/migration_a/migration_a_user_attrs.csv' WITH DELIMITER ',';
27
28IBSng=# COPY (SELECT user_id, normal_username, normal_password, second_normal_username FROM normal_users) TO '/tmp/migration_a/migration_a_normal_users.csv' WITH DELIMITER ',';
29```
30
31Export Group table data:
32```
33IBSng=# COPY (SELECT group_id, group_name, status, isp_id, comment FROM groups) TO '/tmp/migration_a/migration_a_groups.csv' WITH DELIMITER ',';
34```
35
36Now that we have exported data from the database, we need to move them to server **B**:
37```
38# cd /tmp/
39# tar zcf migration_a.tar.gz migration_a
40# rm -rf migration_a
41```
42
43Now we need to move the file `/tmp/migration_a.tar.gz` to server **B** by SCP, FTP, etc.
44
45## Step 2 (Server B)
46Suppose we moved exported data files which was generated in step 1 to `/tmp/` directory, now we need to extract them:
47```
48# cd /tmp/
49# rm -rf migration_a
50# tar xf migration_a.tar.gz
51# rm -rf migration_a.tar.gz
52```
53
54### Step 2.1
55Now we need to create temporary tables and fill them with exported data from server A.
56
57Create temporary User tables:
58```
59IBSng=# CREATE TABLE migration_a_users AS SELECT user_id, isp_id, credit, deposit, deposit_recharge, status, group_id, creation_date, nearest_exp_date FROM users LIMIT 1;
60IBSng=# TRUNCATE migration_a_users;
61
62IBSng=# CREATE TABLE migration_a_user_attrs AS SELECT user_id, attr_name, attr_value FROM user_attrs LIMIT 1;
63IBSng=# TRUNCATE migration_a_user_attrs;
64
65IBSng=# CREATE TABLE migration_a_normal_users AS SELECT user_id, normal_username, normal_password, second_normal_username FROM normal_users LIMIT 1;
66IBSng=# TRUNCATE migration_a_normal_users;
67```
68
69Create temporary Group table:
70```
71IBSng=# CREATE TABLE migration_a_groups AS SELECT group_id, group_name, status, isp_id, comment FROM groups LIMIT 1;
72IBSng=# TRUNCATE migration_a_groups;
73```
74
75### Step 2.2
76Import data from CSV files of server A to temporary tables we just created;
77
78Import User tables data:
79```
80IBSng=# COPY migration_a_users (user_id, isp_id, credit, deposit, deposit_recharge, status, group_id, creation_date, nearest_exp_date) FROM '/tmp/migration_a/migration_a_users.csv' WITH DELIMITER ',';
81
82IBSng=# COPY migration_a_user_attrs (user_id, attr_name, attr_value) FROM '/tmp/migration_a/migration_a_user_attrs.csv' WITH DELIMITER ',';
83
84IBSng=# COPY migration_a_normal_users (user_id, normal_username, normal_password, second_normal_username) FROM '/tmp/migration_a/migration_a_normal_users.csv' WITH DELIMITER ',';
85```
86
87Import Group table data:
88```
89IBSng=# COPY migration_a_groups (group_id, group_name, status, isp_id, comment) FROM '/tmp/migration_a/migration_a_groups.csv' WITH DELIMITER ',';
90```
91
92### Step 2.3
93For this migration we need to clean our imported data according to following requirements:
94
95- Convert all user passwords to lower case
96- Remove users with no username
97- We need to move only some specific attributes of users
98
99#### Step 2.3.1
100Convert all user passwords to lower case:
101```
102IBSng=# UPDATE migration_a_normal_users SET normal_password = LOWER(normal_password);
103```
104
105#### Step 2.3.2
106Remove users with no username:
107```
108IBSng=# CREATE UNIQUE INDEX migration_a_users_user_id_idx ON migration_a_users (user_id);
109IBSng=# CREATE UNIQUE INDEX migration_a_normal_users_user_id_idx ON migration_a_normal_users (user_id);
110
111IBSng=# CREATE TABLE migration_a_no_username AS SELECT user_id FROM migration_a_users WHERE NOT EXISTS (SELECT migration_a_normal_users.user_id FROM migration_a_normal_users WHERE migration_a_users.user_id = migration_a_normal_users.user_id);
112IBSng=# CREATE UNIQUE INDEX migration_a_no_username_user_id_idx ON migration_a_no_username (user_id);
113
114IBSng=# DELETE FROM migration_a_users WHERE EXISTS (SELECT migration_a_no_username.user_id FROM migration_a_no_username WHERE migration_a_users.user_id = migration_a_no_username.user_id);
115IBSng=# DELETE FROM migration_a_user_attrs WHERE EXISTS (SELECT migration_a_no_username.user_id FROM migration_a_no_username WHERE migration_a_user_attrs.user_id = migration_a_no_username.user_id);
116
117IBSng=# DROP TABLE migration_a_no_username;
118```
119
120> Note: We need to execute following queries, because we may have dangling records in user tables:
121```
122IBSng=# DELETE FROM migration_a_normal_users WHERE NOT EXISTS (SELECT migration_a_users.user_id FROM migration_a_users WHERE migration_a_normal_users.user_id = migration_a_users.user_id);
123IBSng=# DELETE FROM migration_a_user_attrs WHERE NOT EXISTS (SELECT migration_a_users.user_id FROM migration_a_users WHERE migration_a_user_attrs.user_id = migration_a_users.user_id);
124```
125
126#### Step 2.3.3
127We need to move only some specific attributes of users, the attributes are:
128
129- abs_exp_date
130- address
131- assign_dns
132- assign_ip
133- assign_route_ip
134- cell_phone
135- comment
136- custom_field_center
137- custom_field_dedicated_sim_number
138- custom_field_document_number
139- custom_field_establishing_date
140- custom_field_melli_code
141- email
142- first_login
143- lock
144- multi_login
145- name
146- night_free
147- phone
148- postal_code
149- radius_attrs
150- real_first_login
151
152Write attribute names to `/tmp/migration_a/migration_a_attr_names.csv`:
153```
154cat << EOF > /tmp/migration_a/migration_a_attr_names.csv
155abs_exp_date
156address
157assign_dns
158assign_ip
159assign_route_ip
160cell_phone
161comment
162custom_field_center
163custom_field_dedicated_sim_number
164custom_field_document_number
165custom_field_establishing_date
166custom_field_melli_code
167email
168first_login
169lock
170multi_login
171name
172night_free
173phone
174postal_code
175radius_attrs
176real_first_login
177EOF
178```
179
180```
181IBSng=# CREATE TABLE migration_a_attr_names AS SELECT attr_name FROM user_attrs LIMIT 1;
182IBSng=# TRUNCATE migration_a_attr_names;
183IBSng=# COPY migration_a_attr_names (attr_name) FROM '/tmp/migration_a/migration_a_attr_names.csv' WITH DELIMITER ',';
184IBSng=# CREATE UNIQUE INDEX migration_a_attr_names_attr_name_idx ON migration_a_attr_names (attr_name);
185
186IBSng=# DELETE FROM migration_a_user_attrs WHERE NOT EXISTS (SELECT migration_a_attr_names.attr_name FROM migration_a_attr_names WHERE migration_a_user_attrs.attr_name = migration_a_attr_names.attr_name);
187
188IBSng=# DROP TABLE migration_a_attr_names;
189```
190
191### Step 2.4
192To make column ids of temporary tables compatible with server B, we create a new column named `<id_column>_new` and rename the current column name to `<id_column>_old`; then we fill `<id_column>_new` with corresponded values.
193
194Add/Rename User tables ID columns:
195```
196IBSng=# ALTER TABLE migration_a_users RENAME COLUMN user_id TO user_id_old;
197IBSng=# ALTER TABLE migration_a_users ADD COLUMN user_id_new bigint;
198IBSng=# ALTER TABLE migration_a_users RENAME COLUMN group_id TO group_id_old;
199IBSng=# ALTER TABLE migration_a_users ADD COLUMN group_id_new bigint;
200
201IBSng=# ALTER TABLE migration_a_user_attrs RENAME COLUMN user_id TO user_id_old;
202IBSng=# ALTER TABLE migration_a_user_attrs ADD COLUMN user_id_new bigint;
203
204IBSng=# ALTER TABLE migration_a_normal_users RENAME COLUMN user_id TO user_id_old;
205IBSng=# ALTER TABLE migration_a_normal_users ADD COLUMN user_id_new bigint;
206```
207
208Add/Rename Group tables ID columns:
209```
210IBSng=# ALTER TABLE migration_a_groups RENAME COLUMN group_id TO group_id_old;
211IBSng=# ALTER TABLE migration_a_groups ADD COLUMN group_id_new bigint;
212```
213
214### Step 2.5
215Fill temporary tables with new IDs.
216
217Generate new IDs for User table:
218```
219IBSng=# UPDATE migration_a_users SET user_id_new = nextval('users_user_id_seq');
220```
221
222Suppose we have a csv file named `migration_a_group_mapping.csv` with two columns in `/tmp/migration_a/` directory:
223
224- old group name
225- new group ID
226
227> Note: The CSV file has no header
228
229Now we need to import group mappings to our database (server B):
230```
231IBSng=# CREATE TABLE migration_a_group_mapping AS SELECT group_name, group_id FROM groups LIMIT 1;
232IBSng=# TRUNCATE migration_a_group_mapping;
233IBSng=# ALTER TABLE migration_a_group_mapping RENAME COLUMN group_name TO group_name_old;
234IBSng=# ALTER TABLE migration_a_group_mapping RENAME COLUMN group_id TO group_id_new;
235
236IBSng=# COPY migration_a_group_mapping (group_name_old, group_id_new) FROM '/tmp/migration_a/migration_a_group_mapping.csv' WITH DELIMITER ',';
237```
238
239Fill Group table with new IDs:
240```
241IBSng=# UPDATE migration_a_groups SET group_id_new = migration_a_group_mapping.group_id_new FROM migration_a_group_mapping WHERE migration_a_groups.group_name = migration_a_group_mapping.group_name_old;
242```
243
244### Step 2.6
245Map old IDs to new IDs and fill corresponded values in temporary tables.
246
247Map User tables old IDs to new IDs:
248```
249IBSng=# UPDATE migration_a_users SET group_id_new = migration_a_groups.group_id_new FROM migration_a_groups WHERE migration_a_users.group_id_old = migration_a_groups.group_id_old;
250
251IBSng=# UPDATE migration_a_user_attrs SET user_id_new = migration_a_users.user_id_new FROM migration_a_users WHERE migration_a_user_attrs.user_id_old = migration_a_users.user_id_old;
252
253IBSng=# UPDATE migration_a_normal_users SET user_id_new = migration_a_users.user_id_new FROM migration_a_users WHERE migration_a_normal_users.user_id_old = migration_a_users.user_id_old;
254```
255
256> Note: We need to fill `group_id_new` column of `migration_a_users` table with default group of **unknown-group** with ID of **3752**, where we have no group mapping:
257```
258IBSng=# UPDATE migration_a_users SET group_id_new = 3752 WHERE group_id_new is NULL;
259```
260
261## Step 3
262Update isp_id of records:
263```
264IBSng=# UPDATE migration_a_users SET isp_id = <province_isp_id>;
265```
266
267> Note: ISP ID of Khorasan Razavi is **40**
268
269## Step 4
270Delete users from temporary tables (server A) with the same normal_username in server B:
271```
272IBSng=# CREATE TABLE migration_a_users_dup AS SELECT user_id_old as user_id_dup FROM migration_a_normal_users WHERE normal_username IN (SELECT normal_username FROM normal_users);
273
274IBSng=# DELETE FROM migration_a_users WHERE user_id_old IN (SELECT user_id_dup FROM migration_a_users_dup);
275
276IBSng=# DELETE FROM migration_a_user_attrs WHERE user_id_old IN (SELECT user_id_dup FROM migration_a_users_dup);
277
278IBSng=# DELETE FROM migration_a_normal_users WHERE user_id_old IN (SELECT user_id_dup FROM migration_a_users_dup);
279
280IBSng=# DROP TABLE migration_a_users_dup;
281``` 
282
283## Step 5
284TODO: Backup from the database and especially a separate backup from tables which are going to be changed.
285
286### Step 5.1
287Insert temporary tables records into main tables.
288
289Insert User tables records:
290```
291IBSng=# INSERT INTO users (user_id, isp_id, credit, deposit, deposit_recharge, status, group_id, creation_date, nearest_exp_date) SELECT user_id_new, isp_id, credit, deposit, deposit_recharge, status, group_id_new, creation_date, nearest_exp_date FROM migration_a_users;
292
293IBSng=# ALTER TABLE user_attrs DISABLE TRIGGER update_user_nearest_exp_date_trigger;
294IBSng=# INSERT INTO user_attrs (user_id, attr_name, attr_value) SELECT user_id_new, attr_name, attr_value FROM migration_a_user_attrs;
295IBSng=# ALTER TABLE user_attrs ENABLE TRIGGER update_user_nearest_exp_date_trigger;
296
297IBSng=# ALTER TABLE normal_users DISABLE TRIGGER users_prevent_duplicate_username_trigger;
298IBSng=# INSERT INTO normal_users (user_id, normal_username, normal_password, second_normal_username) SELECT user_id_new, normal_username, normal_password, second_normal_username FROM migration_a_normal_users;
299IBSng=# ALTER TABLE normal_users ENABLE TRIGGER users_prevent_duplicate_username_trigger;
300```
301
302> Note: In inserting records to **normal_users** table, remember to disable the trigger of the table; otherwise it takes a very long time to insert records.
303
304## Step 7
305Now that we completed the migration, we need to reload new users.
306
307### Step 7.1
308Export IDs of new users.
309
310Export User IDs:
311```
312IBSng=# COPY (SELECT user_id_new FROM migration_a_users) TO '/tmp/migration_a/migration_a_users_reload.csv' WITH DELIMITER ',';
313```
314
315### Step 7.2
316Run reload script for new users. These scripts should be run on master App of IBSng.
317
318Move `reload_users_by_csv.py` to `/opt/` and then run User reload script:
319```
320# python /usr/local/IBSng/addons/client/client.py -u <web_panel_username> -p <web_panel_password> -i /opt/reload_users_by_csv.py
321```
322
323> Source code of `realod_users_by_csv.py` script:
324```python
325from core.user import user_main
326
327with open("/tmp/migration_a/migration_a_users_reload.csv", "r") as fd:
328    user_ids = []
329    for line in fd.readlines():
330        user_id_str = line.strip("\r\n ")
331        if user_id_str:
332            user_ids.append(long(user_id_str))
333            if len(user_ids) == 10:
334                user_main.getActionManager().broadcastChange(user_ids)
335                user_ids = []
336
337    user_main.getActionManager().broadcastChange(user_ids)
338```
339
340## Notes
341Creat mapping files of migrated data for third party applications: (user_id_old, user_id_new, group_name_old, group_name_new, abs_exp_date) and (group_name_old, group_name_new)
342
343Export users summary:
344```
345IBSng=# ALTER TABLE migration_a_users ADD COLUMN group_name_old text;
346IBSng=# ALTER TABLE migration_a_users ADD COLUMN group_name_new text;
347
348IBSng=# UPDATE migration_a_users SET group_name_old = migration_a_groups.group_name FROM migration_a_groups WHERE migration_a_users.group_id_old = migration_a_groups.group_id_old;
349IBSng=# UPDATE migration_a_users SET group_name_new = groups.group_name FROM groups WHERE migration_a_users.group_id_new = groups.group_id;
350
351IBSng=# CREATE TABLE migration_a_users_summary AS SELECT user_id_old, user_id_new, group_name_old, group_name_new FROM migration_a_users;
352IBSng=# ALTER TABLE migration_a_users_summary ADD COLUMN abs_exp_date text;
353IBSng=# UPDATE migration_a_users_summary SET abs_exp_date = migration_a_user_attrs.attr_value FROM migration_a_user_attrs WHERe migration_a_users_summary.user_id_old = migration_a_user_attrs.user_id_old AND migration_a_user_attrs.attr_name = 'abs_exp_date';
354
355IBSng=# COPY (SELECT user_id_old, user_id_new, group_name_old, group_name_new, abs_exp_date FROM migration_a_users_summary) TO '/tmp/migration_a/migration_a_users_summary.csv' WITH DELIMITER ',' CSV HEADER;
356```
357
358Export groups summary:
359```
360IBSng=# ALTER TABLE migration_a_group_mapping ADD COLUMN group_name_new text;
361IBSng=# UPDATE migration_a_group_mapping SET group_name_new = groups.group_name FROM groups WHERE migration_a_group_mapping.group_id_new = groups.group_id;
362
363IBSng=# COPY (SELECT group_name_old, group_name_new FROM migration_a_group_mapping) TO '/tmp/migration_a/migration_a_groups_summary.csv' WITH DELIMITER ',' CSV HEADER;
364```