Skip to content

Instantly share code, notes, and snippets.

@irvingpop

irvingpop/1upgrade_issue_for_purged_data.md

Last active January 20, 2016 21:05
Show Gist options
  • Save irvingpop/cfcc49a0346e4ec5651b to your computer and use it in GitHub Desktop.
Save irvingpop/cfcc49a0346e4ec5651b to your computer and use it in GitHub Desktop.
Download ZIP
Addendum for Reporting 1.5.6 release
Raw
1upgrade_issue_for_purged_data.md

Note to customers have used the data purging feature (—remove-partitions) introduced in Reporting 1.3.0

Due to a bug in the upgrade process, If you’ve removed any of the 2015 data partitions the upgrade will fail. To determine if this applies to you, as well as to resolve the issue, run the following steps on your Standalone or Primary Backend Chef Server:

  1. To see if you’ve removed them, query the list of reporting tables and verify that you have node_run_2015_* and node_run_detail_2015_* tables (12 of each):
# sudo -u opscode-pgsql /opt/opscode/embedded/bin/psql opscode_reporting -c "\dt"
could not change directory to "/root"
                    List of relations
 Schema |          Name           | Type  |     Owner
--------+-------------------------+-------+---------------
 public | duration_buckets        | table | opscode-pgsql
 public | matviews                | table | opscode-pgsql
 public | node_run                | table | opscode-pgsql
 public | node_run_2015_01        | table | opscode-pgsql
 public | node_run_2015_02        | table | opscode-pgsql
 public | node_run_2015_03        | table | opscode-pgsql
 public | node_run_2015_04        | table | opscode-pgsql
 public | node_run_2015_05        | table | opscode-pgsql
 public | node_run_2015_06        | table | opscode-pgsql
 public | node_run_2015_07        | table | opscode-pgsql
 public | node_run_2015_08        | table | opscode-pgsql
 public | node_run_2015_09        | table | opscode-pgsql
 public | node_run_2015_10        | table | opscode-pgsql
 public | node_run_2015_11        | table | opscode-pgsql
 public | node_run_2015_12        | table | opscode-pgsql
 public | node_run_detail         | table | opscode-pgsql
 public | node_run_detail_2015_01 | table | opscode-pgsql
 public | node_run_detail_2015_02 | table | opscode-pgsql
 public | node_run_detail_2015_03 | table | opscode-pgsql
 public | node_run_detail_2015_04 | table | opscode-pgsql
 public | node_run_detail_2015_05 | table | opscode-pgsql
 public | node_run_detail_2015_06 | table | opscode-pgsql
 public | node_run_detail_2015_07 | table | opscode-pgsql
 public | node_run_detail_2015_08 | table | opscode-pgsql
 public | node_run_detail_2015_09 | table | opscode-pgsql
 public | node_run_detail_2015_10 | table | opscode-pgsql
 public | node_run_detail_2015_11 | table | opscode-pgsql
 public | node_run_detail_2015_12 | table | opscode-pgsql
 public | node_run_foreign_key    | table | opscode-pgsql
 public | node_run_in_progress    | table | opscode-pgsql
 public | node_runcounts_day      | table | opscode-pgsql
 public | node_runcounts_hour     | table | opscode-pgsql
 public | node_runcounts_minute   | table | opscode-pgsql
 public | node_runcounts_week     | table | opscode-pgsql
 public | stale_node_runs         | table | opscode-pgsql
  1. It is critical that you restore any missing 2015-XX partitions before upgrading. To restore them, download the following two SQL files to your server ( node_run_2015_partition_tables.sql & node_run_detail_2015_partition_tables.sql ) and execute them like so:
sudo -u opscode-pgsql /opt/opscode/embedded/bin/psql opscode_reporting -f /tmp/node_run_2015_partition_tables.sql
sudo -u opscode-pgsql /opt/opscode/embedded/bin/psql opscode_reporting -f /tmp/node_run_detail_2015_partition_tables.sql
  1. Any errors or notices of the nature relation "idx" already exists can be safely ignored

NOTE: For large data sets, the upgrade may take a long time if upgrading from 1.3.0 because additional indexing work is done. To speed the upgrade, purge as much of the 2015 data as you want before running step 2 above:

