Обсуждение: Changing FS when full
Hi, I have a big database on FS1, now almost full. Have space on FS2, where I created a tablespace and moved every table and index to it. Still, lots of space used on FS1. The problem is not pg_xlog, but base: # du -hs base/105658651/* | fgrep G 1,0G base/105658651/106377323 1,0G base/105658651/106377323.1 1,0G base/105658651/106377323.2 1,0G base/105658651/106377323.3 1,0G base/105658651/106377323.4 1,0G base/105658651/125520217 1,0G base/105658651/127352052 1,0G base/105658651/127352052.1 1,0G base/105658651/127352052.2 1,0G base/105658651/127352052.3 1,0G base/105658651/127352052.4 1,0G base/105658651/127352052.5 Unfortunately no volume management is available, so I can't move disks from FS2 to FS1. I could play soft links tricks, but I'm afraid of paying the FS-traversal penalty on each file access (is that right?). So, any way of instructing PG (8.1 if that matters) to place those files elsewhere without an initdb? Thanks! Fernando.
Fernando Schapachnik wrote: > I could play soft links tricks, but I'm afraid of paying the > FS-traversal penalty on each file access (is that right?). > Compared to everything else that goes into I/O, symlink traversal overhead is pretty low. > So, any way of instructing PG (8.1 if that matters) to place those > files elsewhere without an initdb? > You can create another table just like the original on a tablespace using the new storage, drop the original, and then rename the new one to the original name. This is described as "another way to cluster data" in the Notes section of http://www.postgresql.org/docs/8.4/static/sql-cluster.html , and it has a few warnings related to information that can be lost in this situation. Make sure you've moved all temporary files onto the new filesystem first, observing the warning about that there too. -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com
En un mensaje anterior, Greg Smith escribió: > >So, any way of instructing PG (8.1 if that matters) to place those > >files elsewhere without an initdb? > > > > You can create another table just like the original on a tablespace > using the new storage, drop the original, and then rename the new one to > the original name. This is described as "another way to cluster data" > in the Notes section of > http://www.postgresql.org/docs/8.4/static/sql-cluster.html , and it has > a few warnings related to information that can be lost in this > situation. Make sure you've moved all temporary files onto the new > filesystem first, observing the warning about that there too. One question: is this different from ALTER TABLE ... SET TABLESPACE? Thanks.
On 2010-01-21, Fernando Schapachnik <fernando@mecon.gov.ar> wrote: > Hi, > > I have a big database on FS1, now almost full. Have space on FS2, > where I created a tablespace and moved every table and index to it. > Still, lots of space used on FS1. The problem is not pg_xlog, but > base: > > # du -hs base/105658651/* | fgrep G > 1,0G base/105658651/106377323 > 1,0G base/105658651/106377323.1 > 1,0G base/105658651/106377323.2 > 1,0G base/105658651/106377323.3 > 1,0G base/105658651/106377323.4 > 1,0G base/105658651/125520217 > 1,0G base/105658651/127352052 > 1,0G base/105658651/127352052.1 > 1,0G base/105658651/127352052.2 > 1,0G base/105658651/127352052.3 > 1,0G base/105658651/127352052.4 > 1,0G base/105658651/127352052.5 > > Unfortunately no volume management is available, so I can't move > disks from FS2 to FS1. > > I could play soft links tricks, but I'm afraid of paying the > FS-traversal penalty on each file access (is that right?). > > So, any way of instructing PG (8.1 if that matters) to place those > files elsewhere without an initdb? Linux has "mount -o bind" which works like a symlink, but at a lower level. You appear to use BSD which appears to have an equivalent in nullfs.