Thread: running out of disk space
hello,
We are running out of disk space, and we introduced new volume to it. I am about to create new tablespace X and alter user databases to set to this new tablespace X. So, all new tables will be created in X, but what about existing tables on previous volume? Its data will be split between 2 tbspaces?
Please clarify. Thank you!
On Thu, 2019-05-09 at 15:46 +0000, Julie Nishimura wrote:
hello,We are running out of disk space, and we introduced new volume to it. I am about to create new tablespace X and alter user databases to set to this new tablespace X. So, all new tables will be created in X, but what about existing tables on previous volume? Its data will be split between 2 tbspaces?Please clarify. Thank you!
Tables and indexes can only be on one tablespace. You'll need to individually move them with alter table and alter index. They will be locked while being moved. You should experiment with this on a test server to learn how it works.
However, unless your new volume has different I/O characteristics than the existing volume you really shouldn't use tablespaces. You should use your OS volume manager and filesystem tools to extend the data volume and filesystem into the new space. This will be much easier to manage. I'm sure your system administrator can assist you with this.
Alan, thanks for your reply.
So, we currently have this situation:
/dev/sda1 2.7T 2.4T 298G 90% /data/vol1
/dev/sdb2 2.7T 2.4T 296G 89% /data/vol2
/dev/mapper/vg00-data
16T 16T 373G 98% /data/vol3postgres=# SELECT * FROM pg_tablespace;
spcname | spcowner | spclocation | spcacl
------------+----------+-------------+-----------------------------------
pg_default | 10 | |
pg_global | 10 | |
vol1 | 10 | /data/vol1 | {postgres=C/postgres,=C/postgres}
vol2 | 10 | /data/vol2 | {postgres=C/postgres,=C/postgres}
vol3 | 10 | /data/vol3 | {postgres=C/postgres,=C/postgres}
(5 rows)And we added new volume yday:
/dev/mapper/vg00-vol4
4.0T 195M 3.8T 1% /data/vol4
4.0T 195M 3.8T 1% /data/vol4
So, if I understood you correctly, it is better to extend vol3 instead of creating new tablespace on vol4 (if possible).
If not, then what is my best bet?
Thanks,
Julie
From: Alan Hodgson <ahodgson@lists.simkin.ca>
Sent: Thursday, May 9, 2019 9:15 AM
To: Julie Nishimura; pgsql-general
Subject: Re: running out of disk space
Sent: Thursday, May 9, 2019 9:15 AM
To: Julie Nishimura; pgsql-general
Subject: Re: running out of disk space
On Thu, 2019-05-09 at 15:46 +0000, Julie Nishimura wrote:
hello,We are running out of disk space, and we introduced new volume to it. I am about to create new tablespace X and alter user databases to set to this new tablespace X. So, all new tables will be created in X, but what about existing tables on previous volume? Its data will be split between 2 tbspaces?Please clarify. Thank you!
Tables and indexes can only be on one tablespace. You'll need to individually move them with alter table and alter index. They will be locked while being moved. You should experiment with this on a test server to learn how it works.
However, unless your new volume has different I/O characteristics than the existing volume you really shouldn't use tablespaces. You should use your OS volume manager and filesystem tools to extend the data volume and filesystem into the new space. This will be much easier to manage. I'm sure your system administrator can assist you with this.
On Thu, 2019-05-09 at 16:49 +0000, Julie Nishimura wrote:
Alan, thanks for your reply.So, we currently have this situation:/dev/sda1 2.7T 2.4T 298G 90% /data/vol1/dev/sdb2 2.7T 2.4T 296G 89% /data/vol2/dev/mapper/vg00-data16T 16T 373G 98% /data/vol3postgres=# SELECT * FROM pg_tablespace;spcname | spcowner | spclocation | spcacl------------+----------+-------------+-----------------------------------pg_default | 10 | |pg_global | 10 | |vol1 | 10 | /data/vol1 | {postgres=C/postgres,=C/postgres}vol2 | 10 | /data/vol2 | {postgres=C/postgres,=C/postgres}vol3 | 10 | /data/vol3 | {postgres=C/postgres,=C/postgres}(5 rows)And we added new volume yday:/dev/mapper/vg00-vol4
4.0T 195M 3.8T 1% /data/vol4So, if I understood you correctly, it is better to extend vol3 instead of creating new tablespace on vol4 (if possible).If not, then what is my best bet?
Well you already have a mess of tablespaces, so I guess one more won't hurt.
But since the new device is already in LVM and in the same volume group it really would have been easier to grow vol3 instead of adding a vol4.