Best approach to database design, in this case?

From: Sanjay Arora <skpobox(at)gawab(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Best approach to database design, in this case?
Date: 2004-06-27 01:26:28
Message-ID: 1088299588.18791.81.camel@Sewak.Asr.Lan
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I am planning a vendor management module, which has to areas. One...A
database located in DMZ, that will have user registration info and
second in Green Segment of the Lan, which will have user information and
correspondence, in addition to user registration.

Now a user that comes in to register may already have a presence in the
main database and may have come to the DMZ database from an invitation
initiated from the main database. The DMZ DB is a subset of the main
database, but contact/product information submitted by user overwrites
that already existing.

Now what should be the process? DMZ cannot contact the Green Database
upon update, due to firewalling. So a periodic replication of records
has to happen triggered by something. Or should I put the entire
database in Green and use a SSH tunnel from the web-server in DMZ? What
are the security approaches to this and their implications?

Also, since the main database is heavily normalized and therefore
querying (from internal processes...users from the net would have low
registration & modification needs) would be much slower, I am planning
along two lines.

- Try to use materialized views.
- Try Single Master-Multi Slave Replication and use something like
pgpooling module announced in the announce mailing list today.

What pg contrib/third party modules may I use and what are their
reliability issues?

Also, how does pg deal with NAS, especially the low cost ones built with
EIDE or SATA drives? Anyone built one from low cost components? Looking
for tips on all issues involved...especially using open source and low
cost hardware as I am highly constrained by budgets.

All/Any tips, warnings, help, URLs, pointers to further reading will be
greatly appreciated.

With best regards.
Sanjay.

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2004-06-27 14:22:28 Re: pg_restore usage
Previous Message Sam Barnett-Cormack 2004-06-26 04:56:40 Re: Continue with the original idea, about JOINS....