3.1.0 Migration Deployment Documentation

[pengyin-shan]

Pre-steps:

1. Local migration and check finished. See status in:
   issues.xlsx
2. Rubocop status in Rubocop change documentation #264 .

This ticket is created to record the deployment process and issues.

• Dockerfile.Production report js or db error. Need to do rake assets:precompile separately inside docker #282

[pengyin-shan]

updated some content on Jan 25.

@nmacgreg I have tested the data migration process on UAT. Here are steps on your side that I think would be necessary to perform. Please let me know if you need any more information. I have a separate worksheet to record every step I did on UAT(issues.xlsx) ,and I retrieve the key steps for you:

Steps

1. Set up PostgreSQL 12 instance (same db name and owner info as current MariaDB). Here is the command I use to create PostgreSQL DB on UAT:

CREATE DATABASE "dmp_prod"
WITH OWNER "roadmap_user"
TEMPLATE = template0
ENCODING 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8';

1.b. In addition, temporarily grant the userid the ability to create new databases: postgres=# alter user <userid> CREATEDB;
1.c And, you'll need an entry in the pg_hba.conf, allowing the DB userid to connect to the "postgres" database table!

2. After deploying the 3.1.0 version to staging with a MariaDB connection, run bin/rake mysql_to_postgres:retrieve_data to read all existing data from MariaDB and write to a temp folder at db/seeds/staging. We should see a file called seed_0.rb and a temp folder with many .rb files:

3. Now switch the database to PostgreSQL. On my local machine, I change environmental variables (commented out the MariaDB part and uncomment PostgreSQL part). On UAT I also need to update docker-compose.yml file. In Staging or Prod, this will consist of manual steps:

   • Edit config/secrets.yml
     • Add new entry: database_encoding: "UTF8"
     • Adjust the existing database_url entry, s/mysql2/postgresql/ and s/mariadb-db-tst-primary-1/march/
     • Use this to test your database connection: bin/rails db:drop
   • Edit config/database.yml
     • Set "encoding: UTF8" everywhere

4. Go back to the app and run bin/rails db:reset. This step will trigger the standard database setup, migration and seeding process. I set up the seeding process to read all data we just created in step 2.

5. Wait for the writing process to finish. I set up terminal outputs so that we can watch the process. This step only could take up to 3.5 hours.

6. At the end of the process, we can see a reminder message saying the process is ended, and delete the db/seeds/staging folder

7. (this will be a repeating step) from the 3.1.0 version, we will need to run four rake tasks to periodically retrieve research output information. The following commands should be done in the overnight build (we can add them after the statistics command):
   a. rake external_api:load_rdamsc_standard
   b. rake external_api:load_re3data_repos (SSH on UAT: 1 hour, although message said update to 10 mins)
   c. rake external_api:load_spdx_licenses
   d.rake external_api:add_field_of_science_to_research_domains

8. We also need to run regular rake tasks, such as generating user statistics, translations, etc.

References

1. Environmental variables (secret on the production server):

And this is the secret setting (just change environmental variable name to lower case):

defaults: &defaults
  adapter: <%= ENV["DATABASE_ADAPTER"] || Rails.application.secrets.database_adapter %>
  encoding: <%= ENV["DATABASE_ENCODING"] || Rails.application.secrets.database_encoding %>
  pool: 16
  username: <%= ENV["DATABASE_USER"] || Rails.application.secrets.database_user %>
  password: <%= ENV["DATABASE_PASSWORD"] || Rails.application.secrets.database_password %>
  host: <%= ENV["DATABASE_URL"] || '127.0.0.1' %>

development:
  <<: *defaults
  url: <%= Rails.application.secrets.database_url %>

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *defaults
  url: <%= Rails.application.secrets.database_test_url %>

uat:
  <<: *defaults
  url: <%= Rails.application.secrets.database_url %>

staging:
  <<: *defaults
  url: <%= Rails.application.secrets.database_url %>

sandbox:
  encoding: utf8mb4
  url: <%= Rails.application.secrets.database_url %>
  pool: 5
  timeout: 5000

production:
  <<: *defaults
  url: <%= Rails.application.secrets.database_url %>

4. PostgreSQL 12 instance on UAT after migration:

*After migration*

5. Just for your reference, docker-compose.yml setting on UAT server:

@nmacgreg please let me know if you want any Zoom calls or documents for more detail. On UAT it took me >= 5 hours to finish. It is ok that we do a trial run before the deployment day on staging only to get a better estimation? We prefer to do the formal deployment at the end of Jan.

[pengyin-shan]

Have gone through the data migration process with Neil on staging, so I'm going to close this ticket for now. As a note for future, the steps ON UAT to comprehensively delete and recreate MariaDb, load data, transfer data, create PostgreSQL then load data to PostgreSQL is at the first tab of excel:

Excel File for Migration
issues.xlsx