Logging on Aurora Postgres

[SteveDirschelTR]

Based on the documentation it appears if you use PG Jobmon it allows auditing and monitoring of partition maintenance. PG Jobmon is not available with Aurora Postgres. We would be using pg_cron to schedule pg_partman.

It would be nice if things were logged so one could look at what all was processed during a run of run_maintenance_proc. Are there any types of logging we can enable to be able to see what was successfully processed for any given run of that procedure?

Thanks

[keithf4]

I do have some debugging enabled in certain parts of the code, so if you increase your log level in postgresql, it would show more details in the Postgres logs. It would also include a lot of other debug information for postgresql in general, though.

Outside of that, the reason I use pg_jobmon for logging that sort of information in general is because there's not really an easy way to optionally do that outside of debug lines in functions/procedures. Logging steps within a transaction to another table would be rolled back during an error since the entire transaction would be rolled back. pg_jobmon accounts for that by using secondary connections.

Only other thing I can think of would maybe some sort of code profiler or something else that traces and logs function calls. Either way it's going to be some sort of third party tool to get more details outside of just something like pg_stat_statements or turning up logging for PostgreSQL overall.

[SteveDirschelTR]

Hi Keith, Thanks for the reply below. If it’s ok I have one additional question. This may be documented but so far I have not read it. I currently have 3 tables setup in partman.part_config. I purposely have 1 table that will throw an error because the partition names do not match the expected naming standard. When I run call partman.run_maintenance_proc(); I get an error thrown back to my screen in Psql due to that one table. The other 2 tables I have setup are correct and I expect some older partitions to get dropped on them. But it seems those 2 other tables are not getting processed during this call. Is this expected behavior where if any table being handled by the run_maintenance_proc gets an error EVERYTHING else will get rolled back? So if I had 100 tables getting processed and the 99th table throws an error the other 99 tables will not get processed? If that is the default behavior are there settings I can change to allow the other 99 to get processed? Thanks in advance for your reply. Steve From: Keith Fiske ***@***.***> Sent: Wednesday, December 15, 2021 3:53 PM To: pgpartman/pg_partman ***@***.***> Cc: Dirschel, Steve ***@***.***>; Author ***@***.***> Subject: [EXT] Re: [pgpartman/pg_partman] Logging on Aurora Postgres (Discussion #415) External Email: Use caution with links and attachments. I do have some debugging enabled in certain parts of the code, so if you increase your log level in postgresql, it would show more details in the Postgres logs. It would also include a lot of other debug information for postgresql in general, though. Outside of that, the reason I use pg_jobmon for logging that sort of information in general is because there's not really an easy way to optionally do that outside of debug lines in functions/procedures. Logging steps within a transaction to another table would be rolled back during an error since the entire transaction would be rolled back. pg_jobmon accounts for that by using secondary connections. Only other thing I can think of would maybe some sort of code profiler or something else that traces and logs function calls. Either way it's going to be some sort of third party tool to get more details outside of just something like pg_stat_statements or turning up logging for PostgreSQL overall. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<https://urldefense.com/v3/__https:/github.com/pgpartman/pg_partman/discussions/415*discussioncomment-1817447__;Iw!!GFN0sa3rsbfR8OLyAw!K80njyP5VKuwzm5XJipPBJu3sg0nNh3P6DXkWwD0DLlRT6qUmjm_bGyuNSfN5JZ7Q2qIuc9btQ$>, or unsubscribe<https://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/APMRKUY3WVMHFLWM5BNBPSTUREE3BANCNFSM5KEPACPQ__;!!GFN0sa3rsbfR8OLyAw!K80njyP5VKuwzm5XJipPBJu3sg0nNh3P6DXkWwD0DLlRT6qUmjm_bGyuNSfN5JZ7Q2qK8C1CfA$>. Triage notifications on the go with GitHub Mobile for iOS<https://urldefense.com/v3/__https:/apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675__;!!GFN0sa3rsbfR8OLyAw!K80njyP5VKuwzm5XJipPBJu3sg0nNh3P6DXkWwD0DLlRT6qUmjm_bGyuNSfN5JZ7Q2oXC1GlfQ$> or Android<https://urldefense.com/v3/__https:/play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign*3Dnotification-email*26utm_medium*3Demail*26utm_source*3Dgithub__;JSUlJSU!!GFN0sa3rsbfR8OLyAw!K80njyP5VKuwzm5XJipPBJu3sg0nNh3P6DXkWwD0DLlRT6qUmjm_bGyuNSfN5JZ7Q2ps36ZoyQ$>.

[keithf4]

With run_maintenance_proc that shouldn't the case. But there's no guarantee the order that they will run in so it seems it's just picking your broken one first and stopping there.

If you don't want run_maintenance calls to process a specific set, you can set the automatic_maintenance column in part_config to false for that partition set. That option is there to allow allow complete manual management of partition maintenance for specific sets.

[SteveDirschelTR]

Again, thanks for the reply. Your reply below makes sense. Is there any specific reason the code doesn’t handle the failure of a single table and move on to the remaining tables? If one table failed for some reason I don’t see why it couldn’t attempt to process the rest of them. If not should I open an enhancement request to do that on the git page? Based on your reply below I see I could call the run_maintenance_proc for every table so if 1 failed I would still be doing all of the other tables but that would require a job for every table. It is much easier just to add the table to the part_config table and know the single scheduled job will automatically pick it up. Regards Steve From: Keith Fiske ***@***.***> Sent: Wednesday, December 22, 2021 9:37 AM To: pgpartman/pg_partman ***@***.***> Cc: Dirschel, Steve ***@***.***>; Author ***@***.***> Subject: [EXT] Re: [pgpartman/pg_partman] Logging on Aurora Postgres (Discussion #415) External Email: Use caution with links and attachments. With run_maintenance_proc that shouldn't the case. But there's no guarantee the order that they will run in so it seems it's just picking your broken one first and stopping there. If you don't want run_maintenance calls to process a specific set, you can set the automatic_maintenance column in part_config to false for that partition set. That option is there to allow allow complete manual management of partition maintenance for specific sets. — Reply to this email directly, view it on GitHub<https://urldefense.com/v3/__https:/github.com/pgpartman/pg_partman/discussions/415*discussioncomment-1859096__;Iw!!GFN0sa3rsbfR8OLyAw!PyaG0lxKyeEgp5zRCmYxBzTFg5skioiKO-e8i9lSxzINMaXxX09ZoMP9PH-zv3VEYja0DfwyWQ$>, or unsubscribe<https://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/APMRKU5W5TD6V6RYPQNXHKTUSHWC7ANCNFSM5KEPACPQ__;!!GFN0sa3rsbfR8OLyAw!PyaG0lxKyeEgp5zRCmYxBzTFg5skioiKO-e8i9lSxzINMaXxX09ZoMP9PH-zv3VEYjY72yVMvw$>. Triage notifications on the go with GitHub Mobile for iOS<https://urldefense.com/v3/__https:/apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675__;!!GFN0sa3rsbfR8OLyAw!PyaG0lxKyeEgp5zRCmYxBzTFg5skioiKO-e8i9lSxzINMaXxX09ZoMP9PH-zv3VEYjawmkSS7A$> or Android<https://urldefense.com/v3/__https:/play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign*3Dnotification-email*26utm_medium*3Demail*26utm_source*3Dgithub__;JSUlJSU!!GFN0sa3rsbfR8OLyAw!PyaG0lxKyeEgp5zRCmYxBzTFg5skioiKO-e8i9lSxzINMaXxX09ZoMP9PH-zv3VEYjaB9hH37Q$>. You are receiving this because you authored the thread.Message ID: ***@***.******@***.***>>

[keithf4]

I'd have to consider whether continuing during errors would have any other consequences. Especially if someone isn't able to log errors using pg_jobmon as you're running into.

What I'm saying is that if you set automatic_maintenance to false for that one partition set, then you can use the run_maintenance calls as normal and all other sets will run without issue. So if one set was running into problems, you could just take that out of consideration to fix while the single maintenance call could continue working with the others.