MySQL -> Postgres validation

[kardan]

We need to be 100% sure that all data is transferred and also that things have not broken. Hence we need to create a one time test suit that validates that we are operational and can make the switch.

Part of the validation process have to be manual. We can't script a backup so we will have to have a checklist something like this:

Checklist (draft)

• Make sure Postgres is installed with a database with correct user as owner & (template0)
• Turn of traffic
• Backup MySQL
• Run script to collect validation data
• Run mysql->pg script
• Massage new db (remove trailing whitespace, unused tables such as workflow.*)?
• Run validation script
• Enable new pg database settings
• Turn on traffic
• Take care of mysql

Validation script

Needs to support both collecting data and validating against that data.

• Make sure all rows are transferred (count all table rows?)
• Validate the integrity (can one do md5s on all rows?)
• Validate things like number of items and sums via the Django ORM

[carlio]

Can you also do this process with the testdata please? So that newly provisioned boxes have some data in them.

[kardan]

Sure!

[kardan]

Good progress on this, need to add tests I had before I rewrote the script. But should not require much effort.

[kardan]

Running the script on a larger dataset I found I snag with location data. Projectlocation items starting with id 259 have a precision that is above the Postgres double precision type (15 ). Hence the long lat is concatenated in the postgres data. Since we don't want to loose data this is a problem.

BUT then the mysql original latitude data is: "5.19583258792768" - that is a very precise location.
If I understand https://en.wikipedia.org/wiki/Decimal_degrees correct 8 decimals would be a location almost within a mm. We have 14 decimals. This make me think that either I have something backwards or we have oddly precise locations stored.

[kardan]

A tour with vagrant:

We need to pull down a new base box with Postgres installed so, on the feature/813_postgres_v2 branch:

$ cd vagrant
$ vagrant destroy
$ git checkout -b feature/813_postgres_v2 origin/feature/813_postgres_v2
$ vagrant up
```shell
This will pull down the new base box with postgres installed. Once we have that let's setup postgres:
```shell
$ vagrant ssh
vagrant@rsr1:~$ sudo su postgres
postgres@rsr1:/home/vagrant$ psql
postgres=# CREATE USER rsr WITH PASSWORD 'password';
CREATE ROLE
postgres=# CREATE DATABASE rsr OWNER rsr TEMPLATE template0 ENCODING 'UTF8';
CREATE DATABASE
postgres=# \q
postgres@rsr1:/home/vagrant$ exit

Now we need to reconfigure our database settings, to use postgres as default and name the old mysql database to "mysql" which the validation script expects. Add the following to your 66_local.conf:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'rsr',
        'USER': 'rsr',
        'PASSWORD': 'password',
        'HOST': '192.168.50.101',
        'PORT': '5432',
    },

    'mysql': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'rsr',
        'USER': 'rsr',
        'PASSWORD': 'password',
        'HOST': 'mysql-localdev.localdev.akvo.org',
        'PORT': '',
    }
}

Let's start with deleting all old sessions by truncating the django_session table

$ mysql --user=rsr --password=password rsr
mysql> Truncate table django_session;
mysql> \q

Now it's time to setup the migration tool. The first time the script is executed a config file will be generated. So we will start by running the script, then edit the config and finally run the script with our settings.

vagrant@rsr1:~$ sudo su rsr
rsr@rsr1:/home/vagrant$ cd /var/akvo/rsr/
rsr@rsr1:~$ . venv/bin/activate
(venv)rsr@rsr1:~$ cd code

There is a config in the dev branch but maybe not on live so instructions are for non committed config file.

(venv)rsr@rsr1:~/code$ py-mysql2pgsql
No configuration file found.
A new file has been initialized at: mysql2pgsql.yml
Please review the configuration and retry...
(venv)rsr@rsr1:~/code$ vi mysql2pgsql.yml

Make sure the config file looks like this:

mysql:
 hostname: mysql-localdev.localdev.akvo.org
 port: 3306
 username: rsr
 password: password
 database: rsr
 compress: false
destination:
 # if file is given, output goes to file, else postgres
 file:
 postgres:
  hostname: 192.168.50.101
  port: 5432
  username: rsr
  password: password
  database: rsr

Now we can migrate and then run the validation script. Here we might want to introduce a massage script that clean up some data / removes tables - we need to review that. But having data with trailing whitespace is something which we might want to do. Anyway, let's migrate and validate:

(venv)rsr@rsr1:~/code$ py-mysql2pgsql
(venv)rsr@rsr1:~/code$ validate_postgres.py
....
--------------------------------------------------------
Ran n tests in n s
OK

Done 

(venv)rsr@rsr1:~/code$ exit
exit
vagrant@rsr1:~$ exit
$ 

Now we can remove the mysql database config entry. We still have the old mysql database installed so we should come up with a plan for taking care of that.

[kardan]

@zzgvh & @KasperBrandt I updated the comment above, would be glad for input / review. Actual validation is here: https://github.com/akvo/akvo-rsr/blob/feature/813_postgres/validate_postgres_data.py

[kardan]

Note: I gave up on using checksums on things like collection / column since that would have implied keeping things in a specific order.

[KasperBrandt]

My only comment is that when running (venv)rsr@rsr1:~/code$ py-mysql2pgsql for the first time, there was already a config file (which is similar to the one mentioned above). Other than that, RSR seems to run fine with postgres and all tests pass when these steps are followed.

I only tested it on the test database so far, will try importing the live DB later.

[kardan]

@zzgvh would be grateful for another go, now when the script is a bit less RAM consuming.

[kardan]

@carlio how are the VM's compared to test/staging/production in terms of memory?

[carlio]

Which VM? The Vagrant ones? I think they only have 384mb by default but you can tweak that in the Vagrantfile like this: https://github.com/akvo/akvo-provisioning/blob/develop/vagrant/boxes/puppet/Vagrantfile#L30

For test/UAT/live the machines have 4GB/2GB/4GB but test1 has many other services while uat1 is mostly empty.

[carlio]

Is is possible to run this offline separately? We could fire up a specialised VM only for this task with loads of RAM if you want.

[kardan]

@carlio mostly asking so we know what will happen when we get to test -> production phase

[kardan]

New "help" at https://github.com/akvo/akvo-rsr/blob/feature/813_postgres_v2/pg_readme.md that includes a fix for the bug with wrong column types.

[adriancollier]

All work finalised - with this change, RSR should run on a Postgres database.