From: | Arne Henrik Segtnan <arne(at)basis-consulting(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Add primary key to existing, partitioned table |
Date: | 2023-08-23 07:07:40 |
Message-ID: | CA+KiRjRwhrvVLgDgu1V7R0bcFa7fZQt1WJUqo=HBwVLjJohfdg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
We have an application currently running on PostgreSQL 12, partitioned as
shown below. Database size is approx. 1.4TB. We are now in the process of
upgrading the application, using PostgreSQL 15. In the new version, there
is an optional step to implement 'primary key' on history table.
In the documentation, there is no information/procedure describing how to
do this on a partitioned PostgreSQL database.
There are two questions related to this:
1: Is partitioning considered best options to deal with cleanup of large
chunks of historical data in PostgreSQL 15?
2: Based on findings so far, it seems like the solution is to:
- create script to create temporary tables for main table and partitions
(identical to original tables)
- add primary key and all other required dependencies to the temporary
tables
- copy the data back to main table (not each partition)
Does this seem correct? Any obvious pitfalls? Anyone else who has
experienced similar cases?
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 40;
relname | relpages
----------------------------+----------
events events_1
events
events_pkey
event_recovery_2
housekeeper
event_recovery_pkey
event_recovery_1
items
sessions_pkey
event_recovery
sessions_1
housekeeper_pkey
trends_uint_p2023_02
history_uint_p2023_08_17
trends_uint_p2023_06
trends_uint_p2023_01
trends_uint_p2023_07
history_uint_p2023_08_18
history_uint_p2023_08_19
alerts
history_uint_p2023_08_20
history_uint_p2023_08_16
history_uint_p2023_08_17_1
history_uint_p2023_08_15
history_uint_p2023_08_19_1
history_uint_p2023_08_18_1
history_uint_p2023_08_20_1
history_uint_p2023_08_16_1
trends_uint_p2023_08
trends_uint_p2023_05
trends_uint_p2023_02_1
history_uint_p2023_08_10
history_uint_p2023_08_15_1
history_uint_p2023_08_08
trends_uint_p2023_01_1
history_uint_p2023_08_09
trends_uint_p2023_06_1
history_uint_p2023_08_12
history_uint_p2023_08_13
(40 rows)
Best regards,
Arne Segtnan
From | Date | Subject | |
---|---|---|---|
Next Message | Tushar Takate | 2023-08-23 10:22:37 | PostgreSQL DB cluster migration from centos7/RHEL7/OEL7 to RHEL8 |
Previous Message | Justin Clift | 2023-08-23 06:21:45 | Re: [EXTERNAL] Oracle FDW version |