Horton

Thread

  1. Migrating from Informix to Postgre

    Dinesh Anchan <dinesh@pinksheets.com> — 2002-10-18T15:42:07Z

    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

  2. Re: Migrating from Informix to Postgre

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-10-18T15:51:15Z

    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

  3. Re: Migrating from Informix to Postgre

    Doug McNaught <doug@wireboard.com> — 2002-10-18T15:55:36Z

    "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

  4. Re: Migrating from Informix to Postgre

    Dinesh Anchan <dinesh@pinksheets.com> — 2002-10-18T18:38:21Z

    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

  5. Re: Migrating from Informix to Postgre

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-10-18T18:44:46Z

    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

  6. Re: Migrating from Informix to Postgre

    Herbert Liechti <herbie@thinx.ch> — 2002-10-18T20:30:14Z

    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

  7. Working with foreign key constraints

    Steve Lane <slane@moyergroup.com> — 2002-10-19T19:36:13Z

    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
=======================================================

  8. Re: Migrating from Informix to Postgre

    Tommi Maekitalo <t.maekitalo@epgmbh.de> — 2002-10-21T13:03:00Z

    >
> > 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

  9. Re: Migrating from Informix to Postgre

    Doug McNaught <doug@wireboard.com> — 2002-10-21T14:15:09Z

    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

  10. Re: Migrating from Informix to Postgre

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-21T14:21:01Z

    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

  11. Re: Working with foreign key constraints

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-10-21T15:25:22Z

    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.