opscode-reporting-ctl remove-partitions --before 2016-01
Raw
node_run_2015_partition_tables.sql
-- Deploy node_run_2015_partition_tables
-- requires: base
-- requires: duration_bucket_definitions
-- These are the partition for the node_run table
-- The partions are defined by month
-- PRIMARY KEY creates an index on that field
-- start_time is given an index as that is the primary way data is accessed
CREATE TABLE IF NOT EXISTS node_run_2015_01 (
PRIMARY KEY(run_id),
FOREIGN KEY (duration_id) REFERENCES duration_buckets(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK (start_time >= TIMESTAMPTZ '2015-01-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-02-01 00:00:00-00')
) INHERITS (node_run);
CREATE INDEX node_run_2015_01_start_time_idx ON node_run_2015_01 (start_time);
CREATE INDEX node_run_2015_01_org_id_node_id_idx ON node_run_2015_01(org_id, node_id);
CREATE INDEX node_run_2015_01_start_time_minute_idx ON node_run_2015_01(date_trunc('minute', start_time AT TIME ZONE 'UTC'));
CREATE INDEX node_run_2015_01_status_idx ON node_run_2015_01(status);
CREATE INDEX node_run_2015_01_org_id_node_name_idx ON node_run_2015_01(org_id, node_name);
CREATE TABLE IF NOT EXISTS node_run_2015_02 (
PRIMARY KEY(run_id),
FOREIGN KEY (duration_id) REFERENCES duration_buckets(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK (start_time >= TIMESTAMPTZ '2015-02-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-03-01 00:00:00-00')
) INHERITS (node_run);
CREATE INDEX node_run_2015_02_start_time_idx ON node_run_2015_02 (start_time);
CREATE INDEX node_run_2015_02_org_id_node_id_idx ON node_run_2015_02(org_id, node_id);
CREATE INDEX node_run_2015_02_start_time_minute_idx ON node_run_2015_02(date_trunc('minute', start_time AT TIME ZONE 'UTC'));
CREATE INDEX node_run_2015_02_status_idx ON node_run_2015_02(status);
CREATE INDEX node_run_2015_02_org_id_node_name_idx ON node_run_2015_02(org_id, node_name);
CREATE TABLE IF NOT EXISTS node_run_2015_03 (
PRIMARY KEY(run_id),
FOREIGN KEY (duration_id) REFERENCES duration_buckets(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK (start_time >= TIMESTAMPTZ '2015-03-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-04-01 00:00:00-00')
) INHERITS (node_run);
CREATE INDEX node_run_2015_03_start_time_idx ON node_run_2015_03 (start_time);
CREATE INDEX node_run_2015_03_org_id_node_id_idx ON node_run_2015_03(org_id, node_id);
CREATE INDEX node_run_2015_03_start_time_minute_idx ON node_run_2015_03(date_trunc('minute', start_time AT TIME ZONE 'UTC'));
CREATE INDEX node_run_2015_03_status_idx ON node_run_2015_03(status);
CREATE INDEX node_run_2015_03_org_id_node_name_idx ON node_run_2015_03(org_id, node_name);
CREATE TABLE IF NOT EXISTS node_run_2015_04 (
PRIMARY KEY(run_id),
FOREIGN KEY (duration_id) REFERENCES duration_buckets(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK (start_time >= TIMESTAMPTZ '2015-04-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-05-01 00:00:00-00')
) INHERITS (node_run);
CREATE INDEX node_run_2015_04_start_time_idx ON node_run_2015_04 (start_time);
CREATE INDEX node_run_2015_04_org_id_node_id_idx ON node_run_2015_04(org_id, node_id);
CREATE INDEX node_run_2015_04_start_time_minute_idx ON node_run_2015_04(date_trunc('minute', start_time AT TIME ZONE 'UTC'));
CREATE INDEX node_run_2015_04_status_idx ON node_run_2015_04(status);
CREATE INDEX node_run_2015_04_org_id_node_name_idx ON node_run_2015_04(org_id, node_name);
CREATE TABLE IF NOT EXISTS node_run_2015_05 (
PRIMARY KEY(run_id),
FOREIGN KEY (duration_id) REFERENCES duration_buckets(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK (start_time >= TIMESTAMPTZ '2015-05-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-06-01 00:00:00-00')
) INHERITS (node_run);
CREATE INDEX node_run_2015_05_start_time_idx ON node_run_2015_05 (start_time);
CREATE INDEX node_run_2015_05_org_id_node_id_idx ON node_run_2015_05(org_id, node_id);
CREATE INDEX node_run_2015_05_start_time_minute_idx ON node_run_2015_05(date_trunc('minute', start_time AT TIME ZONE 'UTC'));
CREATE INDEX node_run_2015_05_status_idx ON node_run_2015_05(status);
CREATE INDEX node_run_2015_05_org_id_node_name_idx ON node_run_2015_05(org_id, node_name);
CREATE TABLE IF NOT EXISTS node_run_2015_06 (
PRIMARY KEY(run_id),
FOREIGN KEY (duration_id) REFERENCES duration_buckets(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK (start_time >= TIMESTAMPTZ '2015-06-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-07-01 00:00:00-00')
) INHERITS (node_run);
CREATE INDEX node_run_2015_06_start_time_idx ON node_run_2015_06 (start_time);
CREATE INDEX node_run_2015_06_org_id_node_id_idx ON node_run_2015_06(org_id, node_id);
CREATE INDEX node_run_2015_06_start_time_minute_idx ON node_run_2015_06(date_trunc('minute', start_time AT TIME ZONE 'UTC'));
CREATE INDEX node_run_2015_06_status_idx ON node_run_2015_06(status);
CREATE INDEX node_run_2015_06_org_id_node_name_idx ON node_run_2015_06(org_id, node_name);
CREATE TABLE IF NOT EXISTS node_run_2015_07 (
PRIMARY KEY(run_id),
FOREIGN KEY (duration_id) REFERENCES duration_buckets(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK (start_time >= TIMESTAMPTZ '2015-07-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-08-01 00:00:00-00')
) INHERITS (node_run);
CREATE INDEX node_run_2015_07_start_time_idx ON node_run_2015_07 (start_time);
CREATE INDEX node_run_2015_07_org_id_node_id_idx ON node_run_2015_07(org_id, node_id);
CREATE INDEX node_run_2015_07_start_time_minute_idx ON node_run_2015_07(date_trunc('minute', start_time AT TIME ZONE 'UTC'));
CREATE INDEX node_run_2015_07_status_idx ON node_run_2015_07(status);
CREATE INDEX node_run_2015_07_org_id_node_name_idx ON node_run_2015_07(org_id, node_name);
CREATE TABLE IF NOT EXISTS node_run_2015_08 (
PRIMARY KEY(run_id),
FOREIGN KEY (duration_id) REFERENCES duration_buckets(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK (start_time >= TIMESTAMPTZ '2015-08-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-09-01 00:00:00-00')
) INHERITS (node_run);
CREATE INDEX node_run_2015_08_start_time_idx ON node_run_2015_08 (start_time);
CREATE INDEX node_run_2015_08_org_id_node_id_idx ON node_run_2015_08(org_id, node_id);
CREATE INDEX node_run_2015_08_start_time_minute_idx ON node_run_2015_08(date_trunc('minute', start_time AT TIME ZONE 'UTC'));
CREATE INDEX node_run_2015_08_status_idx ON node_run_2015_08(status);
CREATE INDEX node_run_2015_08_org_id_node_name_idx ON node_run_2015_08(org_id, node_name);
CREATE TABLE IF NOT EXISTS node_run_2015_09 (
PRIMARY KEY(run_id),
FOREIGN KEY (duration_id) REFERENCES duration_buckets(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK (start_time >= TIMESTAMPTZ '2015-09-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-10-01 00:00:00-00')
) INHERITS (node_run);
CREATE INDEX node_run_2015_09_start_time_idx ON node_run_2015_09 (start_time);
CREATE INDEX node_run_2015_09_org_id_node_id_idx ON node_run_2015_09(org_id, node_id);
CREATE INDEX node_run_2015_09_start_time_minute_idx ON node_run_2015_09(date_trunc('minute', start_time AT TIME ZONE 'UTC'));
CREATE INDEX node_run_2015_09_status_idx ON node_run_2015_09(status);
CREATE INDEX node_run_2015_09_org_id_node_name_idx ON node_run_2015_09(org_id, node_name);
CREATE TABLE IF NOT EXISTS node_run_2015_10 (
PRIMARY KEY(run_id),
FOREIGN KEY (duration_id) REFERENCES duration_buckets(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK (start_time >= TIMESTAMPTZ '2015-10-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-11-01 00:00:00-00')
) INHERITS (node_run);
CREATE INDEX node_run_2015_10_start_time_idx ON node_run_2015_10 (start_time);
CREATE INDEX node_run_2015_10_org_id_node_id_idx ON node_run_2015_10(org_id, node_id);
CREATE INDEX node_run_2015_10_start_time_minute_idx ON node_run_2015_10(date_trunc('minute', start_time AT TIME ZONE 'UTC'));
CREATE INDEX node_run_2015_10_status_idx ON node_run_2015_10(status);
CREATE INDEX node_run_2015_10_org_id_node_name_idx ON node_run_2015_10(org_id, node_name);
CREATE TABLE IF NOT EXISTS node_run_2015_11 (
PRIMARY KEY(run_id),
FOREIGN KEY (duration_id) REFERENCES duration_buckets(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK (start_time >= TIMESTAMPTZ '2015-11-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-12-01 00:00:00-00')
) INHERITS (node_run);
CREATE INDEX node_run_2015_11_start_time_idx ON node_run_2015_11 (start_time);
CREATE INDEX node_run_2015_11_org_id_node_id_idx ON node_run_2015_11(org_id, node_id);
CREATE INDEX node_run_2015_11_start_time_minute_idx ON node_run_2015_11(date_trunc('minute', start_time AT TIME ZONE 'UTC'));
CREATE INDEX node_run_2015_11_status_idx ON node_run_2015_11(status);
CREATE INDEX node_run_2015_11_org_id_node_name_idx ON node_run_2015_11(org_id, node_name);
CREATE TABLE IF NOT EXISTS node_run_2015_12 (
PRIMARY KEY(run_id),
FOREIGN KEY (duration_id) REFERENCES duration_buckets(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK (start_time >= TIMESTAMPTZ '2015-12-01 00:00:00-00' and start_time < TIMESTAMPTZ '2016-01-01 00:00:00-00')
) INHERITS (node_run);
CREATE INDEX node_run_2015_12_start_time_idx ON node_run_2015_12 (start_time);
CREATE INDEX node_run_2015_12_org_id_node_id_idx ON node_run_2015_12(org_id, node_id);
CREATE INDEX node_run_2015_12_start_time_minute_idx ON node_run_2015_12(date_trunc('minute', start_time AT TIME ZONE 'UTC'));
CREATE INDEX node_run_2015_12_status_idx ON node_run_2015_12(status);
CREATE INDEX node_run_2015_12_org_id_node_name_idx ON node_run_2015_12(org_id, node_name);
Raw
node_run_detail_2015_partition_tables.sql
-- Deploy node_run_detail_2015_partition_tables
-- requires: base
-- requires: node_run_foreign_key
-- These are the partition for the node_run_detail table
-- The partions are defined by month
-- PRIMARY KEY creates an index on that field
-- start_time is given an index as that is the primary way data is accessed
CREATE TABLE IF NOT EXISTS node_run_detail_2015_01 (
PRIMARY KEY(run_id, seq),
FOREIGN KEY(run_id) REFERENCES node_run_foreign_key(run_id) ON UPDATE RESTRICT ON DELETE CASCADE,
CHECK (start_time >= TIMESTAMPTZ '2015-01-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-02-01 00:00:00-00')
) INHERITS (node_run_detail);
CREATE INDEX node_run_detail_2015_01_start_time_idx ON node_run_detail_2015_01 (start_time);
CREATE TABLE IF NOT EXISTS node_run_detail_2015_02 (
PRIMARY KEY(run_id, seq),
FOREIGN KEY(run_id) REFERENCES node_run_foreign_key(run_id) ON UPDATE RESTRICT ON DELETE CASCADE,
CHECK (start_time >= TIMESTAMPTZ '2015-02-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-03-01 00:00:00-00')
) INHERITS (node_run_detail);
CREATE INDEX node_run_detail_2015_02_start_time_idx ON node_run_detail_2015_02 (start_time);
CREATE TABLE IF NOT EXISTS node_run_detail_2015_03 (
PRIMARY KEY(run_id, seq),
FOREIGN KEY(run_id) REFERENCES node_run_foreign_key(run_id) ON UPDATE RESTRICT ON DELETE CASCADE,
CHECK (start_time >= TIMESTAMPTZ '2015-03-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-04-01 00:00:00-00')
) INHERITS (node_run_detail);
CREATE INDEX node_run_detail_2015_03_start_time_idx ON node_run_detail_2015_03 (start_time);
CREATE TABLE IF NOT EXISTS node_run_detail_2015_04 (
PRIMARY KEY(run_id, seq),
FOREIGN KEY(run_id) REFERENCES node_run_foreign_key(run_id) ON UPDATE RESTRICT ON DELETE CASCADE,
CHECK (start_time >= TIMESTAMPTZ '2015-04-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-05-01 00:00:00-00')
) INHERITS (node_run_detail);
CREATE INDEX node_run_detail_2015_04_start_time_idx ON node_run_detail_2015_04 (start_time);
CREATE TABLE IF NOT EXISTS node_run_detail_2015_05 (
PRIMARY KEY(run_id, seq),
FOREIGN KEY(run_id) REFERENCES node_run_foreign_key(run_id) ON UPDATE RESTRICT ON DELETE CASCADE,
CHECK (start_time >= TIMESTAMPTZ '2015-05-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-06-01 00:00:00-00')
) INHERITS (node_run_detail);
CREATE INDEX node_run_detail_2015_05_start_time_idx ON node_run_detail_2015_05 (start_time);
CREATE TABLE IF NOT EXISTS node_run_detail_2015_06 (
PRIMARY KEY(run_id, seq),
FOREIGN KEY(run_id) REFERENCES node_run_foreign_key(run_id) ON UPDATE RESTRICT ON DELETE CASCADE,
CHECK (start_time >= TIMESTAMPTZ '2015-06-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-07-01 00:00:00-00')
) INHERITS (node_run_detail);
CREATE INDEX node_run_detail_2015_06_start_time_idx ON node_run_detail_2015_06 (start_time);
CREATE TABLE IF NOT EXISTS node_run_detail_2015_07 (
PRIMARY KEY(run_id, seq),
FOREIGN KEY(run_id) REFERENCES node_run_foreign_key(run_id) ON UPDATE RESTRICT ON DELETE CASCADE,
CHECK (start_time >= TIMESTAMPTZ '2015-07-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-08-01 00:00:00-00')
) INHERITS (node_run_detail);
CREATE INDEX node_run_detail_2015_07_start_time_idx ON node_run_detail_2015_07 (start_time);
CREATE TABLE IF NOT EXISTS node_run_detail_2015_08 (
PRIMARY KEY(run_id, seq),
FOREIGN KEY(run_id) REFERENCES node_run_foreign_key(run_id) ON UPDATE RESTRICT ON DELETE CASCADE,
CHECK (start_time >= TIMESTAMPTZ '2015-08-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-09-01 00:00:00-00')
) INHERITS (node_run_detail);
CREATE INDEX node_run_detail_2015_08_start_time_idx ON node_run_detail_2015_08 (start_time);
CREATE TABLE IF NOT EXISTS node_run_detail_2015_09 (
PRIMARY KEY(run_id, seq),
FOREIGN KEY(run_id) REFERENCES node_run_foreign_key(run_id) ON UPDATE RESTRICT ON DELETE CASCADE,
CHECK (start_time >= TIMESTAMPTZ '2015-09-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-10-01 00:00:00-00')
) INHERITS (node_run_detail);
CREATE INDEX node_run_detail_2015_09_start_time_idx ON node_run_detail_2015_09 (start_time);
CREATE TABLE IF NOT EXISTS node_run_detail_2015_10 (
PRIMARY KEY(run_id, seq),
FOREIGN KEY(run_id) REFERENCES node_run_foreign_key(run_id) ON UPDATE RESTRICT ON DELETE CASCADE,
CHECK (start_time >= TIMESTAMPTZ '2015-10-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-11-01 00:00:00-00')
) INHERITS (node_run_detail);
CREATE INDEX node_run_detail_2015_10_start_time_idx ON node_run_detail_2015_10 (start_time);
CREATE TABLE IF NOT EXISTS node_run_detail_2015_11 (
PRIMARY KEY(run_id, seq),
FOREIGN KEY(run_id) REFERENCES node_run_foreign_key(run_id) ON UPDATE RESTRICT ON DELETE CASCADE,
CHECK (start_time >= TIMESTAMPTZ '2015-11-01 00:00:00-00' and start_time < TIMESTAMPTZ '2015-12-01 00:00:00-00')
) INHERITS (node_run_detail);
CREATE INDEX node_run_detail_2015_11_start_time_idx ON node_run_detail_2015_11 (start_time);
CREATE TABLE IF NOT EXISTS node_run_detail_2015_12 (
PRIMARY KEY(run_id, seq),
FOREIGN KEY(run_id) REFERENCES node_run_foreign_key(run_id) ON UPDATE RESTRICT ON DELETE CASCADE,
CHECK (start_time >= TIMESTAMPTZ '2015-12-01 00:00:00-00' and start_time < TIMESTAMPTZ '2016-01-01 00:00:00-00')
) INHERITS (node_run_detail);
CREATE INDEX node_run_detail_2015_12_start_time_idx ON node_run_detail_2015_12 (start_time);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment