<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi,</p>
<p>I'm upgrading a database from 9.4 to 11.5 by dumping from the old
cluster and loading into the new cluster.</p>
<p>The database is tiny: around 2.3G, but importing this table is
proving problematic:</p>
<pre class="c-mrkdwn__pre" data-stringify-type="pre" style="box-sizing: inherit; margin: 4px 0px; padding: 8px; --saf-0:rgba(var(--sk_foreground_low,29,28,29),0.13); font-size: 12px; line-height: 1.50001; font-variant-ligatures: none; white-space: pre-wrap; overflow-wrap: break-word; word-break: normal; tab-size: 4; font-family: Monaco, Menlo, Consolas, "Courier New", monospace !important; border: 1px solid var(--saf-0); border-radius: 4px; background: rgba(var(--sk_foreground_min,29,28,29),0.04); counter-reset: list-0 0 list-1 0 list-2 0 list-3 0 list-4 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; color: rgb(29, 28, 29); font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">Column | Type | Modifiers
----------------+-------------------+----------------------------------------------------------
period | tsrange | not null
col1 | character varying | not null
col2 | character varying | not null
col3 | integer | not null
col4 | character varying | not null default ''::character varying
id | integer | not null default nextval('mkt_profile_id_seq'::regclass)
deleted | boolean | not null default false
managed | boolean | not null default false
col5 | character varying |
Indexes:
"mkt_profile_pkey" PRIMARY KEY, btree (id)
"mkt_profile_period_col1_col4_col2_chan_excl" EXCLUDE USING gist (period WITH &&, col1 WITH =, col4 WITH =, col2 WITH =, col3 WITH =)
Check constraints:
"mkt_profile_period_check" CHECK (period <> 'empty'::tsrange)
Foreign-key constraints:
"mkt_profile_col1_fkey" FOREIGN KEY (col1) REFERENCES host(name)</pre>
<p>It has 4.1 million rows in it and while importing the data only
takes a couple of minutes, when I did a test load into the new
cluster, building the mkt_profile_period_col1_col4_col2_chan_excl
index for the exclude constraint took 15 hours.</p>
<p>I feel like asking what I'm doing wrong here? The new server is
pretty decent hardware...</p>
<p>Concrete questions:</p>
<p>- what, if anything, am I getting badly wrong here?</p>
<p>- what can I do to speed up creation of this index?</p>
<p>- failing that, what can I do to import and then create the index
in the background? <br>
</p>
<p>As you can imagine, a 15hr outage for an upgrade has not met with
large amounts of happiness from the people whose application it is
;-)</p>
<p>Chris<br>
</p>
</body>
</html>