Re: what is the solution like oracle DB's datafile

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