pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to expand existing table-space
8+ messages / 4 participants
[nested] [flat]
* How to expand existing table-space
@ 2015-05-26 18:52 Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 19:03 ` Re: How to expand existing table-space Igor Neyman <ineyman@perceptron.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ravi Krishna @ 2015-05-26 18:52 UTC (permalink / raw)
To: pgsql-admin
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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to expand existing table-space
2015-05-26 18:52 How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
@ 2015-05-26 19:03 ` Igor Neyman <ineyman@perceptron.com>
2015-05-26 19:16 ` Re: How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Igor Neyman @ 2015-05-26 19:03 UTC (permalink / raw)
To: Ravi Krishna <sravikrishna3@gmail.com>; pgsql-admin
-----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 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.
--
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to expand existing table-space
2015-05-26 18:52 How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 19:03 ` Re: How to expand existing table-space Igor Neyman <ineyman@perceptron.com>
@ 2015-05-26 19:16 ` Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 19:19 ` Re: How to expand existing table-space Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Ravi Krishna @ 2015-05-26 19:16 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; +Cc: pgsql-admin
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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to expand existing table-space
2015-05-26 18:52 How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 19:03 ` Re: How to expand existing table-space Igor Neyman <ineyman@perceptron.com>
2015-05-26 19:16 ` Re: How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
@ 2015-05-26 19:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-05-26 19:54 ` Re: How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2015-05-26 19:19 UTC (permalink / raw)
To: Ravi Krishna <sravikrishna3@gmail.com>; +Cc: Igor Neyman <ineyman@perceptron.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to expand existing table-space
2015-05-26 18:52 How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 19:03 ` Re: How to expand existing table-space Igor Neyman <ineyman@perceptron.com>
2015-05-26 19:16 ` Re: How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 19:19 ` Re: How to expand existing table-space Tom Lane <tgl@sss.pgh.pa.us>
@ 2015-05-26 19:54 ` Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 20:11 ` Re: How to expand existing table-space Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ravi Krishna @ 2015-05-26 19:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Igor Neyman <ineyman@perceptron.com>; pgsql-admin
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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to expand existing table-space
2015-05-26 18:52 How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 19:03 ` Re: How to expand existing table-space Igor Neyman <ineyman@perceptron.com>
2015-05-26 19:16 ` Re: How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 19:19 ` Re: How to expand existing table-space Tom Lane <tgl@sss.pgh.pa.us>
2015-05-26 19:54 ` Re: How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
@ 2015-05-26 20:11 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-26 20:33 ` Re: How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Ribe @ 2015-05-26 20:11 UTC (permalink / raw)
To: Ravi Krishna <sravikrishna3@gmail.com>; +Cc: pgsql-admin
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 system cannot 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to expand existing table-space
2015-05-26 18:52 How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 19:03 ` Re: How to expand existing table-space Igor Neyman <ineyman@perceptron.com>
2015-05-26 19:16 ` Re: How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 19:19 ` Re: How to expand existing table-space Tom Lane <tgl@sss.pgh.pa.us>
2015-05-26 19:54 ` Re: How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 20:11 ` Re: How to expand existing table-space Scott Ribe <scott_ribe@elevated-dev.com>
@ 2015-05-26 20:33 ` Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 20:53 ` Re: How to expand existing table-space Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ravi Krishna @ 2015-05-26 20:33 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin
> 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 system cannot 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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to expand existing table-space
2015-05-26 18:52 How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 19:03 ` Re: How to expand existing table-space Igor Neyman <ineyman@perceptron.com>
2015-05-26 19:16 ` Re: How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 19:19 ` Re: How to expand existing table-space Tom Lane <tgl@sss.pgh.pa.us>
2015-05-26 19:54 ` Re: How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 20:11 ` Re: How to expand existing table-space Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-26 20:33 ` Re: How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
@ 2015-05-26 20:53 ` Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 8+ messages in thread
From: Scott Ribe @ 2015-05-26 20:53 UTC (permalink / raw)
To: Ravi Krishna <sravikrishna3@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2015-05-26 20:53 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-05-26 18:52 How to expand existing table-space Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 19:03 ` Igor Neyman <ineyman@perceptron.com>
2015-05-26 19:16 ` Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 19:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-05-26 19:54 ` Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 20:11 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-26 20:33 ` Ravi Krishna <sravikrishna3@gmail.com>
2015-05-26 20:53 ` Scott Ribe <scott_ribe@elevated-dev.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox