0

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?

1
  • Repartition how? What do you want the new partitions to be?
    – jjanes
    Mar 14, 2020 at 15:34

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.