Thread

1. what is the solution like oracle DB's datafile

   Yudianto Prasetyo <mr.yudianto@gmail.com> — 2022-01-29T22:15:33Z

   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
   

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

   Julien Rouhaud <rjuju123@gmail.com> — 2022-01-30T13:11:11Z

   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.
   
   
   
   

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

   Julien Rouhaud <rjuju123@gmail.com> — 2022-01-30T13:23:53Z

   Hi,
   
   Please keep the list in copy and don't top post here:
   https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics
   
   On Sun, Jan 30, 2022 at 08:18:15PM +0700, Yudianto Prasetyo wrote:
   > 
   > what is the function of adding a new tablespace if we will only fill data
   > in table color and HDD1 is full? can't we fill the data in table color
   > because it still refers to the old tablespace ( small_data on HDD 1 not in
   > the new tablespace on HDD 2 )
   
   You can move some of the tables and/or indexes on the new tablespace to free
   some space on the old one, see e.g.:
   
   - ALTER TABLE ... SET TABLESPACE: https://www.postgresql.org/docs/current/sql-altertable.html
   - ALTER INDEX ... SET TABLESPACE: https://www.postgresql.org/docs/current/sql-alterindex.html
   
   
   
   

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

   Julien Rouhaud <rjuju123@gmail.com> — 2022-01-30T13:43:56Z

   I already asked you once to keep the list in copy.  Don't expect me to reply if
   your next email is still addressed to me only.
   
   On Sun, Jan 30, 2022 at 08:36:50PM +0700, Yudianto Prasetyo wrote:
   > 
   > yes of course it can be done. but it is very inconvenient when the database
   > is still running and most importantly it is not practical when having to
   > move some objects to another tablespace. And this will also happen again
   > when HDD 1 will be full again with data entry by other objects on HDD 1.
   
   Then fix the problem at the operating system level.  On GNU/Linux you can use
   for instance LVM to get more flexibility.  With it you can easily increase your
   logical volume space without any interruption, and the database will only see a
   regular disk that magically became bigger.  There is probably something similar
   on the operating system you're using.  Of course, if you don't already use
   something like that, you will need some maintenance window to move all data on
   logical volumes.
   
   
   
   

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

   Yudianto Prasetyo <mr.yudianto@gmail.com> — 2022-01-30T13:51:02Z

   hello,
   
   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?
   
   thank you,
   Yours faithfully
   
   
   yudianto
   
   On Sun, Jan 30, 2022 at 8:44 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
   
   > I already asked you once to keep the list in copy.  Don't expect me to
   > reply if
   > your next email is still addressed to me only.
   >
   > On Sun, Jan 30, 2022 at 08:36:50PM +0700, Yudianto Prasetyo wrote:
   > >
   > > yes of course it can be done. but it is very inconvenient when the
   > database
   > > is still running and most importantly it is not practical when having to
   > > move some objects to another tablespace. And this will also happen again
   > > when HDD 1 will be full again with data entry by other objects on HDD 1.
   >
   > Then fix the problem at the operating system level.  On GNU/Linux you can
   > use
   > for instance LVM to get more flexibility.  With it you can easily increase
   > your
   > logical volume space without any interruption, and the database will only
   > see a
   > regular disk that magically became bigger.  There is probably something
   > similar
   > on the operating system you're using.  Of course, if you don't already use
   > something like that, you will need some maintenance window to move all
   > data on
   > logical volumes.
   >
   

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

   Julien Rouhaud <rjuju123@gmail.com> — 2022-01-30T14:17:06Z

   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.
   
   
   
   

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

   Yudianto Prasetyo <mr.yudianto@gmail.com> — 2022-01-30T14:24:44Z

   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 at the database level.
   
   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.
   >
   

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

   Mladen Gogala <gogala.mladen@gmail.com> — 2022-01-30T17:03:52Z

   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
   

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

   Josef Šimánek <josef.simanek@gmail.com> — 2022-01-30T17:19:42Z

   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 at the 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.
   
   
   
   

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

    Yudianto Prasetyo <mr.yudianto@gmail.com> — 2022-01-30T22:13:32Z

    hello,
    
    thanks for all the solutions. I don't think there is a solution like Oracle
    DB's datafile in postgresql. LVM is probably the best way if using Linux OS.
    
    Thank You
    Yours faithfully
    
    yudianto
    
    <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
    Virus-free.
    www.avg.com
    <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
    <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
    
    On Mon, Jan 31, 2022 at 12:19 AM Josef Šimánek <josef.simanek@gmail.com>
    wrote:
    
    > 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 at the 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.
    >
    

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

    Laurenz Albe <laurenz.albe@cybertec.at> — 2022-01-31T05:10:27Z

    On Sun, 2022-01-30 at 05:15 +0700, Yudianto Prasetyo wrote:
    > 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?
    
    The difference between Oracle and PostgreSQL here is that Oracle implemented its
    own file system and volume manager, while PostgreSQL uses the facilities provided
    by the operating system.
    
    The correspondence is not perfect, but you can compare an Oracle tablespace to
    a logical volume with a file system and a datafile to a physical volume.
    
    So to get the same thing in PostgreSQL, you have to interact with the operating
    system: add a new physical volume to the logical volume where your database
    resides and extend the file system.
    
    To answer the question "why", here are two points:
    
    - PostgreSQL does not have the (wo)manpower to re-invent the wheel on everything,
      so we tend to use existing facilities
    
    - 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
    
    Yours,
    Laurenz Albe
    -- 
    Cybertec | https://www.cybertec-postgresql.com
    
    
    
    
    

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

    Yudianto Prasetyo <mr.yudianto@gmail.com> — 2022-01-31T22:17:50Z

    hello,
    
    thanks for the logical answer. it is true that there is a very big
    difference between open source and commercial DB. but I'm grateful to be
    able to use postgresql which is quite reliable.
    
    thank you
    Yours faithfully
    
    yudianto
    
    <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
    Virus-free.
    www.avg.com
    <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
    <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
    
    On Mon, Jan 31, 2022 at 12:10 PM Laurenz Albe <laurenz.albe@cybertec.at>
    wrote:
    
    > On Sun, 2022-01-30 at 05:15 +0700, Yudianto Prasetyo wrote:
    > > 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?
    >
    > The difference between Oracle and PostgreSQL here is that Oracle
    > implemented its
    > own file system and volume manager, while PostgreSQL uses the facilities
    > provided
    > by the operating system.
    >
    > The correspondence is not perfect, but you can compare an Oracle
    > tablespace to
    > a logical volume with a file system and a datafile to a physical volume.
    >
    > So to get the same thing in PostgreSQL, you have to interact with the
    > operating
    > system: add a new physical volume to the logical volume where your database
    > resides and extend the file system.
    >
    > To answer the question "why", here are two points:
    >
    > - PostgreSQL does not have the (wo)manpower to re-invent the wheel on
    > everything,
    >   so we tend to use existing facilities
    >
    > - 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
    >
    > Yours,
    > Laurenz Albe
    > --
    > Cybertec | https://www.cybertec-postgresql.com
    >
    >
    

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

    Eagna <eagna@protonmail.com> — 2022-12-27T10:37:33Z

    
    <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's and 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
    
    
    
    
    
    

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

    Guillaume Lelarge <guillaume@lelarge.info> — 2022-12-27T15:43:42Z

    Le mar. 27 déc. 2022 à 11:37, Eagna <eagna@protonmail.com> a écrit :
    
    >
    >
    > <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's and 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
    >
    >
    " The origins of PostgreSQL date back to 1986 as part of the POSTGRES
    project at the University of California at Berkeley and has more than 35
    years of active development on the core platform." (
    https://www.postgresql.org/about/)
    
    
    -- 
    Guillaume.