0

Imagine a situation:

  1. I got a Postgres database working on single server on one of HDD partitions (partition size 100GB)
  2. Database grows up to 100GB taking whole free place on the partition
  3. Database stops working because of none free space on HDD

What do I need to do in situation when Postgres database will occupy 100% of partition that the database is on? Can I move the Postgres database into multiple HDD partitions which there will be similar amount of data on all of them?

If it's possible, how to do that?

  • Could you run this at the top of the data directory: du -sh base/* pg_xlog/ and add the results to the question? – Daniel Vérité Oct 24 '14 at 13:09
  • its just a hypothetical situation, I just don't know what to do if this scenario would happen :) – Czachovic Oct 24 '14 at 13:26
  • OK. I'd symlink large directories and tranfer their contents to another partition, with pg_xlog being the first candidate if it's big. Once the db is running again, analyze how it's growing and figure a plan to scale using tablespaces. – Daniel Vérité Oct 24 '14 at 14:15
  • On the begining is it good to move pg_xlog to another HDD partiton? If data directory is too big for HDD space I should symlink it with another empty partition? – Czachovic Oct 24 '14 at 14:21
0

The easy answer is "don't do that".

You can define multiple table-spaces for PostgreSQL to use, and put them on different partitions. But once you've completely run it out of space, that is hard to do. And if you are planning ahead, it would generally be better to just make your partitions larger to start with, using RAID to make them even larger than a single disk.

1

I would go for:

   CREATE TABLESPACE any_name LOCATION '/any_empty_dir';
   ALTER TABLE any_tables SET TABLESPACE any_name;

you can do this for a couple of tables to move them to a different filesystem. it should be pretty easy to do.

you can also put entire databases on to different filesystems.

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.