1

I have a postgresql database on / root partition (61GB used out of 63GB), so I asked the VPS provider to add more disk (50GB to /mnt/vdb1)

How to add another data directory to that new partition (using symlink or something)?

So that newly created tables does not use /var/lib/postgresql/10/main/base but use /mnt/vdb1 instead. I've been looking but it seems multiple data directory only available on old PostgreSQL 6/7?

2 Answers 2

1

As you noted, you can do this with tablespaces to split the data over multiple drives. But using tablespaces makes things like restoration and migration more difficult. Also, you have to actually move some objects to this space, or it won't accomplish anything. You set it as the default, but that only applies to new objects, it won't automatically spill storage for existing objects to the new tablespace. Any single object can not be split.

Is it really necessary? Of the 63GB main drive, how much of that is used by the database itself versus other things? Rather than using a tablespace, it would be cleaner just to move the entire database to the new drive, assuming it has enough space to hold it all on its own. If most of that 61GB is for this database, then of course that wouldn't work. In that case, to make things easier to manage, I'd probably drop the tablespace, and ask them to create for you a single new drive large enough to hold the entire database single-handedly, then move the whole thing to it.

1
  • yes, i'm planning to make tables based on original gigantic tables.. and move the all rows there.. and yes, about 85% of / is the database..
    – Kokizzu
    Commented Jan 5, 2020 at 21:07
0

Nevermind found it, it's called tablespace now

sudo chown -Rv postgres: /mnt/vdb1/pgdata2;

CREATE TABLESPACE data2 LOCATION '/mnt/vdb1/pgdata2';
SET default_tablespace = data2;
SELECT * FROM pg_tablespace;
2
  • Don't forget to mark your own answer as correct - no points I'm afraid but it might help others with the same problem in future!
    – Vérace
    Commented Jan 5, 2020 at 10:41
  • @Vérace must wait 2 days, which usually i always already forgot .__.)/||
    – Kokizzu
    Commented Jan 5, 2020 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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