Skip to content

Instantly share code, notes, and snippets.

@mxamin

mxamin/kh_razavi_migration.md

Created December 5, 2018 13:13
Show Gist options
  • Save mxamin/77f503d897234a3b03ee1e48113bb680 to your computer and use it in GitHub Desktop.
Save mxamin/77f503d897234a3b03ee1e48113bb680 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
kh_razavi_migration.md

TCI Migration (Khorasan Razavi)

We want to move data of following tables from one server (A: Khorasan Razavi) to another server (B: TCI).

User tables:

  • users
  • user_attrs
  • normal_users

Group table; we only need this table for group mapping:

  • groups

Step 1 (Server A)

Export tables data from server A as CSV files and import them into server B.

Create a directory in /tmp/ to save our export files:

# rm -rf /tmp/migration_a/
# mkdir /tmp/migration_a/
# chmod 777 /tmp/migration_a/

Export User tables data:

IBSng=# 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 ',';

IBSng=# COPY (SELECT user_id, attr_name, attr_value FROM user_attrs) TO '/tmp/migration_a/migration_a_user_attrs.csv' WITH DELIMITER ',';

IBSng=# 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 ',';

Export Group table data:

IBSng=# COPY (SELECT group_id, group_name, status, isp_id, comment FROM groups) TO '/tmp/migration_a/migration_a_groups.csv' WITH DELIMITER ',';

Now that we have exported data from the database, we need to move them to server B:

# cd /tmp/
# tar zcf migration_a.tar.gz migration_a
# rm -rf migration_a

Now we need to move the file /tmp/migration_a.tar.gz to server B by SCP, FTP, etc.

Step 2 (Server B)

Suppose we moved exported data files which was generated in step 1 to /tmp/ directory, now we need to extract them:

# cd /tmp/
# rm -rf migration_a
# tar xf migration_a.tar.gz
# rm -rf migration_a.tar.gz

Step 2.1

Now we need to create temporary tables and fill them with exported data from server A.

Create temporary User tables:

IBSng=# 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;
IBSng=# TRUNCATE migration_a_users;

IBSng=# CREATE TABLE migration_a_user_attrs AS SELECT user_id, attr_name, attr_value FROM user_attrs LIMIT 1;
IBSng=# TRUNCATE migration_a_user_attrs;

IBSng=# CREATE TABLE migration_a_normal_users AS SELECT user_id, normal_username, normal_password, second_normal_username FROM normal_users LIMIT 1;
IBSng=# TRUNCATE migration_a_normal_users;

Create temporary Group table:

IBSng=# CREATE TABLE migration_a_groups AS SELECT group_id, group_name, status, isp_id, comment FROM groups LIMIT 1;
IBSng=# TRUNCATE migration_a_groups;

Step 2.2

Import data from CSV files of server A to temporary tables we just created;

Import User tables data:

IBSng=# 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 ',';

IBSng=# COPY migration_a_user_attrs (user_id, attr_name, attr_value) FROM '/tmp/migration_a/migration_a_user_attrs.csv' WITH DELIMITER ',';

IBSng=# 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 ',';

Import Group table data:

IBSng=# COPY migration_a_groups (group_id, group_name, status, isp_id, comment) FROM '/tmp/migration_a/migration_a_groups.csv' WITH DELIMITER ',';

Step 2.3

For this migration we need to clean our imported data according to following requirements:

  • Convert all user passwords to lower case
  • Remove users with no username
  • We need to move only some specific attributes of users

Step 2.3.1

Convert all user passwords to lower case:

IBSng=# UPDATE migration_a_normal_users SET normal_password = LOWER(normal_password);

Step 2.3.2

Remove users with no username:

IBSng=# CREATE UNIQUE INDEX migration_a_users_user_id_idx ON migration_a_users (user_id);
IBSng=# CREATE UNIQUE INDEX migration_a_normal_users_user_id_idx ON migration_a_normal_users (user_id);

IBSng=# 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);
IBSng=# CREATE UNIQUE INDEX migration_a_no_username_user_id_idx ON migration_a_no_username (user_id);

