FAQ
Hi there.

I have a huge database with several tables. Some tables have statistics
information. And it's very huge. I don't want to loose any of this data.
But hard drives on my single server are not eternal. Very soon, there will
be no left space. And the most awful this, that it's a 1U server, and I
can't install new hard drive.

What can I do to enlarge space, without loosing data and performance?

Thanks for patience.

Search Discussions

  • Ivan Voras at Jul 26, 2010 at 11:43 am

    On 26.7.2010 12:43, AlannY wrote:
    Hi there.

    I have a huge database with several tables. Some tables have statistics
    information. And it's very huge. I don't want to loose any of this data.
    But hard drives on my single server are not eternal. Very soon, there will
    be no left space. And the most awful this, that it's a 1U server, and I
    can't install new hard drive.

    What can I do to enlarge space, without loosing data and performance?
    Absolutely nothing quick and easy. In fact, about the only thing you can
    do which won't cause a (long term) data loss and performance degradation
    is a full backup, installing bigger drives to replace the old ones, and
    full restore.

    Some other ideas which might help you if you don't want to swap drives,
    but generally require a lot of work and you *will* lose either data or
    performance:

    * use a file system which supports compression (NTFS on Windows, ZFS on
    FreeBSD & Solaris, don't know any on Linux)
    * move unneeded data out from the database and into a separate,
    compressed data storage format (e.g. move statistical data into gzipped
    csv or text files or something to that effect)
    * buy external storage (NAS, or even an external USB drive), move the
    database to it
    * use an external data storage service like amazon s3 (actually, this is
    a bad idea since you will need to completely rewrite your database and
    application)
    * decide that you really don't need some of the data and just delete it.
  • Andreas Kretschmer at Jul 26, 2010 at 12:02 pm

    In response to Ivan Voras :
    * buy external storage (NAS, or even an external USB drive), move the
    database to it
    buy external USB-Drive, and create a new tablespace, and move some large
    table into this new tablespace and/or use the new tablespace for new
    tables. You can also use table-partitioning with different tablespaces.


    Andreas
    --
    Andreas Kretschmer
    Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
    GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
  • Vincenzo Romano at Jul 26, 2010 at 12:10 pm

    2010/7/26 A. Kretschmer <andreas.kretschmer@schollglas.com>:
    In response to Ivan Voras :
    * buy external storage (NAS, or even an external USB drive), move the
    database to it
    buy external USB-Drive, and create a new tablespace, and move some large
    table into this new tablespace and/or use the new tablespace for new
    tables. You can also use table-partitioning with different tablespaces.
    Table space on a USB drive?
    You must be really sinking for this very option!
    I'd rather move everything else from the crowded partition onto the USB drive,
    as I would suppose that the database (performance and reliability) is
    more important by far ...

    --
    Vincenzo Romano
    NotOrAnd Information Technologies
    NON QVIETIS MARIBVS NAVTA PERITVS
  • Andreas Kretschmer at Jul 26, 2010 at 12:15 pm

    In response to Vincenzo Romano :
    2010/7/26 A. Kretschmer <andreas.kretschmer@schollglas.com>:
    In response to Ivan Voras :
    * buy external storage (NAS, or even an external USB drive), move the
    database to it
    buy external USB-Drive, and create a new tablespace, and move some large
    table into this new tablespace and/or use the new tablespace for new
    tables. You can also use table-partitioning with different tablespaces.
    Table space on a USB drive?
    You must be really sinking for this very option!
    I'd rather move everything else from the crowded partition onto the USB drive,
    as I would suppose that the database (performance and reliability) is
    more important by far ...
    Maybe, depends.... but why not? Maybe there are some big, but rarely
    used, read-only tables?


    Regards, Andreas
    --
    Andreas Kretschmer
    Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
    GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
  • Vincenzo Romano at Jul 26, 2010 at 12:20 pm

    2010/7/26 A. Kretschmer <andreas.kretschmer@schollglas.com>:
    In response to Vincenzo Romano :
    2010/7/26 A. Kretschmer <andreas.kretschmer@schollglas.com>:
    In response to Ivan Voras :
    * buy external storage (NAS, or even an external USB drive), move the
    database to it
    buy external USB-Drive, and create a new tablespace, and move some large
    table into this new tablespace and/or use the new tablespace for new
    tables. You can also use table-partitioning with different tablespaces.
    Table space on a USB drive?
    You must be really sinking for this very option!
    I'd rather move everything else from the crowded partition onto the USB drive,
    as I would suppose that the database (performance and reliability) is
    more important by far ...
    Maybe, depends.... but why not? Maybe there are some big, but rarely
    used, read-only tables?
    ... or maybe not.
    Better move other stuff away, IMHO.

    --
    Vincenzo Romano
    NotOrAnd Information Technologies
    NON QVIETIS MARIBVS NAVTA PERITVS
  • Bill Thoen at Jul 26, 2010 at 12:13 pm

    A. Kretschmer wrote:
    In response to Ivan Voras :
    * buy external storage (NAS, or even an external USB drive), move the
    database to it
    buy external USB-Drive, and create a new tablespace, and move some large
    table into this new tablespace and/or use the new tablespace for new
    tables. You can also use table-partitioning with different tablespaces.
    Can you then unmount that USB drive without causing any damage to the
    other databases?

    --
    - Bill Thoen
    GISnet - www.gisnet.com
    303-786-9961
  • Andreas Kretschmer at Jul 26, 2010 at 12:22 pm

    In response to Bill Thoen :


    A. Kretschmer wrote:
    In response to Ivan Voras :
    * buy external storage (NAS, or even an external USB drive), move the
    database to it
    buy external USB-Drive, and create a new tablespace, and move some large
    table into this new tablespace and/or use the new tablespace for new
    tables. You can also use table-partitioning with different tablespaces.
    Can you then unmount that USB drive without causing any damage to the
    other databases?
    No!


    Andreas
    --
    Andreas Kretschmer
    Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
    GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
  • Greg Smith at Jul 26, 2010 at 7:15 pm

    A. Kretschmer wrote:
    buy external USB-Drive, and create a new tablespace, and move some large
    table into this new tablespace and/or use the new tablespace for new
    tables. You can also use table-partitioning with different tablespaces.
    There are zero USB drives on the market I'd trust to put a database on.
    That interface was not designed with things like the proper write
    caching controls needed for reliable operation.

    There are some eSATA ones that might be useful for this purpose. Those
    are essentially no different than directly connecting a drive. Note
    that you have to be concerned about redundancy when you start doing this
    sort of thing. External drives are more fragile than internal
    ones--there's a reason why the warranties are usually much shorter.

    As for the original question here, I would look for tables that might
    have lots of dead space on them (located via VACUUM VERBOSE or
    pg_stat_user_tables) and run CLUSTER on them to try and reclaim some
    space, *before* you run out completely. Once space is extremely
    limited, it becomes dramatically more difficult to reclaim it using that
    approach.

    --
    Greg Smith 2ndQuadrant US Baltimore, MD
    PostgreSQL Training, Services and Support
    greg@2ndQuadrant.com www.2ndQuadrant.us
  • Scott Marlowe at Jul 26, 2010 at 7:31 pm

    On Mon, Jul 26, 2010 at 4:43 AM, AlannY wrote:
    Hi there.

    I have a huge database with several tables. Some tables have statistics
    information. And it's very huge. I don't want to loose any of this data.
    But hard drives on my single server are not eternal. Very soon, there will
    be no left space. And the most awful this, that it's a 1U server, and I
    can't install new hard drive.

    What can I do to enlarge space, without loosing data and performance?
    So, what can you do? Can you add external storage or some kind? Get
    access to a network mounted drive, at least temporarily? With the
    restrictions you're listing I'm not sure you have a lot of
    alternatives but to back up some of those tables, restore them
    offsite, then drop the tables on the server.
  • John R Pierce at Jul 26, 2010 at 9:10 pm

    On 07/26/10 3:43 AM, AlannY wrote:
    Hi there.

    I have a huge database with several tables. Some tables have statistics
    information. And it's very huge. I don't want to loose any of this data.
    But hard drives on my single server are not eternal. Very soon, there will
    be no left space. And the most awful this, that it's a 1U server, and I
    can't install new hard drive.

    What can I do to enlarge space, without loosing data and performance?
    put a SAS card in the server, and use something like
    http://h10010.www1.hp.com/wwpc/us/en/sm/WF06b/12169-304616-3930445-3930445-3930445-3954787-3954788-3954790.html

    http://www-03.ibm.com/systems/storage/disk/exp3000/index.html

    http://www.dell.com/us/en/business/storage/sas/ct.aspx?refid=sas&s=bsd&cs=04
    <http://www.dell.com/us/en/business/storage/sas/ct.aspx?refid=sas&s=bsd&cs=04>

    etc etc.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJul 26, '10 at 11:02a
activeJul 26, '10 at 9:10p
posts11
users8
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase