Zabbix Postgresql 9.? autopartitioning

[fgallese]

Thanks for your work alledm, I am considering to use it on our database.

Could you please answer me some doubts ?

Will this work on a zabbix 1.8 database or is it just for 2.0 version ?

and,

Will this affect normal housekeeper process operations ?

[Jason]

Table partitioning can only be done in postgres in ver 2.0 because of the way mysql handles foreign keys... I think this should work on 1.8 if you're using postgres.

If you're using partitions then you don't need the housekeeper running as you simply drop the older tables rather than purging the data from the database. I think it is generally recommended that on large installs you disable the housekeeper and do your maintenance via cron jobs at quiet times.

[alledm]

Here we go.
Please test it properly

Example

SELECT delete_partitions('7 days', 'day')
SELECT delete_partitions('10 months', 'month')

-- Function: delete_partitions(interval, text)

-- DROP FUNCTION delete_partitions(interval, text);
-- text can be either 'month' or 'day' or it won't do anything.
-- interval must be a valid Postgresql interval

CREATE OR REPLACE FUNCTION delete_partitions(intervaltodelete interval, tabletype text)
  RETURNS text AS
$BODY$
DECLARE
result record ;
prefix text := '';
table_timestamp timestamp;
delete_before_date date;
tablename text;

BEGIN
    FOR result IN SELECT * FROM pg_tables WHERE schemaname = 'partitions' LOOP

        table_timestamp := to_timestamp(substring(result.tablename from '[0-9_]*$'), 'YYYY_MM_DD');
        delete_before_date := date_trunc('day', NOW() - intervalToDelete);
        tablename := result.tablename;

    -- Was it called properly?
        IF tabletype != 'month' AND tabletype != 'day' THEN
	    RAISE EXCEPTION 'Please specify "month" or "day" instead of %', tabletype;
        END IF;


    --Check whether the table name has a day (YYYY_MM_DD) or month (YYYY_MM) format
        IF length(substring(result.tablename from '[0-9_]*$')) = 10 AND tabletype = 'month' THEN
            --This is a daily partition YYYY_MM_DD
            -- RAISE NOTICE 'Skipping table % when trying to delete "%" partitions (%)', result.tablename, tabletype, length(substring(result.tablename from '[0-9_]*$'));
            CONTINUE;
        ELSIF length(substring(result.tablename from '[0-9_]*$')) = 7 AND tabletype = 'day' THEN
            --this is a monthly partition
            --RAISE NOTICE 'Skipping table % when trying to delete "%" partitions (%)', result.tablename, tabletype, length(substring(result.tablename from '[0-9_]*$'));
            CONTINUE;
        ELSE
            --This is the correct table type. Go ahead and check if it needs to be deleted
	    --RAISE NOTICE 'Checking table %', result.tablename;
        END IF;

	IF table_timestamp <= delete_before_date THEN
		RAISE NOTICE 'Deleting table %', quote_ident(tablename);
		EXECUTE 'DROP TABLE ' || prefix || quote_ident(tablename) || ';';
	END IF;
    END LOOP;
RETURN 'OK';

END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION delete_partitions(interval, text)
  OWNER TO postgres;

[parcival]

alledm,
thank you for your fast replay, i get this error message:

ERROR:  table "history_str_sync_20121017" does not exist
CONTEXT:  SQL statement "DROP TABLE history_str_sync_20121017;"

But this table "exist" in schema "partitions" (db zabbix).

alledm
i have a small little other naming structure, and therefore i cange your script a little.

day table:
history_str_sync_20121017

month table:
events_201210

Your script will find the correct table, but can not delete them.
Why ?

thx for your time

[alledm]

Hi. Just change

prefix text := '';

to

prefix text := 'partitions.';

I have also changed the original script to create the tables in another schema, so we have a similar setup.

I will polish everything up and write the whole solution in a wiki page so that everything is tidy and nice.

Let me know how it goes

[parcival]

alledm, perfetto
Grazie e saluti dalla Germania all'Italia.
However, I could not test the months.
I have only one month (October)

[alledm]

I have edited the WIKI as well

http://www.zabbix.org/wiki/Docs/howto/zabbix2_postgresql_autopartitioning

[parcival]

How i do this in cron?
Because i get this error.

[root@zabbix-m01 /opt]# psql zabbix -U zabbix -c 'SELECT delete_partitions('7 days', 'day')'
psql: warning: extra command-line argument "days, day)" ignored
ERROR:  syntax error at end of input
LINE 1: SELECT delete_partitions(7
                                  ^

[alledm]

You cannot use multiple ' without escaping them.

psql zabbix -U zabbix -c "SELECT delete_partitions('7 days', 'day')"

[Jason]

When I try to run the select delete_partitions ('30 days','day') then all I get is...

delete_partitions
-------------------
OK
(1 row)


and no partitions are deleted...

I can't see what I'm doing wrong...

[Jason]

Hmmm it looks like all of my tables have the schemaname public and not partitions...

[alledm]

Yes sorry this script has gone through a few changes.

Please refer to http://www.zabbix.org/wiki/Docs/howt...topartitioning in the future.

anyway you will see I have added a

prefix text := 'partitions.';

variable to crontrol where to create or delete partitions.
You will need to adjust your scripts accordingly.

Let me know if you still have problems!

[Jason]

Ok...

With the new scripts I'm getting in my logs..

PGRES_FATAL_ERROR:ERROR: schema "partitions" does not exist

Also if I ran the original script without the partitions. bit then can I easily update my tables to this new naming without breaking everything?

[Jason]

I think I've sorted it...

I needed to create schema partitions and grant the zabbix user full permissions to the tables...

I then used lines like this

for tbl in `psql -qAt -c "select tablename from pg_tables where tablename like 'trends_uint_p%';" zabbix` ; do  psql -c "alter table $tbl set schema partitions" zabbix ; done

to change all the schemas...

[rsvancara]

That is a good way to do it

I actually wrote a script in python to manage partitioning. I found this way to be more robust as I could include into a cron job and send out email alerts.