keithf4
commented
3 years ago Can you share the schema for the table involved so I can try to recreate this?
Closed elekgeek closed 3 years ago
keithf4
commented
3 years ago Can you share the schema for the table involved so I can try to recreate this?
elekgeek
commented
3 years ago create zabbix user and database
sudo -u postgres createuser --pwprompt zabbix
sudo -u postgres createdb -O zabbix zabbix
echo "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;" | sudo -u postgres psql zabbixCreate the SCHEMA and EXTENSION
sudo -u postgres psql zabbix
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman schema partman;
# Ensure the zabbix database user can execute all functions, procedures on the partman SCHEMA with the following SQL commands
GRANT ALL ON SCHEMA partman TO zabbix;
GRANT ALL ON ALL TABLES IN SCHEMA partman TO zabbix;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO zabbix;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO zabbix;sudo -u zabbix psql zabbix
-- history
CREATE TABLE public.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
) PARTITION BY RANGE (clock);
CREATE INDEX history_1 ON public.history USING btree (itemid, clock);
-- history_log
CREATE TABLE public.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
) PARTITION BY RANGE (clock);
CREATE INDEX history_log_1 ON public.history_log USING btree (itemid, clock);
-- history_str
CREATE TABLE public.history_str (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
value varchar(255) DEFAULT '' NOT NULL,
ns integer DEFAULT '0' NOT NULL
) PARTITION BY RANGE (clock);
CREATE INDEX history_str_1 ON public.history_str USING btree (itemid, clock);
-- history_text
CREATE TABLE public.history_text (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
value text DEFAULT '' NOT NULL,
ns integer DEFAULT '0' NOT NULL
) PARTITION BY RANGE (clock);
CREATE INDEX history_text_1 ON public.history_text USING btree (itemid, clock);
-- history_uint
CREATE TABLE public.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
) PARTITION BY RANGE (clock);
CREATE INDEX history_uint_1 ON public.history_uint USING btree (itemid, clock);
-- trends
CREATE TABLE public.trends (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
num integer DEFAULT '0' NOT NULL,
value_min DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
value_avg DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
value_max DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
CONSTRAINT trends_pkey PRIMARY KEY (itemid,clock)
) PARTITION BY RANGE (clock);
-- trends_uint
CREATE TABLE public.trends_uint (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
num integer DEFAULT '0' NOT NULL,
value_min numeric(20) DEFAULT '0' NOT NULL,
value_avg numeric(20) DEFAULT '0' NOT NULL,
value_max numeric(20) DEFAULT '0' NOT NULL,
CONSTRAINT trends_uint_pkey PRIMARY KEY (itemid,clock)
) PARTITION BY RANGE (clock);sudo -u zabbix psql zabbix
SELECT partman.create_parent('public.history', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
SELECT partman.create_parent('public.history_uint', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
SELECT partman.create_parent('public.history_str', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
SELECT partman.create_parent('public.history_text', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
SELECT partman.create_parent('public.history_log', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
SELECT partman.create_parent('public.trends', 'clock', 'native', 'monthly', null, 12, 'on', null, true, 'seconds');
SELECT partman.create_parent('public.trends_uint', 'clock', 'native', 'monthly', null, 12, 'on', null, true, 'seconds');This is working for all tables except for trends and trends_uint.
As I have noted earlier, I am using both pg_partman along with timescaledb. Andreas told me that this might cause an issue, not sure if this is the case, however, partitioning works for history tables.. I checked if there is an overlap on the errors with trends, the Epoch are OK i.e. 31 days, 0 hours, 0 minutes and 0 seconds.
elekgeek
commented
3 years ago @keithf4
I thought you might want to know that Andreas (Doctorbal) suggested that I setup a new system and use UTC timezone which turned out to be working. Now I can't figure out the issue cause if it is pg_partman or the OS TBH. Can you please check what's wrong with having timezone set to sth other than UTC.
keithf4
commented
3 years ago My own laptop is set in Eastern time, so that's what I'll be testing when I have the chance. I've been pretty busy lately so unfortunately can't give firm ETA when I'll get to look into this. Will try my best for next week.
elekgeek
commented
3 years ago Hope u got time to see why pg_partman fails to create monthly partitions when timezone is sth other than UTC.
keithf4
commented
3 years ago Apologies. Work has me pretty busy lately.
keithf4
commented
3 years ago So my laptop is in Eastern time and it seems to be working ok for me. What timezone are you in?
keith@keith=# show timezone;
TimeZone
------------------
America/New_York
(1 row)
Time: 1.933 mskeith@keith=# CREATE TABLE public.history (
keith(*# itemid bigint NOT NULL,
keith(*# clock integer DEFAULT '0' NOT NULL,
keith(*# value DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
keith(*# ns integer DEFAULT '0' NOT NULL
keith(*# ) PARTITION BY RANGE (clock);
CREATE TABLE
Time: 6.934 ms
keith@keith=# CREATE INDEX history_1 ON public.history USING btree (itemid, clock);
CREATE INDEX
Time: 3.468 mskeith@keith=# CREATE TABLE public.trends (
keith(*# itemid bigint NOT NULL,
keith(*# clock integer DEFAULT '0' NOT NULL,
keith(*# num integer DEFAULT '0' NOT NULL,
keith(*# value_min DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
keith(*# value_avg DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
keith(*# value_max DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
keith(*# CONSTRAINT trends_pkey PRIMARY KEY (itemid,clock)
keith(*# ) PARTITION BY RANGE (clock);
CREATE TABLE
Time: 6.593 mskeith@keith=# SELECT partman.create_parent('public.history', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
create_parent
---------------
t
(1 row)
Time: 375.258 ms
keith@keith=# \d+ history
Partitioned table "public.history"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------------------+-----------+----------+-----------------------+---------+--------------+-------------
itemid | bigint | | not null | | plain | |
clock | integer | | not null | 0 | plain | |
value | double precision | | not null | '0'::double precision | plain | |
ns | integer | | not null | 0 | plain | |
Partition key: RANGE (clock)
Indexes:
"history_1" btree (itemid, clock)
Partitions: history_p2021_06_28 FOR VALUES FROM (1624852800) TO (1624939200),
history_p2021_06_29 FOR VALUES FROM (1624939200) TO (1625025600),
history_p2021_06_30 FOR VALUES FROM (1625025600) TO (1625112000),
history_p2021_07_01 FOR VALUES FROM (1625112000) TO (1625198400),
history_p2021_07_02 FOR VALUES FROM (1625198400) TO (1625284800),
history_p2021_07_03 FOR VALUES FROM (1625284800) TO (1625371200),
history_p2021_07_04 FOR VALUES FROM (1625371200) TO (1625457600),
history_p2021_07_05 FOR VALUES FROM (1625457600) TO (1625544000),
history_p2021_07_06 FOR VALUES FROM (1625544000) TO (1625630400),
history_p2021_07_07 FOR VALUES FROM (1625630400) TO (1625716800),
history_p2021_07_08 FOR VALUES FROM (1625716800) TO (1625803200),
history_p2021_07_09 FOR VALUES FROM (1625803200) TO (1625889600),
history_p2021_07_10 FOR VALUES FROM (1625889600) TO (1625976000),
history_p2021_07_11 FOR VALUES FROM (1625976000) TO (1626062400),
history_p2021_07_12 FOR VALUES FROM (1626062400) TO (1626148800),
history_default DEFAULTkeith@keith=# SELECT partman.create_parent('public.trends', 'clock', 'native', 'monthly', null, 12, 'on', null, true, 'seconds');
create_parent
---------------
t
(1 row)
keith@keith=# \d+ trends
Partitioned table "public.trends"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+------------------+-----------+----------+-----------------------+---------+--------------+-------------
itemid | bigint | | not null | | plain | |
clock | integer | | not null | 0 | plain | |
num | integer | | not null | 0 | plain | |
value_min | double precision | | not null | '0'::double precision | plain | |
value_avg | double precision | | not null | '0'::double precision | plain | |
value_max | double precision | | not null | '0'::double precision | plain | |
Partition key: RANGE (clock)
Indexes:
"trends_pkey" PRIMARY KEY, btree (itemid, clock)
Partitions: trends_p2020_07 FOR VALUES FROM (1593576000) TO (1596254400),
trends_p2020_08 FOR VALUES FROM (1596254400) TO (1598932800),
trends_p2020_09 FOR VALUES FROM (1598932800) TO (1601524800),
trends_p2020_10 FOR VALUES FROM (1601524800) TO (1604203200),
trends_p2020_11 FOR VALUES FROM (1604203200) TO (1606798800),
trends_p2020_12 FOR VALUES FROM (1606798800) TO (1609477200),
trends_p2021_01 FOR VALUES FROM (1609477200) TO (1612155600),
trends_p2021_02 FOR VALUES FROM (1612155600) TO (1614574800),
trends_p2021_03 FOR VALUES FROM (1614574800) TO (1617249600),
trends_p2021_04 FOR VALUES FROM (1617249600) TO (1619841600),
trends_p2021_05 FOR VALUES FROM (1619841600) TO (1622520000),
trends_p2021_06 FOR VALUES FROM (1622520000) TO (1625112000),
trends_p2021_07 FOR VALUES FROM (1625112000) TO (1627790400),
trends_p2021_08 FOR VALUES FROM (1627790400) TO (1630468800),
trends_p2021_09 FOR VALUES FROM (1630468800) TO (1633060800),
trends_p2021_10 FOR VALUES FROM (1633060800) TO (1635739200),
trends_p2021_11 FOR VALUES FROM (1635739200) TO (1638334800),
trends_p2021_12 FOR VALUES FROM (1638334800) TO (1641013200),
trends_p2022_01 FOR VALUES FROM (1641013200) TO (1643691600),
trends_p2022_02 FOR VALUES FROM (1643691600) TO (1646110800),
trends_p2022_03 FOR VALUES FROM (1646110800) TO (1648785600),
trends_p2022_04 FOR VALUES FROM (1648785600) TO (1651377600),
trends_p2022_05 FOR VALUES FROM (1651377600) TO (1654056000),
trends_p2022_06 FOR VALUES FROM (1654056000) TO (1656648000),
trends_p2022_07 FOR VALUES FROM (1656648000) TO (1659326400),
trends_default DEFAULTkeith@keith=# CREATE TABLE public.trends_uint (
keith(*# itemid bigint NOT NULL,
keith(*# clock integer DEFAULT '0' NOT NULL,
keith(*# num integer DEFAULT '0' NOT NULL,
keith(*# value_min numeric(20) DEFAULT '0' NOT NULL,
keith(*# value_avg numeric(20) DEFAULT '0' NOT NULL,
keith(*# value_max numeric(20) DEFAULT '0' NOT NULL,
keith(*# CONSTRAINT trends_uint_pkey PRIMARY KEY (itemid,clock)
keith(*# ) PARTITION BY RANGE (clock);
CREATE TABLE
Time: 7.540 mskeith@keith=# SELECT partman.create_parent('public.trends_uint', 'clock', 'native', 'monthly', null, 12, 'on', null, true, 'seconds');
create_parent
---------------
t
(1 row)
Time: 584.521 mskeith@keith=# \d+ trends_uint
Partitioned table "public.trends_uint"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------------+-----------+----------+--------------+---------+--------------+-------------
itemid | bigint | | not null | | plain | |
clock | integer | | not null | 0 | plain | |
num | integer | | not null | 0 | plain | |
value_min | numeric(20,0) | | not null | '0'::numeric | main | |
value_avg | numeric(20,0) | | not null | '0'::numeric | main | |
value_max | numeric(20,0) | | not null | '0'::numeric | main | |
Partition key: RANGE (clock)
Indexes:
"trends_uint_pkey" PRIMARY KEY, btree (itemid, clock)
Partitions: trends_uint_p2020_07 FOR VALUES FROM (1593576000) TO (1596254400),
trends_uint_p2020_08 FOR VALUES FROM (1596254400) TO (1598932800),
trends_uint_p2020_09 FOR VALUES FROM (1598932800) TO (1601524800),
trends_uint_p2020_10 FOR VALUES FROM (1601524800) TO (1604203200),
trends_uint_p2020_11 FOR VALUES FROM (1604203200) TO (1606798800),
trends_uint_p2020_12 FOR VALUES FROM (1606798800) TO (1609477200),
trends_uint_p2021_01 FOR VALUES FROM (1609477200) TO (1612155600),
trends_uint_p2021_02 FOR VALUES FROM (1612155600) TO (1614574800),
trends_uint_p2021_03 FOR VALUES FROM (1614574800) TO (1617249600),
trends_uint_p2021_04 FOR VALUES FROM (1617249600) TO (1619841600),
trends_uint_p2021_05 FOR VALUES FROM (1619841600) TO (1622520000),
trends_uint_p2021_06 FOR VALUES FROM (1622520000) TO (1625112000),
trends_uint_p2021_07 FOR VALUES FROM (1625112000) TO (1627790400),
trends_uint_p2021_08 FOR VALUES FROM (1627790400) TO (1630468800),
trends_uint_p2021_09 FOR VALUES FROM (1630468800) TO (1633060800),
trends_uint_p2021_10 FOR VALUES FROM (1633060800) TO (1635739200),
trends_uint_p2021_11 FOR VALUES FROM (1635739200) TO (1638334800),
trends_uint_p2021_12 FOR VALUES FROM (1638334800) TO (1641013200),
trends_uint_p2022_01 FOR VALUES FROM (1641013200) TO (1643691600),
trends_uint_p2022_02 FOR VALUES FROM (1643691600) TO (1646110800),
trends_uint_p2022_03 FOR VALUES FROM (1646110800) TO (1648785600),
trends_uint_p2022_04 FOR VALUES FROM (1648785600) TO (1651377600),
trends_uint_p2022_05 FOR VALUES FROM (1651377600) TO (1654056000),
trends_uint_p2022_06 FOR VALUES FROM (1654056000) TO (1656648000),
trends_uint_p2022_07 FOR VALUES FROM (1656648000) TO (1659326400),
trends_uint_default DEFAULTI see, thank you for your time.
I am working with Asia/Amman (EEST) these days, using Rocky Linux latest stable release.
Let me know plz what OS you are using.
keithf4
commented
3 years ago I am using Linux Mint 19.3 which is based on Ubuntu 18.04. I can try it out on RHEL7/8 as well if needed.
I'll try and see if I can test with your timezone. Are you able to test with America/New York (currently EDT)?
elekgeek
commented
3 years ago Are you able to test with America/New York (currently EDT)?
I will have to create another machine with that timezone so things don't mix up on me. Would you like me to do it?
keithf4
commented
3 years ago So this does appear to be an issue with this timezone. Don't have anymore details at this time. Will try and investigate further when I have some time, but at least have that narrowed down if you want to try and look into it further as well.
keith@keith=# show timezone;
TimeZone
------------------
America/New_York
(1 row)
Time: 0.517 ms
keith@keith=# select now();
now
-------------------------------
2021-07-06 13:24:23.754931-04
(1 row)
Time: 0.751 ms
keith@keith=# set timezone="Asia/Amman";
SET
Time: 1.188 ms
keith@keith=# select now();
now
------------------------------
2021-07-06 20:24:32.65726+03
(1 row)
Time: 0.360 ms
SELECT partman.create_parent('public.trends', 'clock', 'native', 'monthly', null, 12, 'on', null, true, 'seconds');
ERROR: partition "trends_p2022_05" would overlap partition "trends_p2022_04"
CONTEXT: SQL statement "ALTER TABLE public.trends ATTACH PARTITION public.trends_p2022_05 FOR VALUES FROM ('1651352400') TO ('1654030800')"
PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 249 at EXECUTE
PL/pgSQL function partman.create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean) line 473 at assignment
DETAIL:
HINT:
CONTEXT: PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 479 at RAISE
PL/pgSQL function partman.create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean) line 473 at assignment
DETAIL:
HINT:
CONTEXT: PL/pgSQL function partman.create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean) line 771 at RAISE
Time: 540.600 msSo, I think I might've figured out what the problem is here. If you actually use a premake value of 9 or lower as of this month (July 2021), it works since that only makes up to April 2022.
keith@keith=# SELECT partman.create_parent('public.trends', 'clock', 'native', 'monthly', null, 9, 'on', null, true, 'seconds');
NOTICE: relation "template_public_trends" already exists, skipping
create_parent
---------------
t
(1 row)
Time: 467.281 ms
keith@keith=# \d+ trends
Partitioned table "public.trends"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+------------------+-----------+----------+-----------------------+---------+--------------+-------------
itemid | bigint | | not null | | plain | |
clock | integer | | not null | 0 | plain | |
num | integer | | not null | 0 | plain | |
value_min | double precision | | not null | '0'::double precision | plain | |
value_avg | double precision | | not null | '0'::double precision | plain | |
value_max | double precision | | not null | '0'::double precision | plain | |
Partition key: RANGE (clock)
Indexes:
"trends_pkey" PRIMARY KEY, btree (itemid, clock)
Partitions: trends_p2020_10 FOR VALUES FROM (1601499600) TO (1604181600),
trends_p2020_11 FOR VALUES FROM (1604181600) TO (1606773600),
trends_p2020_12 FOR VALUES FROM (1606773600) TO (1609452000),
trends_p2021_01 FOR VALUES FROM (1609452000) TO (1612130400),
trends_p2021_02 FOR VALUES FROM (1612130400) TO (1614549600),
trends_p2021_03 FOR VALUES FROM (1614549600) TO (1617224400),
trends_p2021_04 FOR VALUES FROM (1617224400) TO (1619816400),
trends_p2021_05 FOR VALUES FROM (1619816400) TO (1622494800),
trends_p2021_06 FOR VALUES FROM (1622494800) TO (1625086800),
trends_p2021_07 FOR VALUES FROM (1625086800) TO (1627765200),
trends_p2021_08 FOR VALUES FROM (1627765200) TO (1630443600),
trends_p2021_09 FOR VALUES FROM (1630443600) TO (1633035600),
trends_p2021_10 FOR VALUES FROM (1633035600) TO (1635717600),
trends_p2021_11 FOR VALUES FROM (1635717600) TO (1638309600),
trends_p2021_12 FOR VALUES FROM (1638309600) TO (1640988000),
trends_p2022_01 FOR VALUES FROM (1640988000) TO (1643666400),
trends_p2022_02 FOR VALUES FROM (1643666400) TO (1646085600),
trends_p2022_03 FOR VALUES FROM (1646085600) TO (1648764000),
trends_p2022_04 FOR VALUES FROM (1648764000) TO (1651356000),
trends_default DEFAULTAs soon as you go higher than that, that's when it fails. However, if you then insert some future data to cause it to premake some more partitions, then run maintenance it works fine. Can even update the premake to what you need to to be as well.
keith@keith=# update partman.part_config set premake = 12;
UPDATE 1
Time: 6.234 ms
keith@keith=# select to_timestamp(1638133199);
to_timestamp
------------------------
2021-11-28 22:59:59+02
(1 row)
Time: 3.741 ms
insert into trends values (1, 1638133199, 1, 1.1, 1.1, 1.1);
INSERT 0 1
Time: 7.616 ms
keith@keith=# select partman.run_maintenance();
run_maintenance
-----------------
(1 row)
Time: 287.992 ms
keith@keith=# \d+ trends
Partitioned table "public.trends"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+------------------+-----------+----------+-----------------------+---------+--------------+-------------
itemid | bigint | | not null | | plain | |
clock | integer | | not null | 0 | plain | |
num | integer | | not null | 0 | plain | |
value_min | double precision | | not null | '0'::double precision | plain | |
value_avg | double precision | | not null | '0'::double precision | plain | |
value_max | double precision | | not null | '0'::double precision | plain | |
Partition key: RANGE (clock)
Indexes:
"trends_pkey" PRIMARY KEY, btree (itemid, clock)
Partitions: trends_p2020_10 FOR VALUES FROM (1601499600) TO (1604181600),
trends_p2020_11 FOR VALUES FROM (1604181600) TO (1606773600),
trends_p2020_12 FOR VALUES FROM (1606773600) TO (1609452000),
trends_p2021_01 FOR VALUES FROM (1609452000) TO (1612130400),
trends_p2021_02 FOR VALUES FROM (1612130400) TO (1614549600),
trends_p2021_03 FOR VALUES FROM (1614549600) TO (1617224400),
trends_p2021_04 FOR VALUES FROM (1617224400) TO (1619816400),
trends_p2021_05 FOR VALUES FROM (1619816400) TO (1622494800),
trends_p2021_06 FOR VALUES FROM (1622494800) TO (1625086800),
trends_p2021_07 FOR VALUES FROM (1625086800) TO (1627765200),
trends_p2021_08 FOR VALUES FROM (1627765200) TO (1630443600),
trends_p2021_09 FOR VALUES FROM (1630443600) TO (1633035600),
trends_p2021_10 FOR VALUES FROM (1633035600) TO (1635717600),
trends_p2021_11 FOR VALUES FROM (1635717600) TO (1638309600),
trends_p2021_12 FOR VALUES FROM (1638309600) TO (1640988000),
trends_p2022_01 FOR VALUES FROM (1640988000) TO (1643666400),
trends_p2022_02 FOR VALUES FROM (1643666400) TO (1646085600),
trends_p2022_03 FOR VALUES FROM (1646085600) TO (1648764000),
trends_p2022_04 FOR VALUES FROM (1648764000) TO (1651356000),
trends_p2022_05 FOR VALUES FROM (1651356000) TO (1654034400),
trends_p2022_06 FOR VALUES FROM (1654034400) TO (1656626400),
trends_p2022_07 FOR VALUES FROM (1656626400) TO (1659304800),
trends_p2022_08 FOR VALUES FROM (1659304800) TO (1661983200),
trends_p2022_09 FOR VALUES FROM (1661983200) TO (1664575200),
trends_p2022_10 FOR VALUES FROM (1664575200) TO (1667257200),
trends_p2022_11 FOR VALUES FROM (1667257200) TO (1669849200),
trends_default DEFAULTNow if you look at the value that is the upper boundary of p2022_04 and the lower boundary of p2022_05 it is the following:
keith@keith=# select to_timestamp(1651356000);
to_timestamp
------------------------
2022-05-01 01:00:00+03
(1 row)
Time: 3.618 msHowever if you look at the value in the error, it is the following:
keith@keith=# select to_timestamp(1651352400);
to_timestamp
------------------------
2022-05-01 00:00:00+03
(1 row)
Time: 1.550 msI think this is because Daylight Savings is at the end of March in this timezone. For the create_partition() function and time-based partitioning it is simply adding on the interval value on to the time value of the starting partition (in this case July 2021).
https://github.com/pgpartman/pg_partman/blob/master/sql/functions/create_parent.sql#L437
This does not go out of its way to handle DST. However, the maintenance process simply makes the next partition based on the previously existing one, so it handles it fine.
Why this isn't an issue for the Eastern timezone, i'm really not sure. I even tried doing a create_parent() call in EDT with a premake of 24 so it went out several years. Might be a bug in the timezone code for the EEST timezone?
Even if that didn't work for Eastern time, I'm not sure how I would handle that honestly. Seems like it would be an issue where you'd have to ensure your premake wouldn't be able to cross a DST barrier for the tables it creates.
Honestly, I'd say this is just yet another argument for running your database in UTC.
keithf4
commented
3 years ago So I looked a bit more at the Eastern timezone handling. All data boundaries, even in months that cross DST are at midnight
trends_p2023_03 FOR VALUES FROM (1677646800) TO (1680321600),
trends_p2023_04 FOR VALUES FROM (1680321600) TO (1682913600),keith@keith=# select to_timestamp(1677646800);
to_timestamp
------------------------
2023-03-01 00:00:00-05
(1 row)
Time: 0.604 ms
keith@keith=# select to_timestamp(1680321600);
to_timestamp
------------------------
2023-04-01 00:00:00-04
(1 row)
Time: 0.775 ms
keith@keith=# select to_timestamp(1682913600);
to_timestamp
------------------------
2023-05-01 00:00:00-04
(1 row)
Time: 0.787 msFor some reason, in your timezone, it's jumping ahead an hour when creating the child partitions around the DST boundary. I think this might be pointing to a bug in the timezone info currently in postgres?
trends_p2022_03 FOR VALUES FROM (1646085600) TO (1648764000),
trends_p2022_04 FOR VALUES FROM (1648764000) TO (1651356000),keith@keith=# set timezone="Asia/Amman";
SET
Time: 1.271 ms
keith@keith=# select to_timestamp(1646085600);
to_timestamp
------------------------
2022-03-01 00:00:00+02
(1 row)
Time: 0.660 ms
keith@keith=# select to_timestamp(1648764000);
to_timestamp
------------------------
2022-04-01 01:00:00+03
(1 row)
Time: 0.398 ms
keith@keith=# select to_timestamp(1651356000);
to_timestamp
------------------------
2022-05-01 01:00:00+03
(1 row)
Time: 0.722 msTried a couple other random timezones with a premake value of 24 without issue.
America/Los_Angeles Europe/Prague Australia/Brisbane
elekgeek
commented
3 years ago Can you please try Asia/Jerusalem?
keithf4
commented
3 years ago Sure. Seems to work.
You can test any timezones yourself as well using the SET command. It just changes the timezone for your current session, not the entire server. Soon as you log out and back in again, it's set back to the server default
keith@keith=# set timezone='Asia/Jerusalem';
SET
Time: 1.062 ms
keith@keith=# select now();
now
-------------------------------
2021-07-07 00:14:31.440883+03
(1 row)
Time: 3.423 ms
keith@keith=# CREATE TABLE public.trends (
keith(# itemid bigint NOT NULL,
keith(# clock integer DEFAULT '0' NOT NULL,
keith(# num integer DEFAULT '0' NOT NULL,
keith(# value_min DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
keith(# value_avg DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
keith(# value_max DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
keith(# CONSTRAINT trends_pkey PRIMARY KEY (itemid,clock)
keith(# ) PARTITION BY RANGE (clock);
CREATE TABLE
Time: 8.872 ms
keith@keith=# SELECT partman.create_parent('public.trends', 'clock', 'native', 'monthly', null, 12, 'on', null, true, 'seconds');
NOTICE: relation "template_public_trends" already exists, skipping
create_parent
---------------
t
(1 row)
Time: 597.461 ms
keith@keith=# \d+ trends
Partitioned table "public.trends"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+------------------+-----------+----------+-----------------------+---------+--------------+-------------
itemid | bigint | | not null | | plain | |
clock | integer | | not null | 0 | plain | |
num | integer | | not null | 0 | plain | |
value_min | double precision | | not null | '0'::double precision | plain | |
value_avg | double precision | | not null | '0'::double precision | plain | |
value_max | double precision | | not null | '0'::double precision | plain | |
Partition key: RANGE (clock)
Indexes:
"trends_pkey" PRIMARY KEY, btree (itemid, clock)
Partitions: trends_p2020_07 FOR VALUES FROM (1593550800) TO (1596229200),
trends_p2020_08 FOR VALUES FROM (1596229200) TO (1598907600),
trends_p2020_09 FOR VALUES FROM (1598907600) TO (1601499600),
trends_p2020_10 FOR VALUES FROM (1601499600) TO (1604181600),
trends_p2020_11 FOR VALUES FROM (1604181600) TO (1606773600),
trends_p2020_12 FOR VALUES FROM (1606773600) TO (1609452000),
trends_p2021_01 FOR VALUES FROM (1609452000) TO (1612130400),
trends_p2021_02 FOR VALUES FROM (1612130400) TO (1614549600),
trends_p2021_03 FOR VALUES FROM (1614549600) TO (1617224400),
trends_p2021_04 FOR VALUES FROM (1617224400) TO (1619816400),
trends_p2021_05 FOR VALUES FROM (1619816400) TO (1622494800),
trends_p2021_06 FOR VALUES FROM (1622494800) TO (1625086800),
trends_p2021_07 FOR VALUES FROM (1625086800) TO (1627765200),
trends_p2021_08 FOR VALUES FROM (1627765200) TO (1630443600),
trends_p2021_09 FOR VALUES FROM (1630443600) TO (1633035600),
trends_p2021_10 FOR VALUES FROM (1633035600) TO (1635717600),
trends_p2021_11 FOR VALUES FROM (1635717600) TO (1638309600),
trends_p2021_12 FOR VALUES FROM (1638309600) TO (1640988000),
trends_p2022_01 FOR VALUES FROM (1640988000) TO (1643666400),
trends_p2022_02 FOR VALUES FROM (1643666400) TO (1646085600),
trends_p2022_03 FOR VALUES FROM (1646085600) TO (1648760400),
trends_p2022_04 FOR VALUES FROM (1648760400) TO (1651352400),
trends_p2022_05 FOR VALUES FROM (1651352400) TO (1654030800),
trends_p2022_06 FOR VALUES FROM (1654030800) TO (1656622800),
trends_p2022_07 FOR VALUES FROM (1656622800) TO (1659301200),
trends_default DEFAULTI will try Asia/Jerusalem now! I believe both of them are on same time.
keithf4
commented
3 years ago Ok. Seems this timezone doesn't have an issue adding intervals across boundaries
keith@keith=# select to_timestamp(1646085600);
to_timestamp
------------------------
2022-03-01 00:00:00+02
(1 row)
Time: 1.530 ms
keith@keith=# select to_timestamp(1648760400);
to_timestamp
------------------------
2022-04-01 00:00:00+03
(1 row)
Time: 2.539 ms
keith@keith=# select to_timestamp(1651352400);
to_timestamp
------------------------
2022-05-01 00:00:00+03
(1 row)
Time: 2.472 msBIG Thank you Keith! Today is a good day, Asia/Jerusalem works fine :)
zabbix=> set timezone='Asia/Jerusalem';
SET
zabbix=> CREATE TABLE public.trends_uint_2 (
zabbix(> itemid bigint NOT NULL,
zabbix(> clock integer DEFAULT '0' NOT NULL,
zabbix(> num integer DEFAULT '0' NOT NULL,
zabbix(> value_min numeric(20) DEFAULT '0' NOT NULL,
zabbix(> value_avg numeric(20) DEFAULT '0' NOT NULL,
zabbix(> value_max numeric(20) DEFAULT '0' NOT NULL
zabbix(> ) PARTITION BY RANGE (clock);
CREATE TABLE
zabbix=> SELECT partman.create_parent('public.trends_uint_2', 'clock', 'native', 'monthly', null, 12, 'on', null, true, 'seconds');
create_parent
---------------
t
(1 row)
zabbix=> UPDATE partman.part_config SET retention_keep_table = false, retention = '12 month' WHERE parent_table = 'public.trends_uint_2';
UPDATE 1
zabbix=> SELECT partman.run_maintenance('public.trends_uint_2');
run_maintenance
-----------------
(1 row)
zabbix=> \d+ public.trends_uint_2
Partitioned table "public.trends_uint_2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------------+-----------+----------+--------------+---------+--------------+-------------
itemid | bigint | | not null | | plain | |
clock | integer | | not null | 0 | plain | |
num | integer | | not null | 0 | plain | |
value_min | numeric(20,0) | | not null | '0'::numeric | main | |
value_avg | numeric(20,0) | | not null | '0'::numeric | main | |
value_max | numeric(20,0) | | not null | '0'::numeric | main | |
Partition key: RANGE (clock)
Partitions: trends_uint_2_p2020_07 FOR VALUES FROM (1593550800) TO (1596229200),
trends_uint_2_p2020_08 FOR VALUES FROM (1596229200) TO (1598907600),
trends_uint_2_p2020_09 FOR VALUES FROM (1598907600) TO (1601499600),
trends_uint_2_p2020_10 FOR VALUES FROM (1601499600) TO (1604181600),
trends_uint_2_p2020_11 FOR VALUES FROM (1604181600) TO (1606773600),
trends_uint_2_p2020_12 FOR VALUES FROM (1606773600) TO (1609452000),
trends_uint_2_p2021_01 FOR VALUES FROM (1609452000) TO (1612130400),
trends_uint_2_p2021_02 FOR VALUES FROM (1612130400) TO (1614549600),
trends_uint_2_p2021_03 FOR VALUES FROM (1614549600) TO (1617224400),
trends_uint_2_p2021_04 FOR VALUES FROM (1617224400) TO (1619816400),
trends_uint_2_p2021_05 FOR VALUES FROM (1619816400) TO (1622494800),
trends_uint_2_p2021_06 FOR VALUES FROM (1622494800) TO (1625086800),
trends_uint_2_p2021_07 FOR VALUES FROM (1625086800) TO (1627765200),
trends_uint_2_p2021_08 FOR VALUES FROM (1627765200) TO (1630443600),
trends_uint_2_p2021_09 FOR VALUES FROM (1630443600) TO (1633035600),
trends_uint_2_p2021_10 FOR VALUES FROM (1633035600) TO (1635717600),
trends_uint_2_p2021_11 FOR VALUES FROM (1635717600) TO (1638309600),
trends_uint_2_p2021_12 FOR VALUES FROM (1638309600) TO (1640988000),
trends_uint_2_p2022_01 FOR VALUES FROM (1640988000) TO (1643666400),
trends_uint_2_p2022_02 FOR VALUES FROM (1643666400) TO (1646085600),
trends_uint_2_p2022_03 FOR VALUES FROM (1646085600) TO (1648760400),
trends_uint_2_p2022_04 FOR VALUES FROM (1648760400) TO (1651352400),
trends_uint_2_p2022_05 FOR VALUES FROM (1651352400) TO (1654030800),
trends_uint_2_p2022_06 FOR VALUES FROM (1654030800) TO (1656622800),
trends_uint_2_p2022_07 FOR VALUES FROM (1656622800) TO (1659301200),
trends_uint_2_default DEFAULTI thought it was RHEL, that's why I asked about ur OS. However, when the timezone Asia/Amman has errors, is it possible that it has issues? I can open a ticket with Redhat directly, I have a support contract with them.
Another question on mind, I have a running zabbix on postgresql and pg_partman with UTC timezone. Can I just change the time zone on postgresql/OS to Asia/Jerusalem and that's it? Will the existing partitions go along with the new timezone? What am I supposed to do to change from UTC to Asia/Jerusalem? Can you please guide me?
keithf4
commented
3 years ago Something is really odd with this timezone. This year the DST was March 26 at midnight. For some reason it's set to April 1 at midnight for 2022. That seems to be what's throwing all this off. Changing DST at midnight in general seems odd. Most are at 2am and it would seem like this is a good reason why? Like Asia/Jerusalem is March 26 @ 2am
keith@keith=# set timezone='Asia/Amman';
SET
Time: 1.052 ms
keith@keith=# select '2022-03-31 23:00:00'::timestamptz + '1 hour'::interval;
?column?
------------------------
2022-04-01 01:00:00+03
(1 row)
Time: 0.626 ms
keith@keith=# select '2022-03-31 23:00:00'::timestamptz;
timestamptz
------------------------
2022-03-31 23:00:00+02
(1 row)
Time: 0.563 msI believe you should be able to change the timezone and as long as you're using the timestamptz datatype you should be fine. If you're using timestamp (timestamp without timezone) that data will likely be broken after changing the timezone.
And honestly, I would recreate any partition sets that you created that crossed the April 2022 boundary when creating children. Otherwise the boundaries are going to be off for all remaining child tables after that.
elekgeek
commented
3 years ago Something is really odd with this timezone. This year the DST was March 26 at midnight. For some reason it's set to April 1 at midnight for 2022. That seems to be what's throwing all this off. Changing DST at midnight in general seems odd. Most are at 2am and it would seem like this is a good reason why? Like Asia/Jerusalem is March 26 @ 2am
Since you had the same issue on your OS, this appears to be a general problem on Linux.. I think we should call Linus Torvalds this time :D
keithf4
commented
3 years ago Actually, if it's a problem, it's likely in the IANA database. That's where PostgreSQL gets its info from
elekgeek
commented
3 years ago I never noticed that DST is changed at 2 AM, good hint.
I will just go with Asia/Jerusalem and send an email to IANA.
keithf4
commented
3 years ago So I think this is actually the same issue people have with partitioning hourly along with with DST. Already have an issue open for this
https://github.com/pgpartman/pg_partman/issues/334
I don't know of any solution to this other than running your database in UTC or any timezone that does not have DST.
I think this case is similar where you really cannot do partitioning effectively with a timezone that has its DST change at midnight.
So likely not an issue that will be fixed in IANA either.
elekgeek
commented
3 years ago Then nothing can be done.
One thing that is worth mentioning, I had a false impression about is timescaledb extension. I thought when data is compressed I won't be able to access it, I was wrong, I setup a test server on timescaledb to see if I can graph data earlier than 7 days on zabbix (per the settings Compress records older than 7 days), and it was OK. Some people mentioned that timescaledb is a good candidate and less complex, but not at upgrade times.
tm. will decide which way to go, I must read more about it pg_partman and timescaledb to be able to decide.
Tanks a lot Keith for your help, really appreciate it :)
keithf4
commented
3 years ago Closing this issue for now. Please feel free to respond or reopen if you have any further questions.
Hi,
I am using the following packages for zabbix installation and following this tutorial from doctorbal :
When trying to partition trends and trends_uint tables, I get errors below:
Timezone for
/var/lib/pgsql/13/data/postgresql.confand OS itself is correctly set.I really don't get why it sees these epoch times as overlapping. Help is appreciated on this issue.
Thanks