Daily partition not running automatically for history table

[vanimesh]

Hi,

I see that the daily partition is not created automatically for the history table. I will have to run it manually. Can someone give me some clue if I am doing something wrong?

[Doctorbal]

@vanimesh ,

Please explain your issue in more detail; ideally with what steps you performed.

What step are you specifically talking about?

Partitioning for history table(s) are working if you follow the directions in the guide.

[vanimesh]

Hi @Doctorbal, thank you for responding :-). I am new to the environment, the partitioning was already created in the setup and came to know that the steps which followed are in your GitHub link.

My issue in detail is, We have 7 days of history storage and 7 days premake. We followed the same procedure in production too. When coming to production everything seems to work automatically. But in the test environment daily I will have to run the command "SELECT partman.run_maintenance('public.history_text');" everyday,

[vanimesh]

Just found something in the logs of test environment.

2022-03-24 00:17:54 UTC ERROR: permission denied for table trends_p2024_02 CONTEXT: SQL statement "SELECT max(to_timestamp(clock))::text FROM public.trends_p2024_02" PL/pgSQL function partman.run_maintenance(text,boolean,boolean,boolean) line 213 at EXECUTE SQL statement "SELECT "partman".run_maintenance(p_analyze := false, p_jobmon := true)" DETAIL: HINT: 2022-03-24 00:17:54 UTC CONTEXT: PL/pgSQL function partman.run_maintenance(text,boolean,boolean,boolean) line 415 at RAISE SQL statement "SELECT "partman".run_maintenance(p_analyze := false, p_jobmon := true)" 2022-03-24 00:17:54 UTC LOG: background worker "?;??" (PID 30912) exited with exit code 1

[vanimesh]

I am unable to fix this issue but can someone @Doctorbal can you please help me? We don't have a proper DBA too :-(

[Doctorbal]

Since your error suggest a permissions issue please review the document clearly and ensure partman configuration file includes the zabbix role as the role to run it and run 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;

On Mon, Mar 28, 2022, 4:50 AM vanimesh @.***> wrote:

I am unable to fix this issue but can someone @Doctorbal https://github.com/Doctorbal can you please help me? We don't have a proper DBA too :-(

— Reply to this email directly, view it on GitHub https://github.com/Doctorbal/zabbix-postgres-partitioning/issues/19#issuecomment-1080370743, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABUJVEGEYC4HYI352ARCNQ3VCFXG3ANCNFSM5RKNYTLA . You are receiving this because you were mentioned.Message ID: @.***>

[vanimesh]

Since your error suggest a permissions issue please review the document clearly and ensure partman configuration file includes the zabbix role as the role to run it and run 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; … On Mon, Mar 28, 2022, 4:50 AM vanimesh @.> wrote: I am unable to fix this issue but can someone @Doctorbal https://github.com/Doctorbal can you please help me? We don't have a proper DBA too :-( — Reply to this email directly, view it on GitHub <#19 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABUJVEGEYC4HYI352ARCNQ3VCFXG3ANCNFSM5RKNYTLA . You are receiving this because you were mentioned.Message ID: @.>

How can I check the existing grants on the schema,procedures and functions ?

[Doctorbal]

Try following

$ SELECT * FROM information_schema.table_privileges;

On Tue, Mar 29, 2022, 8:41 AM vanimesh @.***> wrote:

Since your error suggest a permissions issue please review the document clearly and ensure partman configuration file includes the zabbix role as the role to run it and run 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; … <#m-7840414360657256869> On Mon, Mar 28, 2022, 4:50 AM vanimesh @.> wrote: I am unable to fix this issue but can someone @Doctorbal https://github.com/Doctorbal https://github.com/Doctorbal https://github.com/Doctorbal can you please help me? We don't have a proper DBA too :-( — Reply to this email directly, view it on GitHub <#19 (comment) https://github.com/Doctorbal/zabbix-postgres-partitioning/issues/19#issuecomment-1080370743>, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABUJVEGEYC4HYI352ARCNQ3VCFXG3ANCNFSM5RKNYTLA https://github.com/notifications/unsubscribe-auth/ABUJVEGEYC4HYI352ARCNQ3VCFXG3ANCNFSM5RKNYTLA . You are receiving this because you were mentioned.Message ID: @.>

How can I check the existing grants on the schema,procedures and functions ?

— Reply to this email directly, view it on GitHub https://github.com/Doctorbal/zabbix-postgres-partitioning/issues/19#issuecomment-1081823210, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABUJVEBUXWX4QCWKEGXOUTTVCL3BDANCNFSM5RKNYTLA . You are receiving this because you were mentioned.Message ID: @.***>

[vanimesh]

I compared two environments...the one where it's working fine and the one where it's not...i did find any issues with preivileges.

[Doctorbal]

@vanimesh ,

My suggestion is to start from scratch your test environment and replicate the prod environment best you can.

If that is not an option list clearly all the steps you performed and compare it carefully to the documentation.

From there we could pinpoint where things went wrong.

Regards, Andreas