Overview

We change the schema through database patches. These live in branches that go through review and landing, just like code changes.

Schema patches are either applied while the system has no activity ('cold patches') or while the system is under load ('hot patches'). Some things, like changing a table and adding an index, will need to be split into two separate patches - one hot and one cold.

Schema patches must not be combined with changes to the Launchpad python code - any branch landing on devel or db-devel must be one or the other. Exceptions to this rule require approval from the technical architect / project lead, because deploying them will require a 1 hour plus downtime window.

Schema patches always land on lp:launchpad/db-devel. After they are made live they will be promoted to lp:launchpad/devel as part of the go-live process.

Hot Patches

Database/LivePatching contains the explanation of how hot patching works and for what sorts of things we can hot patch. Its the authority - as we get better tooling we may expand the code that can be hot patched.

QA hot patches by having LOSA apply it to qastaging.

Cold Patches

Anything that is not a hot patch is a cold patch and can only be applied while the appservers and so on are disconnected from the database cluster.

For qa on a cold patch, check the application time from the staging log - it must be < 15 seconds [even with cold disk cache], or we will exceed our downtime window. To exceed that window requires signoff by the technical architect or project lead.

Deploying patches

After successful QA on a patch, request a deploy via the internal LaunchpadProductionStatus page.

Reviews

All schema changes should have reviews of type "db" requested from both the DBA (stub) and the technical architect (lifeless). An approve vote from either is sufficient to land the patch; generally the DBA does them all.

As schema changes have no appserver code changes landed at the same time, no other reviews are needed (unless an exception to that rule has been granted, in which case a normal review is also needed).

Changes to the permissions in database/schema/security.cfg or the comments in database/schema/comments.sql are not schema patches and do not require db review when done on their own. If they are included in a schema patch then the db reviewer will review them.

Patch ids

The schema application code needs a unique id for each patch. This is allocated by editing a shared document stored in the dbpatches branch. If you are in ~launchpad please allocate this yourself. Other developers can ask any ~launchpad member to allocate a patch number for them.

Step by step procedure

1. Prepare a branch containing your database patch for review. Feel free to request assistance from or outsource work to the DBA (stub).

2. Submit a merge proposal for your branch into lp:launchpad/db-devel (lp:~launchpad-pqm/launchpad/db-devel), requesting two db reviews from respectively the technical architect (lifeless) and the DBA (stub).

3. Iterate on the branch as needed. The DB review process can sometimes require significant changes to achieve acceptable performance on either the patch application or queries / updates to the resulting schema.
4. The schema change is approved for landing when you have an 'Approved' vote from the DBA or the Technical Architect (unless they explicitly say they want the others input).
5. Ensure the sampledata is up to date on your branch.

6. Land the branch on lp:launchpad/db-devel using ec2 land (lp:~launchpad-pqm/launchpad/db-devel) unless someone has stated it is being landed it on your behalf. (Only Canonical staff can do the landing step).

Making a database patch

You need to run these steps whenever you make a schema change, regardless of whether you intend to delete sample data or not. For example, if you are adding a new column to the Person table, these steps ensure that the sample data will include this new column.

1. Run make schema to get a pristine database of sample data.

2. Claim a patch number in the dbpatches branch (be sure to commit and push back to the branch).

3. Create a SQL file in database/schema/ containing the changes you want. It should look like this:

-- Copyright 2010 Canonical Ltd.  This software is licensed under the
-- GNU Affero General Public License version 3 (see the file LICENSE).

SET client_min_messages=ERROR;

-- put your changes in here.

INSERT INTO LaunchpadDatabaseRevision VALUES (XXXX, YY, Z);

1. Don't use the TRUNCATE or DROP TABLE statements as they don't work with Slony-I replication.

2. To drop a table, rename it into the todrop namespace using a statement like ALTER TABLE FooBar RENAME TO todrop.FooBar.

