8687.15.9
by Karl Fogel
Add the copyright header block to more files (everything under database/). |
1 |
# Copyright 2009 Canonical Ltd. This software is licensed under the
|
2 |
# GNU Affero General Public License version 3 (see the file LICENSE).
|
|
5799.1.12
by Stuart Bishop
Replication maintenance scripts, work in progress |
3 |
|
4 |
"""Common helpers for replication scripts."""
|
|
5 |
||
5799.1.42
by Stuart Bishop
Review feedback, round 1 |
6 |
__metaclass__ = type |
7 |
__all__ = [] |
|
8 |
||
5799.1.12
by Stuart Bishop
Replication maintenance scripts, work in progress |
9 |
import subprocess |
10 |
import sys |
|
5799.1.30
by Stuart Bishop
Work in progress |
11 |
from tempfile import NamedTemporaryFile |
5799.1.55
by Stuart Bishop
Improve initialize, less magic dev setup |
12 |
from textwrap import dedent |
5799.1.12
by Stuart Bishop
Replication maintenance scripts, work in progress |
13 |
|
5799.1.55
by Stuart Bishop
Improve initialize, less magic dev setup |
14 |
from canonical.config import config |
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
15 |
from canonical.database.sqlbase import connect, sqlvalues |
5799.1.26
by Stuart Bishop
Work in progress |
16 |
from canonical.database.postgresql import ( |
5799.1.58
by Stuart Bishop
Hardcode slony user for slony daemon connections |
17 |
fqn, all_tables_in_schema, all_sequences_in_schema, ConnectionString |
5799.1.26
by Stuart Bishop
Work in progress |
18 |
)
|
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
19 |
from canonical.launchpad.scripts.logger import log, DEBUG2 |
5799.1.20
by Stuart Bishop
Work in progress |
20 |
|
5799.1.26
by Stuart Bishop
Work in progress |
21 |
|
5799.1.55
by Stuart Bishop
Improve initialize, less magic dev setup |
22 |
# The Slony-I clustername we use with Launchpad. Hardcoded because there
|
23 |
# is no point changing this, ever.
|
|
24 |
CLUSTERNAME = 'sl' |
|
5799.1.26
by Stuart Bishop
Work in progress |
25 |
|
26 |
# The namespace in the database used to contain all the Slony-I tables.
|
|
27 |
CLUSTER_NAMESPACE = '_%s' % CLUSTERNAME |
|
28 |
||
9795.4.2
by Stuart Bishop
Review feedback |
29 |
# Replication set id constants. Don't change these without DBA help.
|
30 |
LPMAIN_SET_ID = 1 |
|
31 |
HOLDING_SET_ID = 666 |
|
7675.395.72
by Stuart Bishop
Remove remaining authdb awareness from replication scripts |
32 |
LPMIRROR_SET_ID = 4 |
9795.4.2
by Stuart Bishop
Review feedback |
33 |
|
7675.395.72
by Stuart Bishop
Remove remaining authdb awareness from replication scripts |
34 |
# Seed tables for the lpmain replication set to be passed to
|
5799.1.26
by Stuart Bishop
Work in progress |
35 |
# calculate_replication_set().
|
7675.395.72
by Stuart Bishop
Remove remaining authdb awareness from replication scripts |
36 |
LPMAIN_SEED = frozenset([ |
5799.1.26
by Stuart Bishop
Work in progress |
37 |
('public', 'account'), |
7675.395.72
by Stuart Bishop
Remove remaining authdb awareness from replication scripts |
38 |
('public', 'openidnonce'), |
7675.88.8
by Stuart Bishop
Stop ShipIt OpenID consumer sharing tables with the SSO server, dev replication setup fixes and test fixes |
39 |
('public', 'openidassociation'), |
5799.1.26
by Stuart Bishop
Work in progress |
40 |
('public', 'person'), |
41 |
('public', 'launchpaddatabaserevision'), |
|
9795.4.27
by Stuart Bishop
Cache database replication lag information as querying the live Slony tables can be slow |
42 |
('public', 'databasereplicationlag'), |
5799.1.26
by Stuart Bishop
Work in progress |
43 |
('public', 'fticache'), |
44 |
('public', 'nameblacklist'), |
|
7675.88.8
by Stuart Bishop
Stop ShipIt OpenID consumer sharing tables with the SSO server, dev replication setup fixes and test fixes |
45 |
('public', 'openidconsumerassociation'), |
46 |
('public', 'openidconsumernonce'), |
|
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
47 |
('public', 'oauthnonce'), |
5799.1.26
by Stuart Bishop
Work in progress |
48 |
('public', 'codeimportmachine'), |
49 |
('public', 'scriptactivity'), |
|
50 |
('public', 'standardshipitrequest'), |
|
51 |
('public', 'bugtag'), |
|
52 |
('public', 'launchpadstatistic'), |
|
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
53 |
('public', 'parsedapachelog'), |
54 |
('public', 'shipitsurvey'), |
|
7675.395.48
by Stuart Bishop
Add DatabaseReplicationLag as a seed table to pass staging restore sanity checks |
55 |
('public', 'databasereplicationlag'), |
5799.1.26
by Stuart Bishop
Work in progress |
56 |
])
|
57 |
||
58 |
# Explicitly list tables that should not be replicated. This includes the
|
|
59 |
# session tables, as these might exist in developer databases but will not
|
|
60 |
# exist in the production launchpad database.
|
|
61 |
IGNORED_TABLES = set([ |
|
7675.414.5
by Stuart Bishop
Make replication ignore lp_* tables, as we are going to replicate them manually |
62 |
# Session tables that in some situations will exist in the main lp
|
63 |
# database.
|
|
64 |
'public.secret', 'public.sessiondata', 'public.sessionpkgdata', |
|
65 |
# Mirror tables, per Bug #489078. These tables have their own private
|
|
66 |
# replication set that is setup manually.
|
|
7675.395.63
by Stuart Bishop
lp_account needs to be ignored by our replication scripts as it is handled manually |
67 |
'public.lp_account', |
7675.414.5
by Stuart Bishop
Make replication ignore lp_* tables, as we are going to replicate them manually |
68 |
'public.lp_person', |
69 |
'public.lp_personlocation', |
|
70 |
'public.lp_teamparticipation', |
|
7675.686.9
by Stuart Bishop
Basic database utilization report |
71 |
# Database statistics
|
72 |
'public.databasetablestats', |
|
73 |
'public.databasecpustats', |
|
9795.4.18
by Stuart Bishop
Review feedback |
74 |
# Ubuntu SSO database. These tables where created manually by ISD
|
75 |
# and the Launchpad scripts should not mess with them. Eventually
|
|
9795.4.19
by Stuart Bishop
Speling |
76 |
# these tables will be in a totally separate database.
|
9795.4.18
by Stuart Bishop
Review feedback |
77 |
'public.auth_permission', |
78 |
'public.auth_group', |
|
79 |
'public.auth_user', |
|
80 |
'public.auth_message', |
|
81 |
'public.django_content_type', |
|
82 |
'public.auth_permission', |
|
83 |
'public.django_session', |
|
84 |
'public.django_site', |
|
85 |
'public.django_admin_log', |
|
86 |
'public.ssoopenidrpconfig', |
|
87 |
'public.auth_group_permissions', |
|
88 |
'public.auth_user_groups', |
|
9795.4.14
by Stuart Bishop
Handle database dumps containing login service tables, a side effect from standalone authservice progress |
89 |
'public.auth_user_user_permissions', |
7675.395.51
by Stuart Bishop
Add more tables created on production db for ISD |
90 |
'public.oauth_nonce', |
91 |
'public.oauth_consumer', |
|
92 |
'public.oauth_token', |
|
93 |
'public.api_user', |
|
94 |
'public.oauth_consumer_id_seq', |
|
95 |
'public.api_user_id_seq', |
|
96 |
'public.oauth_nonce_id_seq', |
|
7675.414.5
by Stuart Bishop
Make replication ignore lp_* tables, as we are going to replicate them manually |
97 |
])
|
9795.4.14
by Stuart Bishop
Handle database dumps containing login service tables, a side effect from standalone authservice progress |
98 |
|
99 |
# Calculate IGNORED_SEQUENCES
|
|
100 |
IGNORED_SEQUENCES = set('%s_id_seq' % table for table in IGNORED_TABLES) |
|
5799.1.26
by Stuart Bishop
Work in progress |
101 |
|
102 |
||
5799.1.30
by Stuart Bishop
Work in progress |
103 |
def slony_installed(con): |
104 |
"""Return True if the connected database is part of a Launchpad Slony-I
|
|
105 |
cluster.
|
|
106 |
"""
|
|
107 |
cur = con.cursor() |
|
108 |
cur.execute(""" |
|
109 |
SELECT TRUE FROM pg_class,pg_namespace
|
|
110 |
WHERE
|
|
111 |
nspname = %s |
|
112 |
AND relname = 'sl_table'
|
|
113 |
AND pg_class.relnamespace = pg_namespace.oid
|
|
114 |
""" % sqlvalues(CLUSTER_NAMESPACE)) |
|
115 |
return cur.fetchone() is not None |
|
116 |
||
117 |
||
5799.1.55
by Stuart Bishop
Improve initialize, less magic dev setup |
118 |
class TableReplicationInfo: |
119 |
"""Internal table replication details."""
|
|
120 |
table_id = None |
|
121 |
replication_set_id = None |
|
122 |
master_node_id = None |
|
123 |
||
124 |
def __init__(self, con, namespace, table_name): |
|
125 |
cur = con.cursor() |
|
126 |
cur.execute(""" |
|
127 |
SELECT tab_id, tab_set, set_origin
|
|
128 |
FROM %s.sl_table, %s.sl_set |
|
129 |
WHERE tab_set = set_id
|
|
130 |
AND tab_nspname = %s |
|
131 |
AND tab_relname = %s |
|
132 |
""" % ( |
|
133 |
(CLUSTER_NAMESPACE, CLUSTER_NAMESPACE) |
|
134 |
+ sqlvalues(namespace, table_name))) |
|
135 |
row = cur.fetchone() |
|
136 |
if row is None: |
|
137 |
raise LookupError(fqn(namespace, table_name)) |
|
138 |
self.table_id, self.replication_set_id, self.master_node_id = row |
|
139 |
||
140 |
||
5799.1.42
by Stuart Bishop
Review feedback, round 1 |
141 |
def sync(timeout): |
5799.1.20
by Stuart Bishop
Work in progress |
142 |
"""Generate a sync event and wait for it to complete on all nodes.
|
7675.414.6
by Stuart Bishop
Split the DB patch so tables can be created and replicated before rollout |
143 |
|
5799.1.20
by Stuart Bishop
Work in progress |
144 |
This means that all pending events have propagated and are in sync
|
145 |
to the point in time this method was called. This might take several
|
|
146 |
hours if there is a large backlog of work to replicate.
|
|
5799.1.30
by Stuart Bishop
Work in progress |
147 |
|
5799.1.42
by Stuart Bishop
Review feedback, round 1 |
148 |
:param timeout: Number of seconds to wait for the sync. 0 to block
|
149 |
indefinitely.
|
|
5799.1.20
by Stuart Bishop
Work in progress |
150 |
"""
|
5799.1.42
by Stuart Bishop
Review feedback, round 1 |
151 |
return execute_slonik("", sync=timeout) |
5799.1.20
by Stuart Bishop
Work in progress |
152 |
|
153 |
||
7178.4.1
by Stuart Bishop
Script to repair a db built from a slony node dump |
154 |
def execute_slonik(script, sync=None, exit_on_fail=True, auto_preamble=True): |
5799.1.12
by Stuart Bishop
Replication maintenance scripts, work in progress |
155 |
"""Use the slonik command line tool to run a slonik script.
|
156 |
||
157 |
:param script: The script as a string. Preamble should not be included.
|
|
5799.1.20
by Stuart Bishop
Work in progress |
158 |
|
5799.1.30
by Stuart Bishop
Work in progress |
159 |
:param sync: Number of seconds to wait for sync before failing. 0 to
|
160 |
block indefinitely.
|
|
5799.1.42
by Stuart Bishop
Review feedback, round 1 |
161 |
|
162 |
:param exit_on_fail: If True, on failure of the slonik script
|
|
163 |
sys.exit is invoked using the slonik return code.
|
|
164 |
||
7178.4.1
by Stuart Bishop
Script to repair a db built from a slony node dump |
165 |
:param auto_preamble: If True, the generated preamble will be
|
166 |
automatically included.
|
|
167 |
||
5799.1.46
by Stuart Bishop
Review tweaks |
168 |
:returns: True if the script completed successfully. False if
|
5799.1.42
by Stuart Bishop
Review feedback, round 1 |
169 |
exit_on_fail is False and the script failed for any reason.
|
5799.1.12
by Stuart Bishop
Replication maintenance scripts, work in progress |
170 |
"""
|
171 |
||
5799.1.30
by Stuart Bishop
Work in progress |
172 |
# Add the preamble and optional sync to the script.
|
7178.4.1
by Stuart Bishop
Script to repair a db built from a slony node dump |
173 |
if auto_preamble: |
174 |
script = preamble() + script |
|
175 |
||
5799.1.20
by Stuart Bishop
Work in progress |
176 |
if sync is not None: |
7675.395.72
by Stuart Bishop
Remove remaining authdb awareness from replication scripts |
177 |
sync_script = dedent("""\ |
5799.1.55
by Stuart Bishop
Improve initialize, less magic dev setup |
178 |
sync (id = @master_node);
|
5799.1.20
by Stuart Bishop
Work in progress |
179 |
wait for event (
|
7675.395.72
by Stuart Bishop
Remove remaining authdb awareness from replication scripts |
180 |
origin = @master_node, confirmed = ALL,
|
5799.1.55
by Stuart Bishop
Improve initialize, less magic dev setup |
181 |
wait on = @master_node, timeout = %d); |
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
182 |
""" % sync) |
7675.395.72
by Stuart Bishop
Remove remaining authdb awareness from replication scripts |
183 |
script = script + sync_script |
5799.1.20
by Stuart Bishop
Work in progress |
184 |
|
5799.1.30
by Stuart Bishop
Work in progress |
185 |
# Copy the script to a NamedTemporaryFile rather than just pumping it
|
186 |
# to slonik via stdin. This way it can be examined if slonik appears
|
|
187 |
# to hang.
|
|
188 |
script_on_disk = NamedTemporaryFile(prefix="slonik", suffix=".sk") |
|
189 |
print >> script_on_disk, script |
|
190 |
script_on_disk.flush() |
|
191 |
||
192 |
# Run slonik
|
|
193 |
log.debug("Executing slonik script %s" % script_on_disk.name) |
|
7675.395.72
by Stuart Bishop
Remove remaining authdb awareness from replication scripts |
194 |
log.log(DEBUG2, 'Running script:\n%s' % script) |
5799.1.30
by Stuart Bishop
Work in progress |
195 |
returncode = subprocess.call(['slonik', script_on_disk.name]) |
196 |
||
197 |
if returncode != 0: |
|
5799.1.20
by Stuart Bishop
Work in progress |
198 |
log.error("slonik script failed") |
199 |
if exit_on_fail: |
|
200 |
sys.exit(1) |
|
5799.1.30
by Stuart Bishop
Work in progress |
201 |
|
202 |
return returncode == 0 |
|
5799.1.12
by Stuart Bishop
Replication maintenance scripts, work in progress |
203 |
|
204 |
||
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
205 |
class Node: |
206 |
"""Simple data structure for holding information about a Slony node."""
|
|
207 |
def __init__(self, node_id, nickname, connection_string, is_master): |
|
208 |
self.node_id = node_id |
|
209 |
self.nickname = nickname |
|
210 |
self.connection_string = connection_string |
|
211 |
self.is_master = is_master |
|
212 |
||
213 |
||
214 |
def _get_nodes(con, query): |
|
215 |
"""Return a list of Nodes."""
|
|
216 |
if not slony_installed(con): |
|
217 |
return [] |
|
218 |
cur = con.cursor() |
|
219 |
cur.execute(query) |
|
220 |
nodes = [] |
|
221 |
for node_id, nickname, connection_string, is_master in cur.fetchall(): |
|
222 |
nodes.append(Node(node_id, nickname, connection_string, is_master)) |
|
223 |
return nodes |
|
224 |
||
225 |
||
226 |
def get_master_node(con, set_id=1): |
|
227 |
"""Return the master Node, or None if the cluster is still being setup."""
|
|
228 |
nodes = _get_nodes(con, """ |
|
229 |
SELECT DISTINCT
|
|
7675.88.8
by Stuart Bishop
Stop ShipIt OpenID consumer sharing tables with the SSO server, dev replication setup fixes and test fixes |
230 |
set_origin AS node_id,
|
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
231 |
'master',
|
232 |
pa_conninfo AS connection_string,
|
|
233 |
True
|
|
234 |
FROM _sl.sl_set
|
|
7675.88.8
by Stuart Bishop
Stop ShipIt OpenID consumer sharing tables with the SSO server, dev replication setup fixes and test fixes |
235 |
LEFT OUTER JOIN _sl.sl_path ON set_origin = pa_server
|
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
236 |
WHERE set_id = %d |
237 |
""" % set_id) |
|
238 |
if not nodes: |
|
239 |
return None |
|
240 |
assert len(nodes) == 1, "More than one master found for set %s" % set_id |
|
241 |
return nodes[0] |
|
242 |
||
243 |
||
244 |
def get_slave_nodes(con, set_id=1): |
|
245 |
"""Return the list of slave Nodes."""
|
|
246 |
return _get_nodes(con, """ |
|
247 |
SELECT DISTINCT
|
|
248 |
pa_server AS node_id,
|
|
249 |
'slave' || pa_server,
|
|
250 |
pa_conninfo AS connection_string,
|
|
251 |
False
|
|
252 |
FROM _sl.sl_set
|
|
253 |
JOIN _sl.sl_subscribe ON set_id = sub_set
|
|
254 |
JOIN _sl.sl_path ON sub_receiver = pa_server
|
|
255 |
WHERE
|
|
256 |
set_id = %d |
|
257 |
ORDER BY node_id
|
|
258 |
""" % set_id) |
|
259 |
||
260 |
||
261 |
def get_nodes(con, set_id=1): |
|
262 |
"""Return a list of all Nodes."""
|
|
263 |
master_node = get_master_node(con, set_id) |
|
264 |
if master_node is None: |
|
265 |
return [] |
|
266 |
else: |
|
267 |
return [master_node] + get_slave_nodes(con, set_id) |
|
268 |
||
269 |
||
270 |
def get_all_cluster_nodes(con): |
|
271 |
"""Return a list of all Nodes in the cluster.
|
|
272 |
||
273 |
node.is_master will be None, as this boolean doesn't make sense
|
|
274 |
in the context of a cluster rather than a single replication set.
|
|
275 |
"""
|
|
276 |
if not slony_installed(con): |
|
277 |
return [] |
|
278 |
nodes = _get_nodes(con, """ |
|
279 |
SELECT DISTINCT
|
|
280 |
pa_server AS node_id,
|
|
281 |
'node' || pa_server || '_node',
|
|
282 |
pa_conninfo AS connection_string,
|
|
283 |
NULL
|
|
284 |
FROM _sl.sl_path
|
|
285 |
ORDER BY node_id
|
|
286 |
""") |
|
287 |
if not nodes: |
|
288 |
# There are no subscriptions yet, so no paths. Generate the
|
|
289 |
# master Node.
|
|
290 |
cur = con.cursor() |
|
291 |
cur.execute("SELECT no_id from _sl.sl_node") |
|
292 |
node_ids = [row[0] for row in cur.fetchall()] |
|
293 |
if len(node_ids) == 0: |
|
294 |
return [] |
|
295 |
assert len(node_ids) == 1, "Multiple nodes but no paths." |
|
296 |
master_node_id = node_ids[0] |
|
297 |
master_connection_string = ConnectionString( |
|
7675.395.43
by Stuart Bishop
Update replication scripts to cope with recent ro mode changes |
298 |
config.database.rw_main_master) |
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
299 |
master_connection_string.user = 'slony' |
300 |
return [Node( |
|
301 |
master_node_id, 'node%d_node' % master_node_id, |
|
302 |
master_connection_string, True)] |
|
303 |
return nodes |
|
304 |
||
305 |
||
306 |
def preamble(con=None): |
|
5799.1.12
by Stuart Bishop
Replication maintenance scripts, work in progress |
307 |
"""Return the preable needed at the start of all slonik scripts."""
|
5799.1.55
by Stuart Bishop
Improve initialize, less magic dev setup |
308 |
|
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
309 |
if con is None: |
310 |
con = connect('slony') |
|
311 |
||
312 |
master_node = get_master_node(con) |
|
313 |
nodes = get_all_cluster_nodes(con) |
|
314 |
if master_node is None and len(nodes) == 1: |
|
315 |
master_node = nodes[0] |
|
316 |
||
317 |
preamble = [dedent("""\ |
|
318 |
#
|
|
5799.1.55
by Stuart Bishop
Improve initialize, less magic dev setup |
319 |
# Every slonik script must start with a clustername, which cannot
|
320 |
# be changed once the cluster is initialized.
|
|
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
321 |
#
|
5799.1.55
by Stuart Bishop
Improve initialize, less magic dev setup |
322 |
cluster name = sl;
|
323 |
||
324 |
# Symbolic ids for replication sets.
|
|
7675.395.72
by Stuart Bishop
Remove remaining authdb awareness from replication scripts |
325 |
define lpmain_set %d; |
326 |
define holding_set %d; |
|
327 |
define lpmirror_set %d; |
|
328 |
""" % (LPMAIN_SET_ID, HOLDING_SET_ID, LPMIRROR_SET_ID))] |
|
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
329 |
|
330 |
if master_node is not None: |
|
331 |
preamble.append(dedent("""\ |
|
332 |
# Symbolic id for the main replication set master node.
|
|
333 |
define master_node %d; |
|
334 |
define master_node_conninfo '%s'; |
|
335 |
""" % (master_node.node_id, master_node.connection_string))) |
|
336 |
||
337 |
for node in nodes: |
|
338 |
preamble.append(dedent("""\ |
|
339 |
define %s %d; |
|
340 |
define %s_conninfo '%s'; |
|
341 |
node @%s admin conninfo = @%s_conninfo; |
|
342 |
""" % ( |
|
343 |
node.nickname, node.node_id, |
|
344 |
node.nickname, node.connection_string, |
|
345 |
node.nickname, node.nickname))) |
|
9795.4.2
by Stuart Bishop
Review feedback |
346 |
|
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
347 |
return '\n\n'.join(preamble) |
9795.4.2
by Stuart Bishop
Review feedback |
348 |
|
5799.1.26
by Stuart Bishop
Work in progress |
349 |
|
350 |
def calculate_replication_set(cur, seeds): |
|
351 |
"""Return the minimal set of tables and sequences needed in a
|
|
352 |
replication set containing the seed table.
|
|
353 |
||
354 |
A replication set must contain all tables linked by foreign key
|
|
355 |
reference to the given table, and sequences used to generate keys.
|
|
356 |
||
357 |
:param seeds: [(namespace, tablename), ...]
|
|
358 |
||
359 |
:returns: (tables, sequences)
|
|
360 |
"""
|
|
361 |
# Results
|
|
362 |
tables = set() |
|
363 |
sequences = set() |
|
364 |
||
365 |
# Our pending set to check
|
|
366 |
pending_tables = set(seeds) |
|
367 |
||
368 |
# Generate the set of tables that reference the seed directly
|
|
369 |
# or indirectly via foreign key constraints, including the seed itself.
|
|
370 |
while pending_tables: |
|
371 |
namespace, tablename = pending_tables.pop() |
|
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
372 |
|
373 |
# Skip if the table doesn't exist - we might have seeds listed that
|
|
374 |
# have been removed or are yet to be created.
|
|
375 |
cur.execute(""" |
|
376 |
SELECT TRUE
|
|
377 |
FROM pg_class, pg_namespace
|
|
378 |
WHERE pg_class.relnamespace = pg_namespace.oid
|
|
379 |
AND pg_namespace.nspname = %s |
|
380 |
AND pg_class.relname = %s |
|
381 |
""" % sqlvalues(namespace, tablename)) |
|
382 |
if cur.fetchone() is None: |
|
383 |
log.debug("Table %s.%s doesn't exist" % (namespace, tablename)) |
|
384 |
continue
|
|
385 |
||
5799.1.26
by Stuart Bishop
Work in progress |
386 |
tables.add((namespace, tablename)) |
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
387 |
|
5799.1.26
by Stuart Bishop
Work in progress |
388 |
# Find all tables that reference the current (seed) table
|
389 |
# and all tables that the seed table references.
|
|
390 |
cur.execute(""" |
|
391 |
SELECT ref_namespace.nspname, ref_class.relname
|
|
392 |
FROM
|
|
5799.1.42
by Stuart Bishop
Review feedback, round 1 |
393 |
-- One of the seed tables
|
5799.1.26
by Stuart Bishop
Work in progress |
394 |
pg_class AS seed_class,
|
395 |
pg_namespace AS seed_namespace,
|
|
5799.1.42
by Stuart Bishop
Review feedback, round 1 |
396 |
|
397 |
-- A table referencing the seed, or being referenced by
|
|
398 |
-- the seed.
|
|
5799.1.26
by Stuart Bishop
Work in progress |
399 |
pg_class AS ref_class,
|
400 |
pg_namespace AS ref_namespace,
|
|
5799.1.42
by Stuart Bishop
Review feedback, round 1 |
401 |
|
5799.1.26
by Stuart Bishop
Work in progress |
402 |
pg_constraint
|
403 |
WHERE
|
|
404 |
seed_class.relnamespace = seed_namespace.oid
|
|
405 |
AND ref_class.relnamespace = ref_namespace.oid
|
|
5799.1.42
by Stuart Bishop
Review feedback, round 1 |
406 |
|
5799.1.26
by Stuart Bishop
Work in progress |
407 |
AND seed_namespace.nspname = %s |
408 |
AND seed_class.relname = %s |
|
5799.1.42
by Stuart Bishop
Review feedback, round 1 |
409 |
|
5799.1.46
by Stuart Bishop
Review tweaks |
410 |
-- Foreign key constraints are all we care about.
|
5799.1.42
by Stuart Bishop
Review feedback, round 1 |
411 |
AND pg_constraint.contype = 'f'
|
412 |
||
413 |
-- We want tables referenced by, or referred to, the
|
|
414 |
-- seed table.
|
|
5799.1.26
by Stuart Bishop
Work in progress |
415 |
AND ((pg_constraint.conrelid = ref_class.oid
|
416 |
AND pg_constraint.confrelid = seed_class.oid)
|
|
417 |
OR (pg_constraint.conrelid = seed_class.oid
|
|
418 |
AND pg_constraint.confrelid = ref_class.oid)
|
|
419 |
)
|
|
420 |
""" % sqlvalues(namespace, tablename)) |
|
421 |
for namespace, tablename in cur.fetchall(): |
|
422 |
key = (namespace, tablename) |
|
423 |
if key not in tables and key not in pending_tables: |
|
424 |
pending_tables.add(key) |
|
425 |
||
426 |
# Generate the set of sequences that are linked to any of our set of
|
|
427 |
# tables. We assume these are all sequences created by creation of
|
|
428 |
# serial or bigserial columns, or other sequences OWNED BY a particular
|
|
429 |
# column.
|
|
430 |
for namespace, tablename in tables: |
|
431 |
cur.execute(""" |
|
432 |
SELECT seq
|
|
433 |
FROM (
|
|
434 |
SELECT pg_get_serial_sequence(%s, attname) AS seq |
|
435 |
FROM pg_namespace, pg_class, pg_attribute
|
|
436 |
WHERE pg_namespace.nspname = %s |
|
437 |
AND pg_class.relnamespace = pg_namespace.oid
|
|
438 |
AND pg_class.relname = %s |
|
439 |
AND pg_attribute.attrelid = pg_class.oid
|
|
440 |
AND pg_attribute.attisdropped IS FALSE
|
|
441 |
) AS whatever
|
|
442 |
WHERE seq IS NOT NULL;
|
|
443 |
""" % sqlvalues(fqn(namespace, tablename), namespace, tablename)) |
|
444 |
for row in cur.fetchall(): |
|
445 |
sequences.add(row[0]) |
|
446 |
||
447 |
# We can't easily convert the sequence name to (namespace, name) tuples,
|
|
448 |
# so we might as well convert the tables to dot notation for consistancy.
|
|
7178.4.10
by Stuart Bishop
Lint cleanup |
449 |
tables = set(fqn(namespace, tablename) for namespace, tablename in tables) |
5799.1.26
by Stuart Bishop
Work in progress |
450 |
|
451 |
return tables, sequences |
|
452 |
||
453 |
||
5799.1.29
by Stuart Bishop
Tweak |
454 |
def discover_unreplicated(cur): |
5799.1.26
by Stuart Bishop
Work in progress |
455 |
"""Inspect the database for tables and sequences in the public schema
|
456 |
that are not in a replication set.
|
|
7675.85.2
by Jonathan Lange
Undo revision generated by step 2 of process. |
457 |
|
5799.1.26
by Stuart Bishop
Work in progress |
458 |
:returns: (unreplicated_tables_set, unreplicated_sequences_set)
|
459 |
"""
|
|
460 |
all_tables = all_tables_in_schema(cur, 'public') |
|
461 |
all_sequences = all_sequences_in_schema(cur, 'public') |
|
462 |
||
463 |
cur.execute(""" |
|
464 |
SELECT tab_nspname, tab_relname FROM %s |
|
465 |
WHERE tab_nspname = 'public'
|
|
466 |
""" % fqn(CLUSTER_NAMESPACE, "sl_table")) |
|
467 |
replicated_tables = set(fqn(*row) for row in cur.fetchall()) |
|
468 |
||
469 |
cur.execute(""" |
|
470 |
SELECT seq_nspname, seq_relname FROM %s |
|
471 |
WHERE seq_nspname = 'public'
|
|
472 |
""" % fqn(CLUSTER_NAMESPACE, "sl_sequence")) |
|
473 |
replicated_sequences = set(fqn(*row) for row in cur.fetchall()) |
|
474 |
||
475 |
return ( |
|
476 |
all_tables - replicated_tables - IGNORED_TABLES, |
|
9795.4.14
by Stuart Bishop
Handle database dumps containing login service tables, a side effect from standalone authservice progress |
477 |
all_sequences - replicated_sequences - IGNORED_SEQUENCES) |
5799.1.26
by Stuart Bishop
Work in progress |
478 |
|
479 |
||
480 |
class ReplicationConfigError(Exception): |
|
481 |
"""Exception raised by validate_replication_sets() when our replication
|
|
482 |
setup is misconfigured.
|
|
483 |
"""
|
|
484 |
||
485 |
||
486 |
def validate_replication(cur): |
|
487 |
"""Raise a ReplicationSetupError if there is something wrong with
|
|
488 |
our replication sets.
|
|
489 |
||
490 |
This might include tables exist that are not in a replication set,
|
|
491 |
or tables that exist in multiple replication sets for example.
|
|
492 |
||
493 |
These is not necessarily limits with what Slony-I allows, but might
|
|
494 |
be due to policies we have made (eg. a table allowed in just one
|
|
495 |
replication set).
|
|
496 |
"""
|
|
5799.1.29
by Stuart Bishop
Tweak |
497 |
unrepl_tables, unrepl_sequences = discover_unreplicated(cur) |
5799.1.26
by Stuart Bishop
Work in progress |
498 |
if unrepl_tables: |
499 |
raise ReplicationConfigError( |
|
500 |
"Unreplicated tables: %s" % repr(unrepl_tables)) |
|
501 |
if unrepl_sequences: |
|
502 |
raise ReplicationConfigError( |
|
503 |
"Unreplicated sequences: %s" % repr(unrepl_sequences)) |
|
504 |
||
505 |
lpmain_tables, lpmain_sequences = calculate_replication_set( |
|
506 |
cur, LPMAIN_SEED) |
|
507 |