How to remove a partition key in Postgres-CodePudding

I have the following table in PostgreSQL:

CREATE TABLE foo (
   bar text NOT NULL
   ...
)
PARTITION BY LIST (bar);

How can the whole partition key be removed from the table? It is possible to remove a single partition table with DETACH PARTITION, but I can't find a way to drop the partitioning itself.

CodePudding user response：

You cannot drop partitioning. If you want to use an unpartitioned table instead, you have to move a lot of data. That is unavoidable, because the data are actually stored in the partitions.

You'll have to do something along the lines of

CREATE TABLE new_foo (LIKE foo);
INSERT INTO new_foo SELECT * FROM foo;
DROP TABLE foo;
ALTER TABLE new_foo RENAME TO foo;

The details may vary: you will have to take care of constraints and indexes, you can parallelize by running several inserts concurrently (for example for different partitions), and so on.

In any event it will be a longer down time. The only way I know o avoid this is to set up logical replication for the whole database and use an unpartitioned table on the standby side. Once replication has caught up, you can switch over to the standby database.