7

I have a database that takes about 60GB in my disk storage. And it doesn't alow to insert into database anymore.
I have read the documentation about tablespaces link But It's like create space for a new table, whereas I want to continue insert into exist table. Could somebody help me out?

In addition, PostgreSQL database maximum is 64TB, so we have to connect many HDDs to achieve 64TB, then the solution could be apply to this situation (many connected HDDs or shared storage, I'm not sure how to say)

Thanks in advance!

PS: I'm working with postgreSQL 9.2 on ubuntu 12.04 LTS

6

PostgreSQL doesn't support spanning a single table over multiple tablespaces directly.

You can use table partitioning to create a "master" table, with child tables on different tablespaces. It's a bit tricky and it means you can't use foreign keys or enforce a proper unique constraint on the primary key, so it's not generally the preferred option.

A more practical option is usually to use a logical volume manager, RAID system, or similar to span multiple physical disks into a single volume, with a single file-system on it. Then PostgreSQL just uses it like anything else and doesn't need to care. On Linux, this might mean LVM, the md software RAID driver in raid0 mode, btrfs, or all sorts of other options. Most (all) of these require you to build a new filesystem then move the PostgreSQL database onto it, you can't convert a regular filesystem that's directly on top of a partition into a raid volume / logical volume / etc.

Note that neither of these options are safe if the additonal storage is removable. If you remove a disk containing a tablespace, or remove part of a logical volume or spanned raid, your entire database will become inacessible. If you lose the disk/volume or its data, the entire DB will be unrecoverable.

| improve this answer | |
  • After googling about postgresql and RAID I found this. I'm new to linux, I'll read more about RAID. Thank @Craig – Jackie Feb 21 '14 at 8:16
0

You are correct, tablespaces only allow moving entire tables to new drives / file systems, not extending existing the space for existing ones.

In theory you could muck around with symlinks to move individual postgresql data files to other drives manually, but that could be problematic (and must be done while postgresql is not running).

| improve this answer | |
  • 3
    Nonono! You really don't want to do that. Pg would be likely to get extremely confused if you later did a VACUUM FULL, CLUSTER or TRUNCATE. In general messing with datadir contents directly is an extremely bad idea unless you know exactly what you are doing. – Craig Ringer Feb 20 '14 at 13:54
-1

You can run vacummn analyze command on the table so that it release the stale memory(not in use or space required by table for rows which are deleted) which can be used to add new row into table.

| improve this answer | |
  • 2
    Stale memory? Where do you buy such? My tables are stored on disk... – dezso Feb 20 '14 at 15:22
  • @dezso Think they meant "dead rows" – Craig Ringer Feb 21 '14 at 8:51
  • @craig Ringer yes that what stale memory is, it is a space occupoed by dead rows – user234918 Feb 24 '14 at 4:56
  • Please remove negative votes if it is useful. – user234918 Feb 25 '14 at 7:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.