Thread: Migrating from Informix to Postgre
Hi Everyone, I am a first timer in this forum. We are currently using Informix and looking for a more economic database solution(combination of Linux and Opensource database). We currently have 5 database servers; 1 running on Sun E450(4 cpu), 2 on Sun E420(4 cpu), 1 on Sun E250 and 1 on Netra T1, with database sizes 30 GB, 12 GB, 12GB, 15GB, 2GB respectively. Databases on E420s are replicated between them. There are at least 10 tables which hold more than 75 million records and fragmented accross different tablespaces. They have indexes and used extensively in DSS type and as well as OLTP type of queries. I went through lot of material in the discussion group but could not find a straight forward answer for the following questions. I would greatly appreciate if someone can share your experience with Postgre. 1. How comfortably Postgre can support these databases( with respect to volume)? 2. How reliable and what kind of replication Postgre provides? Is it log based or transaction based? 3. Does it support table fragmentation accross multiple tablespaces? 4. Does it support raw devices? 5. I have read that it is transactional. But is it heterogeneous? Can I update multiple databases in multiple database servers of different family (like informix, MySQL etc) in a single transaction? 6. How is the performance? Thank you in advance Dinesh
Dinesh Anchan wrote: > Hi Everyone, > > I am a first timer in this forum. > > We are currently using Informix and looking for a more economic database > solution(combination of Linux and Opensource database). We currently have 5 > database servers; 1 running on Sun E450(4 cpu), 2 on Sun E420(4 cpu), 1 on > Sun E250 and 1 on Netra T1, with database sizes 30 GB, 12 GB, 12GB, 15GB, > 2GB respectively. Databases on E420s are replicated between them. There > are at least 10 tables which hold more than 75 million records and > fragmented accross different tablespaces. They have indexes and used > extensively in DSS type and as well as OLTP type of queries. > > I went through lot of material in the discussion group but could not find a > straight forward answer for the following questions. I would greatly > appreciate if someone can share your experience with Postgre. > > 1. How comfortably Postgre can support these databases( with respect to > volume)? No problem. We have Oracle/Informix folks migrating all the time. > 2. How reliable and what kind of replication Postgre provides? Is it log > based or transaction based? Only single-master replication available in /contrib/rserv and /contrib/dbmirror. We are working on multi-master for a later release. We also don't have point-in-time recovery yet, but that will be in 7.4, due out in about 6 months. > 3. Does it support table fragmentation accross multiple tablespaces? No. > 4. Does it support raw devices? No. We feel the performance improvement on raw devices is minimal. > 5. I have read that it is transactional. But is it heterogeneous? Can I > update multiple databases in multiple database servers of different family > (like informix, MySQL etc) in a single transaction? You can only update one database per session connection. We do have schemas in 7,3, though. We are in 7.3 beta now. > 6. How is the performance? Very similary to Informix/Oracle; faster for some things, slower for others. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
"Dinesh Anchan" <dinesh@pinksheets.com> writes: > We are currently using Informix and looking for a more economic database > solution(combination of Linux and Opensource database). We currently have 5 > database servers; 1 running on Sun E450(4 cpu), 2 on Sun E420(4 cpu), 1 on > Sun E250 and 1 on Netra T1, with database sizes 30 GB, 12 GB, 12GB, 15GB, > 2GB respectively. Databases on E420s are replicated between them. There > are at least 10 tables which hold more than 75 million records and > fragmented accross different tablespaces. They have indexes and used > extensively in DSS type and as well as OLTP type of queries. > > I went through lot of material in the discussion group but could not find a > straight forward answer for the following questions. I would greatly > appreciate if someone can share your experience with Postgre. > > 1. How comfortably Postgre can support these databases( with respect to > volume)? No problems there. I think the docs lay out the table and column limits--check the website for details. > 2. How reliable and what kind of replication Postgre provides? Is it log > based or transaction based? Replication is (currently) master-slave only, and implemented using triggers AFAIK. There is a project (Postgres-R) to implement multi-master replication, but it is not done yet. > 3. Does it support table fragmentation accross multiple tablespaces? Current version does not support tablespaces, so no. Tables are split into 1GB segments (to avoid file size limits on some systems) but there is no way to control what data goes in what segments. You can CLUSTER table rows according to a sort criterion, which can improve performance. > 4. Does it support raw devices? No, just regular files. It;s not clear that raw devices would be a win for Postgres (though people have talked about it). > 5. I have read that it is transactional. But is it heterogeneous? Can I > update multiple databases in multiple database servers of different family > (like informix, MySQL etc) in a single transaction? Not without a lot of custom coding and implementing your own two-phase commit protocol. I don't think you could do this with any database without specialized code. > 6. How is the performance? Good in some cases, bad in others, just like any other DB. ;) In general it works well if you take care to maintain good statistics on your data (using ANALYZE) and tweak queries where necessary. Good luck! It sound like you've got a tall order to fill... -Doug
Thank you for your response. I understand that it is not heterogenous. Is it open? Let's say, I am running a transaction in Informix and part of that transaction has to update another database which is running on Postgre. Is this possible? Informix claims that it supports heterogenous transactions as long as other participating database servers are capable of doing so. We were thinking of replacing servers one at a time. Thank you for your advice. D -----Original Message----- From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] Sent: Friday, October 18, 2002 11:51 AM To: Dinesh Anchan Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Migrating from Informix to Postgre Dinesh Anchan wrote: > Hi Everyone, > > I am a first timer in this forum. > > We are currently using Informix and looking for a more economic database > solution(combination of Linux and Opensource database). We currently have 5 > database servers; 1 running on Sun E450(4 cpu), 2 on Sun E420(4 cpu), 1 on > Sun E250 and 1 on Netra T1, with database sizes 30 GB, 12 GB, 12GB, 15GB, > 2GB respectively. Databases on E420s are replicated between them. There > are at least 10 tables which hold more than 75 million records and > fragmented accross different tablespaces. They have indexes and used > extensively in DSS type and as well as OLTP type of queries. > > I went through lot of material in the discussion group but could not find a > straight forward answer for the following questions. I would greatly > appreciate if someone can share your experience with Postgre. > > 1. How comfortably Postgre can support these databases( with respect to > volume)? No problem. We have Oracle/Informix folks migrating all the time. > 2. How reliable and what kind of replication Postgre provides? Is it log > based or transaction based? Only single-master replication available in /contrib/rserv and /contrib/dbmirror. We are working on multi-master for a later release. We also don't have point-in-time recovery yet, but that will be in 7.4, due out in about 6 months. > 3. Does it support table fragmentation accross multiple tablespaces? No. > 4. Does it support raw devices? No. We feel the performance improvement on raw devices is minimal. > 5. I have read that it is transactional. But is it heterogeneous? Can I > update multiple databases in multiple database servers of different family > (like informix, MySQL etc) in a single transaction? You can only update one database per session connection. We do have schemas in 7,3, though. We are in 7.3 beta now. > 6. How is the performance? Very similary to Informix/Oracle; faster for some things, slower for others. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Dinesh Anchan wrote: > Thank you for your response. > > I understand that it is not heterogenous. Is it open? > > Let's say, I am running a transaction in Informix and part of that > transaction has to update another database which is running on Postgre. Is > this possible? Informix claims that it supports heterogenous transactions > as long as other participating database servers are capable of doing so. > > We were thinking of replacing servers one at a time. No, we don't support anything like that. We do support crossdb/server query execution, but those queries are separate events, not rolled back if the transaction aborts. > > Thank you for your advice. > > D > > -----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > Sent: Friday, October 18, 2002 11:51 AM > To: Dinesh Anchan > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Migrating from Informix to Postgre > > > Dinesh Anchan wrote: > > Hi Everyone, > > > > I am a first timer in this forum. > > > > We are currently using Informix and looking for a more economic database > > solution(combination of Linux and Opensource database). We currently have > 5 > > database servers; 1 running on Sun E450(4 cpu), 2 on Sun E420(4 cpu), 1 on > > Sun E250 and 1 on Netra T1, with database sizes 30 GB, 12 GB, 12GB, 15GB, > > 2GB respectively. Databases on E420s are replicated between them. There > > are at least 10 tables which hold more than 75 million records and > > fragmented accross different tablespaces. They have indexes and used > > extensively in DSS type and as well as OLTP type of queries. > > > > I went through lot of material in the discussion group but could not find > a > > straight forward answer for the following questions. I would greatly > > appreciate if someone can share your experience with Postgre. > > > > 1. How comfortably Postgre can support these databases( with respect to > > volume)? > > No problem. We have Oracle/Informix folks migrating all the time. > > > 2. How reliable and what kind of replication Postgre provides? Is it log > > based or transaction based? > > Only single-master replication available in /contrib/rserv and > /contrib/dbmirror. We are working on multi-master for a later release. > > We also don't have point-in-time recovery yet, but that will be in 7.4, > due out in about 6 months. > > > 3. Does it support table fragmentation accross multiple tablespaces? > > No. > > > 4. Does it support raw devices? > > No. We feel the performance improvement on raw devices is minimal. > > > 5. I have read that it is transactional. But is it heterogeneous? Can I > > update multiple databases in multiple database servers of different family > > (like informix, MySQL etc) in a single transaction? > > You can only update one database per session connection. We do have > schemas in 7,3, though. We are in 7.3 beta now. > > > 6. How is the performance? > > Very similary to Informix/Oracle; faster for some things, slower for > others. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> > > 4. Does it support raw devices? > > No. We feel the performance improvement on raw devices is minimal. > But what about data-integrity? I've learned, that a write on a raw device guarantees, that the data is physically on the harddisk, when it returns. When you write into the filesystem you don't even have control about the order, in which writes are physically executed. It would be difficult to guarantee dataintegrity, when the datapages are written before WAL is on the hard-disk. That's why the Informix-people strongly suggests to use raw-devices. What does Postgresql do to guaratee it? Tommi -- Dr. Eckhardt + Partner GmbH http://www.epgmbh.de
All: A couple of quick questions about foreign key constraints. Any good way to view them in pgsql? A simple \dt will show me indexes, but no constraints. I understand that they're implemented with triggers. I've seen varying accounts of how many. Two? Or three? I'm trying to drop some FK constraints, with no luck. Dropping the two likely-looking triggers didn't do it -- still got integrity violations on certain operations. Any good source of data for this online? Did not find much at techdocs.postgresql.org, or at least I looked in the wrong place. Thanks, Steve ======================================================= Steve Lane Vice President The Moyer Group 833 West Chicago Ave Suite 203 Voice: (312) 433-2421 Email: slane@moyergroup.com Fax: (312) 850-3930 Web: http://www.moyergroup.com =======================================================
On Fri, 18 Oct 2002, Dinesh Anchan wrote: > Thank you for your response. > > I understand that it is not heterogenous. Is it open? > > Let's say, I am running a transaction in Informix and part of that > transaction has to update another database which is running on Postgre. Is > this possible? Informix claims that it supports heterogenous transactions > as long as other participating database servers are capable of doing so. You can do that on the application level. Perl i.e. is capable to handle several db-connections. Best regards Herbie
Tommi Maekitalo <t.maekitalo@epgmbh.de> writes: > > > > > 4. Does it support raw devices? > > > > No. We feel the performance improvement on raw devices is minimal. > > > But what about data-integrity? I've learned, that a write on a raw device > guarantees, that the data is physically on the harddisk, when it returns. > When you write into the filesystem you don't even have control about the > order, in which writes are physically executed. > > It would be difficult to guarantee dataintegrity, when the datapages are > written before WAL is on the hard-disk. That's why the Informix-people > strongly suggests to use raw-devices. > > What does Postgresql do to guaratee it? We call fsync() on the WAL files, which guarantees data is written when it returns. -Doug
Tommi Maekitalo <t.maekitalo@epgmbh.de> writes:
> It would be difficult to guarantee dataintegrity, when the datapages are
> written before WAL is on the hard-disk.
Indeed, which is why we never do that. WAL is always fsync'd.
regards, tom lane
On Sat, 19 Oct 2002, Steve Lane wrote: > All: > > A couple of quick questions about foreign key constraints. > > Any good way to view them in pgsql? A simple \dt will show me indexes, but > no constraints. > > I understand that they're implemented with triggers. I've seen varying > accounts of how many. Two? Or three? I'm trying to drop some FK constraints, > with no luck. Dropping the two likely-looking triggers didn't do it -- still > got integrity violations on certain operations. > > Any good source of data for this online? Did not find much at > techdocs.postgresql.org, or at least I looked in the wrong place. The foreign key tutorials at techdocs should go into details, but there are currently three triggers created per constraint, two on the referenced table and one on the referencing table. You'll need to drop all three before the constraint is really gone.