IBSng=# 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);
IBSng=# 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);

IBSng=# DROP TABLE migration_a_no_username;

Note: We need to execute following queries, because we may have dangling records in user tables:

IBSng=# 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);
IBSng=# 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);

Step 2.3.3

We need to move only some specific attributes of users, the attributes are:

  • abs_exp_date
  • address
  • assign_dns
  • assign_ip
  • assign_route_ip
  • cell_phone
  • comment
  • custom_field_center
  • custom_field_dedicated_sim_number
  • custom_field_document_number
  • custom_field_establishing_date
  • custom_field_melli_code
  • email
  • first_login
  • lock
  • multi_login
  • name
  • night_free
  • phone
  • postal_code
  • radius_attrs
  • real_first_login

Write attribute names to /tmp/migration_a/migration_a_attr_names.csv:

cat << EOF > /tmp/migration_a/migration_a_attr_names.csv
abs_exp_date
address
assign_dns
assign_ip
assign_route_ip
cell_phone
comment
custom_field_center
custom_field_dedicated_sim_number
custom_field_document_number
custom_field_establishing_date
custom_field_melli_code
email
first_login
lock
multi_login
name
night_free
phone
postal_code
radius_attrs
real_first_login
EOF

IBSng=# CREATE TABLE migration_a_attr_names AS SELECT attr_name FROM user_attrs LIMIT 1;
IBSng=# TRUNCATE migration_a_attr_names;
IBSng=# COPY migration_a_attr_names (attr_name) FROM '/tmp/migration_a/migration_a_attr_names.csv' WITH DELIMITER ',';
IBSng=# CREATE UNIQUE INDEX migration_a_attr_names_attr_name_idx ON migration_a_attr_names (attr_name);

IBSng=# 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);

IBSng=# DROP TABLE migration_a_attr_names;

Step 2.4

To 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.

Add/Rename User tables ID columns:

IBSng=# ALTER TABLE migration_a_users RENAME COLUMN user_id TO user_id_old;
IBSng=# ALTER TABLE migration_a_users ADD COLUMN user_id_new bigint;
IBSng=# ALTER TABLE migration_a_users RENAME COLUMN group_id TO group_id_old;
IBSng=# ALTER TABLE migration_a_users ADD COLUMN group_id_new bigint;

IBSng=# ALTER TABLE migration_a_user_attrs RENAME COLUMN user_id TO user_id_old;
IBSng=# ALTER TABLE migration_a_user_attrs ADD COLUMN user_id_new bigint;

IBSng=# ALTER TABLE migration_a_normal_users RENAME COLUMN user_id TO user_id_old;
IBSng=# ALTER TABLE migration_a_normal_users ADD COLUMN user_id_new bigint;

Add/Rename Group tables ID columns:

IBSng=# ALTER TABLE migration_a_groups RENAME COLUMN group_id TO group_id_old;
IBSng=# ALTER TABLE migration_a_groups ADD COLUMN group_id_new bigint;

Step 2.5

Fill temporary tables with new IDs.

Generate new IDs for User table:

IBSng=# UPDATE migration_a_users SET user_id_new = nextval('users_user_id_seq');

Suppose we have a csv file named migration_a_group_mapping.csv with two columns in /tmp/migration_a/ directory:

  • old group name
  • new group ID

Note: The CSV file has no header

Now we need to import group mappings to our database (server B):

IBSng=# CREATE TABLE migration_a_group_mapping AS SELECT group_name, group_id FROM groups LIMIT 1;
IBSng=# TRUNCATE migration_a_group_mapping;
IBSng=# ALTER TABLE migration_a_group_mapping RENAME COLUMN group_name TO group_name_old;
IBSng=# ALTER TABLE migration_a_group_mapping RENAME COLUMN group_id TO group_id_new;

IBSng=# COPY migration_a_group_mapping (group_name_old, group_id_new) FROM '/tmp/migration_a/migration_a_group_mapping.csv' WITH DELIMITER ',';

