pgpartman / pg_partman

Partition management extension for PostgreSQL
Other
2.09k stars 281 forks source link

How to specify tablespaces for native partitioning using create_parent function in Postgres 11.5 #272

Closed rajinderbedi closed 5 years ago

rajinderbedi commented 5 years ago

I've gone through the below blog post titled Native partitioning tablespace inheritance https://www.postgresql.org/message-id/flat/CAODZiv6EBULTz1dP2KV84XMGgDaG%3DR3TxodK%2BovwHSPWECK8sQ%40mail.gmail.com#CAODZiv6EBULTz1dP2KV84XMGgDaG=R3TxodK+ovwHSPWECK8sQ@mail.gmail.com

As per my understanding, it states that the default behavior of PostgeSQL is that it creates Partitioned (Parent) table in the default table space, completely ignoring table space specified when defining table with partition clause.

We need to explicitly set the table space for the partition (child) tables.

I've gone through the documentation of pg_partman and also googled but didn't find how to specify tablespaces for partition tables (child tables) using create_parent() function of pg_partman to create partition tables (child tables) with separate tablespaces.

I am using PostgreSQL 11.5 and native partitioning.

Please guide me on this regard and share same examples/code.

Your help in this regards would be highly appreciated.

keithf4 commented 5 years ago

That is covered in this section of the documentation where I discuss property inheritance

https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#child-table-property-inheritance

Set the tablespace on the template table for native to ensure consistency for now.

keithf4 commented 5 years ago

FYI, PostgreSQL 12 has fixed this issue with tablespace inheritance in native partitioning. Will try and have this fixed in an upcoming release of pg_partman to remove tablespace management from the template table and return it to the parent only for PG12 and later. PG11 and 10 will still have to rely on the template table system.

keithf4 commented 5 years ago

Will be tracked via this Issue: https://github.com/pgpartman/pg_partman/issues/274

keithf4 commented 5 years ago

Closing this issue for now. Please track the issue linked above for when this feature is updated for PG12.