what is the solution like oracle DB's datafile
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
Re: what is the solution like oracle DB's datafile
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 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. >
Re: what is the solution like oracle DB's datafile
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 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. >
Re: what is the solution like oracle DB's datafile
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 wrote: > ne 30. 1. 2022 v 18:13 odesílatel Yudianto Prasetyo > 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 > 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. >
Re: what is the solution like oracle DB's datafile
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 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 > >
