Extending PostgreSQL Storage When Disk is Full – How to Guide

postgresqlUbuntu

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

Best Answer

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.

Related Question