Fill Group table with new IDs:

IBSng=# 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;

Step 2.6

Map old IDs to new IDs and fill corresponded values in temporary tables.

Map User tables old IDs to new IDs:

IBSng=# 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;

IBSng=# 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;

IBSng=# 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;

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:

IBSng=# UPDATE migration_a_users SET group_id_new = 3752 WHERE group_id_new is NULL;

Step 3

Update isp_id of records:

IBSng=# UPDATE migration_a_users SET isp_id = <province_isp_id>;

Note: ISP ID of Khorasan Razavi is 40

Step 4

Delete users from temporary tables (server A) with the same normal_username in server B:

IBSng=# 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);

IBSng=# DELETE FROM migration_a_users WHERE user_id_old IN (SELECT user_id_dup FROM migration_a_users_dup);

IBSng=# DELETE FROM migration_a_user_attrs WHERE user_id_old IN (SELECT user_id_dup FROM migration_a_users_dup);

IBSng=# DELETE FROM migration_a_normal_users WHERE user_id_old IN (SELECT user_id_dup FROM migration_a_users_dup);

IBSng=# DROP TABLE migration_a_users_dup;

Step 5

TODO: Backup from the database and especially a separate backup from tables which are going to be changed.

Step 5.1

Insert temporary tables records into main tables.

Insert User tables records:

IBSng=# 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;

IBSng=# ALTER TABLE user_attrs DISABLE TRIGGER update_user_nearest_exp_date_trigger;
IBSng=# INSERT INTO user_attrs (user_id, attr_name, attr_value) SELECT user_id_new, attr_name, attr_value FROM migration_a_user_attrs;
IBSng=# ALTER TABLE user_attrs ENABLE TRIGGER update_user_nearest_exp_date_trigger;

IBSng=# ALTER TABLE normal_users DISABLE TRIGGER users_prevent_duplicate_username_trigger;
IBSng=# 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;
IBSng=# ALTER TABLE normal_users ENABLE TRIGGER users_prevent_duplicate_username_trigger;

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.

Step 7

Now that we completed the migration, we need to reload new users.

Step 7.1

Export IDs of new users.

Export User IDs:

IBSng=# COPY (SELECT user_id_new FROM migration_a_users) TO '/tmp/migration_a/migration_a_users_reload.csv' WITH DELIMITER ',';

Step 7.2

Run reload script for new users. These scripts should be run on master App of IBSng.

Move reload_users_by_csv.py to /opt/ and then run User reload script:

# python /usr/local/IBSng/addons/client/client.py -u <web_panel_username> -p <web_panel_password> -i /opt/reload_users_by_csv.py

Source code of realod_users_by_csv.py script:

from core.user import user_main

with open("/tmp/migration_a/migration_a_users_reload.csv", "r") as fd:
    user_ids = []
    for line in fd.readlines():
        user_id_str = line.strip("\r\n ")
        if user_id_str:
            user_ids.append(long(user_id_str))
            if len(user_ids) == 10:
                user_main.getActionManager().broadcastChange(user_ids)
                user_ids = []

    user_main.getActionManager().broadcastChange(user_ids)

Notes

Creat 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)

Export users summary:

IBSng=# ALTER TABLE migration_a_users ADD COLUMN group_name_old text;
IBSng=# ALTER TABLE migration_a_users ADD COLUMN group_name_new text;

IBSng=# 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;
IBSng=# UPDATE migration_a_users SET group_name_new = groups.group_name FROM groups WHERE migration_a_users.group_id_new = groups.group_id;

IBSng=# CREATE TABLE migration_a_users_summary AS SELECT user_id_old, user_id_new, group_name_old, group_name_new FROM migration_a_users;
IBSng=# ALTER TABLE migration_a_users_summary ADD COLUMN abs_exp_date text;
IBSng=# 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';

IBSng=# 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;

Export groups summary:

IBSng=# ALTER TABLE migration_a_group_mapping ADD COLUMN group_name_new text;
IBSng=# 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;

IBSng=# 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment