Thread: How to expand existing table-space
If the FS associated with the existing FS is reaching capacity, and sys-admins do not want to expand the FS any more and are advising us to use a new FS, what is the best practice in this case. Since PG does not have concept of adding data files to existing tablespace, how do we add space with no outage. Thanks.
-----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Ravi Krishna Sent: Tuesday, May 26, 2015 2:53 PM To: pgsql-admin@postgresql.org Subject: [ADMIN] How to expand existing table-space If the FS associated with the existing FS is reaching capacity, and sys-admins do not want to expand the FS any more andare advising us to use a new FS, what is the best practice in this case. Since PG does not have concept of adding datafiles to existing tablespace, how do we add space with no outage. Thanks. -- I see no other way, but create new TS on the new FS and move objects (all or some of them) from old into new TS. Regards, Igor Neyman
On Tue, May 26, 2015 at 3:03 PM, Igor Neyman <ineyman@perceptron.com> wrote: > I see no other way, but create new TS on the new FS and move objects (all or some of them) > from old into new TS. But isn't moving objects from old to new TS an offline job (the table is locked). This leads to obvious conclusion that in PG, no single table should be allowed to grow beyond a reasonable limit. Is this being addressed in future versions, like allowing a new storage path to existing tablespace.
Ravi Krishna <sravikrishna3@gmail.com> writes:
> Is this being addressed in future versions, like allowing a new
> storage path to existing tablespace.
As far as PG is concerned, a tablespace is just a directory. Space
provisioning should be addressed at the level of the filesystem.
regards, tom lane
On Tue, May 26, 2015 at 3:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Ravi Krishna <sravikrishna3@gmail.com> writes: >> Is this being addressed in future versions, like allowing a new >> storage path to existing tablespace. > > As far as PG is concerned, a tablespace is just a directory. Space > provisioning should be addressed at the level of the filesystem. I guess one easy way for large tables is to break it up to small tables via inheritance and each table assigned to a different tablespace.
On May 26, 2015, at 1:54 PM, Ravi Krishna <sravikrishna3@gmail.com> wrote: > > I guess one easy way for large tables is to break it up to small > tables via inheritance and each table assigned to a different > tablespace. That’s also a way you could migrate your current table without disrupting production. Partitioning like that is often done as a performance optimization; but it sounds like you’re proposing that a file systemcannot be expected to handle a large table in piece??? I don’t understand that. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ https://www.linkedin.com/in/scottribe/ (303) 722-0567 voice
> That's also a way you could migrate your current table without disrupting production. > > Partitioning like that is often done as a performance optimization; but it sounds like you're proposing that a file systemcannot be expected to handle a large table in piece??? I don't understand that. Let us say that that the sys admins have set a limit of 1TB per FS. This automatically implies that a single table can not grow more than 1TB, unless it is broken into smaller tables via inheritance and each child table goes to a separate table-space. I hope I am clearer now.
On May 26, 2015, at 2:33 PM, Ravi Krishna <sravikrishna3@gmail.com> wrote: > > Let us say that that the sys admins have set a limit of 1TB per FS. > This automatically implies that a single table can not grow more than > 1TB, unless it is broken into smaller tables via inheritance and each > child table goes to a separate table-space. I hope I am clearer now. Yes. There’s an externally-imposed limit, and you just have to deal with it, regardless of how idiotic it might be ;-) -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ https://www.linkedin.com/in/scottribe/ (303) 722-0567 voice