Postgresql- how to rotate table partitions, trunc oldest every day? I need to create a partitioned table with 4 rotating partitions for 4 days, and every day truncate the oldest partition.

I know how to create table partitions on postgres, but I can't understand how to arrange daily rotation of several partitions.

I'm on postgresql 9.6, Amazon RDS instance which means no pg_cron to be able to run daily truncating.

I could write a python script and schedule it in cron on a side micro instance, but I'd preper to use internal postgres tools only.
First approach is creating partition every day which name matches date:

DROP TABLE IF EXISTS log_table;

CREATE TABLE log_table (
    logid         SERIAL,
    logdate         date DEFAULT Now()
) PARTITION BY RANGE (logdate);


CREATE TABLE log_table_2020_05_10 PARTITION OF log_table FOR VALUES FROM ('2020-05-10') TO ('2020-05-11');
CREATE TABLE log_table_2020_05_11 PARTITION OF log_table FOR VALUES FROM ('2020-05-11') TO ('2020-05-12');
CREATE TABLE log_table_2020_05_12 PARTITION OF log_table FOR VALUES FROM ('2020-05-12') TO ('2020-05-13');
CREATE TABLE log_table_2020_05_13 PARTITION OF log_table FOR VALUES FROM ('2020-05-13') TO ('2020-05-14');
CREATE TABLE log_table_2020_05_14 PARTITION OF log_table FOR VALUES FROM ('2020-05-14') TO ('2020-05-15');


And then every day you will have to drop the oldest partition and create the new one:

DROP TABLE log_table_2020_05_10;
CREATE TABLE log_table_2020_05_15 PARTITION OF log_table FOR VALUES FROM ('2020-05-15') TO ('2020-05-16');


The second approach with always-the-same-name of partitions will looks like this:

-- initial tables
DROP TABLE IF EXISTS log_table;

CREATE TABLE log_table (
    logid         SERIAL,
    logdate         date DEFAULT Now()
) PARTITION BY RANGE (logdate);

CREATE TABLE log_table_4 PARTITION OF log_table FOR VALUES FROM ('2020-05-10') TO ('2020-05-11');
CREATE TABLE log_table_3 PARTITION OF log_table FOR VALUES FROM ('2020-05-11') TO ('2020-05-12');
CREATE TABLE log_table_2 PARTITION OF log_table FOR VALUES FROM ('2020-05-12') TO ('2020-05-13');
CREATE TABLE log_table_1 PARTITION OF log_table FOR VALUES FROM ('2020-05-13') TO ('2020-05-14');
CREATE TABLE log_table_current PARTITION OF log_table FOR VALUES FROM ('2020-05-14') TO ('2020-05-15');


And then every day you will schedule something like:

-- drop the oldest partition
DROP TABLE log_table_4;

-- rename others
ALTER TABLE log_table_3 RENAME TO log_table_4;
ALTER TABLE log_table_2 RENAME TO log_table_3;
ALTER TABLE log_table_1 RENAME TO log_table_2;
ALTER TABLE log_table_current RENAME TO log_table_1;

-- create new one
CREATE TABLE log_table_current PARTITION OF log_table FOR VALUES FROM (date_trunc('day', Now())) TO (date_trunc('day', Now())+INTERVAL '1 day');