Migrate Gerrit 2.12 to Postgres

migrate.md

1. Create an RDS instance
2. SSH to gerrit and cd to /opt/gerrit/site
3. Stop gerrit and open the gerrit prompt

  bin/gerrit.sh stop
  java -jar bin/gerrit.war 'gsql' -d .

4. Copy in the below

    CALL CSVWRITE('/opt/gerrit/migrate/accounts', 'SELECT registered_on , full_name, preferred_email, maximum_page_size, show_site_header, use_flash_clipboard, download_url, download_command, copy_self_on_email, date_format, time_format, relative_date_in_change_table, diff_view, size_bar_in_change_table, legacycid_in_change_table, review_category_strategy, mute_common_path_prefixes, inactive, account_id FROM accounts');
    CALL CSVWRITE('/opt/gerrit/migrate/account_external_ids', 'SELECT  ACCOUNT_ID,EMAIL_ADDRESS,PASSWORD,EXTERNAL_ID FROM account_external_ids');
    CALL CSVWRITE('/opt/gerrit/migrate/account_groups', 'SELECT name, description, visible_to_all, group_uuid, owner_group_uuid, group_id FROM account_groups');
    CALL CSVWRITE('/opt/gerrit/migrate/account_group_by_id', 'SELECT group_id, include_uuid FROM account_group_by_id');
    CALL CSVWRITE('/opt/gerrit/migrate/account_group_by_id_aud', ' SELECT  added_by, removed_by, removed_on, group_id, include_uuid, added_on FROM account_group_by_id_aud');
    CALL CSVWRITE('/opt/gerrit/migrate/account_group_members', 'SELECT account_id, group_id FROM account_group_members');
    CALL CSVWRITE('/opt/gerrit/migrate/account_group_members_audit', 'SELECT added_by, removed_by, removed_on, account_id, group_id, added_on FROM account_group_members_audit');
    CALL CSVWRITE('/opt/gerrit/migrate/account_group_names', 'SELECT group_id, name FROM account_group_names');

    CALL CSVWRITE('/opt/gerrit/migrate/account_patch_reviews', 'SELECT account_id, change_id, patch_set_id, file_name FROM account_patch_reviews');
    CALL CSVWRITE('/opt/gerrit/migrate/account_project_watches', 'SELECT notify_new_changes, notify_all_comments, notify_submitted_changes, notify_new_patch_sets, notify_abandoned_changes, account_id, project_name, filter FROM account_project_watches');
    CALL CSVWRITE('/opt/gerrit/migrate/account_ssh_keys', 'SELECT ssh_public_key, valid, account_id, seq FROM account_ssh_keys');

    CALL CSVWRITE('/opt/gerrit/migrate/change_messages', 'SELECT author_id, written_on, message, patchset_change_id, patchset_patch_set_id, change_id, uuid FROM change_messages');
    CALL CSVWRITE('/opt/gerrit/migrate/changes', 'SELECT change_key, created_on, last_updated_on, owner_account_id, dest_project_name, dest_branch_name, status, current_patch_set_id, subject, topic, original_subject, submission_id, row_version, change_id FROM changes');
    CALL CSVWRITE('/opt/gerrit/migrate/patch_comments', 'SELECT line_nbr, author_id, written_on, status, side, message, parent_uuid, range_start_line, range_start_character, range_end_line, range_end_character, change_id, patch_set_id, file_name, uuid FROM patch_comments');
    CALL CSVWRITE('/opt/gerrit/migrate/patch_set_approvals', 'SELECT value, granted, change_id, patch_set_id, account_id, category_id FROM patch_set_approvals');
    CALL CSVWRITE('/opt/gerrit/migrate/patch_sets', 'SELECT revision, uploader_account_id, created_on, draft, groups, push_certficate, change_id, patch_set_id FROM patch_sets');
    CALL CSVWRITE('/opt/gerrit/migrate/schema_version', 'SELECT version_nbr, singleton FROM schema_version');
    CALL CSVWRITE('/opt/gerrit/migrate/starred_changes', 'SELECT account_id, change_id FROM starred_changes');
    CALL CSVWRITE('/opt/gerrit/migrate/submodule_subscriptions', 'SELECT submodule_project_name, submodule_branch_name, super_project_project_name, super_project_branch_name, submodule_path FROM submodule_subscriptions');
    CALL CSVWRITE('/opt/gerrit/migrate/system_config', 'SELECT register_email_private_key, site_path  , admin_group_id, anonymous_group_id, registered_group_id, wild_project_name, batch_users_group_id, owner_group_id, admin_group_uuid, batch_users_group_uuid, singleton FROM system_config');

    SELECT currval('change_message_id');
    SELECT currval('change_id');
    SELECT currval('account_id');
    SELECT currval('account_group_id');

    \q # Quit the command-line

