1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
= How to make database schema changes =
Important: This documentation is mirrored on https://launchpad.canonical.com/DatabaseSchemaChanges
So, make sure to copy any changes you make here to the wiki page!
== Making schema changes ==
1. Make an SQL file in `database/schema/pending/` containing the changes you want, excluding any changes to default values.
2. Run `make schema` to get a pristine database of sampledata.
3. Run the SQL on the database (`psql launchpad_dev -f your-patch.sql`) to ensure it works.
4. In `database/schema/`, `make newsampledata`.
5. Replace `current.sql` with `newsampledata.sql`.
6. Copy your SQL into `database/schema/` with a name like `patch-xx-99-0.sql` (where ''xx'' matches the existing patches), and ending with the line "INSERT INTO Launchpad``Database``Revision VALUES (xx, 99, 0);". Both the name and this last line should be renamed as directed when the patch is approved.
At this point `make schema` should work again.
7. Make any necessary changes to `lib/canonical/lp/dbschema.py`, `database/schema/fti.py`, and to the relevant `lib/canonical/launchpad/database/` classes.
8. Make any changes to the SQL patch to reflect new default values.
== Proposing database schema changes ==
For any tables and fields that you change with an SQL script via Stuart
(stub on IRC), please make sure you include comments.
The process now looks like this:
1. If you think the proposed changes may be controversial, or you are just ensure, it is worth discussing the changes on the launchpad mailing list first to avoid wasting your time.
2. Work on the patch in a branch as documented above.
3. Add the details of your branch to StuartBishop's review queue on PendingReviews.
4. Work on it in revision control till StuartBishop is happy. He will give you an official patch number
5. Rename your patch to match the official patch number
6. Once code is also ready and reviewed, commit as normal.
== 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
}}}
= Production Database Upgrades =
First get a copy of the Launchpad source built and ready on emperor, readable
by the postgres user.
Then, before you do anything else, inform #canonical, #launchpad and
#ubuntu-devel that Launchpad and the Wiki authentication systems will be
offline for 30 minutes (or longer if there is data migration to do).
Stop PostgreSQL:
% pg_ctl -m fast stop
Start PostgreSQL without external connections
% pg_ctl start -o '--tcpip-socket=false' -o '--ssl=false' \
-l /var/log/postgresql/postgres.log
As user postgres, run the upgrade.py, fti.py and security.py scripts.
fti.py can be skipped if you are sure no changes need to be made to the
full text indexes (ie. fti.py has not been modified and no patches affect
the tables being indexed). This process should work without issues, as any
issues (such as DB patches not working on production data) will have been
picked up from the daily updates to the staging environment. Do not run
update.py using the --partial option to ensure that changes will be rolled
back on failure.
% cd dists/launchpad/database/schema
% env LPCONFIG=production \
python upgrade.py -d launchpad_prod -U postgres -H ''
% env LPCONFIG=production \
python fti.py -d launchpad_prod -U postgres -H ''
% env LPCONFIG=production \
python security.py -d launchpad_prod -U postgres -H ''
Restart PostgreSQL with external connections
% pg_ctl -m fast stop
% pg_ctl start -l /var/log/postgresql/postgres.log
At this point, services should be restarted that don't automatically
reconnect, such as the Launchpad web application servers and the Librarian.
== Create a new development baseline ==
After a production update, you should occasionally copy the live schema
back into the development tree. This ensures that any differences that have
crept in between the development database and reality are fixed.
The new baseline dump (launchpad-XX-0-0.sql in this directory) can
be generated on production using the following::
pg_dump -Fc --schema-only --no-owner --no-acl --schema=public \
launchpad_prod > launchpad.dump
pg_restore -l launchpad.dump | \
grep -v PROCEDURAL | grep -v COMMENT | \
grep -v FUNCTION | grep -v VIEW > launchpad.list
pg_restore -l launchpad.dump | grep VIEW >> launchpad.list
echo "-- Generated `date`" > launchpad.sql
echo 'SET client_min_messages=ERROR;' >> launchpad.sql
pg_restore --no-owner --no-acl -L launchpad.list launchpad.dump | \
grep -v '^--' >> launchpad.sql
Move all the existing patches and the old baseline to the archive directory.
Add the new baseline using the next revision number (should be in sync
with the production release version). Create a patch-XX-0-0.sql patch
to populate the LaunchpadDatabaseRevision table with the correct value
so the tests pass.
= Notes =
There is a Makefile in launchpad/database/schema that will
create the launchpad_test database (if it doesn't already exist),
drop all your tables and create the current schema with all patches
applied.
If you want to check anything into the launchpad/database/schema
directory, please do not give it a .sql extension or you will might
confuse the simple Makefile.
|