homer_pgsql_rotate.pl no longer works

[bibace]

It doesnt remove partition tables not starting with "sip",

full of logs_capture_p***** tables in database, make it not working.

[bibace]

https://github.com/sipcapture/homer-api/blob/master/scripts/pgsql/homer_pgsql_rotate.pl

line 306 my @tables = $db->tables( '', '', $table.'_%_p%', '', {noprefix => 1} );

line 314 my($proto, $cap, $type, $ts, $minpart) = split(/_/, $table_name, 5);

I didnt see many partition tables name match $table . '_%_p%' rule. most of tables name look like this format: logs_capture_p2020021200

Furthermore, I can not find many tables name can split to 5 parts. ($proto, $cap, $type, $ts, $minpart)

Could someone explain above two lines for me, please. @adubovikov @StefanYohansson

[adubovikov]

@bibace you don't need any external scripts anymore. The heplify-server does it for you automaticaly. Look at this lines:

https://github.com/sipcapture/heplify-server/blob/master/example/homer7_config/heplify-server.toml#L30-L32

[adubovikov]

Do you use Homer5 or Homer7.7 ?

[bibace]

Do you use Homer5 or Homer7.7 ? Hi Alexandr, Thank you for your prompt response.

I recently joined the voip team, and try to solve our homer_data database too big issue .

I found the issue is due to the script can not remove unnecessary records (more than 10 days' records) automatically.

We are using Homer 5 and postgres 11 in our project.

[adubovikov]

It should be here: https://github.com/sipcapture/homer-api/blob/master/scripts/pgsql/rotation.ini#L20 and here https://github.com/sipcapture/homer-api/blob/master/scripts/pgsql/rotation.ini#L41

this means, the table will be dropped. Anyway, please activate the debug:

https://github.com/sipcapture/homer-api/blob/master/scripts/pgsql/rotation.ini#L60

and you will see all messages in the syslog. This will help you understand the issue.

please reserve your time to do upgrade ;-)

Wbr, Alexandr

On Thu, 13 Feb 2020 at 00:38, bibace notifications@github.com wrote:

Do you use Homer5 or Homer7.7 ? Hi Alexandr, Thank you for your prompt response.

I recently joined the voip team, and try to solve our homer_data database too big issue .

I found the issue is due to the script can not remove unnecessary records (more than 10 days' records) automatically.

We are using Homer 5 and postgres 11 in our project.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/sipcapture/homer-api/issues/192?email_source=notifications&email_token=ABCN2JIIKTBQTK4ZZDUT273RCSB6BA5CNFSM4KTS2GWKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOELSZ26Y#issuecomment-585473403, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABCN2JLROQKJYHTLGNZS6U3RCSB6BANCNFSM4KTS2GWA .

[bibace]

It should be here: https://github.com/sipcapture/homer-api/blob/master/scripts/pgsql/rotation.ini#L20 and here https://github.com/sipcapture/homer-api/blob/master/scripts/pgsql/rotation.ini#L41 this means, the table will be dropped. Anyway, please activate the debug: https://github.com/sipcapture/homer-api/blob/master/scripts/pgsql/rotation.ini#L60 and you will see all messages in the syslog. This will help you understand the issue. please reserve your time to do upgrade ;-) Wbr, Alexandr … On Thu, 13 Feb 2020 at 00:38, bibace @.***> wrote: Do you use Homer5 or Homer7.7 ? Hi Alexandr, Thank you for your prompt response. I recently joined the voip team, and try to solve our homer_data database too big issue . I found the issue is due to the script can not remove unnecessary records (more than 10 days' records) automatically. We are using Homer 5 and postgres 11 in our project. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#192?email_source=notifications&email_token=ABCN2JIIKTBQTK4ZZDUT273RCSB6BA5CNFSM4KTS2GWKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOELSZ26Y#issuecomment-585473403>, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABCN2JLROQKJYHTLGNZS6U3RCSB6BANCNFSM4KTS2GWA .

Hi Alex, Thank you very much for your help.

all sip_ started table can be dropped correctly, but others not.

I found the issue from here,

https://github.com/sipcapture/homer-api/blob/master/scripts/pgsql/homer_pgsql_rotate.pl#L306

regex rule : $table.'_%_p%',

$table is from $CONFIG->{"DATA(/STATS)_TABLE_ROTATION"}/

In homer_data, there're huge number of logs_capture_p**** tables, however their's name not match this regex rule. they not be able to dropped.

all data stored in logs_capture table too, and partition tables are empty (logs_capture_p****) , Do we miss some triggers in pgsql?

In homer_statistic, same thing happens there.

I created a testing environment in docker (pgsql 11, and homer-api), import the sql file.

I also created many log_capture partition tables for recent 30 days. they all follow the rule inherit from logs_capture and have constraint rule.

In debug mode, no logs_capture_p**** tables been deleted and no DROP Partition: message in output as well.

Hope you can help

Best Regards,

[adubovikov]

great, if you have found the issue, please make a fix and submit a PR.

As I wrote already, we don't support Homer 5 anymore. The best way upgrade the system to H 7.7

Regards, Alexandr

On Fri, 14 Feb 2020 at 00:31, bibace notifications@github.com wrote:

It should be here: https://github.com/sipcapture/homer-api/blob/master/scripts/pgsql/rotation.ini#L20 and here https://github.com/sipcapture/homer-api/blob/master/scripts/pgsql/rotation.ini#L41 this means, the table will be dropped. Anyway, please activate the debug: https://github.com/sipcapture/homer-api/blob/master/scripts/pgsql/rotation.ini#L60 and you will see all messages in the syslog. This will help you understand the issue. please reserve your time to do upgrade ;-) Wbr, Alexandr … <#m-5192145668973121092> On Thu, 13 Feb 2020 at 00:38, bibace @.***> wrote: Do you use Homer5 or Homer7.7 ? Hi Alexandr, Thank you for your prompt response. I recently joined the voip team, and try to solve our homer_data database too big issue . I found the issue is due to the script can not remove unnecessary records (more than 10 days' records) automatically. We are using Homer 5 and postgres 11 in our project. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#192 https://github.com/sipcapture/homer-api/issues/192?email_source=notifications&email_token=ABCN2JIIKTBQTK4ZZDUT273RCSB6BA5CNFSM4KTS2GWKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOELSZ26Y#issuecomment-585473403>, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABCN2JLROQKJYHTLGNZS6U3RCSB6BANCNFSM4KTS2GWA .

Hi Alex, Thank you very much for your help.

all sip_ started table can be dropped correctly, but others not.

I found the issue from here,

https://github.com/sipcapture/homer-api/blob/master/scripts/pgsql/homer_pgsql_rotate.pl#L306

regex rule : $table.'_%_p%',

$table is from $CONFIG->{"DATA(/STATS)_TABLE_ROTATION"}/

In homer_data, there're huge number of logs_capture_p**** tables, however their's name not match this regex rule. they not be able to dropped.

In homer_statistic, same thing happens there.

I created a testing environment in docker (pgsql 11, and homer-api), import the sql file.

I also created many log_capture partition tables for recent 30 days. they all follow the rule inherit from logs_capture and have constraint rule.

In debug mode, no logs_capture_p**** tables been deleted and no DROP Partition: message in output as well.

Hope you can help

Best Regards,

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/sipcapture/homer-api/issues/192?email_source=notifications&email_token=ABCN2JMUR7NTG5TNBDVI3QDRCXJ4FA5CNFSM4KTS2GWKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOELXAELQ#issuecomment-586023470, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABCN2JPGUA7L6J6M25WH2RTRCXJ4FANCNFSM4KTS2GWA .