Обсуждение: what is the solution like oracle DB's datafile

Hi,

On Sun, Jan 30, 2022 at 05:15:33AM +0700, Yudianto Prasetyo wrote:
> 
> 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 don't know how those datafiles are working, but with postgres the solution is
to create additional tablespaces pointing to the new drives, see
https://www.postgresql.org/docs/current/sql-createtablespace.html.

Note that a single non-partitioned table can only exist on a single tablespace,
so if you have a gigantic table that's becoming bigger than you disk, the
solution might be to partition it and store different partitions on different
tablespaces.

On Sun, Jan 30, 2022 at 08:51:02PM +0700, Yudianto Prasetyo wrote:
> 
> thanks for other solutions in the operating system section. LVM, RAID is
> indeed one solution to this problem.
> 
> Maybe there is another solution in the postgresql database like the
> datafile in oracle DB?

As I said I don't know how datafiles in oracle are working.  All you have on
postgres is tablespaces, default tablespaces and moving relations from one
tablespaces to another.

In general, postgres doesn't try to reimplement solution to problems that are
nicely solved at the operating system level, so if those datafile are
reimplementing something similar to LVM, then no postgres doesn't have
something like that and probably doesn't want it.

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

ne 30. 1. 2022 v 18:13 odesílatel Yudianto Prasetyo
<mr.yudianto@gmail.com> napsal:
>
> Hello,
>
> dafafile this oracle like this example. can be added to another hdd.
>
> ALTER TABLESPACE lmtbsb
>     ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;
>
> ALTER TABLESPACE lmtbsb
>     ADD DATAFILE '/u03/oracle/data/lmtbsb02.dbf' SIZE 1M;
>
> ALTER TABLESPACE lmtbsb
>     ADD DATAFILE 'e:\data\lmtbsb02.dbf' SIZE 1M;
>
> ALTER TABLESPACE lmtbsb
>     ADD DATAFILE 'f:\data2\lmtbsb02.dbf' SIZE 1M;
>
> i understand about that LVM solution. it is true that using this method can be done. I'm just asking for a solution
atthe database level. 

Per my understanding, there is no exactly the same feature in
PostgreSQL itself. As mentioned before, it is most likely by design.

> thank you
> Yours faithfully
>
> yudianto
>
> On Sun, Jan 30, 2022 at 9:17 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
>>
>> On Sun, Jan 30, 2022 at 08:51:02PM +0700, Yudianto Prasetyo wrote:
>> >
>> > thanks for other solutions in the operating system section. LVM, RAID is
>> > indeed one solution to this problem.
>> >
>> > Maybe there is another solution in the postgresql database like the
>> > datafile in oracle DB?
>>
>> As I said I don't know how datafiles in oracle are working.  All you have on
>> postgres is tablespaces, default tablespaces and moving relations from one
>> tablespaces to another.
>>
>> In general, postgres doesn't try to reimplement solution to problems that are
>> nicely solved at the operating system level, so if those datafile are
>> reimplementing something similar to LVM, then no postgres doesn't have
>> something like that and probably doesn't want it.

<laurenz.albe@cybertec.at> wrote:

> - Oracle was developed earlier, and one can argue that in those days file systems
> were not so great, so there was more need to write your own


Correct me if I'm wrong, but didn't Postgres (or Ingres upon which it's based) development start in the early-to-mid
70'sand Oracle's only in the late 70's - early 80's? 

[1] https://en.wikipedia.org/wiki/Ingres_(database)#Early_history
[2] https://en.wikipedia.org/wiki/PostgreSQL#History

E.


> Laurenz Albe