Recently, I’ve used extensions pg_partman, pg_cron to archive old data. Yesterday I saw a new partition but I cannot access it. I’ve checked that partition’s table owner, and it belongs to _dodb. As I know _dodb is a superuser and I can only log in with doadmin. How can I drop that partition without logging in with _dodb?
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
Hey @nguyenthanhquang152,
You should be able to set the number of partitions you want to keep, then execute the run_maintenance() function to automatically drop (or archive) the old data.
It does require additional GRANTS on a per-database basis to be able to do so, as you’ve done here.
I don’t know what roles will exist and don’t want to grant permissions that may not be wanted by default.
You will tell me how it goes.
Cheers, - Sergio Turpín