How to deploy pg_partman with rolling update on kubernetes

[yaghyesh-fareye]

I had a table with the name 'parcel_event'. I am partitioning it with pg_partman and scheduling monthly maintenance with pg_cron.

What am I doing:
I am using flyway for database migration, in which I am renaming 'parcel_event' to 'parcel_event_old', then creating a new table with the name 'parcel_event'. Then applying partition and scheduling with pg_partman and pg_cron.

After that, I am migrating the last 3 months' data from 'parcel_event_old' to 'parcel_event'.

alter table if exists parcel_event rename to parcel_event_old;

CREATE TABLE IF NOT EXISTS parcel_event (
  id BIGINT NOT NULL,
   order_no VARCHAR(255),
   latitude VARCHAR(255),
   longitude VARCHAR(255),
   status_identifier_value VARCHAR(255),
   carrier_status_code VARCHAR(255),
   carrier_sub_status VARCHAR(255),
   carrier_sub_status_code VARCHAR(255),
   location_name VARCHAR(255),
   extra_info JSON,
   info JSON,
   CONSTRAINT pk_parcel_event_id PRIMARY KEY (id, carrier_status_time_gmt)
) partition by range (carrier_status_time_gmt);

CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman WITH SCHEMA partman;

SELECT partman.create_parent(p_parent_table => 'public.parcel_event',
 p_control => 'carrier_status_time_gmt',
 p_type => 'native',
 p_interval=> 'monthly',
 p_premake => 3,
 p_start_partition => '2023-01-01');

CREATE EXTENSION IF NOT EXISTS pg_cron;

UPDATE partman.part_config
SET infinite_time_partitions = true,
    retention = '3 months',
    retention_keep_table=true
WHERE parent_table = 'public.parcel_event';

SELECT cron.schedule('0 0 25 * *', $$CALL partman.run_maintenance_proc()$$);

Problems that I am facing doing deployment are:
When I rename the 'parcel_event' table then my already running pod with the old version will not find the table 'parcel_event' between renaming time and the new table 'parcel_event' creating + partitioning time.

What should I do?
Should I not rename or is there any other way?
Should I run these commands externally on Postgres?
Is there any way with locking?
Should I stop Kafka consumer inside the service?

[keithf4]

Not sure I fully understand what you're doing here, but if you're trying to make table renaming operations transparent to users, you can wrap all the relevant statements in a transaction (BEGIN/COMMIT) and immediately at the start of the transaction, obtain exclusive locks on all relevant objects. If the locks are obtained, that ensures that nothing else is accessing it and prevents anything else from accessing it until COMMIT.

If possible, it would be good to limit as much client interaction with these objects as possible during this operation. Otherwise you run into both your own session waiting on locks and then all subsequent transactions being held up until COMMIT.

You can see a similar transaction locking method in the example of online partitioning here - https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman_howto_native.md#online-partitioning