Disk Groups/Storage Management for a Large Database in PostgreSQL
Hi, We are building new VMs for PostgreSQL v15 on RHEL 8.x For a large database of 15TB-20TB. I would like to know from the experts that is it a good idea to create LVMs to manage storage for the database? Or are there any other better options/tools for disk groups in PostgreSQL, similar to ASM in Oracle? Thanks Amit
Re: Disk Groups/Storage Management for a Large Database in PostgreSQL
Thanks Olivier and Scot for your inputs! Another data point I would like to share is that VMs will be built in Azure Cloud with Azure Managed Storage and Locally redundant storage (LRS) option with a remote DR as well. LVM or ZFS would still be a good option to allow easy storage/disk management like add, resize or remove disks while PostgreSQL services are up? Is equal data distribution a challenge on LVM/ZFS disks? Thanks Amit On Tue, Jan 23, 2024 at 9:49 AM Scot Kreienkamp < scot.kreienk...@la-z-boy.com> wrote: > El lun, 22 ene 2024 18:44, Amit Sharma escribió: > > Hi, > > > > We are building new VMs for PostgreSQL v15 on RHEL 8.x For a large > database of 15TB-20TB. > > > > I would like to know from the experts that is it a good idea to create > LVMs to manage storage for the database? > > > > Or are there any other better options/tools for disk groups in PostgreSQL, > similar to ASM in Oracle? > > > > Thanks > > Amit > > > > Simple question that requires a somewhat more complex answer. There are > actually 3 metrics to consider: > > > > 1) Capacity > > Your database doesn't fit on a single disk, so you need to distribute your > data across several disks. LVM would indeed be an option (as well as ZFS or > RAID disk arrays) > > > > 2) Safety > > If you loose 1 disk, your data is at risk, as you're likely to loose all > tables partially loaded on that disk. LVM is still an option as long as it > is configured on a RAID array. ZFS can do that natively. > > > > 3) Performance > > Oracle ADM ensures performance by automatically controlling the > distribution of the tables. I would need to see on a real case how it is > actually done. For sure, LVM and ZFS won't have this type of granularity. > > > > On the other hand, you can distribute your data in table partitions to > help this distribution. It is not automatic but will surely help you to > distribute your workload. > > > > > > As he is building VM’s I’m assuming the hardware level has all the > redundancy for RAID/ZFS/etc. If that is the case then you don’t want to > run RAID/ZFS/etc on top of that, let the hardware do its thing. If my > assumption is wrong then ignore everything I’m saying. > > > > One thing I found that helps with speed of reads/writes… you can spread > your read/write load across multiple SCSI controllers/disks using LVM. For > example, I’m assuming VMWare which allows 4 SCSI controllers. Set the OS > disk on SCSI controller 0, then spread your database disks in sets of 3 > across SCSI controllers 1-3, IE 3 disks of 5TB each, one on each SCSI > controller. Then when you create your LVM partition specify the option to > stripe it with 3 stripes. That gives you a setup where you are > multiplexing reads/writes across all 3 SCSI controllers and disks instead > of bottlenecking them all through 1 SCSI controller and disk at a time. > > *Scot Kreienkamp | Applications Infrastructure Architect | La-Z-Boy > Corporate* > One La-Z-Boy Drive | Monroe, Michigan 48162 | ( (734) 384-6403 | | ) > 1-734-915-1444 | * scot.kreienk...@la-z-boy.com > www.la-z-boy.com | facebook.com/lazboy | twitter.com/lazboy | > youtube.com/lazboy > [image: Smaller LZB Only Logo for Sign.png] > > This message is intended only for the individual or entity to which it is > addressed. It may contain privileged, confidential information which is > exempt from disclosure under applicable laws. If you are not the intended > recipient, you are strictly prohibited from disseminating or distributing > this information (other than to the intended recipient) or copying this > information. If you have received this communication in error, please > notify us immediately by e-mail or by telephone at the above number. Thank > you. >
Ora2pg Delta Migration: Oracle to PostgreSQL
Hello, Has anyone tried delta/incremental data migration for Oracle to PostgreSQL using Ora2pg? Or what are the best options to run delta migration for Oracle to PostgreSQL? Thanks Amit
Re: Ora2pg Delta Migration: Oracle to PostgreSQL
Thanks Avinash. Appriciate the help!! Regards Amit On Fri, May 3, 2024 at 5:38 AM Avinash Vallarapu < avinash.vallar...@gmail.com> wrote: > Hi, > > Has anyone tried delta/incremental data migration for Oracle to PostgreSQL >> using Ora2pg? Or what are the best options to run delta migration for >> Oracle to PostgreSQL? > > > There are a few ways of dealing with it when using Ora2Pg. > > *Option 1 : *Ora2Pg configuration file allows us to pass the > conditions(predicates) to be applied to a table while migrating the data > from a table. So, if there is a way to filter the data since the last full > load, you can pass the condition to the configuration file, for each table. > This is some work for sure but it works great if you are able to identify > those conditions such as creation_date or last_updated_date, let's say. > > *Option2 :* Ora2Pg is currently CDC ready. What this means is that, > Ora2Pg can provide the SCN at which the table copy has been initiated. > Technically, we provide the SCN to the CDC tools or custom CDC techniques > to continue replication since that SCN. > > *Option 2.1 :* Leverage Debezium, an Open Source solution to perform > continuous replication from Oracle to PostgreSQL from the SCN produced by > Ora2Pg for that table. > > So, it is all about Ora2Pg + Debezium > > It might be initially challenging, but you will get there. > > Regards, > Avi Vallarapu, > CEO, > HexaCluster Corp. > > > > > > > On Fri, May 3, 2024 at 3:25 AM Muhammad Ikram wrote: > >> Hi, >> >> Not related to Ora2PG but in the past I have used the EDB Migration >> toolkit for such scenarios. Filterprops option can help in specifying >> filter (where clause). >> >> Regards, >> Ikram >> >> >> On Fri, May 3, 2024 at 8:12 AM Ron Johnson >> wrote: >> >>> On Thu, May 2, 2024 at 8:28 PM Amit Sharma wrote: >>> >>>> Hello, >>>> >>>> Has anyone tried delta/incremental data migration for Oracle to >>>> PostgreSQL using Ora2pg? Or what are the best options to run delta >>>> migration for Oracle to PostgreSQL? >>>> >>> >>> What do the ora2pg docs say about whether or not that feature is >>> implemented? (It wasn't when I last used it in 2022.) >>> >>> >> >> >> -- >> Muhammad Ikram >> >> >
