Repartition PostgreSQL tables without losing data

Question

I already have my table partitioned as follows:

CREATE TABLE customer
(
  c_custkey integer NOT NULL, 
  c_name character varying(25) NOT NULL, 
  c_address character varying(40) NOT NULL, 
  c_city character(10) NOT NULL, 
  c_nation character(15) NOT NULL, 
  c_region character(15) NOT NULL, 
  c_phone character(15) NOT NULL,
  c_mktsegment character(10) 
) PARTITION BY List(c_region) ;

create table customer_1 PARTITION of customer for values in ('EUROPE');
create table customer_2 PARTITION of customer for values in ('AFRICA');
create table customer_3 PARTITION of customer for values in ('AMERICA');
create table customer_4 PARTITION of customer for values in ('MIDDLE EAST');
create table customer_5 PARTITION of customer for values in ('ASIA');

My partitions are filled with data. I want to repartition my table without losing data. How to do that with PostgreSQL?

Answer 1

Essentially you create a new partitioned table and fill it with the data from the old one, for example with INSERT INTO ... SELECT. Then you can drop the old table and rename the new one. That requires downtime.

If you mind downtime, create a new database from a schema dump of the old one, except that your partitioned table is now partitioned differently. Then set up logical replication between the two databases and switch over as soon as the replica has caught up.