3. Do not migrate data in schema patches unless the data size is extraordinarily small (< 100's of rows).

4. Similarly, new columns must default NULL unless the data size is extraordinarily small.
   1. If you do decide to do data initialization or data migration, don't use indeterminate values that might give different results on different databases, such as random() or CURRENT_TIMESTAMP. This would create skew in our data between the master and slave databases. If you want such a value, you need to do that via a garbo job post-schema change.

5. Run your new SQL patch on the development database to ensure that it works. Do this by running psql launchpad_dev -1 -f your-patch.sql

6. Run psql launchpad_ftest_playground -f your-patch.sql as the ftest playground db is used to generate newsampledata.sql in the following step. Also run psql launchpad_dev -f your-patch.sql to update the dev database's sampledata.

7. Run make newsampledata.

8. Review the sample data changes that occured using diff database/sampledata/current.sql database/sampledata/newsampledata.sql. This diff can be hard to review as-is. You might want to use a graphical diff viewer like kompare or meld which will make it easier. Make sure that you understand all the changes you see.

9. In database/sampledata/, move newsampledata.sql to current.sql, replacing the latter.

10. In database/sampledata/, move newsampledata-dev.sql to current-dev.sql, replacing the latter.

11. Move your pending SQL file into database/schema/ with a name like patch-xx-yy-zz.sql (where xx matches the existing patches), and ending with the line INSERT INTO LaunchpadDatabaseRevision VALUES (xx, yy, zz);.

12. Run make schema again to ensure that it works, and that you now have a pristine database with the new sample data.

13. Comments on new tables and columns need to be added to database/schema/comments.sql.

14. Make any necessary changes to database/schema/fti.py, database/schema/security.cfg.

15. Run the full test suite to ensure that your new schema doesn't break any existing tests/code by side effect.

16. Commit, push and propose for merging to lp:launchpad/db-devel

Sample data

Let's say your branch needs to make changes to the database schema. You need to follow the steps on this page to ensure that the sample data is updated to match your schema changes.

We have deprecated sample data. That means that you should never add to the sample data.

In fact, there are now two sets of sampledata that need to be updated.

We use sample data to provide well-known baseline data for the test suite, and to populate a developer's Launchpad instance so that launchpad.dev can display interesting stuff. There are some guidelines and recommendations you should be aware of before you make changes to the test suite sample data, or you may break the tests for yourself or others.

Please note that sample data is for developer's instances only. It would make no sense to use the sample data on production systems!

If your tests require new data, you should create the data in your test's harness instead of adding new sample data. This will often make the tests themselves more readable because you're not relying on magical values in the sample database. Doing it this way also reduces the chance that your changes will break other tests by side-effect. Add the new data in your test's setUp() or in the narrative of your doctest. Because the test suite uses the launchpad_ftest database, there is no chance that running the test suite will accidentally alter the sample data.

However, if you interact with the web U/I for launchpad.dev your changes will end up in the launchpad_dev database. This database is used to create the new sample data, so it is imperative that you run make schema to start with a pristine database before generating new sample data. If in fact you do want the effects of your u/i interactions to land in the new sample data, then the general process is to

• run make schema

• interact with launchpad.dev

• follow the make newsampledata steps above.

Be aware though that your generation of new sample data will probably have an effect on tests not related to your changes! For example, if you generate new karma events, you will break the karma_sample_data tests because they expect all karma events to be dated prior to the year 2002. If you make changes to the sample data, you must run the full test suite and ensure that you get no failures, otherwise there is a very high likelihood that you will break the trunk.

Resolving schema conflicts

Resolving conflicts in current.sql manually is usually more trouble than it's worth. Instead, first resolve any conflicts in comments.sql, then:

cd database/schema/
mv {patch-in-question}-0.sql comments.sql pending/
cp {parent branch, e.g. rocketfuel}/database/schema/comments.sql ./
cp ../sampledata/current.sql.OTHER ../sampledata/current.sql
make
psql launchpad_dev -f pending/patch-xx-99-0.sql
make newsampledata
mv ../sampledata/newsampledata.sql ../sampledata/current.sql
mv pending/{patch-in-question}-0.sql pending/comments.sql ./
make   # Just to make sure everything works
cd ../..
bzr resolve database/sampledata/current.sql

Notes on Changing security.cfg

It is possible to land changes to security.cfg on lp:~launchpad-pqm/launchpad/devel rather than lp:~launchpad-pqm/launchpad/db-devel. These changes are deployed during nodowntime rollouts.

Note that adding new users requires manual DB reconfiguration, so you need to ask a LOSA to grant access to relevant machines before the nodowntime or downtime deployment that needs them.