I have partitioned tables in postgresql. With some of them everything is fine and partitions are created. But with some of them there are problems. When I start a transaction to create a partition it does not end, but lasts indefinitely.The transaction took more than enough time to complete - about 12 hours. But after 10 minutes of this transaction execution there is a big load on the database, because of which the applications cannot access the database. Some info about tables
All tables are partitioned by date. The tables are small in size, about 3 GB. Those tables that have partitions created have a slightly smaller size. I have several other servers with the same database template. But everything works fine for them.
This database is just a backup of one of the two, where everything works fine.
Partitions are not created either through the postgres role or the second role that has permissions.
Currently, fine databases have postgresql 14 on them, and postgresql 11 on the problematic one, but even at the moment when other servers had version 11, everything worked.
What I try to do:
I have not found any locks during the execution of the transaction.
I have also stopped the applications that use the database. This did not help either.
I tried to create partition through a regular script and DBeaver.
There are no errors in the database logs.
pg_stat_activitylook when there is a "big load" while creating the partition hangs?"CREATE TABLE table_2024_09 PARTITION OF table FOR VALUES FROM ('2024-09-01 00:00:00+04') TO ('2024-10-01 00:00:00+04')". During transactionpg_stat_activityshow that transaction is active. And evenpg_stat_activityexecuted longSELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activits WHERE state <> 'idle';.