5. Remember the output of the last 4 selects
6. Backup All-Projects:

    cp -aR git/All-Projects.git . 

7. Edit the gerrit config at etc/gerrit.config

    [database]
        type = POSTGRESQL
        hostname = <rds_hostname>
        database = <db name>
        username = <master user name>
        password = <mysupersecretimpossibletocrackpassword, eg: 1234>

8. Initialize gerrit for Postgres

    java -jar bin/gerrit.war init --batch -d

9. Restore All-Projects

    rm -rf git/All-Projects.git/
    cp -aR All-Projects.git git

10. Connect to Postgres

    psql --host=<rds_host> --username <rds master user name> --password --dbname=<rds db name>

11. Run the below

    DELETE FROM account_external_ids;
    \COPY account_external_ids FROM '/opt/gerrit/migrate/account_external_ids' DELIMITER ',' CSV HEADER;

    DELETE FROM account_group_by_id;
    \COPY account_group_by_id FROM '/opt/gerrit/migrate/account_group_by_id' DELIMITER ',' CSV HEADER;

    DELETE FROM account_group_by_id_aud;
    \COPY account_group_by_id_aud FROM '/opt/gerrit/migrate/account_group_by_id_aud' DELIMITER ',' CSV HEADER;

    DELETE FROM account_group_members;
    \COPY account_group_members FROM '/opt/gerrit/migrate/account_group_members' DELIMITER ',' CSV HEADER;

    DELETE FROM account_group_members_audit;
    \COPY account_group_members_audit FROM '/opt/gerrit/migrate/account_group_members_audit' DELIMITER ',' CSV HEADER;

    DELETE FROM account_group_names;
    \COPY account_group_names FROM '/opt/gerrit/migrate/account_group_names' DELIMITER ',' CSV HEADER;

    DELETE FROM account_groups;
    \COPY account_groups FROM '/opt/gerrit/migrate/account_groups' DELIMITER ',' CSV HEADER;

    DELETE FROM account_patch_reviews;
    \COPY account_patch_reviews FROM '/opt/gerrit/migrate/account_patch_reviews' DELIMITER ',' CSV HEADER;

    DELETE FROM account_project_watches;
    \COPY account_project_watches FROM '/opt/gerrit/migrate/account_project_watches' DELIMITER ',' CSV HEADER;

    DELETE FROM account_ssh_keys;
    \COPY account_ssh_keys FROM '/opt/gerrit/migrate/account_ssh_keys' DELIMITER ',' CSV HEADER;

    DELETE FROM accounts;
    \COPY accounts FROM '/opt/gerrit/migrate/accounts' DELIMITER ',' CSV HEADER;

    DELETE FROM change_messages;
    \COPY change_messages FROM '/opt/gerrit/migrate/change_messages' DELIMITER ',' CSV HEADER;

    DELETE FROM changes;
    \COPY changes FROM '/opt/gerrit/migrate/changes' DELIMITER ',' CSV HEADER;

    DELETE FROM patch_comments;
    \COPY patch_comments FROM '/opt/gerrit/migrate/patch_comments' DELIMITER ',' CSV HEADER;

    DELETE FROM patch_set_approvals;
    \COPY patch_set_approvals FROM '/opt/gerrit/migrate/patch_set_approvals' DELIMITER ',' CSV HEADER;

    DELETE FROM patch_sets;
    \COPY patch_sets FROM '/opt/gerrit/migrate/patch_sets' DELIMITER ',' CSV HEADER;

    DELETE FROM schema_version;
    \COPY schema_version FROM '/opt/gerrit/migrate/schema_version' DELIMITER ',' CSV HEADER;

    DELETE FROM starred_changes;
    \COPY starred_changes FROM '/opt/gerrit/migrate/starred_changes' DELIMITER ',' CSV HEADER;

    DELETE FROM submodule_subscriptions;
    \COPY submodule_subscriptions FROM '/opt/gerrit/migrate/submodule_subscriptions' DELIMITER ',' CSV HEADER;

    DELETE FROM system_config;
    \COPY system_config FROM '/opt/gerrit/migrate/system_config' DELIMITER ',' CSV HEADER;

12. Manually run the below, replacing the <...> with values from before

    SELECT setval('change_message_id', <change_message_id>);
    SELECT setval('change_id', <change_id>);
    SELECT setval('account_id', <account_id>);
    SELECT setval('account_group_id', <account_group_id>);

    \q # Exit the postgres client tool

13. Start gerrit

    bin/gerrit.sh start