Postgresql Mysql migration upgrade (sabre outage)

Print Friendly, PDF & Email

Due to time constraints and security concerns I will be going ahead with the low risk process of upgrading sabre to Debian lenny next Tuesday the 13th. I will perform the upgrade between 10pm and 11pm. As this lives on a paravirtualised VM a reboot will be in the region of 20-30s. I expect there to be very minimal disruption to service during the upgrade process. Versions of Mysql and Postgres will NOT change during this upgrade. It is simply the underlying OS which is being upgraded.

Additionally, shortly after the upgrade (at least 2 weeks), I will be wanting to migrate the SQL database services from sabre to their own separate virtual machines. This will involve a change in the host name of the server:

Mysql databases for production services will be provided by my-p0.ilrt.bris.ac.uk

Postgresql databases for production services will be provided by pg-p0.ilrt.bris.ac.uk

I would appreciate your comments on the following suggested ways forward.

Scenario 1 ( if I move the databases)

Mysql will be a straight migration of all databases hosted on sabre in their current form. Mysql binary installation will be managed as a debian package replacing the current source built installation. The version of Mysql will remain the same.

Postgresql will be migrated in the first instance into a version 7.4 cluster container on pg-p0 and then upgraded to a version 8.3 cluster using debian postgresql-common multi instance management. Using this tool will allow for easier management of instances of postgresql on a single server. Again the postgresql binaries will be managed using debian packages.

In both cases all connector configuration would need to be changed together. Identifying all locations of configuration may prove difficult and timing would be sensitive.

Scenario 2 (if developers move databases)

Similar to scenario 1 but both mysql and postgresql databases will be empty. It will be the developers task to identify the database needed and the application using it. Then perform an export of the current database and import into the new server/database. Finally switching the site to use the new database.

Pros are that we’d be able to weed out any unwanted databases and ensure a clean start. This would also cause the least amount of downtime. Downside is that it would involve more people and probably extend the time taken to coordinate the move.

Scenario 3 (sql “proxy”)

There is a possibility that an SQL “proxy” host could be used in place of the sabre identity (IP address). In this scenario a simple host binds the sabre IP address and accepts connections on database tcp ports. It then forwards these requests onto the real database server.

Pros are that we could possibly use a load balancing/failover mechanism to cater for overloaded/unstable backends. This could be accomplished using connection pooling and high availability. It would also relieve the need to rename the host used in connector configuration. Down side is that there is extended work involved in implementing this.

Future work

Once this move has been completed and once the new storage cluster is up and running the database disk storage will be migrated to our local SAN. I am also considering moving the VMs to the storage cluster as Solaris Zones.

However, I am running away with myself. What I would like to know now is what scenario is favoured from the above. We may need to go for all of the above in a staged fashion. I am wondering if a few developers could act as database switchers for other projects if time is slim for some people?