On 1/29/22 17:15, Yudianto Prasetyo wrote:
Hello,
I want to ask why postgresql doesn't create a datafile like it has oracle?
I'm confused when I have 2 HDD. HDD 1 is used to install the OS and postgresql database. when HDD 1 is full. how to increase the capacity of postgresql database with HDD 2 (without RAID system)?
is there any other way like oracle DB's "add datafile" which can be used to add capacity to another HDD?
I'm sorry if it says comparing with Oracle DB, but in essence I'm just looking for a solution to the problem above.
Thank You
Yours faithfully
Yudianto
Number one, Postgres tables are files. Tablespaces are directories which reside in file systems, In Oracle, tables are sets of blocks within data files that tablespace is comprised of. That is a profound difference. In Oracle, we are using direct IO to bypass the OS cache and only use SGA ("System Global Area") for caching data blocks. In Postgres, we are caching blocks from the files in OS cache which is essentially the free memory.
When translated to Postgres, your question reads: how to add space to file system? That depends on the file system and volume manager. If you're using brtfs or zfs (hopefully not) then your file sysems are also volume managers, If you're using LVM with xfs (my combination), then you can add space to your volume and extend the xfs file system. I have also tried using Oracle ASM as volume manager and use ACFS as the file system. The result was very nice, roughly the same as with LVM. However, the installation of ASM is rather complex and since PostgreSQL cannot be clustered, there is no justification for doing that.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com