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
|
Update
======
We are now using launchpad-2-00-0.sql as our baseline, which is a
schema dump of the production server. The old files have all been
moved to the archive directory
How to make database schema changes
===================================
First - don't. Unless there is a problem doing so, get Stuart
or the acting DBA to do it by emailing the launchpad@ mailing list
with your request. The reason we are currently using launchpad@ is so that
other launchpad team members can see what the requests are and have a
chance to comment if it affects their work in any way.
The rest of these instructions are for the acting DBA or for 'we have
to do this now' work.
1. `cd launchpad/database/schema; dropdb launchpad_test; make`
This is the simplest way of making sure your database is clean.
Failing to drop your database risks a bad sampledata export.
2. Create a file launchpad/database/schema/patch-x-yy-0.sql, where
'x' is the current major database revision number (and should not be
changed except after a push to production) and yy is the next available
number. It is important that yy is two digits, as otherwise the patch
will be applied in the wrong order.
3. Put the necessary commands in patch-x-yy-0.sql
4. Inspect the launchpad_prod database on emperor. Check all affected tables
to see if they contain data. If they do, it may be necessary to add extra
commands to the patch to ensure data is migrated or else the patch will fail
when applied to production.
5. Run the commands in patch-x-yy-0.sql one at a time in an interactive
session. This lets you catch errors and cases where the sample data
needs to be migrated. Issue SQL commands as necessary o port sample
data to the new schema.
6. Run `make newsampledata` in launchpad/database/schema.
7. Run `diff current.sql newsampledata.sql` in launchpad/database/sampledata
to confirm the changes are sane
8. Move newsampledata.sql to current.sql
9. Run `make` in launchpad/database/schema. Alter patch as necessary
to minimize noise.
10. Commit
11. Respond to message in launchpad@ informing the group the modification has
been made.
Steps 6, 7 & 8 can be ommitted of course if no table modifications are being
made (eg. just indexes being added).
How to roll out a new database revision
=======================================
1. Create a new database on emperor (or wherever) using the latest
available dump.
2. Check a checkout of the patches you need to roll out
3. Connect to the the test database you setup.
4. Run the patches one at a time, in order, using `\i patch-2-01-0.sql`.
Ensure there are no errors. If things have been done right, there won't be
5. Get clients shutdown nicely
6. Run 'pg_ctl -m fast stop' to drop any remaining connections
7. Turn off remote access in /etc/postgres/pg_hba.conf
8. Run 'pg_ctl start' to start postgres
9. Connect to the real database, and run the patches in order as before except
this time making sure it is done in a single transaction block (`BEGIN`
to start a transaction, `COMMIT` to save it and `ABORT` to rollback).
10. Shutdown again. Revert changes to /etc/postgres/pg_hba.conf. Startup.
Notes
=====
launchpad.sql has been moved from launchpad/database/launchpad.sql
to launchpad/database/schema/launchpad-1-0-0.sql. This file should
never be altered (if someone can tell me how to make arch throw a hissy
fit is someone tries, please let me know).
Alterations to the launchpad database schema need to be done as
patches - you can see the first one in
launchpad/database/schema/patch-1-1-0.sql.
I think a good process for changes to the schema is to email requested
changes to this mailing list (launchpad@lists.ubuntu.com)
and the acting DBA will approve the changes and put them in the
correct place. This is me this week, and Robert Collins for a few
weeks following that. I think the public forum is good for this so
everyone knows who is pissing in the pool and why. After a change,
I don't think I can fix any breakage caused in any areas I'm not
directly responsible or semi-responsible for, as this might cause
conflicts with work-in-progress I'm not aware of. Does this all
sound sane?
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. The process to do this will become cleverer in the future
and not cause your sample data to be reset. A number of the targets
in the old Makefile (launchpad/database/Makefile) are now broken.
If you want to check anything into the launchpad/database/schema
directory, please do not give it a .sql extension or you will confuse
the simple Makefile.
|