diff options
author | Dmitrijs Goloscapovs <dmitrijs.goloscapovs@zabbix.com> | 2021-11-26 12:05:42 +0300 |
---|---|---|
committer | Dmitrijs Goloscapovs <dmitrijs.goloscapovs@zabbix.com> | 2021-11-26 12:05:42 +0300 |
commit | f3b9848e458867c5b834e4f299944e8e90eb0d37 (patch) | |
tree | a294d5b14d3c57df6123f552c1de32460da91d40 /database | |
parent | fa073f5697a8a6d1a2b3b34b0335c88a02dc8365 (diff) |
....I..... [ZBXNEXT-6921] added postgres script for upgrading hist tables to pk
Diffstat (limited to 'database')
-rw-r--r-- | database/postgresql/history_pk_prepare.sql | 49 |
1 files changed, 49 insertions, 0 deletions
diff --git a/database/postgresql/history_pk_prepare.sql b/database/postgresql/history_pk_prepare.sql new file mode 100644 index 00000000000..02d08e82ae5 --- /dev/null +++ b/database/postgresql/history_pk_prepare.sql @@ -0,0 +1,49 @@ +ALTER TABLE history RENAME TO history_old; +CREATE TABLE history ( + itemid bigint NOT NULL, + clock integer DEFAULT '0' NOT NULL, + value DOUBLE PRECISION DEFAULT '0.0000' NOT NULL, + ns integer DEFAULT '0' NOT NULL, + PRIMARY KEY (itemid,clock,ns) +); + +ALTER TABLE history_uint RENAME TO history_uint_old; +CREATE TABLE history_uint ( + itemid bigint NOT NULL, + clock integer DEFAULT '0' NOT NULL, + value numeric(20) DEFAULT '0' NOT NULL, + ns integer DEFAULT '0' NOT NULL, + PRIMARY KEY (itemid,clock,ns) +); + + +ALTER TABLE history_str RENAME TO history_str_old; +CREATE TABLE history_str ( + itemid bigint NOT NULL, + clock integer DEFAULT '0' NOT NULL, + value varchar(255) DEFAULT '' NOT NULL, + ns integer DEFAULT '0' NOT NULL, + PRIMARY KEY (itemid,clock,ns) +); + +ALTER TABLE history_log RENAME TO history_log_old; +CREATE TABLE history_log ( + itemid bigint NOT NULL, + clock integer DEFAULT '0' NOT NULL, + timestamp integer DEFAULT '0' NOT NULL, + source varchar(64) DEFAULT '' NOT NULL, + severity integer DEFAULT '0' NOT NULL, + value text DEFAULT '' NOT NULL, + logeventid integer DEFAULT '0' NOT NULL, + ns integer DEFAULT '0' NOT NULL, + PRIMARY KEY (itemid,clock,ns) +); + +ALTER TABLE history_text RENAME TO history_text_old; +CREATE TABLE history_text ( + itemid bigint NOT NULL, + clock integer DEFAULT '0' NOT NULL, + value text DEFAULT '' NOT NULL, + ns integer DEFAULT '0' NOT NULL, + PRIMARY KEY (itemid,clock,ns) +); |