So you have a Cloud Foundry deployed with the `cf-release` codebase
and you have noticed that in the newer release Cloud Foundry switched to MySQL.
And now you want to upgrade. But first you need to move from PostgreSQL so it will make your life easier for the future upgrades!
The good thing is…
It can be done pretty easy without much downtime.
Please note! that you need to first test the migration in a sandbox/test environment.
LET’S GET STARTED!!
Prerequisites:
- Access to a jumpbox
- pg2mysql
go get --insecure github.com/pivotal-cf/pg2mysql/cmd/pg2mysqlManifest changes:
First let’s copy your current manifest (e.g. cf.yml) that you used to deploy your Cloud Foundry with to a file named cf-mysql.yml.
mv cf.yml to cf-mysql.yml`
Next you will create config files to hold connection information to the ccdb, uaa, and diego databases:
mkdir -p db_configs
touch db_configs/ccdb.yml
touch db_configs/uaadb.yml
touch db_configs/diegodb.yml
Now you will need to populate the contents of each of the files using your favorite editor. For each file you will find the connection information in the cf-mysql.yml file.
Note: You’ll need to edit anything in ALLCAPS to match your environment
Edit the file db_configs/ccdb.yml:
mysql:
database: ccdb
username: ccadmin
password: CHANGEME
host: MYSQL_PROXY_IP
port: 3306
postgresql:
database: ccdb
username: ccadmin
password: CHANGEME
host: POSTGRES_IP
port: 5524
ssl_mode: disable
Edit the file db_configs/uaadb.yml:
mysql:
database: uaadb
username: uaaadmin
password: CHANGEME
host: MYSQL_PROXY_IP
port: 3306
postgresql:
database: uaadb
username: uaaadmin
password: CHANGEME
host: POSTGRES_IP
port: 5524
ssl_mode: disable
Edit the file db_configs/diegodb.yml:
mysql:
database: diego
username: diego
password: CHANGEME
host: MYSQL_PROXY_IP
port: 3306
postgresql:
database: diego
username: diego
password: CHANGEME
host: POSTGRES_IP
port: 5524
ssl_mode: disable
Now back to your cf.yml file, find the postgres job in your manifest:
Change
- name: postgres
release: cf
To
- name: postgres
release: cf
provides:
postgres: {as: pgdb}
Also add the following below the postgres job (please note that the dbnames/passwords in the seeded_databases needs to reflect the ones that we just used in the previous config files)
- default_networks:
- name: cf1
instances: 1
name: mysql_proxy_z1
networks:
- name: cf1
static_ips:
- YOURSTATICIP
resource_pool: small
properties:
cf_mysql:
proxy:
api_password: CHANGEME
templates:
- consumes:
consul_client: nil
consul_common: nil
consul_server: nil
name: consul_agent
release: cf
- name: proxy
release: cf-mysql
provides:
mysql-database: {as: db}
- name: metron_agent
release: cf
update:
serial: true
- default_networks:
- name: cf1
instances: 1
name: mysql_z1
networks:
- name: cf1
persistent_disk: 102400
resource_pool: large
properties:
cf_mysql:
mysql:
admin_password: CHANGEME
cluster_health:
password: CHANGEME
galera_healthcheck:
endpoint_password: CHANGEME
db_password: CHANGEME
seeded_databases:
- name: ccdb
username: ccadmin
password: CHANGEME
- name: uaadb
username: uaaadmin
password: CHANGEME
- name: diego
username: diego
password: CHANGEME
templates:
- consumes:
consul_client: nil
consul_common: nil
consul_server: nil
name: consul_agent
release: cf
- name: mysql
release: cf-mysql
- name: metron_agent
release: cf
update:
serial: true
Now find 3 entries in the manifest that refer to db_scheme: postgres and change them from:
address: PG_STATIC_IP
db_scheme: postgres
port: 5524
to:
address: MYSQL_PROXY_STATIC_IP
db_scheme: mysql
port: 3306
For the uaa database the naming is a bit different, the schema name is postgresql, not postgres.
For the diego database they use the following properties, change these accordingly as well:
db_host: MYSQL_PROXY_STATIC_IP
db_driver: mysql
db_port: 3306
Deployment:
Now the fun/scary part… deploying.
Deploy your bosh manifest
bosh -d YOURDEPLOYMENTNAME deploy cf-mysql.yml
The deployment may fail but that is often due to some drain scripts. In my case, it was the api_worker jobs that failed during the redeploy but it could be any job.
This can be solved by skipping the drain scripts and redeploying.
In this case, we force the job to stop and skip the drain scripts
bosh -d YOURDEPLOYMENTNAME stop api_worker_z1 --force --skip-drain
bosh -d YOURDEPLOYMENTNAME stop api_worker_z2 --force --skip-drain
bosh -d YOURDEPLOYMENTNAME deploy cf-mysql.yml
After a successful redeploy we are going to recreate the workers with the new config:
bosh -d YOURDEPLOYMENTNAME recreate api_worker_z1
bosh -d YOURDEPLOYMENTNAME recreate api_worker_z2
At this point the api servers will be pointed to the new mysql databases instead of the older postgres databases. If you log into CF at this point it will appear to be a completely new/clean environment. This is because we still need to migrate the data from postgres to mysql.
To perform the data migration we need to stop every job that connects to the databases:
- uaa
- api
- api_worker
- clock
- diego_database
Loop through and stop each on of these, for example:
bosh -d YOURDEPLOYMENTNAME stop uaa_z1
Data Migration:
Now for the actual migration:
Run the pg2mysql with the config files we previously populated:
pg2mysql -c db_configs/uaadb.yml migrate --truncate
pg2mysql -c db_configs/ccdb.yml migrate --truncate
pg2mysql -c db_configs/diegodb.yml migrate --truncate
SideNote! the truncate option will remove all the data in the target database, this is needed because MySQL is case sensitive and PostgreSQL is not
You can also use the tool to verify that all the data was migrated:
pg2mysql -c db_configs/uaadb.yml verify
pg2mysql -c db_configs/ccdb.yml verify
pg2mysql -c db_configs/diegodb.yml verify
If the tool successfully validates the migration you are ready to fire up all the vms we stopped in Cloud Foundry again:
bosh -d YOURDEPLOYMENTNAME start
At this point you can check and verify your CF if all apps are up and running.
Please note that it can take a while before all apps are up and running again, depending on how many apps/cells you have.
Spread the word
